Re: question: foreign key constraints and AccessExclusive locks

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> On Sun, Jan 6, 2013 at 4:14 AM, Simon Riggs <simon(at)2ndquadrant(dot)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