From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Filip_Sedl=C3=A1k?= <filip@sedlakovi.org>
cc: pgsql-bugs@lists.postgresql.org,
        =?UTF-8?Q?Michal_Danil=C3=A1k?= <michal@monitora.cz>
Subject: Re: B-tree Index corruption
In-reply-to: <8cf27c03-941e-4b12-9bdc-06237c0302d4@sedlakovi.org>
References: <8cf27c03-941e-4b12-9bdc-06237c0302d4@sedlakovi.org>
Comments: In-reply-to =?UTF-8?Q?Filip_Sedl=C3=A1k?= <filip@sedlakovi.org>
	message dated "Mon, 20 Nov 2023 16:08:39 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1317055.1700493394.1@sss.pgh.pa.us>
Date: Mon, 20 Nov 2023 10:16:34 -0500
Message-ID: <1317056.1700493394@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1317056.1700493394%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Filip_Sedl=C3=A1k?= <filip@sedlakovi.org> writes:
> We're on version 15.3 and I see some index misbehaviour fixed in 15.5 
> but nothing on btree over text values. I saw some posts about glibc 
> upgrade changing collation rules but I don't think it's our case because 
> we see it with plain ASCII values.

Sadly, you're wrong about that; see the example at

https://wiki.postgresql.org/wiki/Locale_data_changes#Testing_collation

If you did an OS upgrade across a glibc collation change, it's best to
assume all your indexes on textual columns are corrupt and REINDEX
them.

> More importantly, we last upgraded 
> the major OS version in June but I was able to find a row missing from 
> the index that was inserted a few weeks ago.

It can take awhile before the results of inconsistent sorting order
become obvious, but the state of the index will progressively degrade
as new entries get inserted into the wrong subtree.

			regards, tom lane