Fix proposal vote timeouts and separate page selection from enrichment
Proposal-vote requests can scan millions of accounts several times even when pagination selects only two votes. Select the requested page first, collect its distinct account IDs, and resolve names in one bounded lookup so these requests use the existing account indexes.
Reorganize the proposal helpers into explicit page-selection and response-enrichment stages, keeping all functions and private page types in backend/endpoint_helpers/proposal.sql:
- Current votes: select ordered vote keys, fetch metadata/proxy information for that page, collect account IDs, then build the response.
- Proposal listings: retain the creator visibility join before pagination, then resolve names for the selected page in one batch.
- Vote history: preserve event ordering when selecting the page, then apply cache/fallback stake enrichment and current proxy semantics.
Public signatures, response fields, filtering, pagination, ordering and count semantics remain unchanged. The endpoint documentation describes the final stages and their limits.
Closes #150 (closed).
CI also restores the original HAfAH dependency by its retained image digest. Pipeline #182342 could not start sync because the fe9aaf53 tag no longer exists. Registry metadata confirms the pinned image was built from the same fe9aaf53 commit; Compose validation passed for both base and CI configurations.
Validation:
- PostgreSQL 17.11 and 18.6: the reorganization passed 1,342 helper/count comparisons plus 108 status comparisons per engine against the bounded-lookup fix. Repeated installation of the private types/helpers and non-superuser calls also passed.
- A separate PostgreSQL 18.6 check against the original code passed the same 1,342 helper/count and 108 status comparisons. These are disposable local SQL fixtures with recorded account-view definitions and simplified block/operation-ID support, not a full HAF node or HTTP test.
- The standalone SQL used for production measurements matched actual functions in 56 comparisons across 28 profiles; 17 phase plans matched the corresponding nested function plans locally.
git diff --checkpassed. Existing Tavern scenarios and expected patterns are unchanged; scratch fixtures and diagnostic tools are excluded from the commits. The branch pipeline supplies the full integration/HTTP validation.
Read-only measurements on hive-3, PostgreSQL 18.6, comparing the original code with b986e3b:
| Query | Original SQL execution | Reorganized SQL phases |
|---|---|---|
| Current votes, blocktrades, limit 2 | Statement timeout at 2 s | 1.859 ms |
| Current votes, blocktrades, limit 100 | Statement timeout at 2 s | 87.518 ms initially; 4.672 ms warm |
| Current votes, no filter, limit 1000 | Statement timeout at 2 s | 504.131 ms initially; 22.209 ms warm |
| Proposal listing, creator ASC, limit 100 | 1420.440 ms | 328.645 ms |
| Proposal listing, total votes DESC, limit 100 | 1431.201 ms | 344.220 ms |
| Vote history, proposal 10, limit 100, two orders of measurement | 705.364–714.176 ms | 738.804–802.279 ms |
| Vote history, proposal 10, blocktrades, one result | 734.635 ms | 319.010 ms |
These are sums of SQL-phase execution times, not full PL/pgSQL or API latency. Planning and intermediate-result capture are excluded; capture adds reads and warms caches. The first 100/1000-vote measurements had 263/1947 shared-buffer reads, versus zero in the warm repeats. A statement timeout is not an exact execution time. Timed-out original queries were not retried or given longer limits.
All five completed production pairs returned identical ordered payloads. Original vote-query timeouts prevented production payload comparisons for those three cases; their functional equivalence was checked locally.
Remaining limitation: vote history still scans approximately 2.62 million accounts to resolve proxy names. The two 100-event samples were 5–12% slower after reorganization and both versions spilled the proxy-name hash to temporary files. This change does not claim a general history speedup or eliminate that separate lookup cost.
Production diagnostics used serial REPEATABLE READ READ ONLY transactions with statement/lock/idle limits of at most 2 s/200 ms/5 s. No production functions, types, tables or application data were changed. All local scratch containers and volumes were removed.