Bare [SET/CREATE TEMP, SELECT] batch on a transaction pooler loses session-local state on the one-shot path #173

Summary

PR #172 (#169 fix) routes a non-in_transaction result-set query in a batch to the one-shot pinned path (exec_query_pooled_txn) on a separate connection. A bare batch (no session_id, no BEGIN) that sets session-local state in an earlier non-result-set statement — a session SET (not SET LOCAL) or a CREATE TEMP TABLE outside a transaction — then runs a result-set SELECT silently loses that state, because the SELECT runs on a fresh one-shot connection that never saw the SET/temp table.

On main (rc.11), run_batch_in_session ran every query via exec_query_on_client on one shared pg_client, so a SET then SELECT in a single bare batch reliably shared state (same connection, same backend). This PR breaks that for the pooler !in_transaction case.

Reproduction

Bare batch on a pooler_mode=transaction connection (no session_id, no BEGIN):

queries: ["SET extra_float_digits = 3", "SELECT 1.0/3.0"]
//   or
queries: ["CREATE TEMP TABLE t AS SELECT 1 AS id", "SELECT * FROM t"]

• SET / CREATE TEMP → non-result-set → else branch → execute_typed on the shared pg_client. transaction_effect(...) = None → in_transaction stays false.
• SELECT → result-set + is_transaction_pooler() + !in_transaction → exec_query_pooled_txn → oneshot_pool_client (a NEW connection). The one-shot applies only the host's schema via SET LOCAL and does NOT replay the user's earlier SET; it can't see the temp table (different session).

Result: the SELECT returns float output with the wrong precision (GUC case) or ERROR: relation "t" does not exist (temp-table case). No error surfaces about the lost state.

Why it's reachable in production

The tabularis host companion PR tabularis#1128 ("feat: model pooler mode in ConnectionParams") merged to the host's main on 2026-10-17 — it adds pooler_mode: Option<String> to the host's ConnectionParams and forwards it verbatim to the plugin as part of params. The plugin's ConnectionParams::from_value reads it (src/models.rs:61) and is_transaction_pooler() gates the routing (src/models.rs:70). So the is_transaction_pooler() paths are live once a host build ships pooler_mode. The host's editor explicitly documents running whole scripts on one pooled connection so statements share session state (SET @var, TEMP TABLE).

Scope / mitigations

• On a real transaction-mode PgBouncer, session-level GUCs are already unreliable across autocommitted statements (backends are reassigned per transaction), so the pure-SET-GUC case is less of a new regression there — main already lost it in practice. The clearest NEW regressions are:
  1. the temp-table case (CREATE TEMP TABLE outside a transaction followed by SELECT * FROM t), and
  2. the direct PostgreSQL + pooler_mode=transaction config (a supported config the plugin's own live tests use, where the same TCP connection maps to the same backend, so main preserved within-batch session state).
• The SET search_path case is partially covered: if the host also passes schema, the one-shot applies it via SET LOCAL. Other GUCs (statement_timeout, extra_float_digits, timezone) and CREATE TEMP TABLE have no host param and are genuinely lost.

Root cause

The routing keys on in_transaction (whether an explicit BEGIN ran), not on whether session-local state exists. The PR's comment ("when there's no transaction there's no transaction-local state to lose by using a different connection") conflates transaction-local state (SET LOCAL, temp tables inside BEGIN) with session-local state (SET without LOCAL, CREATE TEMP TABLE outside a transaction), which persists on the shared connection's backend for the held pool connection's lifetime.

Suggested fix direction

Not a small bolt-on — a proper fix is a routing-design change. Options:

• Detect session-local state (prior SET/CREATE TEMP TABLE in the same bare batch) and keep the SELECT on the shared client with a safe pre-resolve, or
• Replay prior session SETs on the one-shot connection before the SELECT, or
• Document that bare [SET, SELECT] batches on transaction-pooler connections require BEGIN/COMMIT to preserve session state (cheapest, but loses an existing within-batch guarantee).

Deserves its own design rather than rushing a fix onto the #169 deadlock PR.

\cc @aesslinger