På onsdag 03. april 2019 kl. 15:06:03, skrev Felix Ableitner <[email protected]
<mailto:[email protected]>>:
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
<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
Does disabling index-scan make a difference? SET enable_indexscan to off;
How about dumping the relevant data and reloading it into another similar (but
smaller) table, can you reproduce it then? -- Andreas Joseph Krogh CTO / Partner
- Visena AS Mobile: +47 909 56 963 [email protected]
<mailto:[email protected]> www.visena.com <https://www.visena.com>
<https://www.visena.com>
