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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
