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

Torsten Förtsch <torsten.foertsch@xxxxxxx> · Sun, 13 Apr 2014 15:23:51 +0200

On 13/04/14 13:34, Vik Fearing wrote:
> Actually, it is implemented yet.
> 
> http://www.postgresql.org/message-id/E1WWovD-0004Ts-66@xxxxxxxxxxxxxxxxxxxxxx
> 
> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general