Re: Fwd: question on foreign key lock

On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski
<filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> maybe this is a better group for this question?
>
> I can't see why creating foreign key on table A referencing table B,
> generates an AccessExclusiveLock on B.
> It seems (to a layman :-) ) that only writes to B should be blocked.
>
> I'm really interested if this is either expected effect or any open TODO
> item or suboptimal behavior of postgres.

This comment explains it:

/*
* Grab an exclusive lock on the pk table, so that someone doesn't delete
* rows out from under us. (Although a lesser lock would do for that
* purpose, we'll need exclusive lock anyway to add triggers to the pk
* table; trying to start with a lesser lock will just create a risk of
* deadlock.)
*/
pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock);

Concurrent DDL is something that's been discussed in detail on this
list in the past; unfortunately, there are some tricky race conditions
are the shared invalidation queue and SnapshotNow that make it hard to
implement properly. I'm hoping to have some time to work on this at
some point, but it hasn't happened yet.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company