public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wim Rouquart <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Index (primary key) corrupt?
Date: Tue, 28 Oct 2025 10:25:01 +0000
Message-ID: <AS2PR05MB10754BFE319E2594C9E076EE2EFFDA@AS2PR05MB10754.eurprd05.prod.outlook.com> (raw)
In-Reply-To: <CAKAnmmLNaQ9NnjP=-kMj4t6wjt3+5nmMbQc2+F2Ekv_=zJWs5w@mail.gmail.com>
References: <AS2PR05MB1075477BA334D4DA262AD75BFEF16A@AS2PR05MB10754.eurprd05.prod.outlook.com>
<[email protected]>
<AS2PR05MB107548567EEDAAB3AF74A6C59EF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
<[email protected]>
<AS2PR05MB10754A51AB7FC85161189FF7CEF11A@AS2PR05MB10754.eurprd05.prod.outlook.com>
<CAKAnmmLNaQ9NnjP=-kMj4t6wjt3+5nmMbQc2+F2Ekv_=zJWs5w@mail.gmail.com>
Internal
I used the output from DBeaver, guess it acted up. Here’s the output from psql:
select * from pg_index where indexrelid = 'idx_376814_primary'::regclass;
-[ RECORD 1 ]-------+--------
indexrelid | 2006873
indrelid | 1998823
indnatts | 1
indnkeyatts | 1
indisunique | t
indnullsnotdistinct | f
indisprimary | t
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 1
indcollation | 0
indclass | 3124
indoption | 0
indexprs |
indpred |
The steps that show the index is missing is an export of the database while the index is ‘corrupt’, and then importing it. The import fails on foreign keys that are pointing to this index because it is indeed not created.
Checking the export file shows the create statement from the index is indeed missing (I know it can show up lower in the file, a search was done on the index name, it’s not in there, you’re going to have to trust me on this).
After doing a reindex like this:
REINDEX INDEX idx_376814_primary;
the export import story works just fine, the index is in there and is created.
Cheers,
Wim.
On Sat, Sep 20, 2025 at 5:07 AM Wim Rouquart <[email protected]<mailto:[email protected]>> 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://altered.secure4u.kbc.be/https://www.crunchydata.com<https://altered.secure4u.kbc.be/https...;
Enterprise Postgres Software Products & Tech Support
Disclaimer <https://www.kbc.com/KBCmailDisclaimer;
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: RE: Index (primary key) corrupt?
In-Reply-To: <AS2PR05MB10754BFE319E2594C9E076EE2EFFDA@AS2PR05MB10754.eurprd05.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox