How to ensure validity of foreign keys in Postgres

Question

Using Postgres 10.6

The issue:

• Some data in my tables violates the foreign key constraints (not sure how). The constraints are ON DELETE CASCADE ON UPDATE CASCADE
• On a pg_dump of the database, those foreign keys are dropped (due to being in an invalid state?)
• A pg_restore is done into a blank database, which no longer has the foreign keys
• The new database has all its primary keys updated to valid keys not used in a second database. Tables which had invalid data do not have their foreign keys updated, due to the now missing constraint.
• A pg_dump of the new database is done, then the database is deleted
• On a pg_restore into a second database which has the foreign key constraints, the data gets imported in an invalid state, and corrupts the new database.

What I want to do is this: Every few hours (or once a day, depending of how long the query would take), is to verify that all data in all the tables which have foreign keys are valid.

I have read about ALTER TABLE ... VALIDATE CONSTRAINT ... but this wouldn't fix my issue, as the data is not currently marked as NOT VALID. I know could do statements like:

DELETE FROM a WHERE a.b_id NOT IN ( SELECT b.id )

However, I have 144 tables with foreign keys, so this would be rather tedious. I would also maybe not want to immediately delete the data, but log the issue and inform user about a correction which will happen.

Of course, I'd like to know how the original corruption occurred, and prevent that; however at the moment I'm just trying to prevent it from spreading.

Example table:

CREATE TABLE dependencies (
    ...
    from_task int references tasks(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, 
    to_task int references tasks(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, 
    ...
);

Dependencies will end up with values for to_task and from_task which do not exist in the tasks table (see image)

Note:

• Have tried EXPLAIN ANALYZE nothing odd
• pg_tablespace, has just two records. pg_default and pg_global
• relforcerowsecurity, relispartition are both 'false' on both tables
• Arguments to pg_dump (from c++ call) arguments << "--file=" + fileName << "--username=" + connection.userName() << databaseName << "--format=c"

Answer 1

This is either an index (or table) corruption problem, or the constraint has been created invalid to defer the validity check till later.

pg_dump will never silently "drop" a constraint — perhaps there was an error while restoring the dump that you didn't notice.

The proper fix is to clean up the data that violate the constraint and re-create it.

If it is a data corruption problem, check your hardware.

There is no need to regularly check for data corruption, PostgreSQL is not in the habit of corrupting data by itself.

The best test would be to take a pg_dump regularly and see if restoring the dump causes any errors.