I think I have stumbled across a bug here;
While skipping queuing of an RI trigger for a non-FK UPDATE, the
"non-optimizable exception" check (see below) in trigger.c @
AfterTriggerSaveEvent() fails to handle SAVEPOINTs correctly and can
jovially leave inconsistent data behind at transaction completion:
________________________________________________________________________
case RI_TRIGGER_FK:
/* * Update on FK table * * There is one exception when updating FK tables: if the *
updatedrow 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; } break;
________________________________________________________________________
A slightly modified version of a *well-written* test case in
the "foreign_key" regression test category can exhibit this:
________________________________________________________________________
-- test a tricky case: we can elide firing the FK check trigger during
-- an UPDATE if the UPDATE did not change the foreign key
-- field. However, we can't do this if our transaction was the one that
-- created the updated row and the trigger is deferred, since our UPDATE
-- will have invalidated the original newly-inserted tuple, and therefore
-- cause the on-INSERT RI trigger not to be fired.
CREATE TEMP TABLE pktable ( id int primary key, other int
);
CREATE TEMP TABLE fktable ( id int primary key, fk int references pktable deferrable initially deferred
);
INSERT INTO pktable VALUES (5, 10);
BEGIN;
SAVEPOINT insertSavePoint;
-- doesn't match PK, but no error yet
INSERT INTO fktable VALUES (0, 20);
SAVEPOINT updateSavePoint;
-- don't change FK
UPDATE fktable SET id = id + 1;
-- should catch error from initial INSERT
COMMIT;
________________________________________________________________________
Since the old tuple Xmin was assigned the XID for *insertSavePoint
subxact*; at the UPDATE FK trigger event queuing time it is compared
with the XID for *updateSavePoint subxact* and results in missing a
requisite RI check.
--
Affan Salman
EnterpriseDB Corporation http://www.enterprisedb.com
