On 6 January 2013 03:08, Jon Nelson <jnelson+pg...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers