[FR] Add stake_address_id for Pointer address type

[dmitrystas]

At the moment table tx_out has a stake_address_id field as refer to the stake_address table, in case this address has delegation part (stake key). However (as it was found out in this issue IntersectMBO/cardano-node#3491) this is only true for Payment address, but not for Pointer address (https://developers.cardano.org/docs/governance/cardano-improvement-proposals/cip-0019/#pointers). It would be great if DB Sync would also store the key for such addresses.

[erikd]

@dmitrystas I think I have found where db-sync is doing something wrong, but if I fix this, how would I test it (on mainnet would be fine).

[erikd]

For my own reference, the Haskell types from cardano-ledger/libs/cardano-ledger-core/src/Cardano/Ledger/Credential.hs:

data StakeReference crypto
  = StakeRefBase !(StakeCredential crypto)
  | StakeRefPtr !Ptr
  | StakeRefNull
  deriving (Show, Eq, Generic, NFData, Ord)

type Ix = Word64

-- | Pointer to a slot, transaction index and index in certificate list.
data Ptr
  = Ptr !SlotNo !Ix !Ix
  deriving (Show, Eq, Ord, Generic, NFData, NoThunks)
  deriving (ToCBOR, FromCBOR) via CBORGroup Ptr

[dmitrystas]

@dmitrystas I think I have found where db-sync is doing something wrong, but if I fix this, how would I test it (on mainnet would be fine).

For this address addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2 ( 41fbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e0858292b3380b00) the values should be

1. 41fbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e0858292b3380b00 -> 41 fbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e085 8292b3380b00
2. 8292b3380b00 -> slot_no = 4495800, block_index = 11, cert_index = 0

SELECT stake_registration.addr_id
FROM block
LEFT JOIN tx ON tx.block_id = block.id AND tx.block_index = 11
LEFT JOIN stake_registration ON stake_registration.tx_id = tx.id AND cert_index = 0
WHERE block.slot_no = 4495800;

 addr_id
---------
      38

[erikd]

@dmitrystas Any idea of a table and row which should have that addr_id entry of 38 but is currently empty?

Also the erikd/StakeRefPtr (a branch off master) has a candidate solution to this issue but I still need to figure out how to validate it (hence the question above).

[erikd]

For Shelley (and later) the query to find tx_out rows with a stake_address_id = null is:

select tx_out.* from tx_out inner join tx on tx_out.tx_id = tx.id inner join block on tx.block_id = block.id
    where epoch_no > 310
    and stake_address_id is null ;

This query currently returns 0 rows, because my instance is still syncing and is currently only at epoch 309.

[dmitrystas]

Any idea of a table and row which should have that addr_id entry of 38 but is currently empty?

SELECT * FROM "tx_out" WHERE "address" = 'addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2';

   id    |  tx_id   | index |                               address                               |                               address_raw                                | address_has_script |                        payment_cred                        | stake_address_id |  value   | data_hash
----------+----------+-------+---------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------+------------------------------------------------------------+------------------+----------+-----------
 56190596 | 23948902 |     0 | addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2 | \x41fbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e0858292b3380b00 | f                  | \xfbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e085 |                  | 25000000 |
 41006608 | 17863253 |     0 | addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2 | \x41fbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e0858292b3380b00 | f                  | \xfbfce15acccb420982704c9e591f83ab3315c3314a18ecf65346e085 |                  | 25000000 |
(2 rows)

If it's what you're thinking about

[erikd]

@dmitrystas The id and tx_id indices are not the same as in your query (this database was rolled back a number of times) but the rest looks good:

> select id, tx_id, address, stake_address_id from tx_out where
    address = 'addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2' ;
    id    |  tx_id   |                               address                               | stake_address_id 
----------+----------+---------------------------------------------------------------------+------------------
 73421850 | 30908623 | addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2 |               38
 88428361 | 36915094 | addr1g8alec26en95yzvzwpxfukglsw4nx9wrx99p3m8k2drwppvzj2enszcqyu9ps2 |               38
(2 rows)