search

IntersectMBO / cardano-db-sync

A component that follows the Cardano chain and stores blocks and transactions in PostgreSQL
Apache License 2.0
293 stars 160 forks source link

The rewards was distributed to non-registered stake address #415

Closed dmitrystas closed 3 years ago

dmitrystas commented 3 years ago

Mainnet, db-sync 6.0.1, stake address stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86

registration history

SELECT stake_registration.addr_id, block.epoch_no, block.time
FROM stake_address
LEFT JOIN stake_registration ON stake_registration.addr_id = stake_address.id
LEFT JOIN tx ON tx.id = stake_registration.tx_id
LEFT JOIN block ON block.id = tx.block_id 
WHERE stake_address.view = 'stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86';

 addr_id | epoch_no |        time
---------+----------+---------------------
   38278 |      208 | 2020-08-01 07:45:51
   38278 |      233 | 2020-12-01 22:31:13
(2 rows)

de-registration history

SELECT stake_deregistration.addr_id, block.epoch_no, block.time
FROM stake_address
LEFT JOIN stake_deregistration ON stake_deregistration.addr_id = stake_address.id
LEFT JOIN tx ON tx.id = stake_deregistration.tx_id
LEFT JOIN block ON block.id = tx.block_id 
WHERE stake_address.view = 'stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86';

 addr_id | epoch_no |        time
---------+----------+---------------------
   38278 |      232 | 2020-12-01 18:25:59
(1 row)

as we can see, the address has not been registered at the end of the 232 epoch, and the rewards for the epoch 231 should not be distributed to this address, but

SELECT reward.*, block.epoch_no AS block_epoch_no, block.time
FROM reward
LEFT JOIN block ON block.id = reward.block_id 
WHERE reward.addr_id = 38278 AND reward.epoch_no = 231;

   id    | addr_id |  amount   | epoch_no | pool_id | block_id | block_epoch_no |        time
---------+---------+-----------+----------+---------+----------+----------------+---------------------
 1041508 |   38278 | 192969560 |      231 |     144 |  5023676 |            233 | 2020-12-01 21:44:51
(1 row)
erikd commented 3 years ago

:facepalm:

Looked at this for a while and then suddenly realized that the first query does not show what @dmitrystas thinks it shows. By joining on tx.id = stake_registration.tx_id and then getting the block.epoch_no from the transaction, you get the epoch that the stake registration/deregistration occurs, but not when it is actually active.

For most operations related to staking, actions (like registrations, increases in amount staked etc) that occur in epoch N become active in epoch N + 2 .

That means the first query should actually be:

cexplorer=# SELECT stake_registration.addr_id, block.epoch_no + 2 as epoch_no, block.time FROM stake_address 
              INNER JOIN stake_registration ON stake_registration.addr_id = stake_address.id 
              INNER JOIN tx ON tx.id = stake_registration.tx_id INNER JOIN block ON block.id = tx.block_id
              WHERE stake_address.view = 'stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86';
addr_id | epoch_no |        time         
---------+----------+---------------------
   38266 |      210 | 2020-08-01 07:45:51
   38266 |      235 | 2020-12-01 22:31:13

and the second:

cexplorer=# SELECT stake_deregistration.addr_id, block.epoch_no + 2 as epoch_no, block.time FROM stake_address
              INNER JOIN stake_deregistration ON stake_deregistration.addr_id = stake_address.id
              INNER JOIN tx ON tx.id = stake_deregistration.tx_id INNER JOIN block ON block.id = tx.block_id
              WHERE stake_address.view = 'stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86';
 addr_id | epoch_no |        time         
---------+----------+---------------------
   38266 |      234 | 2020-12-01 18:25:59

@dmitrystas Does that all add up? Would it help adding an extra active_epoch_no column to the stake_registration and stake_deregistration tables like I have the the delegation table?

IlyaSofronov commented 3 years ago

