The 'reward' and 'withdrawal' tables have different addr_id for the same stake_address.view

[dmitrystas]

For example, stake1uyenhm5yptcaf4j4dkyel9epvr94g7h36dts6599t84ma0g2msety

cexplorer=# select reward.*
from reward
left join stake_address on stake_address.id = reward.addr_id
where stake_address.view = 'stake1uyenhm5yptcaf4j4dkyel9epvr94g7h36dts6599t84ma0g2msety';

   id   | addr_id |   amount    | epoch_no | pool_id | block_id
--------+---------+-------------+----------+---------+----------
   3586 |   44227 |  6329090352 |      211 |     868 |  4600755
  22880 |   44227 |  6310518365 |      212 |     868 |  4622197
  48139 |   44227 |  2297044842 |      213 |     868 |  4643649
  77794 |   44227 |  7879529107 |      214 |     868 |  4665221
 111368 |   66568 |  3993432068 |      215 |     868 |  4686438
 148901 |   66568 |  4877041310 |      216 |     868 |  4707166
 191182 |   75324 | 10095021304 |      217 |     868 |  4727677
 237191 |   75324 |  5735596691 |      218 |     868 |  4749104
 285299 |   75324 |  7510785958 |      219 |     868 |  4770731
 335184 |   82878 |  6483107438 |      220 |     868 |  4792280
(10 rows)

cexplorer=# select withdrawal.*
from withdrawal
left join stake_address on stake_address.id = withdrawal.addr_id
where stake_address.view = 'stake1uyenhm5yptcaf4j4dkyel9epvr94g7h36dts6599t84ma0g2msety';

  id   | addr_id |   amount    |  tx_id
-------+---------+-------------+---------
 11506 |   44212 |  6329090352 | 2598264
 13575 |   44212 |  6310518365 | 2627660
 15846 |   44212 |  2297044842 | 2659957
 17546 |   44212 |  7879529107 | 2692468
 19158 |   44212 |  3993432068 | 2715814
 20781 |   44212 |  4877041310 | 2736241
 22417 |   44212 | 10095021304 | 2754632
 24155 |   44212 |  5735596691 | 2775005
 25864 |   44212 |  7510785958 | 2794404
 27427 |   44212 |  6483107438 | 2810776
(10 rows)

It's probably related to #326

[erikd]

Is this mainnet?

[dmitrystas]

yes, sorry. mainnet, the latest db-sync master, node 1.21.1

[erikd]

Highly likely that this is related to #326.

[erikd]

After removing the registered_tx_id uniqueness constraint:

cexplorer=# select reward.* from reward left join stake_address on stake_address.id = reward.addr_id
    where stake_address.view = 'stake1uyenhm5yptcaf4j4dkyel9epvr94g7h36dts6599t84ma0g2msety';
   id   | addr_id |   amount    | epoch_no | pool_id | block_id 
--------+---------+-------------+----------+---------+----------
   3586 |   42373 |  6329090352 |      211 |     819 |  4597956
  22880 |   42373 |  6310518365 |      212 |     819 |  4619398
  48139 |   42373 |  2297044842 |      213 |     819 |  4640850
  77794 |   42373 |  7879529107 |      214 |     819 |  4662422
 111368 |   42373 |  3993432068 |      215 |     819 |  4683639
 148901 |   42373 |  4877041310 |      216 |     819 |  4704367
 191182 |   42373 | 10095021304 |      217 |     819 |  4724878
 237191 |   42373 |  5735596691 |      218 |     819 |  4746305
 285299 |   42373 |  7510785958 |      219 |     819 |  4767932
 335184 |   42373 |  6483107438 |      220 |     819 |  4789479
(10 rows)

cexplorer=# select withdrawal.* from withdrawal
    left join stake_address on stake_address.id = withdrawal.addr_id
    where stake_address.view = 'stake1uyenhm5yptcaf4j4dkyel9epvr94g7h36dts6599t84ma0g2msety';
  id   | addr_id |   amount    |  tx_id  
-------+---------+-------------+---------
 11506 |   42373 |  6329090352 | 2596196
 13575 |   42373 |  6310518365 | 2625592
 15846 |   42373 |  2297044842 | 2657889
 17546 |   42373 |  7879529107 | 2690400
 19158 |   42373 |  3993432068 | 2713746
 20781 |   42373 |  4877041310 | 2734173
 22417 |   42373 | 10095021304 | 2752564
 24155 |   42373 |  5735596691 | 2772937
 25864 |   42373 |  7510785958 | 2792336
 27426 |   42373 |  6483107438 | 2808706
(10 rows)

That also looks correct. @dmitrystas please confirm.

[dmitrystas]

Looks good!