Two features left
MS-SQLI have been using PostgreSQL on one of my projects since the beginning
of the year now. Before that I used Oracle and . I am very impressed with
the stability, speed, and usefulness PostgreSQL and think the 7.2.3 release
will be grand. PostgreSQL wins out over the other open source DBs because it
has those basic features needed for a fully formed data model such as foreign
keys, transactions, and the speed to go with them. PostgreSQL is on the
verge of winning big against closed source as well. What is standing in the
way, in my opinion, is two features. I came to this conclusion after
thinking about all the previous projects I have been involved with and how
PostgreSQL could be used in place of the closed source DB in 90% of them with
the following:
Read locks for Foreign Key references
SQL exception should not void a transaction
Based on reading the email list for the past 8 months, others have voiced
these issues as well. Some would say that replication and/or failover should
also be on the list. However, I think interaction within the DB is more
important as there is no work around in many cases.
As many of you know, PostgreSQL takes a write lock on a referenced foreign key
record when you update or lock a record in a transaction. This results in a
great many delays and deadlocks on a high volume system that uses foreign
keys. Some would say to just not use foreign keys and make the application
keep things straight. Foreign keys are one of the things that attracts
people to PostgreSQL, why would you want to tell them not to use them. Also,
there are a lot of existing applications out there that would port themselves
to use PostgreSQL but not if they have to re-write the way their software
works. It is also not a safe assumption that the application will be the
only thing accessing the DB. DBAs make mistakes too, and foreign keys often
catch them. I have made inquires into how much it would cost to make this
feature a reality to see if I could get a customer to finance it but have not
received a response.
The other feature is to allow transactions to continue without being forced to
rollback when a SQL exception occurs. In many applications, a SQL exception
is handled and an appropriate alternative generated so the transaction goes
on. PostgreSQL does not support this and errors on every call made in the
same transaction before calling rollback. Some people are willing and able
to adjust there application code to handle this. Many people have long
running transactions where this is not easily accomplished or are using a
pre-existing application that they can't change.
The point of this email is that I would like to be able to profess the joys
and greatness of PostgreSQL to all my customers and whom ever else will
listen. With these features I could do that easily.
Import Notes
Reply to msg id not found: 20021127161606.9E6A3EE015B@relay2.pgsql.comReference msg id not found: 20021127161606.9E6A3EE015B@relay2.pgsql.com
Jon Swinth wrote:
The other feature is to allow transactions to continue without being forced to
rollback when a SQL exception occurs. In many applications, a SQL exception
is handled and an appropriate alternative generated so the transaction goes
on. PostgreSQL does not support this and errors on every call made in the
same transaction before calling rollback. Some people are willing and able
to adjust there application code to handle this. Many people have long
running transactions where this is not easily accomplished or are using a
pre-existing application that they can't change.
I am going to try to add nested transactions in 7.4. Will that help
you?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.
What syntax will be available to the upper transaction to detect a lower
ABORT?
While there be something à la Java ( try catch)?
JLL
Bruce Momjian wrote:
Show quoted text
Jon Swinth wrote:
The other feature is to allow transactions to continue without being forced to
rollback when a SQL exception occurs. In many applications, a SQL exception
is handled and an appropriate alternative generated so the transaction goes
on. PostgreSQL does not support this and errors on every call made in the
same transaction before calling rollback. Some people are willing and able
to adjust there application code to handle this. Many people have long
running transactions where this is not easily accomplished or are using a
pre-existing application that they can't change.I am going to try to add nested transactions in 7.4. Will that help
you?-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Nested transactions may end up getting PostgreSQL there, but it still has to
be internally implemented. Applications expect that when an exception is
thrown then only the SQL call that generated the transaction rolled back.
Any SQL calls in the same transaction successfully completed prior would
still be there waiting for the commit or rollback call. Having nested
transactions might make it easier for the internal DB code to define a save
point prior to each SQL call so that it can rollback to that point if a SQL
exception is thrown.
Show quoted text
On Wednesday 27 November 2002 11:42 am, Bruce Momjian wrote:
Jon Swinth wrote:
The other feature is to allow transactions to continue without being
forced to rollback when a SQL exception occurs. In many applications, a
SQL exception is handled and an appropriate alternative generated so the
transaction goes on. PostgreSQL does not support this and errors on
every call made in the same transaction before calling rollback. Some
people are willing and able to adjust there application code to handle
this. Many people have long running transactions where this is not
easily accomplished or are using a pre-existing application that they
can't change.I am going to try to add nested transactions in 7.4. Will that help
you?
I'm not sure what abort would have to do with what I am talking about. The
point is that a SQL exception should not require rollback on the previous SQL
calls in the same transaction.
Show quoted text
On Wednesday 27 November 2002 11:58 am, Jean-Luc Lachance wrote:
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.What syntax will be available to the upper transaction to detect a lower
ABORT?
While there be something � la Java ( try catch)?JLL
Jean-Luc Lachance wrote:
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?
What syntax will be available to the upper transaction to detect a lower
ABORT?
While there be something ? la Java ( try catch)?
My initial implementation will be simple:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;
and later savepoints which allow you to abort back to a saved spot in your
transaction.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?
I would be inclined to argue against any such thing; if I'm trying to
confine the effects of an error by doing a subtransaction BEGIN, I don't
think I *want* to allow something inside the subtransaction to abort my
outer transaction ...
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?I would be inclined to argue against any such thing; if I'm trying to
confine the effects of an error by doing a subtransaction BEGIN, I don't
think I *want* to allow something inside the subtransaction to abort my
outer transaction ...
Without it, you are required to keep track of your transaction nesting
levels in the application. I don't see a subtransaction aborting the
outer transaction as a problem because once you do ABORT ALL, you are
out of the outer transaction --- at least that's how I assumed it would
work.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Maybe what you are talking about will not help. The question is are you
trying to make nested transactions or savepoints?
Nested transactions would be useful for trying to interrupt a transaction and
have another action happen or not happen on it's own. An example would be
when you want a credit card transaction to generate a log reguardless of
whether the out transaction is commited or rolled back. The problem with
nested transactions is that it is easy to generate deadlocks, especially with
the write locks currently on foreign keys.
What may help is the concept of savepoint (if implemented internally).
Savepoints are usually named and allow rollback to a specific point in the
transaction. There is no issue with deadlock since everything is still in
the same transaction. You then don't have to have something call ABORT, you
simple need to say ROLLBACK TO <savepoint_name>.
BEGIN;
SELECT...
INSERT...
SAVEPOINT a ;
UPDATE...
ROLLBACK TO a ;
DELETE...
COMMIT;
On Wednesday 27 November 2002 12:25 pm, Bruce Momjian wrote:
Jean-Luc Lachance wrote:
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?What syntax will be available to the upper transaction to detect a lower
ABORT?
While there be something ? la Java ( try catch)?My initial implementation will be simple:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;and later savepoints which allow you to abort back to a saved spot in your>
transaction.
My question again is:
How can the upper transaction be aware of an aborted lower transaction?
JLL
Tom Lane wrote:
Show quoted text
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?I would be inclined to argue against any such thing; if I'm trying to
confine the effects of an error by doing a subtransaction BEGIN, I don't
think I *want* to allow something inside the subtransaction to abort my
outer transaction ...regards, tom lane
The upper transaction really doesn't know of the lower sub-transaction's
abort, unless it looks at the result returned by the subtransaction
commit, just as current code checks the commit of a non-subtransaction.
Is that OK?
---------------------------------------------------------------------------
Jean-Luc Lachance wrote:
My question again is:
How can the upper transaction be aware of an aborted lower transaction?
JLL
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?I would be inclined to argue against any such thing; if I'm trying to
confine the effects of an error by doing a subtransaction BEGIN, I don't
think I *want* to allow something inside the subtransaction to abort my
outer transaction ...regards, tom lane
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Jon Swinth wrote:
Maybe what you are talking about will not help. The question is are you
trying to make nested transactions or savepoints?Nested transactions would be useful for trying to interrupt a transaction and
have another action happen or not happen on it's own. An example would be
when you want a credit card transaction to generate a log reguardless of
whether the out transaction is commited or rolled back. The problem with
Not with my implementation:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;
In the above case, the ABORT cancels the UPDATE. If the outer
transaction ABORTS, everything aborts. Even if you commit a
subtransaction, _all_ transactions above it must commit for the
subtransaction to actually commit.
If you want a log entry regardless of the transaction, put it in a
separate transaction.
nested transactions is that it is easy to generate deadlocks, especially with
the write locks currently on foreign keys.
Again, it isn't really any different from a transaction without
subtransactions except certain parts of the entire transaction can be
aborted.
What may help is the concept of savepoint (if implemented internally).
Savepoints are usually named and allow rollback to a specific point in the
transaction. There is no issue with deadlock since everything is still in
the same transaction. You then don't have to have something call ABORT, you
simple need to say ROLLBACK TO <savepoint_name>.BEGIN;
SELECT...
INSERT...
SAVEPOINT a ;
UPDATE...
ROLLBACK TO a ;
DELETE...
COMMIT;
Right. It is no change in functionality to add savepoints because we
can just do a named BEGIN internally as the SAVEPOINT, then do ABORT
back until we match the nesting level of the savepoint.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Ok, so it looks like your nested transactions and savepoints are really the
same thing. The question is, are you going to change the way SQL exceptions
are handled so that simply abort that SQL statement don't require a rollback?
With your enhancement, it sounds like calling BEGIN before each SQL statement
could acheive what I am asking for, but the issue is existing applications
will not expect to have to do so.
Show quoted text
On Wednesday 27 November 2002 01:16 pm, Bruce Momjian wrote:
Jon Swinth wrote:
Maybe what you are talking about will not help. The question is are you
trying to make nested transactions or savepoints?Nested transactions would be useful for trying to interrupt a transaction
and have another action happen or not happen on it's own. An example
would be when you want a credit card transaction to generate a log
reguardless of whether the out transaction is commited or rolled back.
The problem withNot with my implementation:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;In the above case, the ABORT cancels the UPDATE. If the outer
transaction ABORTS, everything aborts. Even if you commit a
subtransaction, _all_ transactions above it must commit for the
subtransaction to actually commit.If you want a log entry regardless of the transaction, put it in a
separate transaction.nested transactions is that it is easy to generate deadlocks, especially
with the write locks currently on foreign keys.Again, it isn't really any different from a transaction without
subtransactions except certain parts of the entire transaction can be
aborted.What may help is the concept of savepoint (if implemented internally).
Savepoints are usually named and allow rollback to a specific point in
the transaction. There is no issue with deadlock since everything is
still in the same transaction. You then don't have to have something
call ABORT, you simple need to say ROLLBACK TO <savepoint_name>.BEGIN;
SELECT...
INSERT...
SAVEPOINT a ;
UPDATE...
ROLLBACK TO a ;
DELETE...
COMMIT;Right. It is no change in functionality to add savepoints because we
can just do a named BEGIN internally as the SAVEPOINT, then do ABORT
back until we match the nesting level of the savepoint.
Jon,
That is all fine and dandy, but aren't function start point candidate
for a rollback to point?
A transaction is currently implicitely started on function call, and we
get into the same problem as with nested transaction when a function
calls another one.
Don't get me wrong, I thing SAVEPOINT and ROLLBACK TO is a great idea,
but nested transaction is needed.
JLL
Jon Swinth wrote:
Show quoted text
Maybe what you are talking about will not help. The question is are you
trying to make nested transactions or savepoints?Nested transactions would be useful for trying to interrupt a transaction and
have another action happen or not happen on it's own. An example would be
when you want a credit card transaction to generate a log reguardless of
whether the out transaction is commited or rolled back. The problem with
nested transactions is that it is easy to generate deadlocks, especially with
the write locks currently on foreign keys.What may help is the concept of savepoint (if implemented internally).
Savepoints are usually named and allow rollback to a specific point in the
transaction. There is no issue with deadlock since everything is still in
the same transaction. You then don't have to have something call ABORT, you
simple need to say ROLLBACK TO <savepoint_name>.BEGIN;
SELECT...
INSERT...
SAVEPOINT a ;
UPDATE...
ROLLBACK TO a ;
DELETE...
COMMIT;On Wednesday 27 November 2002 12:25 pm, Bruce Momjian wrote:
Jean-Luc Lachance wrote:
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?What syntax will be available to the upper transaction to detect a lower
ABORT?
While there be something ? la Java ( try catch)?My initial implementation will be simple:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;and later savepoints which allow you to abort back to a saved spot in your>
transaction.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Fair enough Bruce.
Bruce Momjian wrote:
Show quoted text
The upper transaction really doesn't know of the lower sub-transaction's
abort, unless it looks at the result returned by the subtransaction
commit, just as current code checks the commit of a non-subtransaction.
Is that OK?---------------------------------------------------------------------------
Jean-Luc Lachance wrote:
My question again is:
How can the upper transaction be aware of an aborted lower transaction?
JLL
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?I would be inclined to argue against any such thing; if I'm trying to
confine the effects of an error by doing a subtransaction BEGIN, I don't
think I *want* to allow something inside the subtransaction to abort my
outer transaction ...regards, tom lane
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
I would like to jump in and make another example to make the case clear.
I have a nightly batch load of a 5000 rows or so which I have wrapped
in BEGIN; ... COMMIT; to make it faster. Some of rows generate errors
due to maiformed date column. Under Oracle the whole load
is commited except for the rows that cause errors. I check script
logs and replly ejected rows. Under PostgreSQL,
however, a single error is causing transaction to abort and
fills logs with nasty error messages, one for every remaining
row in batch, telling that transaction is in abort state.
Maybe it is possible to make a session variable so
we can choose the behavior. Something like:
set ON_TRANSACTION_ERROR=CONTINUE
or
set ON_TRANSACTION_ERROR=ABORT
Regards,
Nick
----- Original Message -----
From: "Jon Swinth" <jswinth@atomicpc.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>; "Jean-Luc Lachance"
<jllachan@nsd.ca>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 27, 2002 10:46 PM
Subject: Re: [GENERAL] Two features left
Maybe what you are talking about will not help. The question is are you
trying to make nested transactions or savepoints?Nested transactions would be useful for trying to interrupt a transaction
and
have another action happen or not happen on it's own. An example would be
when you want a credit card transaction to generate a log reguardless of
whether the out transaction is commited or rolled back. The problem with
nested transactions is that it is easy to generate deadlocks, especially
with
the write locks currently on foreign keys.
What may help is the concept of savepoint (if implemented internally).
Savepoints are usually named and allow rollback to a specific point in the
transaction. There is no issue with deadlock since everything is still in
the same transaction. You then don't have to have something call ABORT,
you
simple need to say ROLLBACK TO <savepoint_name>.
BEGIN;
SELECT...
INSERT...
SAVEPOINT a ;
UPDATE...
ROLLBACK TO a ;
DELETE...
COMMIT;On Wednesday 27 November 2002 12:25 pm, Bruce Momjian wrote:
Jean-Luc Lachance wrote:
Bruce,
I assume one will be able to ABORT the current transaction without
aborting the higher transaction and ABORT ALL to abort all if needed.Right. I hadn't planned on ABORT ALL, but it could be done to abort the
entire transaction. Is there any standard on that?What syntax will be available to the upper transaction to detect a
lower
ABORT?
While there be something ? la Java ( try catch)?My initial implementation will be simple:
BEGIN;
SELECT ...
BEGIN;
UPDATE ...
ABORT;
DELETE ...
COMMIT;and later savepoints which allow you to abort back to a saved spot in
your>
Show quoted text
transaction.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Hmm... I'm not quite sure what you mean by function start point. It has been
a while since I did any embeded DB code. Everything now is through a JDBC
interface using standard SQL calls.
It sounds like you are wanting to be able to place a BEGIN statement within a
function call to make sure the calls within the function are in a transaction
and to be able to abort to that point. The issue I see with doing your
nested transactions vs. savepoint is that you would have to invent a way to
mark the end of the sub-transaction without a commit/rollback. Here is an
example:
BEGIN;
UPDATE...
function_a(){
BEGIN
UPDATE
function_b() {
BEGIN
UPDATE
} //end function_b
UPDATE
ABORT
} //end function_a
UPDATE
COMMIT;
How does the system know that the ABORT in the second half of function_a
should rollback to the BEGIN in function_a rather than the BEGIN in
function_b? The other issue I have seen is where you want to overwrite a
point, which you can usually do with a SAVEPOINT structure. This is
especially usefull in a looping structure where you want to be albe to roll
out one loop.
BEGIN;
UPDATE...
function_a(){
UPDATE
SAVEPOINT a ;
LOOP
function_b() {
SAVEPOINT b;
UPDATE
UPDATE
UPDATE
IF error ROLLBACK TO b ;
} //end function_b
UPDATE
SAVEPOINT a ;
END LOOP;
} //end function_a
UPDATE
COMMIT;
In this case the function_b may be something that tries to place something
somewhere and has multiple updates. If one of the updates fails then you
want to be able to rollback to the beginning loop value and let the next
iteration of the loop try out the next location. This type of structure is
especially usefull when there are many simultanious threads going on doing a
simular operation.
Show quoted text
On Wednesday 27 November 2002 01:45 pm, Jean-Luc Lachance wrote:
Jon,
That is all fine and dandy, but aren't function start point candidate
for a rollback to point?
A transaction is currently implicitely started on function call, and we
get into the same problem as with nested transaction when a function
calls another one.Don't get me wrong, I thing SAVEPOINT and ROLLBACK TO is a great idea,
but nested transaction is needed.JLL
Jon Swinth <jswinth@atomicpc.com> writes:
Ok, so it looks like your nested transactions and savepoints are really the
same thing. The question is, are you going to change the way SQL exceptions
are handled so that simply abort that SQL statement don't require a rollback?
With your enhancement, it sounds like calling BEGIN before each SQL statement
could acheive what I am asking for, but the issue is existing applications
will not expect to have to do so.
Au contraire: existing PG applications would be broken completely if the
behavior of error rollback suddenly changes.
There is also an efficiency issue: nested transactions will not be free,
and one should not be forced to pay for them when not needed.
It might be reasonable to have a GUC parameter that enables an implicit
subtransaction around each command in a transaction block (perhaps only
at the topmost nesting level?) --- but it won't become the default
behavior in the foreseeable future.
Note also that Bruce has no expectation of supporting subtransactions
within a function call; that opens a much larger can of worms than what
he's already getting into. So this facility would only be available at
the interactive-command level.
regards, tom lane
Jon,
What I would like to be able to do is within a loop for example, commit
each iteration.
Jon Swinth wrote:
Show quoted text
Hmm... I'm not quite sure what you mean by function start point. It has been
a while since I did any embeded DB code. Everything now is through a JDBC
interface using standard SQL calls.It sounds like you are wanting to be able to place a BEGIN statement within a
function call to make sure the calls within the function are in a transaction
and to be able to abort to that point. The issue I see with doing your
nested transactions vs. savepoint is that you would have to invent a way to
mark the end of the sub-transaction without a commit/rollback. Here is an
example:BEGIN;
UPDATE...
function_a(){
BEGIN
UPDATE
function_b() {
BEGIN
UPDATE
} //end function_b
UPDATE
ABORT
} //end function_a
UPDATE
COMMIT;How does the system know that the ABORT in the second half of function_a
should rollback to the BEGIN in function_a rather than the BEGIN in
function_b? The other issue I have seen is where you want to overwrite a
point, which you can usually do with a SAVEPOINT structure. This is
especially usefull in a looping structure where you want to be albe to roll
out one loop.BEGIN;
UPDATE...
function_a(){
UPDATE
SAVEPOINT a ;
LOOP
function_b() {
SAVEPOINT b;
UPDATE
UPDATE
UPDATE
IF error ROLLBACK TO b ;
} //end function_b
UPDATE
SAVEPOINT a ;
END LOOP;
} //end function_a
UPDATE
COMMIT;In this case the function_b may be something that tries to place something
somewhere and has multiple updates. If one of the updates fails then you
want to be able to rollback to the beginning loop value and let the next
iteration of the loop try out the next location. This type of structure is
especially usefull when there are many simultanious threads going on doing a
simular operation.On Wednesday 27 November 2002 01:45 pm, Jean-Luc Lachance wrote:
Jon,
That is all fine and dandy, but aren't function start point candidate
for a rollback to point?
A transaction is currently implicitely started on function call, and we
get into the same problem as with nested transaction when a function
calls another one.Don't get me wrong, I thing SAVEPOINT and ROLLBACK TO is a great idea,
but nested transaction is needed.JLL
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Jean-Luc Lachance wrote:
Jon,
What I would like to be able to do is within a loop for example, commit
each iteration.
You mean a PL/PgSQL for-loop? I was going to use command-counter to
separate out parts of a transaction for possible rollback, and a
PL/PgSQL for loop does not increment that counter.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
