Robert, thank you for the answer. 1. "need exclusive lock anyway to add triggers". Why adding a trigger needs exclusive lock? Someone would say blocking reads is not needed (since possible trigger events are: Insert/Update/Delete/Truncate).
2. "will create a risk of deadlock". From user perspective a risk of deadlock is sometimes better than excessive locking. Transactional DDL users should be prepared for exceptions/retries anyway. 3. I made a naive test of simply changing AccessExclusiveLock to ExclusiveLock, and seeing how many regression tests it breaks. It breaks none :-) Current Git head gives me 2 fails/133 tests regardless of this change. regards, Filip On Mon, Nov 12, 2012 at 5:20 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski > <filip.rembialkow...@gmail.com> wrote: >> maybe this is a better group for this question? >> >> I can't see why creating foreign key on table A referencing table B, >> generates an AccessExclusiveLock on B. >> It seems (to a layman :-) ) that only writes to B should be blocked. >> >> I'm really interested if this is either expected effect or any open TODO >> item or suboptimal behavior of postgres. > > This comment explains it: > > /* > * Grab an exclusive lock on the pk table, so that someone doesn't delete > * rows out from under us. (Although a lesser lock would do for that > * purpose, we'll need exclusive lock anyway to add triggers to the pk > * table; trying to start with a lesser lock will just create a risk of > * deadlock.) > */ > pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock); > > Concurrent DDL is something that's been discussed in detail on this > list in the past; unfortunately, there are some tricky race conditions > are the shared invalidation queue and SnapshotNow that make it hard to > implement properly. I'm hoping to have some time to work on this at > some point, but it hasn't happened yet. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers