Thread: question: foreign key constraints and AccessExclusive locks
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. -- Jon
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
On Sun, Jan 6, 2013 at 4:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > 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. I've read and re-read this a few times, and I think I understand. However, could you clarify "you are right that a lower lock is eventually possible" for me, please? -- Jon
Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> On Sun, Jan 6, 2013 at 4:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> 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.
> I've read and re-read this a few times, and I think I understand.
> However, could you clarify "you are right that a lower lock is
> eventually possible" for me, please?
We have some ideas about how to add/drop triggers while locking out only
operations that would actually try to fire the triggers. Right now,
though, any DDL operation done with less than full exclusive lock would
risk having other transactions fetch an inconsistent view of the table's
catalog entries. (This is true for any sort of ALTER TABLE, not just
trigger add/drop.) Simon actually tried to fix this last year, but the
effort crashed and burned, and we're not sure how to get around the
problems. Yet.
regards, tom lane