Thread: 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.
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: > > 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. 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?
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
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. 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
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 19073
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
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.
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
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 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 19073
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. 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 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.
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: > > 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
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>
> transaction.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Fair enough Bruce. Bruce Momjian wrote: > > 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
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
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:
>
> 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
Tom! Thursday, November 28, 2002, 3:31:18 AM, you wrote: TL> 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. TL> Au contraire: existing PG applications would be broken completely if the TL> behavior of error rollback suddenly changes. TL> There is also an efficiency issue: nested transactions will not be free, TL> and one should not be forced to pay for them when not needed. It seems to me that it is very BAD idea to solve the problem of the original poster (to allow transactions to continue after SQL exception) by the means of nested transactions. It is very simple to implement (i think) it other way - just do not force transaction to enter abort state afer exception. There will be no performance penalty. I think there could be some variable, like that: SET TOLERANT_TRANSACTIONS TO TRUE; which is FALSE by default for compatibility. I did not looked at the code and I am not a C or DB guru, but I suspect, there is just a simple check: did last statement failed? if so, enter abort state. It requires just another check of TOLERANT_TRANSACTIONS variable, and if it is true, just notiy app and continue to work as nothing has happened... why to boother with nested transactions for this simple feature? TL> It might be reasonable to have a GUC parameter that enables an implicit TL> subtransaction around each command in a transaction block (perhaps only TL> at the topmost nesting level?) --- but it won't become the default TL> behavior in the foreseeable future. this is not required if the desired feature will be implemented "naturally" :) Sincerely yours, Timur.
"Timur V. Irmatov" <itvthor@sdf.lonestar.org> writes:
> It is very simple to implement (i think) it other way - just do not
> force transaction to enter abort state afer exception.
Better study the backend's error handling before you say that.
regards, tom lane
Tom! Thursday, November 28, 2002, 12:08:39 PM, you wrote: TL> "Timur V. Irmatov" <itvthor@sdf.lonestar.org> writes: >> It is very simple to implement (i think) it other way - just do not >> force transaction to enter abort state afer exception. TL> Better study the backend's error handling before you say that. OK, I'M WRONG, SORRY... but I still insist that using nested transaction to allow transactions to continue after SQL exceptions is not a good idea.. it is like trying to go long round way instead of straight one. Am I alone here with such a thought? Are all happy about this way of solving a problem (or adding a feature) ?? Sincerely yours, Timur.
Hi all!
my $0.02 on this, though I am not an expert in DBs at all.
>TL> "Timur V. Irmatov" <itvthor@sdf.lonestar.org> writes:
>>> It is very simple to implement (i think) it other way - just do not
>>> force transaction to enter abort state afer exception.
>
>TL> Better study the backend's error handling before you say that.
side-note: I never had a look at this code, but if you want to scare off people from changing anything there, because it looks too complicated, it might indicate the need for some refactoring :-)
>but I still insist that using nested transaction to allow
>transactions to continue after SQL exceptions is not a good idea..
What is the whole point of having a nested transaction vs. a single transaction?
IMHO, if you want to abort all outer transactions when an inner transaction fails this behaviour would be no different from having only one transaction for the whole action.
As already stated the outer transactions can check on the return status of an inner transaction and decide on, what needs to be done in cause of its failure.
Jan
Jan! Thursday, November 28, 2002, 4:24:45 PM, you wrote: >>TL> "Timur V. Irmatov" <itvthor@sdf.lonestar.org> writes: >>>> It is very simple to implement (i think) it other way - just do not >>>> force transaction to enter abort state afer exception. >> >>TL> Better study the backend's error handling before you say that. JW> side-note: I never had a look at this code, but if you want to scare JW> off people from changing anything there, because it looks too JW> complicated, it might indicate the need for some refactoring :-) >>but I still insist that using nested transaction to allow >>transactions to continue after SQL exceptions is not a good idea.. JW> What is the whole point of having a nested transaction vs. a single JW> transaction? I am not argueing against nested transactions. I'm just trying to say that there should be more natural way of allowing transactions to continue other than wrapping each command in separate sub-transaction.. JW> IMHO, if you want to abort all outer transactions when an inner JW> transaction fails this behaviour would be no different from having JW> only one transaction for the whole action. Read what Jon Swinth wrote: --- begin quote --- 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. --- end quote --- Sincerely yours, Timur.
Add - cursor out of a transaction - distributed database (two phase commit) - replication regards Haris Peco On Wednesday 27 November 2002 05:13 pm, Jon Swinth wrote: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> From: Timur Irmatov <itvthor@sdf.lonestar.org> > I am not argueing against nested transactions. > I'm just trying to say that there should be more natural way of > allowing transactions to continue other than wrapping each command in > separate sub-transaction.. Timur, I think what you are failing to grasp is that each SQL call in a transaction does multiple things: update records, tuples, indexes, lock other records, etc. The reason that allowing the transaction to go on is not simple is because there currently isn't a way to keep track of these things other than against the transaction itself (from what I gather). Nested transactions may help with this only because the mechinisim could be used internally to mark the beginning of each SQL call. I have no idea what the difference in difficulty is between nested transaction and save point. I will say that nested transaction seems to be complex to understand and difficult for people to agree on how it should work. Complexity means that people are not going to use it correctly and/or report bugs that are not really bugs. I also think there will be endless debate. If the nested transaction functionality being invisioned is not really a seperate transaction within a transaction but rather a marking of a another begin point and the ability to abort back to that point then this is exactly the same thing as save point. This being the case then maybe you shouldn't call it nested transaction because it doesn't really fit the definition of a "transaction". One of the rules of SQL is that two transactions can't both have a write lock on the same record. If a child transaction is allowed to lock and modify the same record that the parent transaction has locked, which I think you would want, then the child transaction really isn't a "transaction" is it? Save point on the other hand is a very clear concept to understand and I think allows you to accomplish the same thing you are trying to do. It requires that changes are tracked in linear order so that you can reverse them to the save point. This is not all that simple since a lock issed before and after the save point on the same record needs to be maintained when rolling back to the save point. I don't know how this compares to the way PostgreSQL trackes changes in a transaction now. Keep in mind that I am really after the ability to catch a SQL exception and not void the entire transaction. Any way we can get that will work for me provided that the speed of the DB isn't cut in half. I am just further offering the opinion that save point may be a better overall solution for various issues faced. It is very possible that the Oracle version of save point came about because the work done to ensure that SQL exceptions did not kill the previously successful operations in the same transaction.
Yes Bruce, I was talking of a PL/PgSQL FOR <cursor> LOOP. I have a 15M row table that I have to scan to update from 3 tables as part of a routing process. It would be nice if I could commit a bunch of rows to reduce the memory and disk requirements, not to mention locks! Right now, updating with a LOOP takes as much resources as with a JOIN. JLL Bruce Momjian wrote: > > 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
I keep reading discussion about the ablility to ABORT part of a transaction, but what I was really looking for is the ability to COMMIT part of a transaction. Actually the word transaction is missleading. By definition, a transaction should be atomic ie commit all or non. My beef is that a transaction is automatically started once you call a function. As someone mentioned earlier, we (I) need cursor out of transaction. Better yet, I need the ability to start and end a transaction anywhere in my code. JLL Jon Swinth wrote: > > > From: Timur Irmatov <itvthor@sdf.lonestar.org> > > I am not argueing against nested transactions. > > I'm just trying to say that there should be more natural way of > > allowing transactions to continue other than wrapping each command in > > separate sub-transaction.. > > Timur, I think what you are failing to grasp is that each SQL call in a > transaction does multiple things: update records, tuples, indexes, lock other > records, etc. The reason that allowing the transaction to go on is not > simple is because there currently isn't a way to keep track of these things > other than against the transaction itself (from what I gather). Nested > transactions may help with this only because the mechinisim could be used > internally to mark the beginning of each SQL call. > > I have no idea what the difference in difficulty is between nested transaction > and save point. I will say that nested transaction seems to be complex to > understand and difficult for people to agree on how it should work. > Complexity means that people are not going to use it correctly and/or report > bugs that are not really bugs. I also think there will be endless debate. > > If the nested transaction functionality being invisioned is not really a > seperate transaction within a transaction but rather a marking of a another > begin point and the ability to abort back to that point then this is exactly > the same thing as save point. This being the case then maybe you shouldn't > call it nested transaction because it doesn't really fit the definition of a > "transaction". One of the rules of SQL is that two transactions can't both > have a write lock on the same record. If a child transaction is allowed to > lock and modify the same record that the parent transaction has locked, which > I think you would want, then the child transaction really isn't a > "transaction" is it? > > Save point on the other hand is a very clear concept to understand and I think > allows you to accomplish the same thing you are trying to do. It requires > that changes are tracked in linear order so that you can reverse them to the > save point. This is not all that simple since a lock issed before and after > the save point on the same record needs to be maintained when rolling back to > the save point. I don't know how this compares to the way PostgreSQL trackes > changes in a transaction now. > > Keep in mind that I am really after the ability to catch a SQL exception and > not void the entire transaction. Any way we can get that will work for me > provided that the speed of the DB isn't cut in half. I am just further > offering the opinion that save point may be a better overall solution for > various issues faced. It is very possible that the Oracle version of save > point came about because the work done to ensure that SQL exceptions did not > kill the previously successful operations in the same transaction. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I am not planning to allow subtransactions inside functions in my first implementation, though it may be possible in the future. --------------------------------------------------------------------------- Jean-Luc Lachance wrote: > Yes Bruce, > > I was talking of a PL/PgSQL FOR <cursor> LOOP. > > I have a 15M row table that I have to scan to update from 3 tables as > part of a routing process. > It would be nice if I could commit a bunch of rows to reduce the memory > and disk requirements, not to mention locks! > > Right now, updating with a LOOP takes as much resources as with a JOIN. > > JLL > > > Bruce Momjian wrote: > > > > 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 > -- 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
I totally agree. As I understand it, every function being executed is either inside an explicit or implicit transaction. What I would like is the ability to commit/rollback within a function (that would also implicitly start another transaction). This would have the following benefits: 1. If a large amount of processing is going on before an exception occurs, only the stuff since the last commit will be lost. 2. It reduces load on your rollback segments (sorry, oracle speak here). 3. It paves the way for exception handling within your code. Exception handling is probably the biggest thing that I need from a programming language that plpgsql does not have. With exception handling, you can rollback the error, but log (and commit) information about the error to an error log table. This is enormously helpful for troubleshooting purposes when such errors occur. Exceptions are also awesome because it enables you to write code that fails gracefully rather than having to anticipate and code for every possible thing that can go wrong. Cheers, Mark ----- Original Message ----- From: "Jean-Luc Lachance" <jllachan@nsd.ca> To: "Jon Swinth" <jswinth@atomicpc.com> Cc: <pgsql-general@postgresql.org> Sent: Friday, November 29, 2002 4:59 AM Subject: Re: [GENERAL] Two features left > I keep reading discussion about the ablility to ABORT part of a > transaction, but what I was really looking for is the ability to COMMIT > part of a transaction. > > Actually the word transaction is missleading. By definition, a > transaction should be atomic ie commit all or non. > > My beef is that a transaction is automatically started once you call a > function. > > As someone mentioned earlier, we (I) need cursor out of transaction. > Better yet, I need the ability to start and end a transaction anywhere > in my code. > > JLL > > > Jon Swinth wrote: > > > > > From: Timur Irmatov <itvthor@sdf.lonestar.org> > > > I am not argueing against nested transactions. > > > I'm just trying to say that there should be more natural way of > > > allowing transactions to continue other than wrapping each command in > > > separate sub-transaction.. > > > > Timur, I think what you are failing to grasp is that each SQL call in a > > transaction does multiple things: update records, tuples, indexes, lock other > > records, etc. The reason that allowing the transaction to go on is not > > simple is because there currently isn't a way to keep track of these things > > other than against the transaction itself (from what I gather). Nested > > transactions may help with this only because the mechinisim could be used > > internally to mark the beginning of each SQL call. > > > > I have no idea what the difference in difficulty is between nested transaction > > and save point. I will say that nested transaction seems to be complex to > > understand and difficult for people to agree on how it should work. > > Complexity means that people are not going to use it correctly and/or report > > bugs that are not really bugs. I also think there will be endless debate. > > > > If the nested transaction functionality being invisioned is not really a > > seperate transaction within a transaction but rather a marking of a another > > begin point and the ability to abort back to that point then this is exactly > > the same thing as save point. This being the case then maybe you shouldn't > > call it nested transaction because it doesn't really fit the definition of a > > "transaction". One of the rules of SQL is that two transactions can't both > > have a write lock on the same record. If a child transaction is allowed to > > lock and modify the same record that the parent transaction has locked, which > > I think you would want, then the child transaction really isn't a > > "transaction" is it? > > > > Save point on the other hand is a very clear concept to understand and I think > > allows you to accomplish the same thing you are trying to do. It requires > > that changes are tracked in linear order so that you can reverse them to the > > save point. This is not all that simple since a lock issed before and after > > the save point on the same record needs to be maintained when rolling back to > > the save point. I don't know how this compares to the way PostgreSQL trackes > > changes in a transaction now. > > > > Keep in mind that I am really after the ability to catch a SQL exception and > > not void the entire transaction. Any way we can get that will work for me > > provided that the speed of the DB isn't cut in half. I am just further > > offering the opinion that save point may be a better overall solution for > > various issues faced. It is very possible that the Oracle version of save > > point came about because the work done to ensure that SQL exceptions did not > > kill the previously successful operations in the same transaction. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >