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 - 1.215
--- src/backend/commands/trigger.c 13 Jul 2007 20:13:13 -
*** 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 - 1.19
--- src/test/regress/sql/foreign_key.sql 13 Jul 2007 21:22:56 -
*** 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 - 1.43
--- src/test/regress/expected/foreign_key.out 13 Jul 2007 21:23:12 -
*** 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
