Postgres Pro
Downloads
Home   >   mailing lists

Query returns no results until REINDEX - Mailing list pgsql-general

From Colin Adler
Subject Query returns no results until REINDEX
Date
Msg-id CAJH166aaj3xtQH2shdZ7_NZ+iAATKm69Hd_smD=ZBtWjXZH+HQ@mail.gmail.com
Whole thread Raw
Responses Re: Query returns no results until REINDEX
List pgsql-general
Tree view
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 by date:

Previous
From: Sam Gendler
Date:
Subject: Re: Fwd: sensible configuration of max_connections
Next
From: Peter Geoghegan
Date:
Subject: Re: Query returns no results until REINDEX