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