Query returns no results until REINDEX

Поиск
Список
Период
Сортировка
От Colin Adler
Тема Query returns no results until REINDEX
Дата
Msg-id CAJH166aaj3xtQH2shdZ7_NZ+iAATKm69Hd_smD=ZBtWjXZH+HQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query returns no results until REINDEX  (Peter Geoghegan <pg@bowt.ie>)
Список pgsql-general
Дерево обсуждения
Hi all,

Earlier today we were trying to debug why a row wasn't being deleted from a
table and we ran into some interesting behavior.

This is the table in question:

coder=# \d+ extensions
    Table "public.extensions"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
----------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
ext_name       | text                     |           | not null |         | extended |              |
publisher_name | text                     |           | not null |         | extended |              |
ext_version    | text                     |           | not null |         | extended |              |
obj            | jsonb                    |           | not null |         | extended |              |
created_at     | timestamp with time zone |           | not null |         | plain    |              |
updated_at     | timestamp with time zone |           | not null |         | plain    |              |
Indexes:
   "extensions_pkey" PRIMARY KEY, btree (ext_name, publisher_name, ext_version)
Foreign-key constraints:
   "extensions_ext_name_fkey" FOREIGN KEY (ext_name, publisher_name) REFERENCES extension_statistics(ext_name, publisher_name) ON DELETE CASCADE
Referenced by:
   TABLE "extension_assets" CONSTRAINT "extension_assets_ext_name_fkey" FOREIGN KEY (ext_name, publisher_name, ext_version) REFERENCES extensions(ext_name, publisher_name, ext_version) ON DELETE CASCADE

coder=# select ext_name from extensions;
    ext_name
-------------------
vim
Go
min-theme
terraform
prettier-vscode
vscode-icons
gitlens
vscode-eslint
cpptools
rust
Angular-BeastCode
(11 rows)

We start to run into issues when querying for the "Go" extension.

coder=# select ext_name from extensions where ext_name = 'Go';
ext_name
----------
(0 rows)

Other extensions seem to be queried just fine.

coder=# select ext_name from extensions where ext_name = 'Angular-BeastCode';
    ext_name
-------------------
Angular-BeastCode
(1 row)

Using LIKE seems to find the broken row fine.

coder=# select ext_name from extensions where ext_name LIKE '%Go';
ext_name
----------
Go
(1 row)

That then begs the question, maybe there's some weird whitespace in front
causing it to fail.

coder=# select encode(ext_name::bytea, 'hex') from extensions where ext_name LIKE '%Go';
encode
--------
476f
(1 row)

Doesn't seem like it. After a bit of confusion I thought to reindex the table.

coder=# reindex table extensions;
REINDEX
coder=# select ext_name from extensions where ext_name = 'Go';
ext_name
----------
Go
(1 row)

Seems to work now. My question is, is this something I should report to the
maintainers? I took a snapshot of the data folder before the reindex in case it
would be helpful. Is index corruption something that should be actively looked
out for?

It's worth noting this particular row has existed in the database for quite a
long time. Probably over 3 months.

Thanks for taking a look,
Colin

В списке pgsql-general по дате отправления:

Предыдущее
От: Sam Gendler
Дата:
Сообщение: Re: Fwd: sensible configuration of max_connections
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: Query returns no results until REINDEX