The following bug has been logged on the website:
Bug reference: 18089
Logged by: Root Cause
Email address: rootcause000@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: Microsoft Windows Server 2019 Standard
Description:
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?