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: (view raw or whole thread)
Lists: pgsql-hackers
On Thu, Feb 23, 2012 at 19:44, Kevin Grittner

> 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


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-2015 The PostgreSQL Global Development Group