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
* 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;
}
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
