Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x

On Tue, Aug 13, 2024 at 10:36 AM Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
wrote:

>
>
> On Tue, Aug 13, 2024 at 10:28 AM Veerendra Pulapa <
> veerendra(dot)pulapa(at)ashnik(dot)com> wrote:
>
>> Dear All,
>>
>> I hope this email finds you well.
>>
>> I am currently facing an issue with a PostgreSQL database that appears to
>> involve index corruption after upgrading the operating system from RHEL 7.x
>> to 8.x. Below are the specific error messages encountered:
>>
>> [snip]

>
>>
>>
>> - *PostgreSQL Version:* 13.15
>> - *OS Version:* RHEL 8.8
>>
>> According to this article
>> <https://www.postgresql.org/docs/current/errcodes-appendix.html>, these
>> errors are related to index corruption. I have managed to resolve the issue
>> by reindexing the affected tables and indexes.
>>
>> However, I would like to understand why this happened and if possible,
>> obtain proof of the root cause. Any insights or recommendations to prevent
>> such issues in the future would be greatly appreciated.
>>
>> Thank you in advance for your assistance.
>>
>>
> [snip]

> This is most likely related to the glibc changes between RHEL 7 and 8. See
> the wiki article below. You will have to reindex all indexes with data that
> could have been affected by these collation changes.
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
>
This should list the relevant indices:
create schema if not exists dba;
create or replace view dba.all_indices_types as
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as
table_name
, ndcl.relname as index_name
, array_agg(ty.typname order by att.attnum) as index_types
from pg_class ndcl
inner join pg_index nd
on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
inner join pg_attribute att
on att.attrelid = nd.indexrelid
inner join pg_type ty
on att.atttypid = ty.oid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2;

select * from dba.all_indices_types where index_types &&
'{"text","varchar","char"}';

--
Death to America, and butter sauce.
Iraq lobster!