MIME-Version: 1.0
References: <AS2PR05MB1075477BA334D4DA262AD75BFEF16A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <1bfa0b6b-11a8-458f-a3f3-3f86574abc1d@aklaver.com> <AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
 <f3ea70b8-c822-4b0f-b564-5e73d34188d4@aklaver.com> <AS2PR05MB10754A51AB7FC85161189FF7CEF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
In-Reply-To: <AS2PR05MB10754A51AB7FC85161189FF7CEF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sat, 11 Oct 2025 21:44:34 -0400
Message-ID: <CAKAnmmLNaQ9NnjP=-kMj4t6wjt3+5nmMbQc2+F2Ekv_=zJWs5w@mail.gmail.com>
Subject: Re: Index (primary key) corrupt?
To: Wim Rouquart <wim.rouquart@kbc.be>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000015c4c40640ec4dea"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLNaQ9NnjP%3D-kMj4t6wjt3%2B5nmMbQc2%2BF2Ekv_%3DzJWs5w%40mail.gmail.com>
Precedence: bulk

--00000000000015c4c40640ec4dea
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Sep 20, 2025 at 5:07=E2=80=AFAM Wim Rouquart <wim.rouquart@kbc.be> =
wrote:

> Name               |Value  |
> -------------------+-------+
> indexrelid         |2006873|
> indrelid           |1998823|
> indnatts           |1      |
> indnkeyatts        |1      |

...

indclass           |{}     |
>

Hold on, that makes no sense at all. The indkey/indclass columns cannot be
empty, especially as indnkeyatts is 1, as it should be. As a matter of
fact, pg_dump would completely choke on a broken table like this and not
even be able to dump it. But that output is clearly not from psql, so I
think whatever client application you are using is not able to reliably
output array columns. Any chance you can run that select command using
psql? As the rest of the columns look sane, I'm going to guess those are as
well, they just don't show up correctly, and the system catalogs are
uncorrupted.

until I do the rebuild and then the issue is fixed


Could you show us exactly the steps that show the index is missing, and
that it is then fixed?

(ponders) Keep in mind that although you declared the primary key in your
create table statement, pg_dump is going to separate the table creation
from the primary key creation by a lot of lines. So you will see in the
pg_dump output:

CREATE TABLE public.bcf_work_type (
    id bigint NOT NULL,
    aml_score bigint NOT NULL
);

and then much later on:

ALTER TABLE ONLY public.bcf_work_type
    ADD CONSTRAINT idx_376814_primary PRIMARY KEY (id);

Also be aware that if you are using the --section argument, the table will
appear in the 'pre-data' section but the primary key will appear in the
'post-data' section.


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--00000000000015c4c40640ec4dea
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Sep 20, 2025 at 5:07=E2=80=AFAM W=
im Rouquart &lt;<a href=3D"mailto:wim.rouquart@kbc.be">wim.rouquart@kbc.be<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex">
Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|Value=C2=A0 |<b=
r>
-------------------+-------+<br>
indexrelid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|2006873|<br>
indrelid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|1998823|<br>
indnatts=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|1=C2=A0 =C2=A0 =C2=A0 |<b=
r>
indnkeyatts=C2=A0 =C2=A0 =C2=A0 =C2=A0 |1=C2=A0 =C2=A0 =C2=A0 |=C2=A0</bloc=
kquote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex">...</blockquote><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex">indclass=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0|{}=C2=A0 =C2=A0 =C2=A0|<br></blockquote><div><br><=
/div><div>Hold on, that makes no sense at all. The indkey/indclass columns =
cannot be empty, especially as indnkeyatts is 1, as it should be. As a matt=
er of fact, pg_dump would completely choke=C2=A0on a broken=C2=A0table like=
 this and not even be able to dump it. But that output is clearly not from =
psql, so I think whatever client application=C2=A0you are using is not able=
 to reliably output array=C2=A0columns. Any chance you can run that select =
command using psql? As the rest of the columns look sane, I&#39;m going to =
guess those are as well, they just don&#39;t show up correctly, and the sys=
tem catalogs are uncorrupted.</div><div><br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex">until I do the rebuild and then the issue is fixed=
</blockquote><div><br></div><div>Could you show us exactly the steps that s=
how the index is missing, and that it is then fixed?</div><div><br></div><d=
iv>(ponders) Keep in mind that although you declared the primary key in you=
r create table statement, pg_dump is going to separate the table creation f=
rom the primary key creation by a lot of lines. So you will see in the pg_d=
ump output:</div><div><br></div><div>CREATE TABLE public.bcf_work_type (<br=
>=C2=A0 =C2=A0 id bigint NOT NULL,<br>=C2=A0 =C2=A0 aml_score bigint NOT NU=
LL<br>);</div><div><br></div><div>and then much later on:</div><div><br></d=
iv><div>ALTER TABLE ONLY public.bcf_work_type<br>=C2=A0 =C2=A0 ADD CONSTRAI=
NT idx_376814_primary PRIMARY KEY (id);</div><div><br></div><div>Also be aw=
are that if you are using the --section argument, the table will appear in =
the &#39;pre-data&#39; section but the primary key will appear in the &#39;=
post-data&#39; section.</div><div><br></div><div><br></div></div><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>=
Greg</div><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"https:=
//www.crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a></d=
iv><div>Enterprise Postgres Software Products &amp; Tech Support</div><div>=
<br></div></div></div></div>

--00000000000015c4c40640ec4dea--