Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqtzu-002pXl-0s for pgsql-general@arkaria.postgresql.org; Fri, 13 Feb 2026 14:18:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vqtzt-00EdyD-1j for pgsql-general@arkaria.postgresql.org; Fri, 13 Feb 2026 14:18:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqtzt-00Edy4-0b for pgsql-general@lists.postgresql.org; Fri, 13 Feb 2026 14:18:18 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vqtzr-00000000V7K-48tq for pgsql-general@postgresql.org; Fri, 13 Feb 2026 14:18:17 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-662f9aeb782so614800eaf.3 for ; Fri, 13 Feb 2026 06:18:15 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770992293; cv=none; d=google.com; s=arc-20240605; b=RPhWYQ9W4AkLOBXsf71C84wPWYqQQ3WS+upJ+ZNUQalROxbHEd9HRPyeZ6SxKt4O53 dMbAsreOO4MM9LnixsfGxEMa5qS3lk+OS09API25PKY2lKfnoJ3bUaHeQ8QuoibCASkj 0V3Qx9SN/jcD1E4rjwpSCDV2WyLDukbCmEGPDMrhUxFc+DTnfKliB2o5xzbvxQoNeyzt N94vpn38RHmwkXQFRGO4KfU58SPyCbl4I2AXJlvqTBoQNESVdzp0N1rotQqp1C1SPa4H xeChty7ubL6r6gpm8T+B8Srm5pWD1u0NU7L/f9SPo94yf/K0wag7uGxsoFZ2Ja1+cYyM R40g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=tF698BZ4OOn7YFxPfJz3va3xwZtCeq98/lTHnSaq/74=; fh=KNSq+t9BltSXFnT3Yof/aKGBtqxeA+bTALiYdvTslaY=; b=F2H79HAPSIxMhoud4gdO3P4Lp91Nbi7ba1oL1yQFaOLMVUFZPyB2nl21/THURv1gyJ KdhQiQIyP6/Zr4j9wirxuTqICfz1lWgxcq5ThboqbT28eB61FzfkF9jrL9wySzYvn208 R5S25mo+7IUbQ/aHspThixIhrBtmTqY3N0qbb9ubQCCzaR67ktTew71VTEU4iPmYxoBi EOShyJhMGD+fgq1TFImYOcicqpWdsnafzPa/iemIyCYiBQB0Euv2WKsIzbhiv8Ew53Jj PBOru0uNW5TN6G3yBUVsbmomAqBbIgLQs+UTVlZUeCOUcSB4eZZiVMB5ZgCf8aByQTnX ngvQ==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770992293; x=1771597093; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=tF698BZ4OOn7YFxPfJz3va3xwZtCeq98/lTHnSaq/74=; b=bpNsddLEc68ri3YCJ9d/ac/eljEp35dLwC/yR9OUtIZ/G90bWvbNAnzO+AuhSoxsbE 6jvVJr4J7LrKK9Ao9z36skDD3R7A1fbN+YlYGRM+P23WYfHFLaspLQqM/to/iD0yGoL2 qpKpsPGBZcOzkM75yjsUeJoFKKQieZwNBBaVuuPPjRW/Z/TJ5+ZlKFnlUcNPDYBKR1sd IqHEvmrQPzJs9v4GE1OhTbCD9cPUl44/N6srdUDFXTUFtgP/OkHw9jc4ksNar7mtelrP gQ4sohtSIHBLkp0U5mS9KWMXmPzGH96sTzJ/tqVhe3vl4li/jG1hvgXa/4nJ8kSe4TVj exog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770992293; x=1771597093; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=tF698BZ4OOn7YFxPfJz3va3xwZtCeq98/lTHnSaq/74=; b=RDzt+KQiUV0farXRvK3p297bpx8+p9Z2yhJQYw1Jz6dfzAgNwMFWwCL6elQhXxr07O 0gsXE7QaKCaCQX5Qi9pe6GseUY5cCUKjJfM+fbnC2edl7ZedMkkKkR1gtB0ZmlY3xvxw HDranxOcjgB/Fr/crFDEFfc9OFqIwvT5srVgyZWKrypQ24TAHNSMpWSlqH7taqJ5hR4H Clcil3dwlAOHSY96fKyv5bsateoothoxzCDHhMq0woX7ul3iw179c3UutD+78sapdHbg YntHa+G1oUMN68aoRpMendKp1JePZy0Vh1tQKdDsV8rUDW653y1PfEtoZ7Ljs9QrnnEd Y1wg== X-Gm-Message-State: AOJu0YwKNS1fPuhNRa7sqakGTTDmGjbZOWFPLTREjVrikhbyaCTbmqD2 +ic8MqVTWS7f7wSVXWZHag2OuWGD51Qs+SWw5KsRsiXlw8xl1PmhLM32i+qsZBfLlP6rlVMScfa u05oqd7VGjvqp4egehDhikm/dmlR75if6Stpj X-Gm-Gg: AZuq6aLlSuZD7T3NnRn8XCS9Nq/c95MMuG99a3g9GawXkHa11A+YyG8+i5maU4ECvoB AI1yMZCOmvI4ssYIOrWfRo0nfWVsk4hiTN0bcmFo02fb8H/Px5BnV8BMRolyfbw1Oquz8N7n70s ofLcakjPLv5YXTcdicJhhd4tO8sheYTraZ7t7BukB+EVi2SFy7ZhdrHptZegwv68O0ddEBw2P66 Rei1Wo/yZe+7mHYxf8BWWjEpPb63yKSWUeRvtmSr/cDQYpWR6N/vOkQ9CnL3gAgc1G6e62oXxCx FSRdP9sM X-Received: by 2002:a4a:b341:0:b0:659:9a49:9026 with SMTP id 006d021491bc7-6772370b43cmr799293eaf.83.1770992293340; Fri, 13 Feb 2026 06:18:13 -0800 (PST) MIME-Version: 1.0 References: <6511b21d495f1c010f05ae9841ee3fdc649dcff2.camel@cybertec.at> In-Reply-To: <6511b21d495f1c010f05ae9841ee3fdc649dcff2.camel@cybertec.at> From: Ron Johnson Date: Fri, 13 Feb 2026 09:18:02 -0500 X-Gm-Features: AaiRm51B4btlsvnKkOmbqjNIHpDkc_2xeDjf5oGrpB-LLN5Lv8oI1C5rzxS-hvI Message-ID: Subject: Re: pg_restore failed on foreign key constraint To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000008b7008064ab546a8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008b7008064ab546a8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Feb 13, 2026 at 1:43=E2=80=AFAM Laurenz Albe 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)=3D(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=3D# \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=3D# select * from rel_user_email where access_email_id=3D2073; > > user_id | access_email_id | modified_by | modified_on > > ---------+-----------------+-------------+------------------------- > > 2452 | 2073 | 41 | 2013-03-11 10:52:20.331 > > (1 row) > > > > TAPd=3D# \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=3D# select * from access_email where access_email_id=3D2073; > > 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 =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 > 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 =3D 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=3D2073 is in the source access_email, so I've got to figure out why it's not being loaded into the target. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000008b7008064ab546a8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Feb 13, 2026 at 1:43=E2=80=AFAM L= aurenz Albe <laurenz.albe@cy= bertec.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 dum= p, I got this in the log:
>
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 171927; 2606 3149123877 FK CONSTRAINT rel_u= ser_email fk_rel_user_email_2 TAP
> pg_restore: error: could not execute query: ERROR: =C2=A0insert or upd= ate on table "rel_user_email" violates foreign key constraint &qu= ot;fk_rel_user_email_2"
> DETAIL: =C2=A0Key (access_email_id)=3D(2073) is not present in table &= quot;access_email".
> Command was: ALTER TABLE ONLY public.rel_user_email
> =C2=A0 =C2=A0 ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_e= mail_id) REFERENCES public.access_email(access_email_id);
>
> So, I went to the source database:
>
> 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 | Collat= ion | Nullable | Default
> -----------------+-----------------------------+-----------+----------= +---------
> =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 |
> =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 = null |
> =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|
> =C2=A0modified_on =C2=A0 =C2=A0 | timestamp without time zone | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
> Indexes:
> =C2=A0 =C2=A0 "idx_rel_user_email" UNIQUE, btree (user_id, a= ccess_email_id)
> Foreign-key constraints:
> =C2=A0 =C2=A0 "fk_rel_user_email_1" FOREIGN KEY (user_id) RE= FERENCES access_user(user_id)
> =C2=A0 =C2=A0 "fk_rel_user_email_2" FOREIGN KEY (access_emai= l_id) REFERENCES access_email(access_email_id)
>
> 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 m= odified_on =C2=A0 =C2=A0 =C2=A0
> ---------+-----------------+-------------+------------------------- > =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)
>
> 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 | Collat= ion | 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 = null | 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|
> =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|
> =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 |
> =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|
> =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|
> =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|
> Indexes:
> =C2=A0 =C2=A0 "pk_access_email" PRIMARY KEY, btree (access_e= mail_id)
> Referenced by:
> =C2=A0 =C2=A0 TABLE "rel_user_email" CONSTRAINT "fk_rel= _user_email_2" FOREIGN KEY (access_email_id) REFERENCES access_email(a= ccess_email_id)
>
> TAPd=3D# select * from access_email where access_email_id=3D2073;
> =C2=A0access_email_id | type | email_address | created_on | modified_o= n | created_by | modified_by
> -----------------+------+---------------+------------+-------------+--= ----------+-------------
> (0 rows)
>
> Looks like index corruption.=C2=A0=C2=A0
>
> $ 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=C2=A0data: am I missing= something obvious?

Try

=C2=A0 SET enable_indexscan =3D off;

=C2=A0 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.=C2=A0 The normal ways to end up wit= h broken foreign
keys are

=C2=A0 SET session_replication_role =3D replica;

and

=C2=A0 ALTER TABLE rel_user_email DISABLE TRIGGER ALL;

both of which require superuser privileges.

=
Turns out that there's a nightly=C2=A0cron job that dumps this (an= d other) tables with the "--data-only --disable-triggers" options= and then does "psql -Xaf mumble.sql" to load them into this data= base.

But access_email_id=3D2073 is in the source = access_email, so I've got to figure out why it's not being loaded i= nto the target.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I&= #39;m still alive.
<Redacted> lobster!
=
--0000000000008b7008064ab546a8--