pindexer: swap_volume_over_window contains null values

[conorsch]

Describe the bug
While testing recent pindexer bug fixes by rerunning pindexer from block zero, I encountered a failure on the penumbra-1 chain:

2024-11-18T23:27:34.974561Z DEBUG sqlx::query: summary="INSERT INTO insights_supply(height, total, …" db.statement="\n\nINSERT INTO\n  insights_supply(\n    height,\n    total,\n    staked,\n    price,\n    price_numeraire_asset_id\n  )\nVALUES\n  ($1, $2, $3, $5, $4) ON CONFLICT (height) DO\nUPDATE\nSET\n  total = excluded.total,\n  staked = excluded.staked,\n  price = excluded.price,\n  price_numeraire_asset_id = excluded.price_numeraire_asset_id\n" rows_affected=0 rows_returned=0 elapsed=3.095199ms
2024-11-18T23:27:34.974774Z DEBUG sqlx::query: 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 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      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  events.rowid ASC\n" rows_affected=0 rows_returned=0 elapsed=830.672533ms
Error: error returned from database: null value in column "swap_volume_over_window" of relation "dex_ex_pairs_summary" violates not-null constraint

To Reproduce
Steps to reproduce the behavior:

1. Dump a cometbft event db from penumbra-1 chain (i used a dump from height 2129921, available here: https://artifacts.plinfra.net/penumbra-1/)
2. Import cometbft event db locally
3. Check out main branch in this repo, at commit 0b413ac
4. Run pindexer against that cometbft db writing to a newly created dest db
5. Wait for pindexer to reach "current" height on that process (~25m)
6. Dump the pindexer db, restore it to an environment with with a live cometbft db
7. Observe error shortly after pindexer start

Expected behavior
Pindexer syncs up to current height and is happy.

Additional context
I'm surprised that when dealing with dumps, pindexer works fine. Only when I restore that dump to a db and hook up pindexer ingesting a live source db does the error occur.

[conorsch]

Filed this early: I'm going to try again to reproduce to confirm the inputs and outputs were correct.

[conorsch]

Ah-ha, container image was stale, so I wasn't testing against latest code. Image builds broke a few hours ago, with merge of #4930. Investigating...

[conorsch]

Testing with #4934 shows that the problem was indeed not using the latest code. That's resolved now, and when using the latest pindexer code, I no longer see this specific error. I do see a new error, however, but filed a new report in #4935 for that, for clarity.