Обсуждение: BUG #16938: Duplicate records with same Primary Key
The following bug has been logged on the website: Bug reference: 16938 Logged by: Susana Castela Ibáñez Email address: susanacastela@hotmail.com PostgreSQL version: 13.2 Operating system: Ubuntu Description: Hi, We're running an instance of postgres 13.2 and some days ago we noticed duplicated rows with the same primary key. This was noticed restoring a pg_dump of our db that would be failing because it tried to insert a duplicate primary key that violated the primary key restriction. Our live aplication showed no signs of error during this time. The issue is very similar to what is described in this discussion: https://dba.stackexchange.com/questions/62675/why-does-my-table-hold-duplicate-primary-keys Running a "SELECT primary_key FROM table GROUP BY primary_key HAVING COUNT(*) > 1" would be yielding the affected records. We would be pleased to know if this is a known bug or what could be the cause of it, also a definitive solution for it (we removed the conflictive rows of the table but the issue reapeared after some days). For the time being we're going to try to reindex our database. Thanks a lot.
PG Bug reporting form <noreply@postgresql.org> writes: > We're running an instance of postgres 13.2 and some days ago we noticed > duplicated rows with the same primary key. If the key column is of a text type, you might have gotten bit by operating-system-level changes of sorting rules, which would effectively make indexes on text columns corrupt [1]. If so, reindexing is the appropriate fix. There's work in progress to try to notice this sort of problem automatically, but it's not done yet. regards, tom lane [1] https://wiki.postgresql.org/wiki/Locale_data_changes