public inbox for [email protected]
help / color / mirror / Atom feedpg_restore failed on foreign key constraint
2+ messages / 2 participants
[nested] [flat]
* pg_restore failed on foreign key constraint
@ 2026-02-12 21:35 Ron Johnson <[email protected]>
2026-02-13 06:43 ` Re: pg_restore failed on foreign key constraint Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Ron Johnson @ 2026-02-12 21:35 UTC (permalink / raw)
To: pgsql-general
When running pg_restore 17.7 against a PG 14.20 database directory dump, I
got this in the log:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 171927; 2606 3149123877 FK CONSTRAINT
rel_user_email fk_rel_user_email_2 TAP
pg_restore: error: could not execute query: ERROR: insert or update on
table "rel_user_email" violates foreign key constraint "fk_rel_user_email_2"
DETAIL: Key (access_email_id)=(2073) is not present in table
"access_email".
Command was: ALTER TABLE ONLY public.rel_user_email
ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_email_id)
REFERENCES public.access_email(access_email_id);
So, I went to the source database:
TAPd=# \d rel_user_email
Table "public.rel_user_email"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+---------
user_id | integer | | not null |
access_email_id | integer | | not null |
modified_by | integer | | |
modified_on | timestamp without time zone | | not null |
Indexes:
"idx_rel_user_email" UNIQUE, btree (user_id, access_email_id)
Foreign-key constraints:
"fk_rel_user_email_1" FOREIGN KEY (user_id) REFERENCES
access_user(user_id)
"fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES
access_email(access_email_id)
TAPd=# select * from rel_user_email where access_email_id=2073;
user_id | access_email_id | modified_by | modified_on
---------+-----------------+-------------+-------------------------
2452 | 2073 | 41 | 2013-03-11 10:52:20.331
(1 row)
TAPd=# \d access_email
Table "public.access_email"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+-------------------------------------------------------
access_email_id | integer | | not null |
nextval('access_email_access_email_id_seq'::regclass)
type | numeric(10,0) | | |
email_address | character varying(255) | | |
created_on | timestamp without time zone | | not null |
modified_on | timestamp without time zone | | |
created_by | integer | | |
modified_by | integer | | |
Indexes:
"pk_access_email" PRIMARY KEY, btree (access_email_id)
Referenced by:
TABLE "rel_user_email" CONSTRAINT "fk_rel_user_email_2" FOREIGN KEY
(access_email_id) REFERENCES access_email(access_email_id)
TAPd=# select * from access_email where access_email_id=2073;
access_email_id | type | email_address | created_on | modified_on |
created_by | modified_by
-----------------+------+---------------+------------+-------------+------------+-------------
(0 rows)
Looks like index corruption.
$ pg_amcheck -t public.rel_user_email -t public.access_email TAPd
$ echo $?
0
$ pg_amcheck -i idx_rel_user_email -i pk_access_email --parent-check
--heapallindexed TAPd
$ echo $?
0
But amcheck shows no problems.
Before I get worried that there' s corrupt data: am I missing something
obvious?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_restore failed on foreign key constraint
2026-02-12 21:35 pg_restore failed on foreign key constraint Ron Johnson <[email protected]>
@ 2026-02-13 06:43 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2026-02-13 06:43 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On Thu, 2026-02-12 at 16:35 -0500, Ron Johnson wrote:
> When running pg_restore 17.7 against a PG 14.20 database directory dump, I got this in the log:
>
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 171927; 2606 3149123877 FK CONSTRAINT rel_user_email fk_rel_user_email_2 TAP
> pg_restore: error: could not execute query: ERROR: insert or update on table "rel_user_email" violates foreign key constraint "fk_rel_user_email_2"
> DETAIL: Key (access_email_id)=(2073) is not present in table "access_email".
> Command was: ALTER TABLE ONLY public.rel_user_email
> ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_email_id) REFERENCES public.access_email(access_email_id);
>
> So, I went to the source database:
>
> TAPd=# \d rel_user_email
> Table "public.rel_user_email"
> Column | Type | Collation | Nullable | Default
> -----------------+-----------------------------+-----------+----------+---------
> user_id | integer | | not null |
> access_email_id | integer | | not null |
> modified_by | integer | | |
> modified_on | timestamp without time zone | | not null |
> Indexes:
> "idx_rel_user_email" UNIQUE, btree (user_id, access_email_id)
> Foreign-key constraints:
> "fk_rel_user_email_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
> "fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES access_email(access_email_id)
>
> TAPd=# select * from rel_user_email where access_email_id=2073;
> user_id | access_email_id | modified_by | modified_on
> ---------+-----------------+-------------+-------------------------
> 2452 | 2073 | 41 | 2013-03-11 10:52:20.331
> (1 row)
>
> TAPd=# \d access_email
> Table "public.access_email"
> Column | Type | Collation | Nullable | Default
> -----------------+-----------------------------+-----------+----------+-------------------------------------------------------
> access_email_id | integer | | not null | nextval('access_email_access_email_id_seq'::regclass)
> type | numeric(10,0) | | |
> email_address | character varying(255) | | |
> created_on | timestamp without time zone | | not null |
> modified_on | timestamp without time zone | | |
> created_by | integer | | |
> modified_by | integer | | |
> Indexes:
> "pk_access_email" PRIMARY KEY, btree (access_email_id)
> Referenced by:
> TABLE "rel_user_email" CONSTRAINT "fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES access_email(access_email_id)
>
> TAPd=# select * from access_email where access_email_id=2073;
> access_email_id | type | email_address | created_on | modified_on | created_by | modified_by
> -----------------+------+---------------+------------+-------------+------------+-------------
> (0 rows)
>
> Looks like index corruption.
>
> $ pg_amcheck -t public.rel_user_email -t public.access_email TAPd
> $ echo $?
> 0
> $ pg_amcheck -i idx_rel_user_email -i pk_access_email --parent-check --heapallindexed TAPd
> $ echo $?
> 0
>
> But amcheck shows no problems.
>
> Before I get worried that there' s corrupt data: am I missing something obvious?
Try
SET enable_indexscan = off;
SELECT * FROM access_email WHERE access_email_id = 2073;
Only if that returns a row, I would assume index corruption, and that one should have been
caught with "heapallindexed".
It is the foreign key that is violated. The normal ways to end up with broken foreign
keys are
SET session_replication_role = replica;
and
ALTER TABLE rel_user_email DISABLE TRIGGER ALL;
both of which require superuser privileges.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-02-13 06:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-12 21:35 pg_restore failed on foreign key constraint Ron Johnson <[email protected]>
2026-02-13 06:43 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox