Re: question: foreign key constraints and AccessExclusive locks

On 6 January 2013 03:08, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> When adding a foreign key constraint on tableA which references
> tableB, why is an AccessExclusive lock on tableB necessary? Wouldn't a
> lock that prevents writes be sufficient, or does PostgreSQL have to
> modify *both* tables in some fashion? I'm using PostgreSQL 8.4 on
> Linux.

FKs are enforced by triggers currently. Adding triggers requires
AccessExclusiveLock because of catalog visibility issues; you are
right that a lower lock is eventually possible.

SQLStandard requires the check to be symmetrical, so adding FKs
requires a trigger on each table and so an AEL is placed on tableB.

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