@erikd Good day. That is original issue https://github.com/Emurgo/yoroi-frontend/issues/1832#issue-754776885 Maybe could be usefull

Yoroi & Adalite show wrong 192 FAKE ADA reward but actually it is 0. The last 2 rewards are withdrawn through adawallet.io (it does not use db-sync) image

Short description of the issue in steps: 1) Epoch 232. I withdraw rewards and deregister staking key at the same time. Rewards are 0. I have enough ADA for fees 2) Epoch 233. I should recieve first remaining reward if my key was registered but it is not, but Yoroi shows 192ADA recieved. Yoroi throws an error when I try to withdraw. Other wallets that dont use db-sync show reward 0ADA 3) Epoch 233. I delegate to the same pool again. Key is registered. True rewards 0. Yoroi rewards 192. 4) Epoch 234. I recieve second remaining reward 135ADA. True reward balance is 135ADA. Yoroi shows 327ADA (192+135). Can not withdraw any reward through Yoroi I have the Error received from server while sending transaction. 5) Epoch 235. I recieve the last third reward 145ADA. True reward balance is 280ADA (135+145). Yoroi shows 472ADA (192+135+145) Can not withdraw any reward through Yoroi I have the Error received from server while sending transaction. 6) Epoch 235. I withdraw 280ADA smoothly through adawallet.io that does not use db-syns and shows correctly reward balance 280ADA 7) After true rewards of 280ADA are withdrawn (remains 0ADA) Yoroi still shows reward balance 192ADA.

erikd commented 3 years ago

Yoroi still shows reward balance 192ADA.

There are at least two possibilities;

I only deal with db-sync. That means that problems need to be presented to me as SQL queries not as information from Yoroi.

Is stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86 the stake key of interest here?

IlyaSofronov commented 3 years ago

@erikd yes the stake key is stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86 Note that not only Yoroi HAS THAT BUG but Adalite.io and adastat.net Theese DO NOT HAVE that bug: cardanoscan.io and adawallet.io

erikd commented 3 years ago

If some online wallets have this bug and others do not, and all of these depend on db-sync then the problem is very unlikey to be in db-sync.

IlyaSofronov commented 3 years ago

@erikd But adawallet.io does not use db-sync and shows correct reward balance (0ADA) and it let me withdraw last rewards

erikd commented 3 years ago

Again that suggests this is a Yoroi bug, not a db-sync bug.

roccomuso commented 3 years ago

Exodus encountered this bug as well.

SebastienGllmt commented 3 years ago

This is definitely a cardano-db-sync bug and not a Yoroi bug given how this affects not just Yoroi but multiple other users of cardano-db-sync as well. cardanoscan and adawallet both do not use cardano-db-sync which is why they aren't affected.

What cardano-db-sync reports

Here is the SQL query that gives the reward history for the address provided by @IlyaSofronov 

select stake_address.hash_raw as "stakeAddress"
      , "totalReward".*

from stake_address

left outer join (
  SELECT addr_id, amount, epoch_no
  FROM reward
) as "totalReward" on stake_address.id = "totalReward".addr_id

where encode(stake_address.hash_raw, 'hex') = 'e1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde'

and gives the following result

 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162720747 |      211
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162485461 |      212
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167344099 |      213
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167043060 |      214
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 200722225 |      215
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 191413549 |      216
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 143111843 |      217
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147836812 |      218
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 154740829 |      219
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145433327 |      220
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165631299 |      221
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151825651 |      222
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 169141624 |      223
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165184716 |      224
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 148899668 |      225
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 157838234 |      226
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 223252352 |      227
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147889017 |      228
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151858721 |      229
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165782849 |      230
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 192969560 |      231
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 135711434 |      232
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145964545 |      233
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    100702 |      235
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    102078 |      236

Note: Sum of these rows is 3,765,004,402‬

Current (incorrect) state from our backend

