Deprecate use of was_storage_removed and was_state_removed stored Postgres functions #101

New Issue

i-norden · 2021-09-13T13:47:32Z

i-norden commented

2021-09-13 13:47:32 +00:00

These stored functions were needed prior to https://github.com/vulcanize/go-ethereum/pull/58 in order to properly consider "Removed" node types when querying our state and storage_cids tables for the state of a leaf node with a given leaf key at a given height/hash. Now that we have leaf keys stored for "Removed" node types, we no longer need to use these inefficient and messy functions and instead the "Removed" nodes are considered in the same way as a "Leaf" node when querying against leaf keys.

Related to #95 , #94 , #98

These stored functions were needed prior to https://github.com/vulcanize/go-ethereum/pull/58 in order to properly consider "Removed" node types when querying our state and storage_cids tables for the state of a leaf node with a given leaf key at a given height/hash. Now that we have leaf keys stored for "Removed" node types, we no longer need to use these inefficient and messy functions and instead the "Removed" nodes are considered in the same way as a "Leaf" node when querying against leaf keys. Related to #95 , #94 , #98

🚀 1

i-norden commented

2021-09-13 14:07:08 +00:00

E.g. instead of

SELECT state_cids.cid, data, was_state_removed(state_path, block_number, $2) AS removed
FROM eth.state_cids
    INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id)
    INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key)
WHERE state_leaf_key = $1
AND block_number <= (SELECT block_number
    FROM eth.header_cids
    WHERE block_hash = $2)
AND header_cids.id = (SELECT canonical_header_id(block_number))
ORDER BY block_number DESC
LIMIT 1

We can just do

SELECT state_cids.cid, data, node_type
FROM eth.state_cids
    INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id)
    INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key)
WHERE state_leaf_key = $1
AND block_number <= (SELECT block_number
    FROM eth.header_cids
    WHERE block_hash = $2)
AND header_cids.id = (SELECT canonical_header_id(block_number))
ORDER BY block_number DESC
LIMIT 1

This will return the latest node entry for a specific leaf key, including "Removed" types. We can check if the node_type returned is "Removed" and if it is handle the zero value appropriately. Alternatively, if we want to get the latest state of a node value at a leaf_key (last value before it was "Removed"), we just need to add a condition to the query to exclude "Removed" type nodes from the considered set.

I.e.

SELECT state_cids.cid, data
FROM eth.state_cids
    INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id)
    INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key)
WHERE state_leaf_key = $1
AND block_number <= (SELECT block_number
    FROM eth.header_cids
    WHERE block_hash = $2)
AND header_cids.id = (SELECT canonical_header_id(block_number))
AND node_type IS NOT 3
ORDER BY block_number DESC
LIMIT 1

The above will return the latest "Leaf" type node entry for a given leaf key at a given hash/height, ignoring "Removed" typed nodes.

E.g. instead of ```sql SELECT state_cids.cid, data, was_state_removed(state_path, block_number, $2) AS removed FROM eth.state_cids INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id) INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key) WHERE state_leaf_key = $1 AND block_number <= (SELECT block_number FROM eth.header_cids WHERE block_hash = $2) AND header_cids.id = (SELECT canonical_header_id(block_number)) ORDER BY block_number DESC LIMIT 1 ``` We can just do ```sql SELECT state_cids.cid, data, node_type FROM eth.state_cids INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id) INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key) WHERE state_leaf_key = $1 AND block_number <= (SELECT block_number FROM eth.header_cids WHERE block_hash = $2) AND header_cids.id = (SELECT canonical_header_id(block_number)) ORDER BY block_number DESC LIMIT 1 ``` This will return the latest node entry for a specific leaf key, including "Removed" types. We can check if the `node_type` returned is "Removed" and if it is handle the zero value appropriately. Alternatively, if we want to get the latest state of a node value at a leaf_key (last value before it was "Removed"), we just need to add a condition to the query to exclude "Removed" type nodes from the considered set. I.e. ```sql SELECT state_cids.cid, data FROM eth.state_cids INNER JOIN eth.header_cids ON (state_cids.header_id = header_cids.id) INNER JOIN public.blocks ON (state_cids.mh_key = blocks.key) WHERE state_leaf_key = $1 AND block_number <= (SELECT block_number FROM eth.header_cids WHERE block_hash = $2) AND header_cids.id = (SELECT canonical_header_id(block_number)) AND node_type IS NOT 3 ORDER BY block_number DESC LIMIT 1 ``` The above will return the latest "Leaf" type node entry for a given leaf key at a given hash/height, ignoring "Removed" typed nodes.

i-norden commented

2021-09-17 12:06:56 +00:00

Closed in https://github.com/vulcanize/ipld-eth-server/pull/98

Sign in to join this conversation.

No Milestone

No project

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: cerc-io/ipld-eth-server#101