> On Apr 3, 2019, at 2:06 PM, Felix Ableitner <me@xxxxxxxxxxx> wrote:
>
> Hello,
>
> I'm having a very strange problem with the Postgres database for my website. Some SQL queries are not matching on certain fields. I am running these commands via the psql command.
>
> Here is a query that works as expected:
>
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='emma';
> id | preferredUsername
> -------+-------------------
> 48952 | emma
> 58672 | emma
> (2 rows)
>
> The following query should work as well, because the username exists. But in fact, it consistently returns nothing:
>
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='mailab';
> id | preferredUsername
> ----+-------------------
>
> (0 rows)
>
> There are some workarounds which fix the WHERE statement, all of the following work as expected:
>
> SELECT id, "preferredUsername" FROM actor WHERE trim("preferredUsername")='mailab';
>
>
>
> SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 'mailab';
>
> SELECT id, "preferredUsername" FROM actor WHERE md5("preferredUsername")=md5('mailab');
>
>
> Now you might think that there is something wrong with the encoding, or the field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected. And I checked the individual bytes with get_byte(), all of them are in the range 97-122.
>
> About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS (see below for all versions etc). I had this problem before on the same setup, so I did an export to text file with pg_dump, and imported into a completely new database with psql. That fixed the problem for a few days, but it came back soon after.
>
> The problem only seems to affect one or two specific columns, and only a few specific rows in those columns. Most other rows work normally. Affected columns also randomly start working again after a few days, and other columns get affected. I havent noticed any kind of pattern.
>
> You can find the table definition here: https://gitlab.com/snippets/1840320
You can use "explain" to see what plan is being used for the query, but I'm betting that it's using an index on preferredUsername. Your workarounds won't use that index, they'll scan the table.
If that index is corrupted it could cause the symptoms you're seeing. You can use "reindex" to rebuild the index from scratch and see if it fixes it but corrupted indexes aren't normal, and the issue seems to be recurring. On physical hardware I'd be wondering about filesystem corruption and taking a good look at my system logs. On someone else's VPS you don't have the same visibility, but I'd still check the system logs for issues.
Cheers,
Steve
>
> Version info:
>
> Postgres Docker Image: postgres:10.7-alpine
> Docker version: 18.09.2
> OS: Ubuntu 18.04.2
>
> Please tell me if you have any idea how to fix or debug this. I already asked multiple people, and no one has a clue what is going on.
>
> Best,
> Felix Ableitner
>
