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:


        * 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))

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.

   id int primary key,
   other int

   id int primary key,
   fk int references pktable deferrable initially deferred

INSERT INTO pktable VALUES (5, 10);


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

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?


Reply via email to