0

I have a SQL table that looks like this (irrelevant parts excluded):

# \d users
                                          Table "public.users"
     Column     |            Type             | Collation | Nullable |              Default
----------------+-----------------------------+-----------+----------+-----------------------------------
 id             | integer                     |           | not null | nextval('users_id_seq'::regclass)
 name           | character varying(64)       |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_name_key" UNIQUE CONSTRAINT, btree (name)

Now I encountered this very strange behavior, where I cannot select a specific row (of which I know it's there). However, if I use functions like trim, lower or also ILIKE (without wildcards) then the select works. All of those functions do not alter the value I filter for. It's already lower-case, trimmed & completely ASCII:

-- Does not work (no results returned):
SELECT * FROM users WHERE name = 'user@somedomain.com';
SELECT * FROM users WHERE name LIKE 'user@somedomain.com';

-- Works (selects the expected row):
SELECT * FROM users WHERE name LIKE '%user@somedomain.com';
SELECT * FROM users WHERE name LIKE 'user@somedomain.com%';
SELECT * FROM users WHERE name ILIKE 'user@somedomain.com';
SELECT * FROM users WHERE lower(name) = 'user@somedomain.com';
SELECT * FROM users WHERE trim(name) = 'user@somedomain.com';

Attempts to export the value as bytea also did not show things like a nul-byte or other unprintable characters. The curious part is that the query used to work and still works on another machine that has the exact same PostgreSQL version and data (replicated from the machine that does not work). I use PostgreSQL version 14.0, as container from the official Docker Hub account (not Alpine).

Any clues?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Sahib
  • 223
  • 1
  • 10
  • could you create a db fiddle: https://www.db-fiddle.com ? – Andronicus Feb 28 '22 at 12:49
  • 2
    Try to re-create the index: `reindex index users_name_key;` But it's more likely you have some non-printable characters inside your values (e.g. CR or a tab or something similar) –  Feb 28 '22 at 12:50
  • @a_horse_with_no_name: ``reindex`` did the trick! Thanks! But... why? CR or tab: I did check that, I'm sure there are none. – Sahib Feb 28 '22 at 13:13
  • 3
    If a reindex did the trick, you had a corrupted index on the column. Did you recently upgrade the database or the C library on the system? – Laurenz Albe Feb 28 '22 at 13:15
  • A few months back we jumped from 13.0 to 14.0. The libc below did change even longer ago during a switch from the Alpine version of the image to the Debian based one. I'm not exactly sure when the issue first started, so can't say for sure which of the two events were the cause. I now did a `reindexdb -a` to make sure all of them are rebuild. – Sahib Feb 28 '22 at 13:23
  • https://wiki.postgresql.org/wiki/Locale_data_changes –  Feb 28 '22 at 13:29
  • Thanks @a_horse_with_no_name. Now I understand - that is definitely something users should keep an eye on. At least something I was not aware of. Probably safest to do a reindex on every db upgrade or image change. – Sahib Feb 28 '22 at 13:43

0 Answers0