skip to Main Content

I have this table:

dbchatbot=# d messages
                            Table "public.messages"
     Column      |            Type             | Collation | Nullable | Default 
-----------------+-----------------------------+-----------+----------+---------
 uuid            | character varying(36)       |           | not null | 
 content         | text                        |           |          | 
Indexes:
    "messages_pkey" PRIMARY KEY, btree (uuid)
    "messages_uuid_idx" UNIQUE, btree (uuid)

*(I know I should be using a uuid type field, but I am inheriting this table and I can not change this.)

Some times when I am querying by uuid the match fails:

select uuid from messages where uuid = '3UUkCMIw3IzrYuwLnezDvL';
 uuid 
------
(0 rows)

It works if I use ilike:

select uuid from messages where uuid ilike '3UUkCMIw3IzrYuwLnezDvL';
          uuid          
------------------------
 3UUkCMIw3IzrYuwLnezDvL
(1 row)

Or if I trim the field value:

select uuid from messages where trim(uuid) = '3UUkCMIw3IzrYuwLnezDvL';
          uuid          
------------------------
 3UUkCMIw3IzrYuwLnezDvL
(1 row)

It doesn’t fail in all records of the table:

select uuid from messages where uuid = '3wvMFFjZLD4aIsj64yLScA';
          uuid          
------------------------
 3wvMFFjZLD4aIsj64yLScA
(1 row)

I can not use ilike or trim because I am using a ORM and I don’t want to mess up with the primary keys default search.

How can I fix the issue?

Update

Another example to demonstrate that there is not leading or trailing spaces:

select "uuid", length("uuid"), '>' || "uuid" || '<' AS debug_uuid  from messages where "uuid" ilike '3UUkCMIw3IzrYuwLnezDvL';
          uuid          | length |        debug_uuid        
------------------------+--------+--------------------------
 3UUkCMIw3IzrYuwLnezDvL |     22 | >3UUkCMIw3IzrYuwLnezDvL<
(1 row)

More weird the db doesn’t return any record by this filter:

select uuid from messages where uuid <> trim(uuid);
 uuid 
------
(0 rows)

Update 2

More tests:

select uuid, length(uuid), length(trim(uuid)) from messages where length(uuid) <> length(trim(uuid));
 uuid | length | length 
------+--------+--------
(0 rows)
select uuid, encode(uuid::bytea, 'hex') as uuid_hex, encode('3UUkCMIw3IzrYuwLnezDvL', 'hex') as uuid_hex  from messages where trim(uuid) = '3UUkCMIw3IzrYuwLnezDvL';
          uuid          |                   uuid_hex                   |                   uuid_hex                   
------------------------+----------------------------------------------+----------------------------------------------
 3UUkCMIw3IzrYuwLnezDvL | 3355556b434d497733497a725975774c6e657a44764c | 3355556b434d497733497a725975774c6e657a44764c
(1 row)

Update3

It is not the field. It is the index that has an issue. If I deactivate the indexes the query returns the record:

BEGIN;

SET LOCAL enable_indexscan = OFF;
SET LOCAL enable_bitmapscan = OFF;

select uuid from messages where uuid = '3UUkCMIw3IzrYuwLnezDvL';
          uuid          
------------------------
 3UUkCMIw3IzrYuwLnezDvL
(1 row)

COMMIT;

2

Answers


  1. Chosen as BEST ANSWER

    The index was corrupted. Reindexing the table fixed the issue:

    REINDEX TABLE messages;
    REINDEX
    select uuid from messages where uuid = '3UUkCMIw3IzrYuwLnezDvL';
              uuid          
    ------------------------
     3UUkCMIw3IzrYuwLnezDvL
    

  2. It seems that some UUIDs have useless leading or trailing spaces. Just fix them:

    update messages set
    uuid = trim(uuid)
    where uuid <> trim(uuid);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search