Weird behavior with unique constraint not respected, and random results on same queries

From: Thomas SIMON <tsimon(at)neteven(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Weird behavior with unique constraint not respected, and random results on same queries
Date: 2020-10-12 15:43:29
Message-ID: 01d4165c-201a-971f-b1e4-acea04872238@neteven.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Magnus Hagander 2020-10-12 15:53:47 Re: Weird behavior with unique constraint not respected, and random results on same queries
Previous Message David G. Johnston 2020-10-12 04:05:06 Re: Upgrade question