{
    "remainingAmount": "193172340",
    "rewards": "3765004402",
    "withdrawals": "3571832062",
}

You can see our backend returns the sum of the db-sync rows for the rewards value (will explain why this is the issue later)

You can also verify the withdrawals amount is correct by summing up the values on cardanoscan which we know isn't affected by this bug.

remainingAmount is just rewards - withdrawals

Registration history for the wallet

By looking at the history of this staking key, you can see:

Current state from cardano-node

According to my cardano-node instance, this is the current value inside the staking address

[
    {
        "address": "stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86",
        "delegation": "pool1kkfkdces5mdcyc9dn2hgg3463jggjvw3h89nejjarkz25uavaqu",
        "rewardAccountBalance": 202780
    }
]

So you can see that according to the node, the balance of the wallet is epoch 235 + epoch 236 (100702 + 102078 = 202780)

So what is the issue?

By looking at cardanoscan we can see the full withdrawal history of the wallet. Let's see what each withdrawal corresponds to:

Epoch withdrawn Amount withdrawn db-sync epoch rows included
228 2601.373144 211, 212, ..., 225, 226
230 371.141369 227, 228
232 317.641570 229, 230
234 135.711434 232
235 145.964545 233

HOWEVER You can see epoch 231 in cardano-db-sync (192969560) is not included in any of these!

Sure enough, if you look at our backend response, "remainingAmount": "193172340", and you remove the erroneously included epoch 231, you get 193172340 - 192969560 = 202780 which is the expected result from our fullnode!

Conclusion

Based on my investigation, it seems cardano-db-sync includes row 231 in the reward history result even though it shouldn't.

erikd commented 3 years ago

I simplified the first query and validated that I get same results (I did).

Current (incorrect) state from our backend

I would prefer to get all information from a single source so:

cexplorer=# select sum (amount) from stake_address
        left outer join (SELECT addr_id, amount, epoch_no 
        FROM reward) as "totalReward" on stake_address.id = "totalReward".addr_id
        where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
    sum     
------------
 3765004402

and:

cexplorer=# select sum (amount) from withdrawal where addr_id = 38266;
    sum     
------------
 3571832062

both of which agree with your figures.

Interestingly 3571832062 + 192969560 + 202780 - 3765004402 where 3571832062 is the withdrawal sum and 3765004402 is the reward sum. The other values are; 202780 which is your figure for the reward balance after withdrawals and 192969560 is the rewards for epoch 231.

cexplorer=# select stake_address.view as "stakeAddress", "totalReward".* from stake_address
     left outer join (SELECT addr_id, amount, epoch_no  FROM reward) as "totalReward"
     on stake_address.id = "totalReward".addr_id
     where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde'
     and epoch_no = 231 ;
                        stakeAddress                         | addr_id |  amount   | epoch_no 
-------------------------------------------------------------+---------+-----------+----------
 stake1u838ke609j09ezrgj5nwnplvc7g3cq62pa2n9z0x6w3clhsagtp86 |   38266 | 192969560 |      231

So is that reward for epoch 231 (which db-sync simply pulls out of the ledger state and inserts in the database with zero extra processing) correct? Lets look at the stake registration history for that stake address:

cexplorer=# select stake_address.hash_raw, stake_registration.tx_id, block.epoch_no
    as tx_epoch_no, (block.epoch_no + 2) as active_epoch_no
    from stake_registration inner join stake_address on stake_registration.addr_id = stake_address.id 
    inner join tx on tx.id = stake_registration.tx_id inner join block on tx.block_id = block.id
    where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
                           hash_raw                           |  tx_id  | tx_epoch_no | active_epoch_no 
--------------------------------------------------------------+---------+-------------+-----------------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde | 2449294 |         208 |             210
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde | 3103705 |         233 |             235
(2 rows)

