Replay blocks idempotently
Every indexer will process some block twice. The design that survives it keys derived state on chain identity and upserts, so replay is a no-op rather than a doubling.
Procedure
- Choose the natural key from chain data:
(block_hash, tx_hash, log_index)for events,(block_hash, tx_hash)for transactions. - Write every derived row as an upsert:
INSERT INTO transfers (block_hash, tx_hash, log_index, from_addr, to_addr, value) VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (block_hash, tx_hash, log_index) DO UPDATE SET value = EXCLUDED.value; - Derive aggregates from the row table, never incrementally, so a replay recomputes rather than adds:
total = SELECT sum(value) FROM transfers WHERE .... - Never use a bare
INSERTon chain data;UPDATE ... SET x = x + 1is the counter that doubles on replay. - Advance the cursor in the same transaction as the block's writes so cursor and data commit together.
- Test it: process the same block twice and assert the row counts are unchanged.
Pitfalls
- Increment counters (
balance += value) applied twice inflate; recompute from logged rows instead. - A cursor committed before its data leaves a hole on replay-forward; commit together.
ON CONFLICT DO NOTHINGhides a decoder change; useDO UPDATEwhen the decoder version changed so re-decoded fields actually land.- Sequence-generated surrogate IDs are not idempotent keys; they differ on every replay.
Verification
for i in 1 2; do python -m indexer replay --block $N; done
psql -c "SELECT count(*) FROM transfers WHERE block_number=$N;"
# the count is identical after both runs
Report the block replayed twice and identical derived row counts after each pass.