On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <[email protected]>
wrote:
> 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.
>
Turns out that there's a nightly cron job that dumps this (and other)
tables with the "--data-only --disable-triggers" options and then does
"psql -Xaf mumble.sql" to load them into this database.
But access_email_id=2073 is in the source access_email, so I've got to
figure out why it's not being loaded into the target.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
