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: "Vik Reykja" <vikreykja(at)gmail(dot)com>
Cc: "Simon Riggs" <simon(at)2ndquadrant(dot)com>, "Alvaro Herrera" <alvherre(at)commandprompt(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-25 18:06:38
Message-ID: 4F48CECE0200002500045BBF@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackers
Vik Reykja <vikreykja(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>wrote:
> 
>> 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.
> 
> I'm not sure what Florian's patch does, but I've been trying to
> advocate syntax like the following for this exact scenario:
> 
> foreign key (manager_id, true) references person (id, is_manager)
> 
> Basically, allow us to use constants instead of field names as
> part of foreign keys.
 
Interesting.  IMV, a declarative approach like that is almost always
better than the alternatives, so something like this (possibly with
different syntax) would be another step in the right direction.  I
suspect that there will always be a few corner cases where the
business logic required is too esoteric to be handled by a
generalized declarative construct, so I think Florian's idea still
has merit -- especially if we want to ease the transition to
PostgreSQL for large shops using other products.
 
> I have no idea what the implementation aspect of this is,
> but I need the user aspect of it and don't know the best way to
> get it.
 
There are those in the community who make their livings by helping
people get the features they want.  If you have some money to fund
development, I would bet you could get this addressed -- it sure
sounds reasonable to me.
 
-Kevin

In response to

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2012-02-25 18:24:37
Subject: Re: COPY with hints, rebirth
Previous:From: Jean-Baptiste QuenotDate: 2012-02-25 17:03:27
Subject: Re: Fix PL/Python metadata when there is no result

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