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

Internal

Bottom line the index exists, it is just not being applied.

-> It seems to exist indeed, but not visible for pg_dump and some other catalog queries...

Questions:

1) What is the restore command being used?

-> It's just an untar of the full backup created with pg_basebackup. No need to focus on this imo, the restore was done
fromthe production db so I could have a playground for this situation. It's clear the situation is the same on the
originaland the backup copy.
 

2) From this post:

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

What does this:

"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."
 

mean?

-> Well, we export the database using pg_dump, and on import some foreign key indexes which reference the problem
primarykey index fail to create because it's not created, which makes sense. It's not created because it's not
exported.

If you REINDEX before the export is the index attached to the table on import?

-> Yes, then it gets exported and hence imported

Define 'corrupt'.

-> Well, it's not behaving as it should, let's keep it at that.

3) The field the index points at, id, has:

bigint nextval('bcf_work_type_id_seq'::regclass).

Is that coming from a bigserial definition or a DEFAULT setting?

-> I talked to the devs. There is no packaged code for this as it turns out this was originally a mysql/mariadb db
whichgot converted to Postgresql using pgloader. But this issue has nothing to do with that migration, we did
datarefreshessuccessfully (after the migration) before it started failing.
 

4) What happens if you create a test database and restore bcf_work_type by itself, with and without data?

-> I could test this, how would you suggest to do the backup/restore part, also pg_dump?


>
> 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)
ONUPDATE RESTRICT ON DELETE RESTRICT
 
>      TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES
bcf_work_type(id)ON UPDATE 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)
ONUPDATE RESTRICT ON DELETE RESTRICT
 
>      TABLE "bcf_id_information" CONSTRAINT "fk_f56a0f6b108734b1" FOREIGN KEY (work_type_id) REFERENCES
bcf_work_type(id)ON UPDATE RESTRICT ON DELETE RESTRICT
 
>
> So the first result stays the same, in the description of the table now the index shows up...
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

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