pindexer: data loss in the dex_ex_batch_swap_traces table

[VanishMax]

Describe the bug
Some old swaps are not stored in pindexer. In my personal mainnet account, I have a UM-USDC swap at height 2732174. However, pindexer has no swaps at heights between 2730000 and 2736000. The nearest swap in pindexer is at 2736015, 3838 blocks from my swap.

To Reproduce

Make a similar SQL query:

select
    "amount_hops", "asset_hops", "asset_end", "asset_hops", "asset_start", "batch_input", "batch_output", "height", "input", "output", "position_id_hops", "price_float", "rowid", "time"
from "dex_ex_batch_swap_traces"
where "asset_start" = decode('29ea9c2f3371f6a487e7e95c247041f4a356f983eb064e5d2b3bcf322ca96a10', 'hex')
  and "asset_end" = decode('07ef660132a4c3235fab272d43d9b9752a8337b2d108597abffaff5f246d0f0f', 'hex')
  and "height" in (2639003, 2639573, 2732177)
order by "time" desc, "rowid" asc;

Expected behavior
Every swap, old or new, has a corresponding row in pindexer.

Additional context

Relates to penumbra-zone/dex-explorer#337

[erwanor]

Good flag, I think that makes sense, because we added this table a couple weeks ago, so it would take a reindex to surface older data. If we confirm that's the approximate cause for the gap, then I think we can punt on it for now, and fix it later.

[conorsch]

we added this table a couple weeks ago, so it would take a reindex to surface older data.

The relevant logic was added in #4990, which (first) shipped in v0.81.1. Given the regressions we encountered in v1, liberal reindexing was required, the last of which was completed around 2025-01-24. Everything @VanishMax is expecting to see in the pindexer db is a reasonable assumption in my mind.

Another reason data could be missing from pindexer is because it wasn't in the source cometbft data. That was actually the specific symptom we observed in #4999, but all signs point to that situation being resolved across our data sources.

Today, I'll rerun a pindexer-only reindexing batch, using v1.0.1, which should take just a few hours. That's distinct from a full event reindex, which takes on the order of a day.

[erwanor]

Today, I'll rerun a pindexer-only reindexing batch, using v1.0.1, which should take just a few hours. That's distinct from a full event reindex, which takes on the order of a day.

I don't think it's important to do today vs. everything we have to do: LQT upgrade, Noble testing, etc. If it's really that small of a hanging fruit, go for it, but otherwise feel free to focus on something else.

[conorsch]

I'll rerun a pindexer-only reindexing batch, using v1.0.1, which should take just a few hours.

Done, and sadly, same behavior on the query that @VanishMax shared: I get no hits. Further debugging should inspect whether the raw cometbft database includes the necessary data for pindexer to construct the swap traces. If it doesn't, there's the problem in the data layer. If it does, perhaps the pindexer impl overlooks some edges cases. The former is more likely in my mind but we need to substantiate it either way.