Обсуждение: SQL queries not matching on certain fields

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 | emma58672 | 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

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

> On Apr 3, 2019, at 2:06 PM, Felix Ableitner <me@nutomic.com> wrote:
>
> Hello,
>
> I'm having a very strange problem with the Postgres database for my website. Some SQL queries are not matching on
certainfields. 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
SHOWSERVER_ENCODING and SHOW SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected. And I checked
theindividual 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
problembefore on the same setup, so I did an export to text file with pg_dump, and imported into a completely new
databasewith 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
otherrows 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
scratchand see if it fixes it but corrupted indexes aren't normal, and the issue seems to be recurring. On physical
hardwareI'd be wondering about filesystem corruption and taking a good look at my system logs. On someone else's VPS
youdon'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
whatis going on. 
>
> Best,
> Felix Ableitner
>

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

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com