Thread: COMMIT leads to ROLLBACK
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.
"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. On 5/21/06, Jaime Casanova <systemguards@gmail.com> wrote: > 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=# select * from t2; > > ERROR: current transaction is aborted, commands ignored until end of > > transaction block > > test=# commit; > > 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
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, Jaime Casanova <systemguards@gmail.com> wrote: > > there was a thread about that two months ago... > > here's Tom's response: > http://archives.postgresql.org/pgsql-hackers/2006-03/msg00786.php >
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