COMMIT after an ERROR?

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.

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:

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

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 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 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 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.

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.