Re: foreign key locks, 2nd attempt

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
);
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"