Re: Fwd: question on foreign key lock

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 <robertmhaas@gmail.com> wrote:
> On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski
> <filip.rembialkowski@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