text column compares equal to where clause but does not select matching row

Question

We are having trouble when querying a table in our production database. One text column will compare equal to a string we filter on in the where clause, but postgres will not select the row. (We are on postgres 11.11) Our table set up like this:

(PROD)=> \d names;
                           Table "public.names"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 name                 | text                        |           | not null |
 processed_name       | text                        |           | not null |
 name_index           | integer                     |           | not null |
 when_created         | timestamp without time zone |           | not null |
Indexes:
    "names_pkey" PRIMARY KEY, btree (name, processed_name)
    "names_name_index_key" UNIQUE CONSTRAINT, btree (name_index)
    "ix_names_name" btree (name)
    "ix_names_processed_name" btree (processed_name)

When we process a list of names we check if they are already in the table in order to prevent double adding and violating the primary key constraint.

However on one name, 'Сергей Иванович МЕНЯЙЛО', the query to see if the name is already present returns an empty set
I would expect to get back the row with the same name. However when we attempt to insert the row in the table we get a primary key conflict

Here are some queries which might explain the problem better

(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names where name_index = 75128;
      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

However filtering instead on the name column selects no rows.

2021-05-24 20:37:41 UTC
(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' 
    names_compare_equal 
FROM names 
WHERE name = 'Сергей Иванович МЕНЯЙЛО';

       name_index     | name | names_compare_equal
----------------------+------+---------------------
(0 rows)

So then if we try and insert the row we get a primary key conflict:

(PROD)>=> INSERT INTO names (name_index, name, processed_name, when_created) 
  VALUES (89266, 'Сергей Иванович МЕНЯЙЛО', lower('Сергей Иванович МЕНЯЙЛО'), now());

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name, processed_name)=(Сергей Иванович МЕНЯЙЛО, сергей иванович меняйло) already exists.

What's more, if I query based on the hash of the rows I get the correct result:

(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names 
WHERE md5(name) = md5('Сергей Иванович МЕНЯЙЛО');

      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

This only happens on our production database - which has the following encoding settings

      Name      |     Owner      | Encoding |   Collate   |    Ctype    |
----------------+----------------+----------+-------------+-------------+
 PROD DB        | PROD DB OWNER  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

This is pretty baffling to me so an ideas about what to check next would be helpful

Answer 1

A corrupted index would be the prime suspect here. Test with:

SELECT * FROM names WHERE name || '' = 'Сергей Иванович МЕНЯЙЛО';

The expression name || '' cannot use any indexes, so you get a sequential scan. If that query finds your entry, you have your diagnosis: corrupted index. Probably the one on just (name), but since multiple indexes qualify, recheck with EXPLAIN. (It's probably not the PK as that one still throws a unique violation in your test, but that one may be corrupted, too ...)

There is a note in the release notes for Postgres 11.11 in particular:

... see the second changelog item below, which describes cases in which reindexing indexes after the upgrade may be advisable.

Or maybe locales have been updated in your underlying OS? Same fix: reindex.

There are other reasons for index corruption, but the only other common one is hardware issues. That should trigger more drastic measures immediately, starting with a backup.

Recreate affected indexes. You can use REINDEX:

REINDEX INDEX ix_names_name;

If you need to allow concurrent access to the table use the non-blocking (but slower) CONCURRENTLY:

REINDEX INDEX ix_names_name CONCURRENTLY;

If there is reason to believe the problem might be systemic, recreate all indexes on the table:

REINDEX TABLE names;

Or the whole the database:

REINDEX DATABASE name_of_current_database;

In case of a bigger cleanup, I would advise a maintenance window without concurrent access. And plenty of maintenance_work_mem.

Aside, you have these indexes for the two columns name and processed_name:

"names_pkey" PRIMARY KEY, btree (name, processed_name)
"ix_names_name" btree (name)
"ix_names_processed_name" btree (processed_name)

The PK index on (name, processed_name) can be used for everything that the additional index on just (name) can be used. That additional index only might be useful if processed_name is a rather large column - in which case I would consider a more efficient PK to begin with. See:

• Is a composite index also good for queries on the first field?