0

I am running a Postgresql database with docker-compose, using the image postgres:10-alpine. For some reason, the database is unable to select certain rows based on string comparison. For example, this query works as expected:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user1';
  id   | preferredUsername 
-------+-------------------
 38793 | user1             
(1 row)

However, the same query is not working for another user:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user2';
 id | preferredUsername 
----+-------------------
(0 rows)

The user definitely exists, and the query works if I use ILIKE instead of =:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 'user2'; 
  id   | preferredUsername 
-------+-------------------
 41576 | user2
(1 row)

What could be the reason for this wrong behaviour? I suspected it might have something to do with the encoding, but SHOW SERVER_ENCODING and SHOW CLIENT_ENCODING both show UTF8. I also tried to export the data and import it into a fresh database. That fixed the problem for a few days, but it came back after that.

I'm happy about any possible solutions or debugging ideas.

Edit: Some more queries, it works with trim() in the WHERE clause:

peertube=# SELECT "preferredUsername", "preferredUsername" FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | btrim  |               md5
-------------------+--------+----------------------------------
 mailab            | mailab | 3d83ba6a9e5391c0c4d0253fbb2b01aa

However, this still seems weird because if there is whitespace, the hash of the field should be different. In fact, the md5 is the same:

peertube=# SELECT "preferredUsername", md5("preferredUsername"), md5('mailab') FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername |               md5                |               md5
-------------------+----------------------------------+----------------------------------
 mailab            | 3d83ba6a9e5391c0c4d0253fbb2b01aa | 3d83ba6a9e5391c0c4d0253fbb2b01aa

Edit 2: Length is the same with or without trim, so that seems to rule out the whitespace theory:

peertube=# SELECT "preferredUsername", length("preferredUsername"), length(trim("preferredUsername")) FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | length | length
-------------------+--------+--------
 mailab            |      6 |      6

Edit 3: Details of the affected table: https://gitlab.com/snippets/1840320

Edit 4: Someone suggested the get_byte() function to check that there are no unexpected utf8 characters. And in fact, every single character corresponds to an ascii value in the range 97-122.

migrated from serverfault.com Mar 29 at 13:01

This question came from our site for system and network administrators.

  • 1
    Any white spaces after the user2 value in the table ? Have you tried trimming the value in the table ? – armitage Mar 29 at 13:28
  • 1
    Could be a corrupted index due to binary-migrating the index across systems with incompatible libc collations. The weird thing is you fix it by a dump-reload and the problem comes back after a few days? – Daniel Vérité Mar 29 at 13:50
  • @armitage Good idea, the query is working with that. But it is still weird because the hashes are the same (see my edit). – Nutomic Mar 29 at 14:13
  • @DanielVérité Exactly, I dumped to plain text, and after that everything was fine. It is possible that this is some kind of application bug. – Nutomic Mar 29 at 14:15
  • 1
    Questions about the corrupted index theory: once it happens, is it always reproducible? Is the column indexed? If the failing query is run with SET enable_indexscan to off, does it give the same result? What is the collation of the column? – Daniel Vérité Mar 29 at 15:42
1

I would check and see if there are any white spaces after the Value 'User2' in the database.

I would also look at the application inserting the data and if possible profile the data stream before insertion to determine if the application is incorrectly adding white spaces.

Alternatively I would look at the source of the 'User2' and ensure that the source does not contain white spaces.

  • Do you know any command that I can use to show whitespace in the database field? I am already talking with the application developer to debug this. – Nutomic Mar 29 at 14:48
  • 1
    I'm not a PostgreSql expert but could you get the length of the data in the field and compare to the length of the trimmed data in the field? If there is a difference you have white space. I've also seen this with hidden control characters. – armitage Mar 29 at 14:56
  • Turns out length is exactly the number of visible characters, so that seems to rule out the whitespace theory (see my last edit). – Nutomic Mar 29 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.