On Tue, Aug 13, 2024 at 10:36 AM Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:
On Tue, Aug 13, 2024 at 10:28 AM Veerendra Pulapa <veerendra.pulapa@xxxxxxxxxx> 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, 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.
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 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!
