Re: Partitioning/inherited tables vs FKs - Mailing list pgsql-hackers

From Nicolas Barbier
Subject Re: Partitioning/inherited tables vs FKs
Date
Msg-id h2hb0f3f5a11005110555v4dc49b55v26e14ddbe4e3d9dd@mail.gmail.com
Whole thread Raw
In response to Re: Partitioning/inherited tables vs FKs  (Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>)
Responses Re: Partitioning/inherited tables vs FKs
Re: Partitioning/inherited tables vs FKs
List pgsql-hackers
2010/5/11 Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>:

> On 2010-05-11 14:29 +0200, Robert Haas wrote:
>
>> On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fozzy@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.
>
> TXA => delete from foo;
> DELETE 1
>
> TXB => select a from foo for share; -- waits
>
> What am I missing?

Slightly verbose example of what can go wrong:

CREATE TABLE a (i int PRIMARY KEY);
INSERT INTO a VALUES (1);

CREATE TABLE b (a_id int);

>>>>>> Start with T1:

T1> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
T1> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Does a with i = 1 exist?i
---1
(1 Zeile)

T1> INSERT INTO b VALUES (1); -- Great, it existed, insert row
pointing to it in b.
INSERT 0 1

>>>>>> Switch to T2:

T2> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Evil
transaction T2 is intervening!
BEGIN
T2> SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE.i
---1
(1 Zeile)

T2> SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got
anything pointing to it.a_id
------
(0 Zeilen)

T2> DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this
blocks, because T1 is still holding the lock).

>>>>>> Switch to T1:

1> COMMIT; -- Commit the insertion of a row pointing to a with i = 1
(this releases all locks that T1 is holding).
COMMIT

>>>>>> T2 continues:

DELETE 1
T2> COMMIT; -- Commit the deletion of a with i = 1.
COMMIT
T2> SELECT * FROM b EXCEPT SELECT * FROM a; -- Check for inconsistencies.a_id
------   1
(1 Zeile)

Woops.

Nicolas


pgsql-hackers by date:

Previous
From: Marko Tiikkaja
Date:
Subject: Re: Partitioning/inherited tables vs FKs
Next
From: Marko Tiikkaja
Date:
Subject: Re: Partitioning/inherited tables vs FKs