RE: Index (primary key) corrupt? - Mailing list pgsql-general

Internal

Again, sorry for the late response. More pressing things tend to get in the way.

So the output of these queries before the reindex is:

db_name_hidden=# 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             |

db_name_hidden =# \d bcf_work_type

                     Table "name_hidden.bcf_work_type"
  Column   |  Type  | Collation | Nullable |                  Default
-----------+--------+-----------+----------+-------------------------------------------
 id        | bigint |           | not null | nextval('bcf_work_type_id_seq'::regclass)
 aml_score | bigint |           | not null |
Referenced by:
    TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES
bcf_work_type(id)ON UPDATE RESTRICT ON DELETE RESTRICT
 
    TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON
UPDATERESTRICT ON DELETE RESTRICT
 
    TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id)
ONUPDATE RESTRICT ON DELETE RESTRICT
 


After the REINDEX command (REINDEX INDEX idx_376814_primary; ) this becomes:

db_name_hidden=# 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             |

db_name_hidden =# \d bcf_work_type
                     Table "name_hidden.bcf_work_type"
  Column   |  Type  | Collation | Nullable |                  Default
-----------+--------+-----------+----------+-------------------------------------------
 id        | bigint |           | not null | nextval('bcf_work_type_id_seq'::regclass)
 aml_score | bigint |           | not null |
Indexes:
    "idx_376814_primary" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "bcf_work_type_translation" CONSTRAINT "fk_3cf130ab108734b1" FOREIGN KEY (work_type_id) REFERENCES
bcf_work_type(id)ON UPDATE RESTRICT ON DELETE RESTRICT
 
    TABLE "bcf_investment" CONSTRAINT "fk_83580679108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id) ON
UPDATERESTRICT ON DELETE RESTRICT
 
    TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES bcf_work_type(id)
ONUPDATE RESTRICT ON DELETE RESTRICT
 

So the first result stays the same, in the description of the table now the index shows up...



-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: dinsdag 28 oktober 2025 16:18
To: Wim Rouquart <wim.rouquart@kbc.be>; Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Index (primary key) corrupt?



The real sender of this external email is adrian.klaver@aklaver.com





On 10/28/25 03:25, Wim Rouquart wrote:
> Internal
>
>
> I used the output from DBeaver, guess it acted up. Here’s the output
> from psql:

1) As side note, find a different client to use then DBeaver. I have seen many Postgres questions on Stack Overflow
wherethe answer was, use something other then DBeaver.
 

2) Where and when was the query below run, on the original instance before the pg_dump or on the new instance after the
restoreand index rebuild?
 

3) From this post:


https://www.postgresql.org/message-id/AS2PR05MB107548567EEDAAB3AF74A6C59EF11A%40AS2PR05MB10754.eurprd05.prod.outlook.com

"
It is in neither, that’s why I'm sure it doesn't get exported. After a REINDEX statement it is."


 From here:

https://www.postgresql.org/docs/current/sql-reindex.html

"REINDEX rebuilds an index using the data stored in the index's table,
replacing the old copy of the index."

The question then is, how  does a REINDEX work on an index that
supposedly does not exist?

When you do the original restore and before the REINDEX, in psql, what
does the below return?:

\d bcf_work_type

>
> 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 <wim.rouquart@kbc.be
> <mailto: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://altered.secure4u.kbc.be/https://
> https://altered.secure4u.kbc.be/www.crunchydata.com <https://altered.secure4u.kbc.be/https:/
> https://altered.secure4u.kbc.be/www.crunchydata.com>
>
> Enterprise Postgres Software Products & Tech Support
>
>
> Disclaimer <https://www.kbc.com/KBCmailDisclaimer>


--
Adrian Klaver
adrian.klaver@aklaver.com

Disclaimer <https://www.kbc.com/KBCmailDisclaimer>