Prev: [HACKERS] Partitioning/inherited tables vs FKs
Next: LD_LIBRARY_PATH versus rpath
From: Tom Lane on 6 May 2010 12:06
Florian Pflug <fgp(a)phlo.org> writes:
> Anyway, I was wondering why we need guaranteed uniqueness for FK
> relationships anyway.

It's required by spec, and the semantics aren't terribly sensible
without it.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Dmitry Fefelov on 11 May 2010 02:16
> 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?

Regargs,
Dmitry

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 11 May 2010 07:29
On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fozzy(a)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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on 11 May 2010 08:10
On 2010-05-11 14:29 +0200, Robert Haas wrote:
> On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov <fozzy(a)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?


Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Nicolas Barbier on 11 May 2010 08:55
2010/5/11 Marko Tiikkaja <marko.tiikkaja(a)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(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: [HACKERS] Partitioning/inherited tables vs FKs
Next: LD_LIBRARY_PATH versus rpath