recheck_all_fks.sql

/*

Recheck all the FK constraints to see if they are "still" valids. They
may have been invalid for two reasons:

1) Someone disabled all triggers by mystake
2) A PostgreSQL bug (as one corrected on 9.3.3 - http://www.postgresql.org/docs/9.3/static/release-9-3-3.html)

To run

    $ psql -AXtf recheck_all_fks.sql | psql -AX

The output will be one line per constraint. For valid ones:

    <date>: constraint <fk name> (<referrer table> -> <referred table>): OK

For invalid ones:

    <date>: constraint <fk name> (<referrer table> -> <referred table>): INVALID

*/

SELECT format(
	'SELECT date_trunc(''second'', now())||'': constraint ''||%L||'' (''||%L||'' -> ''||%L||''): '' || CASE WHEN EXISTS(SELECT 1 FROM %s a WHERE %s AND NOT EXISTS(SELECT 1 FROM %s b WHERE %s = %s)) THEN ''INVALID'' ELSE ''OK'' END;',
	c.conname,
	c.conrelid::regclass::text,
	c.confrelid::regclass::text,
	c.conrelid::regclass::text,
	string_agg('a.'||quote_ident(a.attname)||' IS NOT NULL', ' AND '),
	c.confrelid::regclass::text,
	'('||string_agg('a.'||quote_ident(a.attname), ',')||')', '('||string_agg('b.'||quote_ident(af.attname), ',')||')'
) AS verify_sql
FROM pg_constraint c
LEFT JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum =ANY(c.conkey)
LEFT JOIN pg_attribute af ON af.attrelid = c.confrelid AND af.attnum =ANY(c.confkey)
WHERE contype = 'f'
GROUP BY c.conname, c.conrelid, c.confrelid;