public inbox for [email protected]
help / color / mirror / Atom feedAssistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
3+ messages / 3 participants
[nested] [flat]
* Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
@ 2024-08-13 14:28 Veerendra Pulapa <[email protected]>
2024-08-13 14:35 ` Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Keith Fiske <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Veerendra Pulapa @ 2024-08-13 14:28 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <[email protected]>
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:
Error_Code=XX002|ERROR: table tid from new index tuple (4079845,29)
overlaps with invalid duplicate tuple at offset 7 of block 63289 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4079436,18)
overlaps with invalid duplicate tuple at offset 33 of block 19069 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4079436,19)
overlaps with invalid duplicate tuple at offset 33 of block 19069 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4104179,12)
overlaps with invalid duplicate tuple at offset 82 of block 20748 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4104179,13)
overlaps with invalid duplicate tuple at offset 82 of block 20748 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (3961910,9)
overlaps with invalid duplicate tuple at offset 106 of block 57227 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (3961910,11)
overlaps with invalid duplicate tuple at offset 106 of block 57227 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4103661,18) cannot
find insert offset between offsets 162 and 175 of block 13410 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4103661,19) cannot
find insert offset between offsets 162 and 175 of block 13410 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082422,13)
overlaps with invalid duplicate tuple at offset 100 of block 80633 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082346,2)
overlaps with invalid duplicate tuple at offset 100 of block 80633 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082466,17)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082405,12)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082504,8)
overlaps with invalid duplicate tuple at offset 100 of block 80633 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082502,8)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082537,5)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082533,2)
overlaps with invalid duplicate tuple at offset 100 of block 80633 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082517,7)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082486,21)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082560,12)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082486,22)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
Error_Code=XX002|ERROR: table tid from new index tuple (4082542,17)
overlaps with invalid duplicate tuple at offset 40 of block 133565 in index
"****"
- *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.
Br,
Veerendra Pulapa | Technical Consultant
M: +91-9949349894 | www.ashnik.com
<https://www.linkedin.com/company/ashnik-pte-ltd/;
<https://www.facebook.com/AshnikBiz;
<https://www.youtube.com/user/ashnikbiz;
<https://www.instagram.com/ashnikbiz/; <https://twitter.com/Ashnikbiz;
--
*________________________________________________________________________________________
*_This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s). If you are not the
rightful recipient of this email, please delete this email immediately and
inform the recipient.
_
*
*
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
2024-08-13 14:28 Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Veerendra Pulapa <[email protected]>
@ 2024-08-13 14:35 ` Keith Fiske <[email protected]>
2024-08-13 14:54 ` Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Ron Johnson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Keith Fiske @ 2024-08-13 14:35 UTC (permalink / raw)
To: Veerendra Pulapa <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]>
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:
> Error_Code=XX002|ERROR: table tid from new index tuple (4079845,29)
> overlaps with invalid duplicate tuple at offset 7 of block 63289 in index
> "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4079436,18)
> overlaps with invalid duplicate tuple at offset 33 of block 19069 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4079436,19)
> overlaps with invalid duplicate tuple at offset 33 of block 19069 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4104179,12)
> overlaps with invalid duplicate tuple at offset 82 of block 20748 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4104179,13)
> overlaps with invalid duplicate tuple at offset 82 of block 20748 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (3961910,9)
> overlaps with invalid duplicate tuple at offset 106 of block 57227 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (3961910,11)
> overlaps with invalid duplicate tuple at offset 106 of block 57227 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4103661,18)
> cannot find insert offset between offsets 162 and 175 of block 13410 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4103661,19)
> cannot find insert offset between offsets 162 and 175 of block 13410 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082422,13)
> overlaps with invalid duplicate tuple at offset 100 of block 80633 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082346,2)
> overlaps with invalid duplicate tuple at offset 100 of block 80633 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082466,17)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082405,12)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082504,8)
> overlaps with invalid duplicate tuple at offset 100 of block 80633 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082502,8)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082537,5)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082533,2)
> overlaps with invalid duplicate tuple at offset 100 of block 80633 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082517,7)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082486,21)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082560,12)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082486,22)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
> Error_Code=XX002|ERROR: table tid from new index tuple (4082542,17)
> overlaps with invalid duplicate tuple at offset 40 of block 133565 in
> index "****"
>
>
>
>
> - *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.
>
>
> Br,
> Veerendra Pulapa | Technical Consultant
> M: +91-9949349894 | www.ashnik.com
>
>
> <https://www.linkedin.com/company/ashnik-pte-ltd/;
> <https://www.facebook.com/AshnikBiz;
> <https://www.youtube.com/user/ashnikbiz;
> <https://www.instagram.com/ashnikbiz/; <https://twitter.com/Ashnikbiz;
>
>
> *______________________________________________________________________________________This
> email may contain confidential, privileged or copyright material and is
> solely for the use of the intended recipient(s). If you are not the
> rightful recipient of this email, please delete this email immediately and
> inform the recipient. *
>
>
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
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x
2024-08-13 14:28 Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Veerendra Pulapa <[email protected]>
2024-08-13 14:35 ` Re: Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Keith Fiske <[email protected]>
@ 2024-08-13 14:54 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2024-08-13 14:54 UTC (permalink / raw)
To: Keith Fiske <[email protected]>; +Cc: Veerendra Pulapa <[email protected]>; pgsql-admin; Pgsql-admin <[email protected]>
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-13 14:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-13 14:28 Assistance Required for Index Corruption Issue Post OS Upgrade to RHEL 8.x Veerendra Pulapa <[email protected]>
2024-08-13 14:35 ` Keith Fiske <[email protected]>
2024-08-13 14:54 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox