Re: problem about reindex

Hi Julien,
Yes, the operating system was upgraded from RHEL 7.9 to 8.4. Does this mean
that I will do reindex operation on all indexes in the system? Or how can I
detect corrupted indexes due to upgrade? Also shouldn't it be seen in the
log in case of corruption, like this "ERROR: could not read block xxx in
file".
Thanks for your reply.

Julien Rouhaud <rjuju123(at)gmail(dot)com>, 22 Eyl 2021 Çar, 14:37 tarihinde şunu
yazdı:

> Hi,
>
> On Wed, Sep 22, 2021 at 7:19 PM liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
> wrote:
> >
> > I'm using PostgreSQL 12.7 on RHEL 8.4. I have a table that has 3132
> rows. I faced an interesting case when I run this query.
> >
> > select * from enterprise.private_room where name = 'mannheis';
> > id | name | user_id | create_time | sub_package_id | password |
> tenant_id
> >
> ----+------+---------+-------------+----------------+----------+-----------
> > (0 rows)
> >
> > But the table has this row:
> >
> > select * from enterprise.private_room where lower(name) = 'mannheis';
> >
> > id | name | user_id |
> create_time | sub_package_id | password | tenant_id
> >
> -------+---------------------+--------------------------------------+----------------------------+----------------+----------+-----------
> > 67182 | mannheis | f4e14cbe-f8e2-4c04-85cf-30271cc08526 |
> 2020-06-15 14:35:51 | 101361 | | 3
> >
> > I got the same result with this query : select * from
> enterprise.private_room where trim(name) = 'mannheis';
> >
> > After doing reindex the table, the problem was solved. But I don't
> understand why this problem occurs.
>
> It means that your index was corrupted, and your REINDEX fixed it.
> Given that it's an index on a collatable datatype, one reason for the
> initial corruption would be an upgrade of your operating system
> library providing the collation (glibc or icu, depending on your
> index) without a subsequent REINDEX of all your indexes depending on a
> collatable datatype. Have you upgraded your system's collation
> library since the initial creation of this index?
>