Historical indexing support for multichain

[stwiname]

Background

Currently historical indexing works by having a hidden column on tables with the block height range. This doesn't work for multichain where each chains blocks are produced independently. In order to support historical with multichain we can use a timestamp instead

Requirements

Database changes

• Option to specify historical indexing unit (block height, timestamp). Timestamp will be forced if multichain is used
• The appropriate timestamp resolution, some chains only record the timestamp to the nearest second, but something more detailed like down to the MS should probably be used.
• Timestamps should be stored as a unix timestamp from UTC timezone
• The query service should not need any changes as queries should be made with unix timestamps

Rewind support

This will be more complex as currently each indexer runs independently.

• Implement a master "indexer" that initiates the rewind and ensures that other indexers are aware of the rewind. This would mean some form of communication between nodes to notify them. This could also be used to run schema migrations.
• The ability to find the nearest blocks to a timestamp. Some chains provide a RPC method for this, otherwise a binary search could be used to find the block.

Other considerations/unknowns

• It could be worth adding another hidden column which is a list of chain ids that have touched a record. This could lead to an optimisation when reindexing a chain or rewinding.
• What happens if multiple blocks across different chains have different timestamps? This could cause other problems with being deterministic.

Future

Enabling historical indexing for multichain is a step towards enabling more features that are already supported with single chain indexing

• Unfinalized blocks
• POI - https://github.com/subquery/subql/issues/2146

[jiqiang90]

The ability to find the nearest blocks to a timestamp. Some chains provide a RPC method for this, otherwise a binary search could be used to find the block.

My concern is currently indexing for multi chain also didn't following a timestamp order (there is no order), we can add an additional field indexing_order_id to record the indexing order, and we can find the record match network height, remove any record beyond this id