Обсуждение: Index (primary key) corrupt?
Internal
Hello,
When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
This concerns a PostgreSQL version 15 btw.
Thanks!
Disclaimer
On Thu, Sep 18, 2025 at 10:58 AM Wim Rouquart <wim.rouquart@kbc.be> wrote:
Hello,
When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
Does this problem keep happening, or has it only happened once?
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
pg_checksums is available in PG 15.
This concerns a PostgreSQL version 15 btw.
Are you at the current patch level?
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 9/18/25 05:25, Wim Rouquart wrote:
> Internal
>
>
> Hello,
>
> When doing a pg_dump of one of our databases one of the tables primary
> keys doesn’t get exported. Pg_dump just skips this index, without any
> warning whatsoever (verbose mode was used to doublecheck).
What is the complete table definition?
What is the complete pg_dump command being given?
Is the PK definition in the pg_dump file?
For plain text format can you grep/find it?
For custom format does:
pg_restore -s -t <the_table> <dump_file>
show it?
How is the dump file being restored?
>
> When doing a REINDEX the issue is fixed.
>
> As this seems to me to be some form of index corruption, I tried using
> amcheck (bt_index_check and bt_index_parent_check) to verify for
> corruption but both resulted with no issues (the index is a btree).
>
> I would expect the corruption to show up when using amcheck, am I
> hitting some kind of bug here?
>
> Are there any other ways to doublecheck for corruption (without enabling
> checksum upfront)?
>
> This concerns a PostgreSQL version 15 btw.
>
> Thanks!
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
--
Adrian Klaver
adrian.klaver@aklaver.com
Internal
Hello,
When doing a pg_dump of one of our databases one of the tables primary keys doesn’t get exported. Pg_dump just skips this index, without any warning whatsoever (verbose mode was used to doublecheck).
When doing a REINDEX the issue is fixed.
As this seems to me to be some form of index corruption, I tried using amcheck (bt_index_check and bt_index_parent_check) to verify for corruption but both resulted with no issues (the index is a btree).
I would expect the corruption to show up when using amcheck, am I hitting some kind of bug here?
>> Does this problem keep happening, or has it only happened once?
It is consistent on this database/index, haven’t noticed it anywhere else yet luckily. Seems to be a one-off.
Are there any other ways to doublecheck for corruption (without enabling checksum upfront)?
>> pg_checksums is available in PG 15.
It is indeed, so to test I enabled checksums, and did a checksum test, no errors, and yes, the file containing the index is mentioned in the checks…
This concerns a PostgreSQL version 15 btw.
>> Are you at the current patch level?
Yes.
Disclaimer
Internal
>> Internal
>>
>>
>> Hello,
>>
>>When doing a pg_dump of one of our databases one of the tables primary
>> keys doesn’t get exported. Pg_dump just skips this index, without any
>> warning whatsoever (verbose mode was used to doublecheck).
> What is the complete table definition?
CREATE TABLE bcf_work_type (
id bigserial NOT NULL,
aml_score int8 NOT NULL,
CONSTRAINT idx_376814_primary PRIMARY KEY (id)
);
> What is the complete pg_dump command being given?
pg_dump --verbose -p <xxx> -U <xxx> -d <xxx> > dumpverbose.sql 2> dumpverbose.log
> Is the PK definition in the pg_dump file? For plain text format can you grep/find it?
It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is.
>How is the dump file being restored?
As the code to generate the index is not in the dumpfile this seems irrelevant to me.
>>
>> When doing a REINDEX the issue is fixed.
>>
>> As this seems to me to be some form of index corruption, I tried using
>> amcheck (bt_index_check and bt_index_parent_check) to verify for
>> corruption but both resulted with no issues (the index is a btree).
>>
>> I would expect the corruption to show up when using amcheck, am I
>> hitting some kind of bug here?
>>
>> Are there any other ways to doublecheck for corruption (without
>> enabling checksum upfront)?
>>
>> This concerns a PostgreSQL version 15 btw.
>>
>> Thanks!
>>
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 9/19/25 02:40, Wim Rouquart wrote: > Internal > >>> Internal >>> >>> >>> Hello, >>> >>> When doing a pg_dump of one of our databases one of the tables primary >>> keys doesn’t get exported. Pg_dump just skips this index, without any >>> warning whatsoever (verbose mode was used to doublecheck). > >> What is the complete table definition? > > CREATE TABLE bcf_work_type ( > id bigserial NOT NULL, > aml_score int8 NOT NULL, > CONSTRAINT idx_376814_primary PRIMARY KEY (id) > ); The table was defined in one step using the above definition? Or was the the PK added later? What is the result for the query?: select * from pg_index where indexrelid = 'idx_376814_primary'::regclass; How is the table populated with data? > >> What is the complete pg_dump command being given? > > pg_dump --verbose -p <xxx> -U <xxx> -d <xxx> > dumpverbose.sql 2> dumpverbose.log > >> Is the PK definition in the pg_dump file? For plain text format can you grep/find it? > > It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is. > >> How is the dump file being restored? > > As the code to generate the index is not in the dumpfile this seems irrelevant to me. > -- Adrian Klaver adrian.klaver@aklaver.com
Internal
>>>> Hello,
>>>>
>>>> When doing a pg_dump of one of our databases one of the tables
>>>> primary keys doesn’t get exported. Pg_dump just skips this index,
>>>> without any warning whatsoever (verbose mode was used to doublecheck).
>>
>>> What is the complete table definition?
>>
>> CREATE TABLE bcf_work_type (
>> id bigserial NOT NULL,
>> aml_score int8 NOT NULL,
>> CONSTRAINT idx_376814_primary PRIMARY KEY (id) );
>The table was defined in one step using the above definition? Or was the the PK added later?
I'm not the developer of the application, can't really answer these ones I'm afraid.
> What is the result for the query?:
> select * from pg_index where indexrelid = 'idx_376814_primary'::regclass;
Name |Value |
-------------------+-------+
indexrelid |2006873|
indrelid |1998823|
indnatts |1 |
indnkeyatts |1 |
indisunique |true |
indnullsnotdistinct|false |
indisprimary |true |
indisexclusion |false |
indimmediate |true |
indisclustered |false |
indisvalid |true |
indcheckxmin |false |
indisready |true |
indislive |true |
indisreplident |false |
indkey |{} |
indcollation |{} |
indclass |{} |
indoption |{} |
indexprs | |
indpred | |
> How is the table populated with data?
Same answer as above.
>>
>>> What is the complete pg_dump command being given?
>>
>> pg_dump --verbose -p <xxx> -U <xxx> -d <xxx> > dumpverbose.sql 2>
>> dumpverbose.log
>>
>>> Is the PK definition in the pg_dump file? For plain text format can you grep/find it?
>>
>> It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is.
>>
>>> How is the dump file being restored?
>>
>> As the code to generate the index is not in the dumpfile this seems irrelevant to me.
>>
Disclaimer <https://www.kbc.com/KBCmailDisclaimer>
On 9/19/25 06:23, Wim Rouquart wrote:
>> What is the result for the query?:
>
>> select * from pg_index where indexrelid = 'idx_376814_primary'::regclass;
>
> Name |Value |
> -------------------+-------+
> indexrelid |2006873|
> indrelid |1998823|
> indnatts |1 |
> indnkeyatts |1 |
> indisunique |true |
> indnullsnotdistinct|false |
> indisprimary |true |
> indisexclusion |false |
> indimmediate |true |
> indisclustered |false |
> indisvalid |true |
> indcheckxmin |false |
> indisready |true |
> indislive |true |
> indisreplident |false |
> indkey |{} |
> indcollation |{} |
> indclass |{} |
> indoption |{} |
> indexprs | |
> indpred | |
>
Hmm, when I do the above on 15.14 I get:
-[ RECORD 1 ]-------+-------
indexrelid | 242209
indrelid | 242205
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 | NULL
indpred | NULL
What is full(15.x) version of Postgres are you using?
Is it the community version or a fork or SaaS?
What do you get for queries below?:
select * from pg_opclass where oid = 3124;
select * from pg_opclass where opcname = 'int8_ops';
>
>> How is the table populated with data?
Might be a good idea to find out.
Per a comment from Ron, does this lack of export happen every time you
dump the table?
>
> Same answer as above.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/19/25 02:29, Wim Rouquart wrote: > Internal > >>> Does this problem keep happening, or has it only happened once? > > It is consistent on this database/index, haven’t noticed it anywhere > else yet luckily. Seems to be a one-off. The above is not clear to me. One-off as for this database/index only and repeatable across dumps or that it only happen in one dump. > This concerns a PostgreSQL version 15 btw. > >>> Are you at the current patch level? > > Yes. Please specify the actual version, current is a relative term. It matters for folks using this thread for researching an issue in the future. > > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer> -- Adrian Klaver adrian.klaver@aklaver.com
On Friday, September 19, 2025, Wim Rouquart <wim.rouquart@kbc.be> wrote:
>> Does this problem keep happening, or has it only happened once?
It is consistent on this database/index, haven’t noticed it anywhere else yet luckily. Seems to be a one-off.
So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem to recur after you’ve done reindex to make it work?
David J.
> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? > > David I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? >
On 9/20/25 09:26, Rob Sargent wrote: > >> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? >> >> David > > I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? From this post: https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com " > Is the PK definition in the pg_dump file? For plain text format can you grep/find it? It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is. >How is the dump file being restored? As the code to generate the index is not in the dumpfile this seems irrelevant to me. " Make of that what you will. -- Adrian Klaver adrian.klaver@aklaver.com
> On Sep 20, 2025, at 9:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 9/20/25 09:26, Rob Sargent wrote: >>> So the problem goes away once you’ve reindexed yet you claim it’s consistent? What are you doing to get the problem torecur after you’ve done reindex to make it work? >>> David >> I was assuming the OP has a dump of the affected condition and is restoring (and perhaps re-fixing). No? > > From this post: > > https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com > > " > > Is the PK definition in the pg_dump file? For plain text format can you grep/find it? > > > It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is. > > > >How is the dump file being restored? > > > As the code to generate the index is not in the dumpfile this seems irrelevant to me. > " > > Make of that what you will. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com I don’t see the complete REINDEX command used but from the output of the query on pg_index it looks like reindex using indexname would succeed, no? Again assuming this was done against the dump which may or may not have matched a grep attempt.Lord knows I’ve had my share of false negatives with grep.