Message-ID: <6511b21d495f1c010f05ae9841ee3fdc649dcff2.camel@cybertec.at>
Subject: Re: pg_restore failed on foreign key constraint
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-general
	 <pgsql-general@postgresql.org>
Date: Fri, 13 Feb 2026 07:43:33 +0100
In-Reply-To: <CANzqJaD1JNeJfNJkxZOvqSw=ueJLzq3hM3tUf5FJNkbxEr8Z4A@mail.gmail.com>
References: 
	<CANzqJaD1JNeJfNJkxZOvqSw=ueJLzq3hM3tUf5FJNkbxEr8Z4A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.2 (3.58.2-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/6511b21d495f1c010f05ae9841ee3fdc649dcff2.camel%40cybertec.at>
Precedence: bulk

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:
>=20
> 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: =C2=A0insert or update=
 on table "rel_user_email" violates foreign key constraint "fk_rel_user_ema=
il_2"
> DETAIL: =C2=A0Key (access_email_id)=3D(2073) is not present in table "acc=
ess_email".
> Command was: ALTER TABLE ONLY public.rel_user_email
> =C2=A0 =C2=A0 ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_emai=
l_id) REFERENCES public.access_email(access_email_id);
>=20
> So, I went to the source database:
>=20
> TAPd=3D# \d rel_user_email
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0Table "public.rel_user_email"
> =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collation=
 | Nullable | Default=20
> -----------------+-----------------------------+-----------+----------+--=
-------
> =C2=A0user_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 | not null |=20
> =C2=A0access_email_id | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not nul=
l |=20
> =C2=A0modified_by =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20
> =C2=A0modified_on =C2=A0 =C2=A0 | timestamp without time zone | =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |=20
> Indexes:
> =C2=A0 =C2=A0 "idx_rel_user_email" UNIQUE, btree (user_id, access_email_i=
d)
> Foreign-key constraints:
> =C2=A0 =C2=A0 "fk_rel_user_email_1" FOREIGN KEY (user_id) REFERENCES acce=
ss_user(user_id)
> =C2=A0 =C2=A0 "fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFEREN=
CES access_email(access_email_id)
>=20
> TAPd=3D# select * from rel_user_email where access_email_id=3D2073;
> =C2=A0user_id | access_email_id | modified_by | =C2=A0 =C2=A0 =C2=A0 modi=
fied_on =C2=A0 =C2=A0 =C2=A0=20
> ---------+-----------------+-------------+-------------------------
> =C2=A0 =C2=A0 2452 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A02073 | =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A041 | 2013-03-11 10:52:20.331
> (1 row)
>=20
> TAPd=3D# \d access_email
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.access_email"
> =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collation=
 | Nullable | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0Default =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
> -----------------+-----------------------------+-----------+----------+--=
-----------------------------------------------------
> =C2=A0access_email_id | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not nul=
l | nextval('access_email_access_email_id_seq'::regclass)
> =C2=A0type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| numeric(10,0) =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20
> =C2=A0email_address =C2=A0 | character varying(255) =C2=A0 =C2=A0 =C2=A0|=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=
=20
> =C2=A0created_on =C2=A0 =C2=A0 =C2=A0| timestamp without time zone | =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |=20
> =C2=A0modified_on =C2=A0 =C2=A0 | timestamp without time zone | =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20
> =C2=A0created_by =C2=A0 =C2=A0 =C2=A0| integer =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20
> =C2=A0modified_by =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20
> Indexes:
> =C2=A0 =C2=A0 "pk_access_email" PRIMARY KEY, btree (access_email_id)
> Referenced by:
> =C2=A0 =C2=A0 TABLE "rel_user_email" CONSTRAINT "fk_rel_user_email_2" FOR=
EIGN KEY (access_email_id) REFERENCES access_email(access_email_id)
>=20
> TAPd=3D# select * from access_email where access_email_id=3D2073;
> =C2=A0access_email_id | type | email_address | created_on | modified_on |=
 created_by | modified_by=20
> -----------------+------+---------------+------------+-------------+-----=
-------+-------------
> (0 rows)
>=20
> Looks like index corruption.=C2=A0=C2=A0
>=20
> $ 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 --he=
apallindexed TAPd
> $ echo $?
> 0
>=20
> But amcheck shows no problems.
>=20
> Before I get worried that there' s corrupt=C2=A0data: am I missing someth=
ing obvious?

Try

  SET enable_indexscan =3D off;

  SELECT * FROM access_email WHERE access_email_id =3D 2073;

Only if that returns a row, I would assume index corruption, and that one s=
hould have been
caught with "heapallindexed".

It is the foreign key that is violated.  The normal ways to end up with bro=
ken foreign
keys are

  SET session_replication_role =3D replica;

and

  ALTER TABLE rel_user_email DISABLE TRIGGER ALL;

both of which require superuser privileges.

Yours,
Laurenz Albe