Skip site navigation (1) Skip section navigation (2)

Re: foreign key locks, 2nd attempt

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Simon Riggs" <simon(at)2ndquadrant(dot)com>, "Noah Misch" <noah(at)leadboat(dot)com>, "Pg Hackers" <pgsql-hackers(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: foreign key locks, 2nd attempt
Date: 2012-02-23 18:44:50
Message-ID: 4F4634C20200002500045ACE@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackers
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Kevin Grittner's message:
 
>> Since the limitation on what can be stored in xmax was the killer
>> for Florian's attempt to support SELECT FOR UPDATE in a form
>> which was arguably more useful (and certainly more convenient for
>> those converting from other database products), I'm wondering
>> whether anyone has determined whether this new scheme would allow
>> Florian's work to be successfully completed.  The issues seem
>> very similar. If this approach also provides a basis for the
>> other work, I think it helps bolster the argument that this is a
>> good design; if not, I think it suggests that maybe it should be
>> made more general or extensible in some way.  Once this has to be
>> supported by pg_upgrade it will be harder to change the format,
>> if that is needed for some other feature.
> 
> I have no idea what improvements Florian was seeking, but
> multixacts now have plenty of bit flag space to indicate whatever
> we want for each member transaction, so most likely the answer is
> yes.  However we need to make clear that a single SELECT FOR
> UPDATE in a tuple does not currently use a multixact; if we wish
> to always store flags then we are forced to use one which incurs a
> performance hit.
 
Well, his effort really started to go into a tailspin on the related
issues here:
 
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01743.php
 
... with a summary of the problem and possible directions for a
solution here:
 
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01833.php
 
One of the problems that Florian was trying to address is that
people often have a need to enforce something with a lot of
similarity to a foreign key, but with more subtle logic than
declarative foreign keys support.  One example would be the case
Robert has used in some presentations, where the manager column in
each row in a project table must contain the id of a row in a person
table *which has the project_manager boolean column set to TRUE*. 
Short of using the new serializable transaction isolation level in
all related transactions, hand-coding enforcement of this useful
invariant through trigger code (or application code enforced through
some framework) is very tricky.  The change to SELECT FOR UPDATE
that Florian was working on would make it pretty straightforward.
 
-Kevin

In response to

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2012-02-23 18:54:00
Subject: Re: overriding current_timestamp
Previous:From: Simon RiggsDate: 2012-02-23 18:41:21
Subject: Re: foreign key locks, 2nd attempt

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group