Thread: BUG #15868: Creating foreign key fails to find data key that exists
BUG #15868: Creating foreign key fails to find data key that exists
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15868 Logged by: Kurt Roeckx Email address: kurt@roeckx.be PostgreSQL version: 11.3 Operating system: Debian Description: Hi, I've dumped and and started a restore of a large database (the restore is sitll in progress.) The data was in a 10.8 version, and it's being imported in an 11.3 version. The following 2 queries returned an error: ALTER TABLE ONLY public.certificates ADD CONSTRAINT certificates_raw_certificate_id_fkey FOREIGN KEY (raw_certificate_id) REFERENCES public.raw_certificates(id); ERROR: insert or update on table "certificates" violates foreign key constraint "certificates_raw_certificate_id_fkey" DETAIL: Key (raw_certificate_id)=(162071571) is not present in table "raw_certificates". ALTER TABLE ONLY public.ct_entry ADD CONSTRAINT ct_entry_raw_certificate_id_fkey FOREIGN KEY (raw_certificate_id) REFERENCES public.raw_certificates(id); ERROR: insert or update on table "ct_entry" violates foreign key constraint "ct_entry_raw_certificate_id_fkey" DETAIL: Key (raw_certificate_id)=(521867241) is not present in table "raw_certificates". Both ids (162071571 and 521867241) exist in the raw_certificates table. The id field looks like: Column | Type | Collation | Nullable | Default --------------------+---------+-----------+----------+--------------------------------------------- id | bigint | | not null | nextval('raw_certificate_id_seq'::regclass) It's a rather large table. The data itself is about 2.2 TB, it contains about 2G entries, the index on the id colum is 32 GB. Running the queries above takes a very long time. Kurt