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.
Best Answer
If you get that message although the
image_id
is present inmcqueen_base_image
, you must be dealing with data corruption.Try to
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.