Weird behavior with unique constraint not respected, and random results on same queries
От | Thomas SIMON |
---|---|
Тема | Weird behavior with unique constraint not respected, and random results on same queries |
Дата | |
Msg-id | 01d4165c-201a-971f-b1e4-acea04872238@neteven.com обсуждение исходный текст |
Ответы |
Re: Weird behavior with unique constraint not respected, and random results on same queries
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: