Some ids are missing from pool_hashes table

[rcmorano]

Brief description

Digging into my testnet deploy, I found there is a gap in the pool_hashes table for ids between 175-206 (both included).

Given that I also tried official explorer while debugging this issue for cardano-graphql and the pool count is exactly the same for that deploy, I guess this is a bug and not some fault/problem-during-sync on my deployment.

Environment

I'm using these official docker images:

• inputoutput/cardano-node:1.20.0
• inputoutput/cardano-db-sync:5.0.1

Steps to reproduce the bug

cdbs=# select id from pool_hash where id >=170 and id <= 210;
 id  
-----
 170
 171
 172
 173
 174
 207
 208
 209
 210
(9 rows)

cdbs=# select count(id) from pool_hash;
 count 
-------
   198
(1 row)

cdbs=# select count(id) from pool_retire;
 count 
-------
    20
(1 row)

cdbs=# 

What is the expected behavior?

Not sure, but I'd say that gap shouldn't be there or if those pools were deleted because they were retired, pool_retire count should match the gap row count and currently is not.

[erikd]

Is this possibly #315 which was fixed after the 5.0.1 release?

[rcmorano]

I'll do a sync from the scratch using master and let you know. Thanks for reviewing it!

[dmitrystas]

I'll do a sync from the scratch using master and let you know. Thanks for reviewing it!

I did, yesterday. It looks a bit different now, but a gap still is

# select id from pool_hash where id >=175 and id <= 235;
 id
-----
 175
 176
 177
 178
 179
 180
 181
 231
 232
 233
 234
 235
(12 rows)

[erikd]

Ok, why do you think these missing ids should even be there? The ids are generated by Postgres, and in the presence of rollbacks, Postgres may end up skipping some.

Is there any other evidence of something being wrong?

[dmitrystas]

No issue for me, I just noted that a gap is still present. I'll check but at the mometn all the pools look good

[erikd]

@rcmorano I do not think this is incorrect. Yes, the id field does not just increment by one, but that is a Postgres generated field and when rollbacks occur previously created id numbers (which are removed during the rollback) are not reused. This is standard Postgres behavior.

[rcmorano]

ACK! I wasn't sure if we were missing some pools because daedalus-testnet showed an amount, db count another and graphql a different one (now clarified by this bug), but it seems it's not the case, so I think we can close the issue :)