Re: foreign key locks, 2nd attempt

On Thu, Nov 10, 2011 at 8:17 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> 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
> );

This is a good idea but doesn't do what KEY LOCKS are designed to do so.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services