pindexer: duplicate constraint on block_details_pkey

[conorsch]

Describe the bug
Yesterday I ran pindexer version 0.80.8 (#4915) against a testnet cometbft db, and it failed with this error:

2024-11-06T17:53:57.966026Z DEBUG sqlx::query: summary="SELECT events.rowid, events.type, blocks.height …" db.statement="\n\nSELECT\n  events.rowid,\n  events.type,\n  blocks.height AS block_height,\n  tx_results.tx_hash,\n  events.attrs\nFROM\n  (\n    SELECT\n      rowid,\n      type,\n      block_id,\n      tx_id,\n      jsonb_object_agg(attributes.key, attributes.value) AS attrs\n    FROM\n      events\n      LEFT JOIN attributes ON rowid = attributes.event_id\n    WHERE\n      rowid > $1\n    GROUP BY\n      rowid,\n      type,\n      block_id,\n      tx_id\n  ) events\n  LEFT JOIN LATERAL (\n SELECT\n      *\n    FROM\n      blocks\n    WHERE\n      blocks.rowid = events.block_id\n    LIMIT\n      1\n  ) blocks ON TRUE\n  LEFT JOIN LATERAL (\n    SELECT\n      *\n    FROM\n      tx_results\n WHERE\n      tx_results.rowid = events.tx_id\n    LIMIT\n      1\n  ) tx_results ON TRUE\nORDER BY\n  events.rowid ASC\n" rows_affected=0 rows_returned=4 elapsed=5.439628ms
Error: error returned from database: duplicate key value violates unique constraint "block_details_pkey"

Caused by:
    duplicate key value violates unique constraint "block_details_pkey"

Running against the same source db using v0.80.7 showed no problems.

To Reproduce
Steps to reproduce the behavior:

1. Download cometbft dbdump from testnet node here https://artifacts.plinfra.net/penumbra-testnet-phobos-2/cometbft-dbdump-height-782476.sql.gz
2. Import that into a local postgres db
3. Run pindexer v0.80.8 against it; does it fail?
4. Drop db and re-try on v0.80.7; does it pass?

Unclear whether the problem is with the latest release of pindexer (if so then #4910 is suspect) or if it's an edge case in the testnet chain data.

Expected behavior
Pindexer v0.80.8 should work just fine on existing testnet data.

[cronokirby]

The batch processing API on main seems to have fixed this ; closing for now

[conorsch]

The batch processing API on main seems to have fixed this

Merged in #4913