Thread
-
pg_restore failed on foreign key constraint
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-12T21:35:09Z
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! -
Re: pg_restore failed on foreign key constraint
Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-13T06:43:33Z
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 -
Re: pg_restore failed on foreign key constraint
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-13T14:18:02Z
On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <laurenz.albe@cybertec.at> 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! -
Re: pg_restore failed on foreign key constraint
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-13T15:55:17Z
On 2/13/26 06:18, Ron Johnson wrote: > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <laurenz.albe@cybertec.at > <mailto:laurenz.albe@cybertec.at>> wrote: > 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. Is it in the dump file from the source? > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: pg_restore failed on foreign key constraint
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-13T16:05:03Z
On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 2/13/26 06:18, Ron Johnson wrote: > > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <laurenz.albe@cybertec.at > > <mailto:laurenz.albe@cybertec.at>> wrote: > > > 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. > > Is it in the dump file from the source? > Some tables aren't being dumped at the source; Thus, the missing records. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: pg_restore failed on foreign key constraint
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-13T16:14:49Z
On 2/13/26 08:05, Ron Johnson wrote: > On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 2/13/26 06:18, Ron Johnson wrote: > > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe > <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at> > > <mailto:laurenz.albe@cybertec.at > <mailto:laurenz.albe@cybertec.at>>> wrote: > > > 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. > > Is it in the dump file from the source? > > > Some tables aren't being dumped at the source; Thus, the missing records. Un-confuse me, how do the below relate?: "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." and "Some tables aren't being dumped at the source" > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: pg_restore failed on foreign key constraint
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-13T16:40:42Z
On Fri, Feb 13, 2026 at 11:14 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 2/13/26 08:05, Ron Johnson wrote: > > On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver > > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > > > On 2/13/26 06:18, Ron Johnson wrote: > > > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe > > <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at> > > > <mailto:laurenz.albe@cybertec.at > > <mailto:laurenz.albe@cybertec.at>>> wrote: > > > > > 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. > > > > Is it in the dump file from the source? > > > > > > Some tables aren't being dumped at the source; Thus, the missing > records. > > Un-confuse me, how do the below relate?: > > "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." > > and > > "Some tables aren't being dumped at the source" > Table name Source Dumped Target Loaded public.access_email No No public.rel_user_email Yes Yes Thus, while new and modified records are being added to public.access_email at the source, they are not making it to the Target database. That plus "--disable-triggers" lets the public.rel_user_email loads succeed on the target even though it breaks RI. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
-
Re: pg_restore failed on foreign key constraint
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-13T17:35:31Z
On 2/13/26 8:40 AM, Ron Johnson wrote: > On Fri, Feb 13, 2026 at 11:14 AM Adrian Klaver > > Un-confuse me, how do the below relate?: > > "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." > > and > > "Some tables aren't being dumped at the source" > > > Table name Source Dumped Target Loaded > public.access_email No No > public.rel_user_email Yes Yes > > Thus, while new and modified records are being added > to public.access_email at the source, they are not making it to the > Target database. That plus "--disable-triggers" lets > the public.rel_user_email loads succeed on the target even though it > breaks RI. This means you know what the issue is now? > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster!
-
Re: pg_restore failed on foreign key constraint
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-13T20:17:36Z
On Fri, Feb 13, 2026 at 12:35 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: [snip] > > > > Thus, while new and modified records are being added > > to public.access_email at the source, they are not making it to the > > Target database. That plus "--disable-triggers" lets > > the public.rel_user_email loads succeed on the target even though it > > breaks RI. > > This means you know what the issue is now? > Yes it does. 😀 -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!