FK Deferred RI Triggers & SAVEPOINTs --- A"C"ID violation

From: "Affan Salman" <affan(at)enterprisedb(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: FK Deferred RI Triggers & SAVEPOINTs --- A"C"ID violation
Date: 2007-07-12 18:51:42
Message-ID: 4696783E.30107@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-07-12 19:41:47 Re: 2PC-induced lockup
Previous Message Andrew Sullivan 2007-07-12 18:44:24 Re: 2PC-induced lockup