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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers