Re: complex referential integrity constraints

>-----Original Message-----
>From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
>Sent: donderdag 22 februari 2007 23:15
>To: Joris Dobbelsteen
>Cc: Robert Haas; pgsql-general(at)postgresql(dot)org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, Feb 22, 2007 at 06:51:49PM +0100, Joris Dobbelsteen wrote:
>> >Err, foreign keys are implemented using triggers, so this statement
>> >is self-contradictary.
>>
>> Are you really sure they are executed under the same
>visibility rules?
>
>Reasonably. I have no idea what visibility rules would make
>any difference at all. AIUI a foreign key just takes a shared
>lock on the referenced row and all the magic of MVCC makes
>sure the row exists when the transaction completes.

Try this:
(sorry for any typo's in SQL, if they exist)

CREATE TABLE a (val integer NOT NULL PRIMARY KEY);
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2);
-- we will be doing foreign key ourselves
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (1,1);
INSERT INTO a VALUES (2,2);

-- Now two transaction (T1, T2)
T1: BEGIN ISOLATION LEVEL SERIALIZABLE;
T2: BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Lets see what we have got.
T1: SELECT * FROM a;
T1: SELECT * FROM b;
T2: SELECT * FROM a;
T2: SELECT * FROM b;
-- lets insert something...
T2: INSERT INTO a VALUES (2,100);
-- results in a lock being acquired
T2: SELECT 1 FROM a x WHERE val = 2 FOR SHARE ON x; -- this is your
lock
-- Ok, done for now...
T2: COMMIT; -- now the lock is gone
-- This means T1 doesn't see the row, right?
T1: SELECT * FROM b;
-- now lets delete
T1: DELETE FROM a WHERE val = 2;
-- on cascade delete, thus:
T1: DELETE FROM b WHERE val = 2; -- won't see new tuple
(serializable isolation)
T1: COMMIT;

SELECT * FROM b;
val val2
2 100

Sorry, constraint wasn't enforced ;)
It does matter.

Now try it with this:
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2), FOREIGN KEY val REFERENCES a(val) ON UPDATE CASCADE ON
DELETE CASCADE);
That won't inhibit this behaviour, but proberly enforces the constraint
(as one would have expected). I believe T2 will abort as in the manual.

Your statement might be correct, but it doesn't take enough account of
how the visibility rules under MVCC are played. It seems the foreign
keys (as well as primary keys) have there rules applied differently,
they see that row and will cause an abort.

- Joris