public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: Keith Fiske <[email protected]>
Cc: Veerendra Pulapa <[email protected]>
Cc: pgsql-admin <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
Date: Tue, 13 Aug 2024 10:54:19 -0400
Message-ID: <CANzqJaCN0HgYy-HJ9F-6Z5wfjHuk1c6+L=Z9QbmAEcTMfAxQGg@mail.gmail.com> (raw)
In-Reply-To: <CAODZiv57QcZOETRVnT0D_you=5i5LZDY0oWT5o-XVyRnvj8Wyw@mail.gmail.com>
References: <CAND8iOu3Zi34rjZKYe7m+LY_V4P8dg6Va_Bk=r_VUpZes1hm+Q@mail.gmail.com>
<CAODZiv57QcZOETRVnT0D_you=5i5LZDY0oWT5o-XVyRnvj8Wyw@mail.gmail.com>
On Tue, Aug 13, 2024 at 10:36 AM Keith Fiske <[email protected]>
wrote:
>
>
> On Tue, Aug 13, 2024 at 10:28 AM Veerendra Pulapa <
> [email protected]> 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!
view thread (3+ messages)
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: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
In-Reply-To: <CANzqJaCN0HgYy-HJ9F-6Z5wfjHuk1c6+L=Z9QbmAEcTMfAxQGg@mail.gmail.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