Thread: Re: Problem with constraint unique.
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. Check if your hardware has problems. Yours, Laurenz Albe
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.
Regards,
Mani.
On Thu, 12 Dec, 2024, 12:17 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
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.
Check if your hardware has problems.
Yours,
Laurenz Albe
On Thu, Dec 12, 2024 at 12:52 PM Mani Sankar <manisankar01695@gmail.com> wrote:
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.
session_replication_role can disable triggers and rules, but primary keys will still be enforced.
Cheers,
Greg
Yeah correct got confused with PK and FK
Thanks, for correcting
Regards,
Mani.
On Fri, 13 Dec, 2024, 12:05 am Greg Sabino Mullane, <htamfids@gmail.com> wrote:
On Thu, Dec 12, 2024 at 12:52 PM Mani Sankar <manisankar01695@gmail.com> wrote: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.session_replication_role can disable triggers and rules, but primary keys will still be enforced.Cheers,Greg