cexplorer=# select stake_address.hash_raw, stake_deregistration.tx_id, block.epoch_no
    as tx_epoch_no, (block.epoch_no + 2) as active_epoch_no from stake_deregistration
    inner join stake_address on stake_deregistration.addr_id = stake_address.id
    inner join tx on tx.id = stake_deregistration.tx_id inner join block
    on tx.block_id = block.id
    where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
                           hash_raw                           |  tx_id  | tx_epoch_no | active_epoch_no 
--------------------------------------------------------------+---------+-------------+-----------------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde | 3101670 |         232 |             234

Note that for both the registration and the deregistration i have included the tx_id (the id of the tx that contained the registration/deregistration), tx_epoch_no (the epoch in which the tx was included) and active_epoch_no (the epoch in which the registration/deregistration becomes active).

Looking at the active epoch numbers, the stake registration should have been active in epochs [210 .. 233] and [235 ..] . This matches the actual rewards for this address:

cexplorer=# select stake_address.hash_raw as "stakeAddress", "totalReward".* from stake_address
    left outer join (SELECT addr_id, amount, epoch_no  FROM reward) as "totalReward"
     on stake_address.id = "totalReward".addr_id
     where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
                         stakeAddress                         | addr_id |  amount   | epoch_no 
--------------------------------------------------------------+---------+-----------+----------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162720747 |      211
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162485461 |      212
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167344099 |      213
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167043060 |      214
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 200722225 |      215
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 191413549 |      216
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 143111843 |      217
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147836812 |      218
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 154740829 |      219
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145433327 |      220
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165631299 |      221
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151825651 |      222
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 169141624 |      223
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165184716 |      224
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 148899668 |      225
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 157838234 |      226
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 223252352 |      227
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147889017 |      228
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151858721 |      229
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165782849 |      230
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 192969560 |      231
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 135711434 |      232
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145964545 |      233
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    100702 |      235
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    102078 |      236
(25 rows)

Ie, there are rewards for every epoch except 234.

Therefore I think your conclusion:

Based on my investigation, it seems cardano-db-sync includes row 231 in the reward history result even though it shouldn't.

is incorrect, that stake address should have rewards for epoch 231.

Furthermore, I believe that your comment:

Current (incorrect) state from our backend { "remainingAmount": "193172340", "rewards": "3765004402", "withdrawals": "3571832062", }

is actually correct. The difference between the rewards value and the withdrawal value is the current unwithdrawn reward balance.

SebastienGllmt commented 3 years ago

is actually correct. The difference between the rewards value and the withdrawal value is the current unwithdrawn reward balance.

That can't be the case because we know the node returns 202780 as the reward balance for the account and not 193172340

Ie, there are rewards for every epoch except 234.

You should expect two epochs to be missing:

  1. The epoch where the rewards were sent back to the reserves because they didn't have their stake key registered
  2. The epoch representing the 1 epoch gap between when they deregistered their key and when they re-registered

The missing 234 epoch corresponds to (2), and I believe 231 corresponds to (1) since 231 means it should be visible in the user's wallet at epoch 233 which is precisely the epoch we expect to have been forfeited for (1)

erikd commented 3 years ago

You should expect two epochs to be missing:

Why? According to the operations on this stake address:

operation tx_epoch_no active_epoch_no
registraion 208 210
deregistraion 232 234
registraion 233 235

it was only deregistered for a single epoch (234) and received no rewards for that epoch.

Why would there be two epoch's without rewards? I am pretty sure the two cases you mention are actually the same thing and hence only one epoch goes without a reward.

erikd commented 3 years ago

@SebastienGllmt in Slack said:

deregistration is special in that it takes place right away, not in 2 epochs

If deregistration takes pace immediately, this one happened in tx_epoch_no == 232 so rewards for epochs [231..234] would all be lost, not just the rewards for 231.

I think we need @JaredCorduan on this.

JaredCorduan commented 3 years ago

@SebastienGllmt 's explanation is consistent with the ledger rules.

