Re: foreign key locks, 2nd attempt

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(at)xs4all(dot)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