Re: Partitioning/inherited tables vs FKs

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Marko Tiikkaja" <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, "Nicolas Barbier" <nicolas(dot)barbier(at)gmail(dot)com>
Cc: "Jaime Casanova" <jaime(at)2ndquadrant(dot)com>, "Dmitry Fefelov" <fozzy(at)ac-sw(dot)com>, "Hans-Juergen Schoenig" <hs(at)cybertec(dot)at>, <sandor(at)cybertec(dot)at>,"Boszormenyi Zoltan" <zb(at)cybertec(dot)at>, "Robert Haas" <robertmhaas(at)gmail(dot)com>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Partitioning/inherited tables vs FKs
Date: 2010-05-11 14:07:17
Message-ID: 4BE91E450200002500031507@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> wrote:

>>>>>>> Switch to T1:
>
> 1> COMMIT; -- Commit the insertion...
> COMMIT
>
>>>>>>> T2 continues:
>
> DELETE 1
> T2> COMMIT; -- Commit the deletion of a with i = 1.
> COMMIT
> T2> SELECT * FROM b EXCEPT SELECT * FROM a;
> a_id
> ------
> 1
> (1 Zeile)
>
> Woops.

This is exactly the sort of issue for which true serializable
behavior will provide a solution. I will be offering a patch to
implement that for 9.1 once 9.0 settles down. FWIW when you commit
T1, the patched code rolls back T2 with this message:

T2> DELETE FROM a WHERE i = 1;
ERROR: could not serialize access due to read/write dependencies
among transactions
HINT: The transaction might succeed if retried.

Thanks for the example; I will it to the others.

-Kevin

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2010-05-11 14:10:05 Re: List traffic
Previous Message Magnus Hagander 2010-05-11 14:06:36 Re: List traffic