It's a bit philosophical "when" a stake credential is deregistered, but I would phrase it like this: deregistration takes place immediately, but rewards are delayed. If a credential is de-registered in epoch e, then it will receive rewards on the e / e+1 boundary (coming from the snapshot taken on the e-2 / e -1 boundary) and it will also receive rewards on the e+1 / e+2 boundary (coming from the snapshot taken on the e-1 / e boundary).

erikd commented 3 years ago

This sounds like we have some confusion with nomenclature.

From the POV of dbs-ync: :

JaredCorduan commented 3 years ago

if the stake credential was in a deregistration certificate from a transaction in epoch 232, then it would not be included in the snapshot taken on the 232/233 boundary. this means that it would not be a part of the reward update that is handed out on the 235/236 boundary. which, in @erikd 's terms, is the 234 rewards. moreover, if the (re)registration certificate landed in a transaction in epoch 233, then this would be the only reward update that it would miss out on.

JaredCorduan commented 3 years ago

it's worth noting that things like "de-registered when", "rewards at", and "active when", etc, can be confusing if we aren't really clear. so it's not clear to me who all I am agreeing with :)

SebastienGllmt commented 3 years ago

Since there seems to be confusion, I made a picture that shows the two expected missing epochs (db-sync 231 and db-sync 234)

I think the confusion comes from the fact that 231 may not technically be missing -- it could be the ledger state just considers it distributed to the reserves instead of the user. From the ledger state point of view, the rewards exist (distributed to the reserves), but from the user's point of view 231 shouldn't be included

image

erikd commented 3 years ago

I made a picture that shows the two expected missing epochs (db-sync 231 and db-sync 234)

But the actual rewards as reported by db-sync are:

cexplorer=# select stake_address.hash_raw as "stakeAddress", "totalReward".* from stake_address
    left outer join (SELECT addr_id, amount, epoch_no  FROM reward) as "totalReward"
     on stake_address.id = "totalReward".addr_id
     where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
                         stakeAddress                         | addr_id |  amount   | epoch_no 
--------------------------------------------------------------+---------+-----------+----------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162720747 |      211
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162485461 |      212
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167344099 |      213
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167043060 |      214
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 200722225 |      215
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 191413549 |      216
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 143111843 |      217
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147836812 |      218
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 154740829 |      219
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145433327 |      220
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165631299 |      221
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151825651 |      222
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 169141624 |      223
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165184716 |      224
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 148899668 |      225
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 157838234 |      226
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 223252352 |      227
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147889017 |      228
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151858721 |      229
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165782849 |      230
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 192969560 |      231
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 135711434 |      232
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145964545 |      233
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    100702 |      235
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    102078 |      236
(25 rows)

and only epoch 234 has no rewards. db-sync gets the contents of this table from ledger-state and the only thing db-sync adds is the epoch_no column.

Your diagram does not match this table, so your diagram is likely incorrect. There may also be a bug in the node (specifically the address reward balance calculation) but I would prefer to only deal with a single variable (db-sync) for now.

erikd commented 3 years ago

In your diagram you have "rewards delivered" crossed out for epoch 233, and both Jared and I think that is incorrect and that rewards should be delivered (because they are based on the snapshot at the start of epoch 231, not the current state of the chain).

erikd commented 3 years ago

@SebastienGllmt says:

I think the confusion comes from the fact that 231 may not technically be missing -- it could be the ledger state just considers it distributed to the reserves instead of the user.

But the rewards table shows the reward being distributed to the stake address. If it was distributed to reserves, it would not be in this table. db-sync receives this reward information as a map from stake address to reward amount. Now ledger state itself may be wrong, but there is very little room for errors sneaking in after the map is retrieved from ledger state.

JaredCorduan commented 3 years ago

I was wrong when I said:

moreover, if the (re)registration certificate landed in a transaction in epoch 233, then this would be the only reward update that it would miss out on.

In fact, @SebastienGllmt was exactly right when he said:

