Re: COMMIT leads to ROLLBACK

On 5/21/06, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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