One possible reason I can think of is setting the session_replication_role to replica do some insert that violates the constraints and then set it back to orgin. Or it's still set to replica ?
In this case when it's set to replica no constraint validation will be done.
On Wed, 2024-12-11 at 17:47 +0300, Марат Гасанян wrote: > I apologies for bothering your team, before send this message we google the > problem and was not able to find any tips about it. Our project encounter > with quite strange situation where we found violation of unique constraint > of primary key. We have a table that has an attribute ID as PK. The table > name enerstorymain_task_info. Surprisingly the query > select id, count(*) from enerstroymain_task_info group by 1 having count(*) > 1 > return a lot of rows. Than we rechecked PK it is ok. So we supposed that > some how we had dropped the PK and then created duplicates and than rebuild > the PK, but in this case PK would not rebuild. To avoid any weird settings > I run the query from PGAdmin and DBView from different computers. > Also I doubt that any kind problem like repeatable red could be the reason > due to insert operation is done only a server without any complicated query. > I mean simple INSERT INTO ….
No, dropping and re-creating the constraint cannot be the explanation, unless the primary key constraint is NOT VALID.
With "uuid", it also cannot be a collation problem.
The best thing to get rid of this data corruption is to delete the extra entries and then to sump and restore the data to a new database.
