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

From Christopher Browne
Subject Re: foreign key locks, 2nd attempt
Date
Msg-id CAFNqd5W41U8sGcOzCPvz3pOay-wYYX9QqQE2KMqht3U0UXk-WA@mail.gmail.com
Whole thread Raw
In response to Re: foreign key locks, 2nd attempt  (Jeroen Vermeulen <jtv@xs4all.nl>)
Responses Re: foreign key locks, 2nd attempt
Re: foreign key locks, 2nd attempt
Re: foreign key locks, 2nd attempt
Re: foreign key locks, 2nd attempt
List pgsql-hackers
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
);
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: type privileges and default privileges
Next
From: Pavel Stehule
Date:
Subject: Re: foreign key locks, 2nd attempt