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

On 13/04/14 13:34, Vik Fearing wrote:
> Actually, it is implemented yet.
>
> http://www.postgresql.org/message-id/E1WWovD-0004Ts-66@gemulon.postgresql.org
>
> It'll be in 9.4.

That's good news.

So, I could validate a FK constraint this way:

UPDATE pg_constraint
   SET convalidated = NOT EXISTS(
    SELECT 1
      FROM ONLY fkrel a
      LEFT JOIN ONLY pkrel b
        ON (a.fkcol1=b.pkcol1 AND ...)   -- all fk columns
     WHERE b.pkcol1 IS NULL              -- inner join failed
       AND (a.fkcol1 IS NOT NULL
            OR/AND                       -- MATCH SIMPLE: AND; FULL: OR
            a.fkcol2 IS NOT NUL
            ...)
   )
 WHERE contype='f'
   AND ...

fkrel is confrelid::regclass and pkrel conrelid::regclass.

That's essentially what AT VALIDATE CONSTRAINT does.

Torsten