Re: question: foreign key constraints and AccessExclusive locks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: question: foreign key constraints and AccessExclusive locks
Date: 2013-01-07 02:48:56
Message-ID: 12495.1357526936@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Takeshi Yamamuro 2013-01-07 07:29:58 Improve compression speeds in pg_lzcompress.c
Previous Message Tatsuo Ishii 2013-01-07 02:29:42 Re: too much pgbench init output