After 10.22 release args_base64 became unencoded on transaction_actions table

[frol]

In #305 we resolved the issue on the action_receipt_actions table, but forgot to fix it for transactions.

P.S. We will need to re-index those blocks again, and potentially re-index mainnet as well 😨

P.P.S. It would be helpful to have a force-override flag for re-indexing the broken data records, but that might be harder to implement than it sounds

[khorolets]

DELETE FROM action_receipt_actions WHERE
  receipt_included_in_block_timestamp >= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-08-16 07:00:00+01') as bigint) * 1000000000)
  AND
  receipt_included_in_block_timestamp <= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-09-15 20:00:00+01') as bigint) * 1000000000);

DELETE FROM transaction_actions 
USING transactions
WHERE
  block_timestamp >= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-08-16 07:00:00+01') as bigint) * 1000000000)
  AND
  block_timestamp <= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-09-15 20:00:00+01') as bigint) * 1000000000);

[AngelBlock]

@khorolets Thanks for the hints, but attempt to execute DELETE FROM transaction_actions USING transactions..... failed, probably due to lack of indexes, as never finished (waited a few hours)

Any other way deleting the data? Perhaps identifying the data type, encoded vs. unenconded.

[khorolets]

@khorolets Thanks for the hints, but attempt to execute DELETE FROM transaction_actions USING transactions..... failed, probably due to lack of indexes, as never finished (waited a few hours)

Any other way deleting the data? Perhaps identifying the data type, encoded vs. unenconded.

I've ended up using subqueries and lowering the from-to to a couple of days. This seems to take long, but not a matter of hours anyway:

WHERE transaction_hash IN (SELECT transaction_hash FROM transactions WHERE
  block_timestamp >= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-09-03 07:00:00+01') as bigint) * 1000000000)
  AND
  block_timestamp <= (CAST(EXTRACT(epoch FROM TIMESTAMP '2022-09-08 20:00:00+01') as bigint) * 1000000000));

[frol]

Both testnet and mainnet are expected to be recovered as all the data is re-indexed.