Re: Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

On 04/13/2014 12:58 PM, Torsten Förtsch wrote:
> Hi,
>
> currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints
> acquires an AccessExclusiveLock on the referencing table.
>
> Why?
>
> If the constraint is in place but not validated (ADD CONSTRAINT ... NOT
> VALID) it already prevents new modifications from violating the constraint.
>
> The code that is called to validate the constraint, RI_Initial_Check,
> contains this comment:
>
>  * We expect that the caller has made provision to prevent any problems
>  * caused by concurrent actions. This could be either by locking rel and
>  * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring
>  * that triggers implementing the checks are already active.
>  * Hence, we do not need to lock individual rows for the check.
>
> Doesn't the presence of the NOT VALID constraint qualify as "otherwise
> ensuring that triggers implementing the checks are already active"?
>
> Is there any deeper reason? Or is it simply not implemented yet?
>


Actually, it is implemented yet.

http://www.postgresql.org/message-id/E1WWovD-0004Ts-66@gemulon.postgresql.org

It'll be in 9.4.

--
Vik