Re-adding constraint after ALTER fails on row that exists

Question

I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter:

mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2
mcqueen-#     ADD CONSTRAINT mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b FOREIGN KEY (image_id) REFERENCES public.mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED;
ERROR:  insert or update on table "mcqueen_base_imagemeta2" violates foreign key constraint "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b"
DETAIL:  Key (image_id)=(5648463223) is not present in table "mcqueen_base_image".

I can select that image_id in the both referenced tables and the rows do exist.

I tried changing the PK on the row in mcqueen_base_image to a difference one and back and even deleting and re-adding the row, but neither worked. I finally deleted the row, but then failed on another. Since this table has some 7 billion rows, waiting 2 hours for each failure and deleting that row is not a workable solution.

Is there anything I can do to "repair" this issue?

UPDATE: The root issue ended up being some corruption in our backup restore process, so the test DB was just plain corrupt, so no re-indexing or even reloading of tables helped the issue.

Answer 1

If you get that message although the image_id is present in mcqueen_base_image, you must be dealing with data corruption.

Try to

REINDEX mcqueen_base_image;

and see if the problem persists.

If there are still problems, you'll have to repair the data manually. Then

• dump and restore the database to a new cluster to get rid of any lingering data corruption.

• investigate the cause of the corruption.