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

Re: foreign key locks, 2nd attempt

From: Vik Reykja <vikreykja(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, 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-25 01:06:49
Message-ID: CALDgxVvr=O60Om6Y58f1s4kb=9UiOgMoAq0wmDkNe26KiaNpaA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Feb 23, 2012 at 19:44, 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.  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.

In response to

Responses

pgsql-hackers by date

Next:From: Noah MischDate: 2012-02-25 02:01:35
Subject: Re: [PATCH] Support for foreign keys with arrays
Previous:From: Maciek SakrejdaDate: 2012-02-25 00:08:34
Subject: Re: Runtime SHAREDIR for testing CREATE EXTENSION

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