Re: foreign key locks, 2nd attempt - Mailing list pgsql-hackers

From David Kerr
Subject Re: foreign key locks, 2nd attempt
Date
Msg-id 20111111173018.GA6219@mr-paradox.net
Whole thread Raw
In response to Re: foreign key locks, 2nd attempt  (Christopher Browne <cbbrowne@gmail.com>)
Responses Re: foreign key locks, 2nd attempt
List pgsql-hackers
On Thu, Nov 10, 2011 at 03:17:59PM -0500, Christopher Browne wrote:
- On Sun, Nov 6, 2011 at 2:28 AM, Jeroen Vermeulen <jtv@xs4all.nl> wrote:
- > On 2011-11-04 01:12, Alvaro Herrera wrote:
- >
- >> I would like some opinions on the ideas on this patch, and on the patch
- >> itself.  If someone wants more discussion on implementation details of
- >> each part of the patch, I'm happy to provide a textual description --
- >> please just ask.
- >
- > Jumping in a bit late here, but thanks for working on this: it looks like it
- > could solve some annoying problems for us.
- >
- > I do find myself idly wondering if those problems couldn't be made to go
- > away more simply given some kind of “I will never ever update this key”
- > constraint.  I'm having trouble picturing the possible lock interactions as
- > it is.  :-)
- 
- +1 on that, though I'd make it more general than that.  There's value
- in having an "immutability" constraint on a column, where, in effect,
- you're not allowed to modify the value of the column, once assigned.
- That certainly doesn't prevent issuing DELETE + INSERT to get whatever
- value you want into place, but that's a big enough hoop to need to
- jump through to get rid of some nonsensical updates.
- 
- And if the target of a foreign key constraint consists of immutable
- columns, then, yes, indeed, UPDATE on that table no longer conflicts
- with references.
- 
- In nearly all cases, I'd expect that SERIAL would be reasonably
- followed by IMMUTABLE.
- 
- create table something_assigned (
-    something_id serial immutable primary key,
-    something_identifier text not null unique
- );

Is this being suggested in lieu of Alvaro's patch? because it seems to be adding
complexity to the system (multiple types of primary key definitions) instead of 
just fixing an obvious problem (over-aggressive locking done on FK checks).

If it's going to be in addition to, then it sounds like it'd be really nice.

Dave


pgsql-hackers by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: Manual anti-wraparound vacuums
Next
From: Tom Lane
Date:
Subject: Re: why do we need two snapshots per query?