PostgreSQL – How to Validate All Constraints

constraintforeign keypostgresqlpostgresql-9.3primary-key

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?

Best Answer

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;
$$;
Related Question