Duplicate entries for pools for epoch 568 on mainnet in pool_stat table

[gregbgithub]

OS
Your OS:
Ubuntu 24.04

Versions
The db-sync version (eg cardano-db-sync --version): 13.6.0.5, git revision cb61094, while cardano-node is 10.1.4
PostgreSQL version: 17

Build/Install Method
The method you use to build or install cardano-db-sync: prebuilt binaries

Run method
The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): systemd

Additional context
Seems to be similar to previously opened on sanchonet issue - #1901. Reported by users who were using koios and started getting duplicate entries for pools returned by koios pool_info endpoint that does a join with pool_stat on pool_hash_id and epoch_no assuming uniqueness.

Problem Report
Screenshot of sql query output summaring duplicate entries attached

[Cmdv]

Just took a look, seems we have no conflict checks/unique constraints on the table.

Simple fix with SQL would be

INSERT INTO pool_stat (...) 
VALUES (...) 
ON CONFLICT (pool_hash_id, epoch_no) DO NOTHING

Will work out if conflict check or constraint is better solution for this issue.

edit: Should add, this was happening because if the value wasn't in the cache it would just try to insert it with no checks if it already existed.

[kderme]

I was wondering why DBSync tried to reinsert them 2 times. Even if there was a rollback, old values should be deleted, so after reinsertion there shouldn't be any duplicates.