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

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:
> 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