Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group