Re: Fwd: question on foreign key lock

On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski
<filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> 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.

Sure. You could probably downgrade it quite a bit further without
breaking the regression tests, but that doesn't mean it's safe in all
cases. Rather than having this discussion all over again, I suggest
that you have a look at commits
2dbbda02e7e688311e161a912a0ce00cde9bb6fc,
2c3d9db56d5d49bdc777b174982251c01348e3d8,
a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list
discussions pertaining thereto, particularly the thread "ALTER TABLE
lock strength reduction patch is unsafe" which was started by Tom
Lane.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company