Обсуждение: problem about reindex

Поиск
Список
Период
Сортировка

problem about reindex

От
liam saffioti
Дата:
Hello team,
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.

Thanks in advance,
Liam

Re: problem about reindex

От
Julien Rouhaud
Дата:
Hi,

On Wed, Sep 22, 2021 at 7:19 PM liam saffioti <liam.saffiotti@gmail.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?



Re: problem about reindex

От
liam saffioti
Дата:
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@gmail.com>, 22 Eyl 2021 Çar, 14:37 tarihinde şunu yazdı:
Hi,

On Wed, Sep 22, 2021 at 7:19 PM liam saffioti <liam.saffiotti@gmail.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?

Re: problem about reindex

От
Julien Rouhaud
Дата:
On Wed, Sep 22, 2021 at 8:06 PM liam saffioti <liam.saffiotti@gmail.com> wrote:
>
> Yes, the operating system was upgraded from RHEL 7.9 to 8.4.

After a quick check it seems that RHEL 7.X comes with glibc 2.17 and
RHEL 8?X comes with glibc 2.28.  So unfortunately all your indexes
using collatable datatypes are corrupted.  See
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html for
more details.

> Does this mean that I will do reindex operation on all indexes in the system?

Only indexes using collatable datatypes.

> Or how can I detect corrupted indexes due to upgrade?

That's not easy, as you have to check all directly used columns, but
also expression and predicates.  You could use amcheck to detect
corrupted indexes, but it will be quite costly.

If you're not sure of how to do that, a database-wide REINDEX on each
database is safer.

> Also shouldn't it be seen in the log in case of corruption, like this  "ERROR: could not read block xxx in file".

Unfortunately no, because it's a "logical corruption", due to an
underlying ordering change.



Re: problem about reindex

От
liam saffioti
Дата:
Hi again Julien,
I am very grateful for your explain and solution. I understand the issue.
Thank you so much again.

Julien Rouhaud <rjuju123@gmail.com>, 22 Eyl 2021 Çar, 15:16 tarihinde şunu yazdı:
On Wed, Sep 22, 2021 at 8:06 PM liam saffioti <liam.saffiotti@gmail.com> wrote:
>
> Yes, the operating system was upgraded from RHEL 7.9 to 8.4.

After a quick check it seems that RHEL 7.X comes with glibc 2.17 and
RHEL 8?X comes with glibc 2.28.  So unfortunately all your indexes
using collatable datatypes are corrupted.  See
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html for
more details.

> Does this mean that I will do reindex operation on all indexes in the system?

Only indexes using collatable datatypes.

> Or how can I detect corrupted indexes due to upgrade?

That's not easy, as you have to check all directly used columns, but
also expression and predicates.  You could use amcheck to detect
corrupted indexes, but it will be quite costly.

If you're not sure of how to do that, a database-wide REINDEX on each
database is safer.

> Also shouldn't it be seen in the log in case of corruption, like this  "ERROR: could not read block xxx in file".

Unfortunately no, because it's a "logical corruption", due to an
underlying ordering change.