From: Thomas SIMON <tsimon@neteven.com>
To: pgsql-admin@lists.postgresql.org
Subject: Weird behavior with unique constraint not respected, and random results on same queries
Date: Mon, 12 Oct 2020 17:43:29 +0200
Message-ID: <01d4165c-201a-971f-b1e4-acea04872238@neteven.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tsimon@neteven.com, pgsql-admin@lists.postgresql.org
Subject: Re: Weird behavior with unique constraint not respected, and random results on same queries
In-Reply-To: <01d4165c-201a-971f-b1e4-acea04872238@neteven.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox