BUG #15868: Creating foreign key fails to find data key that exists

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

BUG #15868: Creating foreign key fails to find data key that exists

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15868
Logged by:          Kurt Roeckx
Email address:      [hidden email]
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