Re: foreign key constraint lock behavour in postgresql

I wrote:
> > One idea that occurs to me is that it might be possible to add to PG
> > some tuple lock modes that are intended to cover updates that don't
> > touch indexed columns. So, say:
> >
> > SHARED NONINDEX - conflicts only with EXCLUSIVE locks
> > SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
> > EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
> > must have this level or higher to update tuple.
> > EXCLUSIVE - conflicts with any other lock. must have this to update
> > any indexed column of a tuple.
> >
> > Then a foreign key constraint could take a SHARED NONINDEX lock on the
> > target tuple, because any column that's the target of a foreign key
> > must be indexed; and so we don't care if the nonindexed columns get
> > updated under us. I think. Also, I believe you'd also need to
> > duplicate any SHARED NONINDEX locks for any new versions of the tuple
> > that got created while the lock was held, which might be sticky.
>
> That should work and improve concurrency in PostgreSQL!

Even more if EXCLUSIVE NONINDEX is also used for updates that
change indexed columns where the index is not UNIQUE.

Yours,
Laurenz Albe