2010/5/11 Florian Pflug <f...@phlo.org>: > On May 11, 2010, at 13:29 , Robert Haas wrote: >> On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fo...@ac-sw.com> wrote: >>>> The referential integrity triggers contain some extra magic that isn't >>>> easily simulatable in userland, and that is necessary to make the >>>> foreign key constraints airtight. We've discussed this previously but >>>> I don't remember which thread it was or the details of when things >>>> blow up. I think it's something like this: the parent has a tuple >>>> that is not referenced by any child. Transaction 1 begins, deletes >>>> the parent tuple (checking that it has no children), and pauses. >>>> Transaction 2 begins, adds a child tuple that references the parent >>>> tuple (checking that the parent exists, which it does), and commits. >>>> Transaction 1 commits. >>> >>> Will SELECT ... FOR SHARE not help? >> >> Try it, with the example above. I think you'll find that it doesn't. > > That example does in fact work. Here is the precise sequence of commands I > tested with constraint checking triggers implemented in PL/PGSQL. [...] > The serialization error, however, disappears if the two transactions are > swapped. The following sequence of commands succeeds, even though the FK > constraint is not satisfied.
Thanks for figuring this out. I thought there was a case like this but I couldn't remember exactly how to reproduce it. > C1: BEGIN > C1: INSERT INTO child (parent_id) VALUES (0) > C2: BEGIN > C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > C2: SELECT TRUE -- Take snapshot *before* C1 commits > C1: COMMIT > C2: DELETE FROM parent WHERE parent_id = 0 -- Works! > C2: COMMIT > > It seems that while SHARE-locking a concurrently deleted row causes a > serialization error, deleting a concurrently SHARE-locked is allowed. I do > wonder if this shouldn't be considered a bug - whether locks conflict or not > does not usually depend on the other in which they are taken. Wait - I'm confused. The DELETE in your example happens after C1 commits, so C1 can't still be holding any locks (nor does C2 take any locks prior to the commit of C1). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
