With some time to spare, I thought I'd submit a quick-fix patch to the
issue I reported here:
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00339.php
This should preclude optimizing away a deferred RI trigger if the
UPDATEd row (in the FK table) was inserted by "current" transaction
(i.e. defined by TransactionIdIsCurrentTransactionId()) and not
necessarily "by our own transaction" as the code currently says.
________________________________________________________________________
backend/commands/trigger.c | 17 !!!!!!!!!!!!!!!!!
test/regress/expected/foreign_key.out | 15 +++++++++++++++
test/regress/sql/foreign_key.sql | 19 +++++++++++++++++++
3 files changed, 34 insertions(+), 17 modifications(!)
________________________________________________________________________
--
Affan Salman
EnterpriseDB Corporation http://www.enterprisedb.com
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /home/affan/repos/cvs/pgsql/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.215
diff -p -c -b -r1.215 trigger.c
*** src/backend/commands/trigger.c 1 Jul 2007 17:45:42 -0000 1.215
--- src/backend/commands/trigger.c 13 Jul 2007 20:13:13 -0000
*************** AfterTriggerSaveEvent(ResultRelInfo *rel
*** 3389,3403 ****
* Update on FK table
*
* There is one exception when updating FK tables: if the
! * updated row was inserted by our own transaction and the
! * FK is deferred, we still need to fire the trigger. This
! * is because our UPDATE will invalidate the INSERT so the
! * end-of-transaction INSERT RI trigger will not do
! * anything, so we have to do the check for the UPDATE
! * anyway.
*/
! if (HeapTupleHeaderGetXmin(oldtup->t_data) !=
! GetCurrentTransactionId() &&
RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup))
{
continue;
--- 3389,3404 ----
* Update on FK table
*
* There is one exception when updating FK tables: if the
! * updated row was inserted by "current" transaction (any
! * active or sub-committed xact from the local transaction
! * tree) and the FK is deferred, we still need to fire the
! * trigger. This is because our UPDATE will invalidate the
! * INSERT so the end-of-transaction INSERT RI trigger will
! * not do anything, so we have to do the check for the
! * UPDATE anyway.
*/
! if (!TransactionIdIsCurrentTransactionId(
! HeapTupleHeaderGetXmin(oldtup->t_data)) &&
RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup))
{
continue;
Index: src/test/regress/sql/foreign_key.sql
===================================================================
RCS file: /home/affan/repos/cvs/pgsql/pgsql/src/test/regress/sql/foreign_key.sql,v
retrieving revision 1.19
diff -p -c -b -r1.19 foreign_key.sql
*** src/test/regress/sql/foreign_key.sql 5 Jun 2007 21:31:09 -0000 1.19
--- src/test/regress/sql/foreign_key.sql 13 Jul 2007 21:22:56 -0000
*************** UPDATE fktable SET id = id + 1;
*** 830,832 ****
--- 830,851 ----
-- should catch error from initial INSERT
COMMIT;
+
+ -- Now test the same UPDATE from a SAVEPOINT to validate that we do
+ -- not optimize away the FK RI trigger when the transaction that
+ -- created the being-UPDATEd row isn't the same as the transaction
+ -- UPDATing the row; but is a "current" transaction.
+
+ BEGIN;
+
+ -- doesn't match PK, but no error yet
+ INSERT INTO fktable VALUES (0, 20);
+
+ -- subxact for this SAVEPOINT will be active now
+ SAVEPOINT updSavept;
+
+ -- don't change FK
+ UPDATE fktable SET id = id + 1;
+
+ -- should catch error from initial INSERT
+ COMMIT;
Index: src/test/regress/expected/foreign_key.out
===================================================================
RCS file: /home/affan/repos/cvs/pgsql/pgsql/src/test/regress/expected/foreign_key.out,v
retrieving revision 1.43
diff -p -c -b -r1.43 foreign_key.out
*** src/test/regress/expected/foreign_key.out 5 Jun 2007 21:31:09 -0000 1.43
--- src/test/regress/expected/foreign_key.out 13 Jul 2007 21:23:12 -0000
*************** UPDATE fktable SET id = id + 1;
*** 1193,1195 ****
--- 1193,1210 ----
COMMIT;
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
DETAIL: Key (fk)=(20) is not present in table "pktable".
+ -- Now test the same UPDATE from a SAVEPOINT to validate that we do
+ -- not optimize away the FK RI trigger when the transaction that
+ -- created the being-UPDATEd row isn't the same as the transaction
+ -- UPDATing the row; but is a "current" transaction.
+ BEGIN;
+ -- doesn't match PK, but no error yet
+ INSERT INTO fktable VALUES (0, 20);
+ -- subxact for this SAVEPOINT will be active now
+ SAVEPOINT updSavept;
+ -- don't change FK
+ UPDATE fktable SET id = id + 1;
+ -- should catch error from initial INSERT
+ COMMIT;
+ ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+ DETAIL: Key (fk)=(20) is not present in table "pktable".
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
