COMMIT leads to ROLLBACK

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:

Issuing a COMMIT or an END as the last command leads to a
ROLLBACK. Although the behaviour is correct, shouldn't the user be
atleast warned about having issued a wrong command to end the
transaction?

Please see the archives. This has been discussed ad nauseam before,
and no consensus has emerged to change it.

regards, tom lane

On 5/21/06, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:

Hi All,

While trying to implement a recent TODO item, I noticed this behaviour:

test=# drop table t2; drop table t1;
DROP TABLE
DROP TABLE
test=# create table t1(a int primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t1_pkey" for table "t1"
CREATE TABLE
test=# create table t2( a int references t1(a ));
CREATE TABLE
test=# begin;
BEGIN
test=# insert into t2 values( 5 );
ERROR: insert or update on table "t2" violates foreign key constraint
"t2_a_fkey"
DETAIL: Key (a)=(5) is not present in table "t1".
test=# select * from t2;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
test=# commit;
ROLLBACK

Issuing a COMMIT or an END as the last command leads to a
ROLLBACK. Although the behaviour is correct, shouldn't the user be
atleast warned about having issued a wrong command to end the
transaction? An application might believe that everything was OK if it
recieves a SUCCESS for a COMMIT, although the data (INSERT or any
other subsequent DML) never made it to the data-files because the
COMMIT was converted into a ROLLBACK!!!

Either:
1) COMMIT in an aborted transaction should lead to an 'ERROR:
Cannot COMMIT an aborted transaction.'
2) At least a 'WARNING: transaction is being rolled back to last
known consistent state.' should precede the success (ROLLBACK)
message.

Regards,
Gurjeet.

there was a thread about that two months ago...

here's Tom's response:
http://archives.postgresql.org/pgsql-hackers/2006-03/msg00786.php

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

refer: [HACKERS] Commit turns into rollback?
On 3/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

So it's not the fact that it rolls back that bugs you, it's the way that
the action is reported? We could talk about changing that maybe --- it
wouldn't break existing scripts AFAICS. It might break applications
though.

Yes, we should change the message. A silent SUCCESS message leads
the onlooker (script/application) to believe that everything was okay.
As Peter mentioned in the above-mentioned thread:

<snip>
The standard does address the issue of transactions that cannot be committed
because of an error. In 16.6. <commit statement> GR 6 it basically says that
if the transaction cannot be completed (here: because of a constraint
violation), then an exception condition should be raised. That is, the
transaction is over but you get an error.
</snip>

As far as breaking-the-applications goes, I think we'll be
catching the broken applications rather than breaking any well wrtten
application (which detects a DML error as soon as it occurs, and
issues a rollback).

These are a few scenarios after entering an ABORTed transaction state:

1)
=# END;
ROLLBACK

2)
=# ROLLBACK;
ROLLBACK

3)
=# COMMIT;
ERROR: The transaction has been ended with a ROLLBACK.

So, in effect, all these statements do end the ABORTed transaction
with a ROLLBACK. Whereas situations (1) and (2) behave just as
expected, situation (3) needs to be implemented with a
appropriate/correct error message.

Regards,
Gurjeet.

One more question comes to mind. IIRC, Oracle doesn't need you to
rollback the whole transaction if one statement fails (like constarint
violation in this case)!!! Does the standard dictate that an error in
a transaction should force a rollback?

I could be wrong about Oracle; I do not have an Oracle
installation to check, can somebody verify this?

Regards,
Gurjeet.

On 5/21/06, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:

One more question comes to mind. IIRC, Oracle doesn't need you to
rollback the whole transaction if one statement fails (like constarint
violation in this case)!!! Does the standard dictate that an error in
a transaction should force a rollback?

I could be wrong about Oracle; I do not have an Oracle
installation to check, can somebody verify this?

Regards,
Gurjeet.

that is only possible if you use savepoints and we have them in postgres too.
more than that it's beyond spec i think...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook