How do I validate all my constraints in postgresql?

Question

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html

I now have e.g. duplicate primary keys. What is the best approach on doing a loop of re-checking of all my constraints (pkeys, fkeys) -> fixing issues -> checking again, to make sure that my data is OK?

UPDATE

I decided to go with an approach on fixing the issue by deleting all constraints, and then recreating them using the following method http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html. However I'm currently stuck on an error message, trying to recreate a pkey:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What does this mean, and how do I fix that one (I can live with deleting it, if that's an option)?

Another question: Would my data actually be all good, if I just got rid of any duplicate rows by deleting them, and then did a pg_dump, and restored the database from that dump. It would rebuild the data structure - right?

Answer 1

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema )

do $$
  declare r record;
BEGIN 
FOR r IN  (
  SELECT FORMAT(
    'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc  
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE' 
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
  WHERE  constraint_type = 'FOREIGN KEY' 
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);  
  END LOOP;
END;
$$;

Answer 2

The solution proposed By Evan Carroll was not working for me.

I had to adapt it to mark before all constraints as not valid.

do $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'UPDATE pg_constraint SET convalidated=false WHERE conname = ''%I''; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.constraint_name,
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;

Answer 3

This will work on all constraints safely.,

SELECT FORMAT(
  'ALTER TABLE %I.%I.%I VALIDATE CONSTRAINT %I;',
  current_database(),
  nsp.nspname,
  cls.relname,
  con.conname
)                                         
FROM pg_constraint AS con
JOIN pg_class AS cls
  ON con.conrelid = cls.oid
JOIN pg_namespace AS nsp
  ON cls.relnamespace = nsp.oid
WHERE convalidated IS FALSE
  -- or delete it for all constraints in all schemas
  AND nsp.nspname = 'mySchema';

You can either save that to a file and Q/A it or execute it all at once if using psql with \gexec.

Answer 4

Building on the previous answers here, here's the code which checks FKs and CHECKs, properly handles multiple schemas having same constraint names, and lists all errors without exiting on a first one:

do $$
  declare
    r record;
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN 
raise notice '===== Re-validating constraints =====';
FOR r IN  (
  SELECT FORMAT(
    'UPDATE pg_constraint SET convalidated=false WHERE oid = %s; ALTER TABLE %I.%I VALIDATE CONSTRAINT %I;',
    cc.oid,
    tc.table_schema,
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc  
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_schema = tc.table_schema and t.table_type = 'BASE TABLE' 
  left outer JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name and kcu.table_schema = tc.table_schema and kcu.table_name = tc.table_name 
  left outer JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.table_schema = tc.table_schema and ccu.table_name = tc.table_name 
  join pg_catalog.pg_class c on c.relname = tc.table_name and c.relnamespace::regnamespace::text = tc.table_schema
  join pg_constraint cc on cc.conrelid = c.oid and cc.conname = tc.constraint_name
  WHERE tc.constraint_type in ('FOREIGN KEY', 'CHECK')
    and tc.table_schema in ('schema1', 'schema2')
    and tc.table_name in ('table1', 'table2', 'table3')
)
  loop
    begin
        raise notice 'Running constraint verify: % ...', r.x;
        EXECUTE (r.x);
    exception when others then
        get stacked diagnostics
            v_state   = returned_sqlstate,
            v_msg     = message_text,
            v_detail  = pg_exception_detail,
            v_hint    = pg_exception_hint,
            v_context = pg_exception_context;
    
        raise notice E'Got exception:
            state  : %
            message: %
            detail : %
            hint   : %
            context: %', v_state, v_msg, v_detail, v_hint, v_context;
    
        raise notice E'Got exception:
            SQLSTATE: % 
            SQLERRM: %', SQLSTATE, SQLERRM;
    end;
  END LOOP;
END;
$$;