Problem
The Robinhood production indexer intermittently fails an internal-transaction import with PostgreSQL 23505 on the unfetched_balances index:
duplicate key value violates unique constraint "unfetched_balances"
Key (address_hash, block_number)=(..., 31144612) already exists
The import is retried, so head indexing continues, but the failure is noisy and wastes work during sustained ingestion.
Live evidence
- observed repeatedly on backend commit 7781fc6
- one combined Blockscout API/indexer instance; this is not caused by multiple indexer replicas
- the failure comes from Explorer.Chain.Import.Runner.Address.CoinBalances during the internal_transaction fetcher
- current imports can overlap between confirmed-block ingestion and internal-transaction catch-up
Analysis
address_coin_balances has two unique indexes over the same key:
- the unconditional unique index on (address_hash, block_number)
- the partial unique index named unfetched_balances on the same columns where value_fetched_at IS NULL
The insert uses ON CONFLICT (address_hash, block_number). Under concurrent inserts, PostgreSQL can arbitrate the conflict through one unique index while the other unique index still raises 23505. The partial index is useful for unfetched-balance scans, but its uniqueness is redundant because the unconditional index already guarantees the same key.
Current upstream master has the same index and runner behavior; no existing upstream issue or PR matching this error was found.
Suggested fix
Replace unfetched_balances with an equivalent non-unique partial index, using the heavy/background index migration framework because address_coin_balances is large. Add a concurrent-import regression test demonstrating that duplicate placeholders are handled by ON CONFLICT instead of failing on the second unique index.
Before changing production, compare the query plan for stream_unfetched_balances and preserve the partial-index name/columns/predicate so its read performance is unchanged.
Problem
The Robinhood production indexer intermittently fails an internal-transaction import with PostgreSQL 23505 on the unfetched_balances index:
The import is retried, so head indexing continues, but the failure is noisy and wastes work during sustained ingestion.
Live evidence
Analysis
address_coin_balances has two unique indexes over the same key:
The insert uses ON CONFLICT (address_hash, block_number). Under concurrent inserts, PostgreSQL can arbitrate the conflict through one unique index while the other unique index still raises 23505. The partial index is useful for unfetched-balance scans, but its uniqueness is redundant because the unconditional index already guarantees the same key.
Current upstream master has the same index and runner behavior; no existing upstream issue or PR matching this error was found.
Suggested fix
Replace unfetched_balances with an equivalent non-unique partial index, using the heavy/background index migration framework because address_coin_balances is large. Add a concurrent-import regression test demonstrating that duplicate placeholders are handled by ON CONFLICT instead of failing on the second unique index.
Before changing production, compare the query plan for stream_unfetched_balances and preserve the partial-index name/columns/predicate so its read performance is unchanged.