pindexer: error on undefined field

[conorsch]

Describe the bug
pindexer errors out on mainnet db data due to a missing field

To Reproduce
Steps to reproduce the behavior:

1. Run pindexer (at least v1.0.0) against mainnet events db
2. Index up to block 3139617
3. Observe crash

Expected behavior
pindexer ingests block 3139617 and continues without error.

Screenshots

Error message:

2025-01-21T21:37:58.584465Z DEBUG cometindex::indexer::indexing_state: local_rowid=23110210 type_str="block" height=3139617 tx_hash=None
2025-01-21T21:37:58.584787Z  WARN sqlx::query: slow statement: execution time exceeded alert threshold summary="SELECT events.rowid, events.type, events.height, …" db.statement="\n\nSELECT\n  events.row
id,\n  events.type,\n  events.height,\n  tx_results.tx_hash,\n  events.attrs\nFROM\n  (\n    SELECT\n      (\n        SELECT\n          height\n        FROM\n          blocks\n        WHERE\n          b
locks.rowid = block_id\n      ) as height,\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      LEF
T JOIN attributes ON rowid = attributes.event_id\n    WHERE\n      block_id >= (\n        SELECT\n          rowid\n        FROM\n          blocks\n        where\n          height = $1\n      )\n      AN
D block_id <= (\n        SELECT\n          rowid\n        FROM\n          blocks\n        where\n          height = $2\n      )\n    GROUP BY\n      rowid,\n      type,\n      block_id,\n      tx_id\n
  ORDER BY\n      rowid ASC\n  ) events\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 T
RUE\nORDER BY\n  events.rowid ASC\n" rows_affected=0 rows_returned=1 elapsed=1.445861129s elapsed_secs=1.445861129 slow_threshold=1s
Error: error reading from database

Caused by:
    0: error returned from database: field name must not be null
    1: field name must not be null

Additional context
Looks like the problem is caused by slow queries on the target database. Indeed, the database I'm talking to can be quite slow, due to heavy traffic. However, I don't think that's the source of the problem: I think we've got a syntax error in a query. We can rule this out by checking against a local db and verifying that the crash still happens.

[conorsch]

To aid in debugging, I've uploaded a fresh copy of a raw cometbft event db, in custom postgres format, here: https://artifacts.plinfra.net/penumbra-1/cometbft-dbdump-height-3141978.dump. Plugging that into a local setup will allow rerunning pindexer quickly to 1) reproduce the problem; and 2) try candidate fixes if necessary.

[conorsch]

I was able to reproduce the failure from that dump, although I can't identify where the null field is coming from. Pulled another dump from a different nodes cometbft db, and also encountered a failure at a different block:

2025-01-22T06:11:00.685928Z DEBUG cometindex::indexer::indexing_state: local_rowid=22489362 type_str="block" height=3138970 tx_hash=None
2025-01-22T06:11:00.686163Z  WARN sqlx::query: slow statement: execution time exceeded alert threshold summary="SELECT events.rowid, events.type, events.height, …" db.statement="\n\nSELECT\n  events.rowid,
\n  events.type,\n  events.height,\n  tx_results.tx_hash,\n  events.attrs\nFROM\n  (\n    SELECT\n      (\n        SELECT\n          height\n        FROM\n          blocks\n        WHERE\n          blocks.
rowid = block_id\n      ) as height,\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 at
tributes ON rowid = attributes.event_id\n    WHERE\n      block_id >= (\n        SELECT\n          rowid\n        FROM\n          blocks\n        where\n          height = $1\n      )\n      AND block_id <
= (\n        SELECT\n          rowid\n        FROM\n          blocks\n        where\n          height = $2\n      )\n    GROUP BY\n      rowid,\n      type,\n      block_id,\n      tx_id\n    ORDER BY\n
   rowid ASC\n  ) events\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  e
vents.rowid ASC\n" rows_affected=0 rows_returned=4220 elapsed=18.781166029s elapsed_secs=18.781166029 slow_threshold=1s
Error: error reading from database

Caused by:
    0: error returned from database: field name must not be null
    1: field name must not be null

which makes me suspect we've got a data integrity issue in the databases. In order to debug we'll need to update the reindexer to support v0.81.x migrations and rebuild from scratch. It's been a long while since we've done that on the dbs in question.

[conorsch]

Prepared a bump for the reindexer in penumbra-zone/reindexer#21, running that code now to get a fresh database.