From: | Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Fwd: question on foreign key lock |
Date: | 2012-12-05 12:08:18 |
Message-ID: | CAP_rwwnpem7Bd4i9zLFSn+NnAf9jr4ezzSuSEmzYZksrA3QQLQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)gmail(dot)com> wrote:
> On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski
> <filip(dot)rembialkowski(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2012-12-05 13:00:24 | Re: how do I grant select to one user for all tables in a DB? |
Previous Message | ERR ORR | 2012-12-05 11:59:20 | Re: Table with million rows - and PostgreSQL 9.1 is not using the index |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2012-12-05 12:32:01 | Re: Switching timeline over streaming replication |
Previous Message | Kohei KaiGai | 2012-12-05 11:16:30 | Re: Review of Row Level Security |