From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: rootcause000@gmail.com
Subject: BUG #18089: Orphaned Rows During PostgreSQL Data Migration
Date: Tue, 05 Sep 2023 13:22:58 +0000
Message-ID: <18089-72525e9fc0926c18@postgresql.org> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, rootcause000@gmail.com
Subject: Re: BUG #18089: Orphaned Rows During PostgreSQL Data Migration
In-Reply-To: <18089-72525e9fc0926c18@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox