public inbox for [email protected]help / color / mirror / Atom feed
Re: pg_restore failed on foreign key constraint 4+ messages / 2 participants [nested] [flat]
* Re: pg_restore failed on foreign key constraint @ 2026-02-13 14:18 Ron Johnson <[email protected]> 2026-02-13 15:55 ` Re: pg_restore failed on foreign key constraint Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Ron Johnson @ 2026-02-13 14:18 UTC (permalink / raw) To: pgsql-general 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore failed on foreign key constraint 2026-02-13 14:18 Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> @ 2026-02-13 15:55 ` Adrian Klaver <[email protected]> 2026-02-13 16:05 ` Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2026-02-13 15:55 UTC (permalink / raw) To: Ron Johnson <[email protected]>; pgsql-general On 2/13/26 06:18, Ron Johnson wrote: > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <[email protected] > <mailto:[email protected]>> 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore failed on foreign key constraint 2026-02-13 14:18 Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> 2026-02-13 15:55 ` Re: pg_restore failed on foreign key constraint Adrian Klaver <[email protected]> @ 2026-02-13 16:05 ` Ron Johnson <[email protected]> 2026-02-13 16:14 ` Re: pg_restore failed on foreign key constraint Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Ron Johnson @ 2026-02-13 16:05 UTC (permalink / raw) To: pgsql-general On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver <[email protected]> wrote: > On 2/13/26 06:18, Ron Johnson wrote: > > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <[email protected] > > <mailto:[email protected]>> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore failed on foreign key constraint 2026-02-13 14:18 Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> 2026-02-13 15:55 ` Re: pg_restore failed on foreign key constraint Adrian Klaver <[email protected]> 2026-02-13 16:05 ` Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> @ 2026-02-13 16:14 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2026-02-13 16:14 UTC (permalink / raw) To: Ron Johnson <[email protected]>; pgsql-general On 2/13/26 08:05, Ron Johnson wrote: > On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver > <[email protected] <mailto:[email protected]>> wrote: > > On 2/13/26 06:18, Ron Johnson wrote: > > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe > <[email protected] <mailto:[email protected]> > > <mailto:[email protected] > <mailto:[email protected]>>> 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-02-13 16:14 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-13 14:18 Re: pg_restore failed on foreign key constraint Ron Johnson <[email protected]> 2026-02-13 15:55 ` Adrian Klaver <[email protected]> 2026-02-13 16:05 ` Ron Johnson <[email protected]> 2026-02-13 16:14 ` Adrian Klaver <[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