Обсуждение: Duplicate keys inserted even with unique constraint.
Hello pgsql-bugs,
I have a table containing a key on two fields, among other indexes and other tables. So I can't insert values where both match an existing pair, that's my expectation. There is an index with the uniqueness constraint, that index was created from the start. All those key-pairs should be unique.
Now i ran an UPDATE .. SET .. WHERE "table"."some_other_field" and that fails with an error message: duplicate key values value violates unique constraint (a, b).. already exists.
I thought, that can't be possible, since I don't even touch the key attributes. I ran a SELECT and there really are duplicate keys in the table. How? Why?
I can't find anything on the web. Most people just get that error message when they try to INSERT duplicate values. I on the other hand really have duplicate values and found them because UPDATE failed.
The table_size is around 59 GB, while the index_size is around 30 GB. Multiple clients access and write at the same time.
Software versions: 9.4 -> 9.6 -> 10.4 (current) using Fedora 26, 27 and 28 (current)
So there was a database upgrade step from 9.x to 10.x.
It appears that postgres let's you store duplicate keys and only complains later when you want to update one of those duplicated key rows, even when you don't touch the key attributes.
That may be a bug, or I made some other mistake I didn't catch.
Threads and a database containing several GBs of data may be needed to reproduce it.
The settings are the defaults for version 10.4, plus additional custom settings, which were generated using the pgtune config generator with those parameters:
# DB Version: 10
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 64 GB
# CPUs num: 8
# Data Storage: ssdThat's actually all I can say at the moment.
Regards.
On Wed, Aug 15, 2018 at 10:34 AM, Gam Er <gamero.mails@gmail.com> wrote: > I have a table containing a key on two fields, among other indexes and other > tables. So I can't insert values where both match an existing pair, that's > my expectation. There is an index with the uniqueness constraint, that index > was created from the start. All those key-pairs should be unique. > > Now i ran an UPDATE .. SET .. WHERE "table"."some_other_field" and that > fails with an error message: duplicate key values value violates unique > constraint (a, b).. already exists. > > I thought, that can't be possible, since I don't even touch the key > attributes. I ran a SELECT and there really are duplicate keys in the table. > How? Why? It's probably some kind of data corruption. It's not impossible that it's caused by a known bug, or even an unknown one. Can you run amcheck on the affected index? It sounds like the "heapallindexed" check [1] would be most likely to further isolate the problem here, which is something that the v10 contrib/amcheck doesn't have. You may wish to install the externally distributed amcheck from the PGDG repo (sometimes called "amcheck_next"), in order to be able to use this additional capability. [1] https://www.postgresql.org/docs/devel/static/amcheck.html#id-1.11.7.11.8 -- Peter Geoghegan