6

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?

  • I wrote a script for this, but I'm not able to publish it at this time. I'll check and see if I can get it opened. You should be able to bang a script together to do it by looping over the contents of pg_constraint, but there's some hoop jumping required to deal with multi-column constraints etc. – Craig Ringer Apr 8 '14 at 11:20
  • I found this, that I'm hacking around with currently... blog.hagander.net/archives/… – Niels Kristian Apr 8 '14 at 11:30
  • 1
    Dropping and re-creating constraints is certainly one way to do it - probably quite good enough for the job, too :-) – Craig Ringer Apr 8 '14 at 11:34
  • This script by depesz seems relative: github.com/omniti-labs/pgtreats/blob/master/tools/… – ypercubeᵀᴹ Apr 9 '14 at 12:23
6

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;
$$;
2

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.

1

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;
$$;
  • THIS! If you disable contraints while fiddling with the DB and reenable them afterwards they get not marked as unvalidated automatically. Therefore the original answer to the question in not valid. v9.5 here. – Daniel Oct 28 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.