Thread: COMMIT after an ERROR?
Does an error during a transaction invalidate the transaction? eg BEGIN SomeQueryThatSucceeds SomeQueryThatFails SomeOtherQueryThatSucceeds COMMIT Will the transaction successfully COMMIT, or will the COMMIT fail? Translation: Do I need to monitor for error conditions during a transaction and manually do the ROLLBACK in those cases? -- J C Lawrence ---------(*) Satan, oscillate my metallic sonatas. claw@kanga.nu He lived as a devil, eh? http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.
On Fri, 12 Oct 2001, J C Lawrence wrote: > > Does an error during a transaction invalidate the transaction? > > eg > > BEGIN > SomeQueryThatSucceeds > SomeQueryThatFails > SomeOtherQueryThatSucceeds > COMMIT > > Will the transaction successfully COMMIT, or will the COMMIT fail? > > Translation: Do I need to monitor for error conditions during a > transaction and manually do the ROLLBACK in those cases? > > Hey JC... What will happen is: BEGIN WORK; --> BEGIN SELECT * FROM sometable; --> (select results) INSERT INTO sometable VALUES (blah); --> insert fails, gives error UPDATE sometable SET somefield=somedata WHERE somecondition=somepattern; --> NOTICE: transaction is in error, will not continue If you COMMIT WORK or ABORT WORK, either way it will close the transaction which is in an errored state and automatically rollback the changes. --Nathan
The failed query will abort the transaction, and throw out every command that comes before or after it until your rollback. You don't have to specifically issue a rollback unless you want to issue more commands though. When you disconnect from the DB it will automatically roll back the transaction.
At 05:18 AM 10/12/01, J C Lawrence wrote:
At 05:18 AM 10/12/01, J C Lawrence wrote:
Does an error during a transaction invalidate the transaction?
eg
BEGIN
SomeQueryThatSucceeds
SomeQueryThatFails
SomeOtherQueryThatSucceeds
COMMIT
Will the transaction successfully COMMIT, or will the COMMIT fail?
Translation: Do I need to monitor for error conditions during a
transaction and manually do the ROLLBACK in those cases?
--
J C Lawrence
---------(*) Satan, oscillate my metallic sonatas.
claw@kanga.nu He lived as a devil, eh?
http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Fri, 12 Oct 2001, J C Lawrence wrote: > > Does an error during a transaction invalidate the transaction? > > eg > > BEGIN > SomeQueryThatSucceeds > SomeQueryThatFails > SomeOtherQueryThatSucceeds > COMMIT > > Will the transaction successfully COMMIT, or will the COMMIT fail? > > Translation: Do I need to monitor for error conditions during a > transaction and manually do the ROLLBACK in those cases? The transaction will be forced to roll back.
J C Lawrence <claw@kanga.nu> writes: > Does an error during a transaction invalidate the transaction? Yes, the transaction goes into ABORTED state and nothing after the error will succeed. > Translation: Do I need to monitor for error conditions during a > transaction and manually do the ROLLBACK in those cases? Yup. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
On Fri, 12 Oct 2001 19:35:30 -0700 patrick keshishian <patrick@pioneerdigital.com> wrote: > The only things that are not rolled back are advancing of > sequences and such. I suspect serial types also exhibit similar > behavior to sequences. Ahh, that's actually critical information (which makes sense too BTW). <thinks> Excellent. Then as long as the auto-ROLLBACK on error is a guaranteed ANSI behaviour rather than a PGSQL thing, everything is just perfect. Thanks. -- J C Lawrence ---------(*) Satan, oscillate my metallic sonatas. claw@kanga.nu He lived as a devil, eh? http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.
On Fri, 12 Oct 2001 11:05:21 -0700 (PDT) Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > On Fri, 12 Oct 2001, J C Lawrence wrote: >> Does an error during a transaction invalidate the transaction? >> >> eg >> >> BEGIN >> SomeQueryThatSucceeds >> SomeQueryThatFails >> SomeOtherQueryThatSucceeds >> COMMIT >> >> Will the transaction successfully COMMIT, or will the COMMIT >> fail? > The transaction will be forced to roll back. Thanks. Is this a PostgresQL specific behaviour, or is it defined under ANSI? I've been reading thru the SQL books I have here, and its pretty ambiguous. -- J C Lawrence ---------(*) Satan, oscillate my metallic sonatas. claw@kanga.nu He lived as a devil, eh? http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.
On Sat, 13 Oct 2001, J C Lawrence wrote: > On Fri, 12 Oct 2001 19:35:30 -0700 > patrick keshishian <patrick@pioneerdigital.com> wrote: > > > The only things that are not rolled back are advancing of > > sequences and such. I suspect serial types also exhibit similar > > behavior to sequences. > > Ahh, that's actually critical information (which makes sense too > BTW). <thinks> Excellent. Then as long as the auto-ROLLBACK on > error is a guaranteed ANSI behaviour rather than a PGSQL thing, > everything is just perfect. Thanks. Well, that's difficult. The spec is very hard to understand about that. I believe we determined that our behavior was very very close to complient, but that it does not match the standard reading of that section. IIRC, usually only the statement rolls back unless its a commit that failed (which causes the transaction to roll back entirely), but there's a clause for a class of errors which cause the transaction to rollback entirely which is not defined and postgres treats any error as falling into that category.
The only things that are not rolled back are advancing of sequences and such. I suspect serial types also exhibit similar behavior to sequences. On Fri, Oct 12, 2001 at 04:10:49PM -0300, Charles Tassell wrote: > The failed query will abort the transaction, and throw out every command > that comes before or after it until your rollback. You don't have to > specifically issue a rollback unless you want to issue more commands > though. When you disconnect from the DB it will automatically roll back > the transaction. > > > At 05:18 AM 10/12/01, J C Lawrence wrote: > > >Does an error during a transaction invalidate the transaction? > > > >eg > > > > BEGIN > > SomeQueryThatSucceeds > > SomeQueryThatFails > > SomeOtherQueryThatSucceeds > > COMMIT > > > >Will the transaction successfully COMMIT, or will the COMMIT fail? > > > >Translation: Do I need to monitor for error conditions during a > >transaction and manually do the ROLLBACK in those cases? > > > >-- > >J C Lawrence > >---------(*) Satan, oscillate my metallic sonatas. > >claw@kanga.nu He lived as a devil, eh? > >http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live. > > > >---------------------------(end of broadcast)--------------------------- > >TIP 4: Don't 'kill -9' the postmaster -- patrick keshishian Pioneer Digital Technologies Gnu __ _ -o)/ / (_)__ __ ____ __ /\\ /__/ / _ \/ // /\ \/ / _\_v __/_/_//_/\_,_/ /_/\_\
On Sat, 13 Oct 2001 07:13:25 -0700 (PDT) Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > Well, that's difficult. The spec is very hard to understand about > that. I believe we determined that our behavior was very very > close to complient, but that it does not match the standard > reading of that section. My reading of the spec came out as: The spec in regard to exact behaviour at COMMIT in the presence of errors is, in spec language, "undefined". The current PostgresQL behaviour doesn't violate the spec, but is also one of many possible mutually contradictory behaviours (such as not rolling back on error) that don't appear to violate the spec. Ahh well, its not like inserting explicit ROLLBACKs is that hard. -- J C Lawrence ---------(*) Satan, oscillate my metallic sonatas. claw@kanga.nu He lived as a devil, eh? http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.