Re: foreign key locks

On 2012-11-16 13:17:47 -0300, Alvaro Herrera wrote:
> Noah Misch wrote:
> > On Wed, Nov 14, 2012 at 01:27:26PM -0300, Alvaro Herrera wrote:
> > > https://github.com/alvherre/postgres/commit/df2847e38198e99f57e52490e1e9391ebb70d770
> > >
> > > (I don't think this is worth a v24 submission).
> >
> > Are you aware of any defects in or unanswered questions of this version that
> > would stall your commit thereof?
>
> Yeah, I am revisiting the list of XXX/FIXME comments you pointed out
> previously.
>
> And I would still like someone with EPQ understanding to review the
> ExecLockRows / EvalPlanQual / heap_lock_tuple interactions.

I am in the process of looking at those atm, but we need somebody that
actually understands the intricacies here...

> Andres is on the verge of convincing me that we need to support
> singleton FOR SHARE without multixacts due to performance concerns.

I don't really see any arguments against doing so. We aren't in a that
big shortage of flags and if we need more than available I think we can
free some (e.g. XMAX/XMIN_INVALID).

> It
> would be useful for more people to chime in here: is FOR SHARE an
> important case to cater for? I wonder if using FOR KEY SHARE (keep
> performance characteristics, but would need to revise application code)
> would satisfy Andres' users, for example.

It definitely wouldn't help in the cases I have seen because the point
is to protect against actual content changes of the rows, not just the
keys.
Note that you actually need to use explicit FOR SHARE/UPDATE for
correctness purposes in many scenarios unless youre running in 9.1+
serializable mode. And that cannot be used in some cases (try queuing
for example) because the rollback rates would be excessive.

Even if applications could be fixed, requiring changes to applications
locking behaviour, which possibly is far from trivial, seems like a too
big upgrade barrier.

Greetings,

Andres Freund

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