You should expect two epochs to be missing:

  1. The epoch where the rewards were sent back to the reserves because they didn't have their stake key registered
  2. The epoch representing the 1 epoch gap between when they deregistered their key and when they re-registered

By not being registered on the 232/233 epoch boundary, the stake credential both:

I can guess how db-sync is reporting rewards being handed out on the 232/233 boundary (which is really just @SebastienGllmt 's guess :) ). The ledger state cannot know until the last moment before the epoch boundary which credentials are still registered. We return rewards for unregistered credentials to the reserves. (See Figure 51: Reward Update Application in the formal spec). db-sync is probably not removing unregistered credentials.

JaredCorduan commented 3 years ago

@SebastienGllmt 's picture above looks great, but note that in fact the arrows need to span another epoch to account for the block producing phase. for example, the rewards from the snapshot taken on the 230/231 boundary is not actually handed out until the 233/234 boundary.

erikd commented 3 years ago

Ok now we have an explanation of this. db-sync maintains legder-state and pulls out the epoch rewards as a Map StakeAddress Coin which it inserts into the database. However, that map contain StakeAddress entries which are not valid and legder-state would drop these entries effectively contributing the rewards earned back to reserves.

The solution is to also pull the set of valid StakeAddress from ledger state and then filter the Map dropping all entries that do not occur in the set of valid StakeAddresses.

dmitrystas commented 3 years ago

It would be great if this 'phantom' rewards will be put in a new table like 'nondistributed_rewards' or something like this. This will allow us to correctly calculate the pool profitability

erikd commented 3 years ago

@dmitrystas :

It would be great if this 'phantom' rewards will be put in a new table like 'nondistributed_rewards' or something like this. This will allow us to correctly calculate the pool profitability.

I can see that these undistributed rewards (that go back to the reserves) could be used to calculate poll profitability but I am pretty sure that is not the best or the easiest way to calculate it.

I have created a ticket specifically for this issue.

erikd commented 3 years ago

Ok, I have a solution that filters out invalid rewards from the rewards list before being inserted and indeed there is on reward for that address in epoch 231 .

I now need to modify that so that these "invalid" rewards can be added to a separate table.

erikd commented 3 years ago

Using the version of db-sync in PR #469. the reward for stake address \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde is correctly inserted into the orphaned_rewards table:

cexplorer=# select stake_address.hash_raw, orphaned_reward.epoch_no, orphaned_reward.amount
         from orphaned_reward inner join stake_address on stake_address.id = orphaned_reward.addr_id
         where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde'; 
                           hash_raw                           | epoch_no |  amount   
--------------------------------------------------------------+----------+-----------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |      231 | 192969560

and it is also absent from the rewards table:

cexplorer=# select stake_address.hash_raw as "stakeAddress", "totalReward".* from stake_address
    left outer join (SELECT addr_id, amount, epoch_no  FROM reward) as "totalReward"
     on stake_address.id = "totalReward".addr_id
     where stake_address.hash_raw = '\xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde' ;
                         stakeAddress                         | addr_id |  amount   | epoch_no 
--------------------------------------------------------------+---------+-----------+----------
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162720747 |      211
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 162485461 |      212
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167344099 |      213
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 167043060 |      214
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 200722225 |      215
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 191413549 |      216
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 143111843 |      217
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147836812 |      218
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 154740829 |      219
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145433327 |      220
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165631299 |      221
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151825651 |      222
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 169141624 |      223
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165184716 |      224
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 148899668 |      225
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 157838234 |      226
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 223252352 |      227
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 147889017 |      228
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 151858721 |      229
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 165782849 |      230
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 135711434 |      232
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 | 145964545 |      233
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    100702 |      235
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |    102078 |      236
 \xe1e27b674f2c9e5c88689526e987ecc7911c034a0f553289e6d3a38fde |   38266 |        65 |      238
(25 rows)