Inconsistent (missing) "reserve", "treasury" reward in reward table

[kikai-user]

OS
Your OS: Ubuntu 20.04.3 LTS

Versions
cardano-db-sync version: 15a434b
PostgreSQL version: 12.8

Build/Install Method
nix-build -A cardano-db-sync -o db-sync-node

Problem Report
I believe that some "reserve" and "treasury" rewards are missing from reward table. I think the number of rows in reserve table should be equal to the number of rows in the reward table with type = 'reserves'. However, I got different results:

cexplorer=# select count(*) from reward where type = 'reserves';
 count 
-------
 54823
(1 row)

cexplorer=# select count(*) from reserve;
 count 
-------
 86906
(1 row)

I tried to investigate a bit further and found an example of what's missing. It's the reserve reward from transaction \xad664478fbff01cdb64d555ab6896c4370c337cda35c416f5c898fb15bcdafd5:

cexplorer=# select A.amount, A.spendable_epoch from reward A, stake_address B 
cexplorer-# where A.addr_id = B.id 
cexplorer-#       and type = 'reserves' 
cexplorer-#       and B.view = 'stake1u8s27etuhx4nt69cwd9wev3h3mrxkph9cwmhyuqchmfmdcc9e050h';
  amount  | spendable_epoch 
----------+-----------------
 22359414 |             286
(1 row)

cexplorer=# select A.amount, B.hash from reserve A, tx B, stake_address C
cexplorer-# where A.tx_id = B.id and A.addr_id = C.id
cexplorer-#       and C.view = 'stake1u8s27etuhx4nt69cwd9wev3h3mrxkph9cwmhyuqchmfmdcc9e050h';
  amount  |                                hash                                
----------+--------------------------------------------------------------------
 72822172 | \xad664478fbff01cdb64d555ab6896c4370c337cda35c416f5c898fb15bcdafd5
 22359414 | \xfb8e870a76dbd64999d986dead30815152307c3cdc7df1f6cef05a8cdc942f8e
(2 rows)

I found the same issue with the treasury reward:

cexplorer=# select count(*) from treasury;
 count 
-------
 24029
(1 row)

cexplorer=# select count(*) from reward where type = 'treasury';
 count 
-------
 24026
(1 row)

I might be wrong about the stuffs each table tracking. But, if you group all kinds of rewards into the reward table, you can get rid of two tables treasury and reserve, right?

[erikd]

I might be wrong about the stuffs each table tracking. But, if you group all kinds of rewards into the reward table, you can get rid of two tables treasury and reserve, right?

No, its still needed. The IOHK devops team uses the reserve and treasury table to validate that payments are correct.

I believe that some "reserve" and "treasury" rewards are missing from reward table. I think the number of rows in reserve table should be equal to the number of rows in the reward table with type = 'reserves'.

The reward table is much more likely to be correct because the accounting of the values in that table have undergone MUCH more scrutiny.

I THINK what is happening is that more than one payment is made from the same pot go to the same address, only the last is actually kept. I am trying to get that confirmed.

[erikd]

I THINK what is happening is that more than one payment is made from the same pot go to the same address, only the last is actually kept. I am trying to get that confirmed.

Yes, confirmed for eras before Alonzo. For Alonzo and later they are summed.

[erikd]

I think the correct fix for this is to make db-sync mirror the behavior of the ledger as closely as possible. For epochs before Alonzo, when an address receives two or more payments from the same source, the last should overwrite all previous payments.

All tables except for one (epoch) are immutable (ignoring rollbacks) which is nice from a state consistency point of view.

I think the best solution here is to document the oddities of these two tables and otherwise leave them as they are.

[WubbleWobble]

Sorry for commenting on this old issue, but I was hoping you could help provide a little clarity.

I have found a pre-Alonzo entry (addr_id 1237210 / epoch 285) where the reserve table has two payments, and indeed only the last of those is reflected in the rewards table as per your explanation above.

Does the rewards table then provide the actual true representation of what happened on the blockchain with regards to payments made?

(i.e. Only one payment was actually made (the latter); The reserve table shows two intended payments on the same epoch, but only one was actioned?)

[erikd]

@WubbleWobble This should probably be its own issue.

I have found a pre-Alonzo entry (addr_id 1237210 / epoch 285) where the reserve table has two payments, and indeed only the last of those is reflected in the rewards table as per your explanation above.

Before Alonzo, only a single payment to any address is allowed per epoch. That would explain what you are seeing. I think this changed in a later epoch.

[WubbleWobble]

@erikd Brilliant - previously I just wasn't sure whether what you were describing pertained to Cardano, or a quirk of db-sync, so this has cleared things up for me.

Many thanks! :)

[erikd]

@WubbleWobble can this be closed now?

[WubbleWobble]

@erikd Yes - thank you (it's already closed?)