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.