COMMIT leads to ROLLBACK

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "Pgsql Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: COMMIT leads to ROLLBACK
Date: 2006-05-21 16:57:04
Message-ID: 65937bea0605210957p5e5fd6b0y11344273faa1f4e7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-05-21 17:07:22 Re: COMMIT leads to ROLLBACK
Previous Message Martijn van Oosterhout 2006-05-21 11:44:27 Re: Compression and on-disk sorting