Version - PostgreSQL 10.21, compiled by Visual C++ build 1800, 64-bit
Platform - Windows
When migrating data from PostgreSQL 10.21 to 14.7, during the dump process, we encountered an issue with orphaned rows. Upon checking the source database, we confirmed that the table has all the necessary constraints in place. However, it appears that orphaned rows are still present.
GenDataDB=> \d+ nf_intz;
Table "public.nf_intz"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------+-----------+----------+-------------------+----------+--------------+-------------
intz_id | bigint | | not null | | plain | |
Indexes:
"nf_intz_pk" PRIMARY KEY, btree (intz_id)
"nf_intz_fk1_idx" btree (intz_id)
Foreign-key constraints:
"nf_intz_fk1" FOREIGN KEY (intz_id) REFERENCES id_genz(uq_id) ON DELETE CASCADE
GenDataDB=> \d+ id_genz;
Table "public.id_genz"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
uq_id | bigint | | not null | | plain | |
Indexes:
"id_genz_pk" PRIMARY KEY, btree (uq_id)
Referenced by:
TABLE "nf_intz" CONSTRAINT "nf_intz_fk1" FOREIGN KEY (intz_id) REFERENCES id_genz(uq_id) ON DELETE CASCADE
GenDataDB=> select intz_id from nf_intz where intz_id not in (select uq_id from id_genz);
intz_id
-----------
5000001
5000002
5000003
5000004
(4 rows)
GenDataDB=> reindex table nf_intz;
REINDEX
GenDataDB=> reindex table id_genz;
REINDEX
GenDataDB=> select intz_id from nf_intz where intz_id not in (select uq_id from id_genz);
intz_id
-----------
5000001
5000002
5000003
5000004
(4 rows)
GenDataDB=>
For now, we have proceeded with the data migration by adding the missing entries to the parent table. Nevertheless, please advise if there is still a possibility of encountering orphaned rows even when constraints are in place. Is there a way to prevent such occurrences in the future?