PostgreSQL: per-query causal reads using lsn_from_hostgroup (3.1/4.0) #6485

Status: paused implementation checkpoint

The PostgreSQL causal-read implementation is committed on feat/pgsql-lsn-causal-reads at ed1ceef9af5d1cacc835c26034069fd5959d03ca. Work is temporarily paused as of 2026-10-17. Keep this feature tracker open. The linked implementation PR must remain draft, marked [DONOTMERGE], and labeled ci:skip until work resumes and the remaining review/validation is addressed.

Feature and agreed behavior

Add pgsql_query_rules.lsn_from_hostgroup for native PostgreSQL application connections in ProxySQL 3.1/4.0. PostgreSQL does not return an execution's LSN in its normal completion packet. Instead, each frontend keeps one {hostname, port, completion timestamp} record per hostgroup. A causal read resolves that timestamp against independently collected background LSN history when backend selection is reached.

• The last actual application execution replaces the hostgroup's record, including when a different server is used. No aggregation of prior servers or read/write classification is required.
• A rule with lsn_from_hostgroup uses the first retained successful source sample whose send timestamp is strictly later than the recorded execution. An execution already captured before rule activation is usable.
• The resulting LSN/history requirement is fixed for that request. The normal destination hostgroup is filtered to servers that have reached it; pending progress uses ordinary backend acquisition retries/timeouts. There is no alternate-hostgroup fallback or client LSN wait-timeout setting.
• Application connections send no additional LSN or identity queries. The PostgreSQL monitor owns polling and does not traverse or update sessions.
• Ordinary queries remain outside LSN lookup/selection. Existing query-cache lookup, insertion and invalidation are unchanged; a cache hit returns before backend selection.
• Hostgroups are routing memberships, not writer/reader identities. Sampling, history and statistics deduplicate physical endpoints by hostname/port.

Implemented scope

• Nullable lsn_from_hostgroup between multiplex and log; HG0 supported, NULL chaining preserves an earlier source setting.
• Compile-time PROXYSQL31 gating, inherited by 4.0; legacy 3.0 schemas and behavior retained.
• Transactional ordered disk migration preserving rules, signed rule IDs, neighboring values and deleted-ID AUTOINCREMENT watermarks.
• Versioned cluster projections and non-mutating legacy/extended rejection. A narrow 3.1↔4.0 bridge permits PostgreSQL query rules only; unrelated modules retain their compatibility checks. Peer compatibility transitions revoke stale donor eligibility before requiring fresh checksums.
• Monitor variables pgsql-monitor_lsn_poll_interval (10 ms), pgsql-monitor_lsn_poll_timeout (100 ms, background only), and pgsql-monitor_lsn_history (60000 ms).
• Progress pruning with a real predecessor and independent age pruning with one older anchor, including stuck/down/unknown peers. Retention uses shared hostgroup summaries rather than Cartesian membership copies.
• stats_pgsql_lsn contains exactly hostname, port, lsn, one row per physical endpoint, NULL for unknown/withdrawn progress.
• Native simple/extended/COPY completion and pool-selection integration; incompatible transaction/portal refusal discards affected work and cleans abandoned extended frames.
• Background-only idle WAL page-header normalization for inspected PostgreSQL 14–18 layouts; real insertion progress is preserved rather than substituting write/flush LSN.

Authoritative documentation

The October 4 design supersedes all October 3 drafts and their discarded routing, batching, bootstrap and cache policies. Resume from the final source tree and revised documents, rather than reconstructing behavior from earlier commits.

• Revised design
• Operator documentation
• Validation record and limitations
• Implementation plan and execution record

Completed verification

Recorded on 2026-10-04 for the committed implementation; publishing this checkpoint does not represent a fresh test run against the current base branch.

Live coverage includes rule activation after execution, HG0, physical deduplication, unchanged cache hits, lag/catch-up, ordinary acquisition timeout, COPY, commit, extended execution, transaction refusal/rollback, old history with a stuck peer, cancellation, monitor restart, pool exhaustion and zero added application SQL. Cluster checks cover populated upgrades and compatible pulls, with incompatible pulls preserving memory/runtime/disk/checksums.

Next session / remaining work

• Review the final implementation at the checkpoint against the revised design; preserve the agreed no-extra-application-query, unchanged-cache, no-fallback scope.
• Assess changes since original base 546abf5c885ff985c3e98852630bac28de9223a3. The target v3.0 branch has advanced; resolve integration deliberately and rerun affected tier builds/tests after any source change.
• Complete representative non-causal throughput/latency comparison against the same base with polling inactive/active, full-window history RSS, worker-wake overhead and sustained stuck-replica memory measurements. Existing helper/housekeeping microbenchmarks are not production throughput claims.
• Address remaining fault/concurrency coverage: blocked DNS, TLS/GSS failures, integrated native sanitizers/TSan, forced asynchronous-commit/flush-lag behavior, and broader PostgreSQL version/layout testing.
• Review endpoint identity tombstone growth under lifetime endpoint churn and determine whether bounded reclamation is needed before merge. Keep stable-endpoint/external-fencing assumptions explicit.
• Enable CI when ready, run the applicable merge validation, and remove [DONOTMERGE] only after review is complete. Keep this issue open until the feature is accepted.

The full repository suite has not been run. Configured init_connect was excluded from live testing because of an existing native base-path dispatcher issue; this feature does not change connection initialization. Detailed limitations are in the committed validation record.

Local resume information

• Dedicated worktree: /data/rene/proxysql/.worktrees/pgsql-lsn-causal-reads (preserve).
• Branch: feat/pgsql-lsn-causal-reads; checkpoint: ed1ceef9a.
• Local logs and review/reproduction artifacts: .superpowers/sdd/2026-10-04-pgsql-lsn-query-rules/ inside that worktree. These ignored artifacts are local; the committed validation record is the portable evidence summary.
• Resume order: read this tracker and linked draft PR, revised design, operator documentation, validation limitations, then inspect the final code.

Linked tracking

• Draft implementation: [DONOTMERGE] PostgreSQL causal reads using lsn_from_hostgroup #6486 ([DONOTMERGE], ci:skip).
• Linear: PRO-23 — Backlog, paused implementation checkpoint.

Publishing observation — 2026-10-17

The branch push triggered the push-only lint run 39037237109 before a PR existed. The PR already had ci:skip when opened; this does not suppress an earlier push event. Lint stopped at groups.json formatting: Keys not sorted: 'pgsql_lsn_manager_unit-t' should come before 'pgsql_waiter_list_unit-t'. Later lint stages did not run; the workflow ran no product tests.

First resume task: run python3 test/tap/groups/lint_groups_json.py --fix, inspect the formatting diff, then run test/infra/control/run-ci-lint.bash and commit the correction with a descriptive body before enabling CI. This is a known outstanding formatting fix, additional to the October 4 validation limits. The implementation checkpoint remains ed1ceef9a; publishing did not change source or history.