To: pgsql-admin@lists.postgresql.org
From: Thomas SIMON <tsimon@neteven.com>
Subject: Weird behavior with unique constraint not respected, and random
 results on same queries
Message-ID: <01d4165c-201a-971f-b1e4-acea04872238@neteven.com>
Date: Mon, 12 Oct 2020 17:43:29 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.3.2
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: fr
Precedence: bulk

Hi all,

I encounter strange behavior since a few days, and the promote of a 
recently installed server as master in my infrastructure (debian 10, was 
added as slave a few days ago)
I use postgresql 12.4 on master an slave.

I have unicity constraint on an items table, based on 2 fields :
     "items_account_id_key" UNIQUE CONSTRAINT, btree (account_id, sku)

Since the promote, I find on database some duplicated entries , despite 
the fact that the constraint should not allow this.
It seems to only happens with entries having "special" characters 
(understand " ", "-", "+", ...)

Example of duplicated entry:
account_id sku item_id
1234 "IP6S+64SILHA+" 45231
1234 "IP6S+64SILHA+" 478212

I've tried to insert manually other "duplicated" data, it sometimes 
works, and sometimes I got (normal) error of duplicated key entry.

(Other) strange thing is when I request database with only sku field, I 
got all duplicated entries (for example, say 10)
SELECT account_id,sku
FROM items
WHERE sku = 'IP6S+64SILHA+'
--> 10results

When I request database with account_id and sku, some entries aren't 
returned (of course, all these values belongs to requested account_id)
SELECT account_id,sku
FROM items
WHERE sku = 'IP6S+64SILHA+'
AND account_id = 1234;
--> 1 result

Using this same request a few time later, I had 9 results... (on the 10 
"real" entries in db)

And sometimes new added entries does not show up :

 > SELECT *
   FROM items
   WHERE account_id = 1234
   and sku = 'IP6S+64GRLMB'
[2020-10-12 13:37:32] 0 rows retrieved in 110 ms (execution: 30 ms, 
fetching: 80 ms)
 > INSERT INTO public.items (item_id, account_id, item_id) VALUES (1234, 
'IP6S+64GRLMB', 45231)
[2020-10-12 13:38:01] 1 row affected in 76 ms
 > SELECT *
   FROM items
   WHERE account_id = 1234
   and sku = 'IP6S+64GRLMB'
[2020-10-12 13:38:01] 0 rows retrieved in 66 ms (execution: 25 ms, 
fetching: 41 ms)

I've checked locales on new server, thinking that it could be related 
with the "special" characters problem, but I didn't see something 
relevant. (I'm using en_US.UTF-8).
I guess it is related to this new server, but on postgres side, I use 
same config as old master excepted some memory parameters, and 
pg_stat_statement acvivation, and on OS side, I don't know what to look for.

I'm a little overwhelmed by all of this, do you have any idea what the 
problem is?

Best regards
thanks
thomas