"Table t_N does not exist" (42P01): SQLTemporaryPool loses sync with backend temp tables — lifecycle races, rollback compensation gaps, no self-heal in aborted transactions

[CrushBy]

Summary

A production installation (~270 concurrent sessions, PostgreSQL) shows waves of 42P01 errors relation "t_N" does not exist on temporary tables. Root-cause investigation (sources + diagnostic logging deployed to production) identified two independent mechanisms that kill physical temp tables while their names/references stay registered, plus the absence of self-healing that turns each incident into repeated user-visible errors.

Line references: v6 @ 82521b44.

Background: SQLTemporaryPool (per ExConnection) is a Java-side registry of t_N names; physical temp tables are session-scoped per JDBC connection. Once the registry and the backend diverge, a stale name keeps being handed out (or a live SessionTable keeps referencing a dead table), and every use fails with 42P01. Observed pattern: errors come in batches per connection, each name fails 2–6 times, and apply retries walk across several stale names in a row.

---

Part 1 — root causes (why tables die while still registered/referenced)

1.1 Tables fall out of sessionTablesMap while still referenced; connection restart then kills them (main production source)

restartConnection migrates only sessionTablesMap.keySet() to the new physical connection; every other temp table dies with the old one. Tables can fall out of sessionTablesMap/accounting while still referenced due to races in asynchronous closes:

• TABLE WAS REMOVED BEFORE asserts with stacks in SQLSession.registerSessionChange during formApply running on a pausable-daemon thread;
• TABLE WAS DROPPED BEFORE (double SessionTableUsage.drop) — 87 hits over two days;
• UPDATED RETURNED TABLE (checkTableOwner) — writes into an already-returned table, stack shows a TIMER thread asynchronously closing a RemoteSession/form (localClose → DataSession.onClose → dropTables → SessionTableUsage.drop) returning a table a second time;
• FORM CREATED IN TRANSACTION SHOULD BE CLOSED IN TRANSACTION — forms surviving the transaction that created their tables.

Direct confirmation from the enriched 42P01 log: errors with IN TRANSACTION : true on the session's uncommitted-changes tables (debug info: upktable, changed-property tables) started 70 seconds after that connection's scheduled restart (CONNECTION STARTED == restart timestamp, restart migrated almost nothing). From that moment every apply() of the session fails — the user's pending changes are effectively lost until relogin.

Note this class is worse than the visible error: a table returned to the pool while a live owner keeps writing to it also means silent data corruption (truncate/reuse under a live owner), not just 42P01.

Proposed direction (root cause): make SessionTableUsage.drop/table returns idempotent and race-free across async close paths (pendingCleaners, TIMER-thread closes), and revisit the WeakReference model of sessionTablesMap. Mitigation that works today: keep percentRestartConnections at the default 1 (the installation ran at ~5 ⇒ 13–14 restarts/min; every restart materializes accumulated lifecycle violations into user-visible errors).

1.2 Rollback of a transaction leaves names of tables created inside it in the pool

CREATE TEMPORARY TABLE is transactional in PostgreSQL — rollback removes the physical table. rollbackTransaction has a compensation loop (removes transactionTables from the pool, resets the name counter), but it runs only at inTransaction == 1, while endTransaction clears transactionCounter/transactionTables at every nesting level. A nested startTransaction path (e.g. synchronizeDB-style flows) whose inner level ends before the outer level rolls back loses the registry ⇒ a whole batch of stale names per rollback.

Production confirmation: errored tables were created after the connection's previous restart and died with no restart in between (42P01 with IN TRANSACTION : false on a connection alive since its last restart), i.e. rollback is the only possible killer; and each next restart's pool-cleanup list shows contiguous t_N ranges with gaps at exactly the previously errored names (the pool only healed via the error path).

Proposed fix (root cause): clear transaction registries only at the outer level (inTransaction == 1); make popVolatileStats symmetric with pushVolatileStats (outer level only); startTransaction/attemptCountMap need save/restore on a stack for nested levels (an inner DataSession passes its own attempt map, so "don't overwrite" is not enough).

---

Part 2 — consequences: the pool must self-heal (currently it doesn't inside transactions)

Whatever the root cause, a single stale name today produces a stream of errors. Four defects prevent self-healing:

2.1 No self-heal in aborted transactions

On 42P01 inside a transaction handle() sets problemInTransaction = EXCEPTION; truncate() then silently no-ops (if (problemInTransaction == null)), so returnTemporaryTable sees no exception and skips its removeTable/drop branch — the stale name goes straight back to the pool and is handed out again on retry. A diagnostic counter recorded 1757 "return without truncate" events in 26 minutes under load; same-name errors repeated up to 6 times within a minute, and apply retries walked across several stale names.

Fix: pass a forceRemove flag into returnTemporaryTable when the 42P01 message names the target table (parse the quoted name from the message — 42P01 may refer to another missing relation in the SELECT part). In the forceRemove branch remove the name from the pool only, no DROP (the table doesn't exist; in an aborted transaction DROP would fail with 25P02 and mask the original exception via the suppressed-exception chain).

2.2 SQLTemporaryPool.removeTable throws NPE on double removal

Double removal (possible once owner invariants are violated, see Part 1) breaks whole cleanup loops — e.g. in rollbackTransaction one runSuppressed wraps the entire compensation loop, so one NPE aborts cleanup of the remaining tables. Fix: idempotent removeTable with a non-throwing diagnostic (sqlSuppLog, not assertLog — the latter throws under -ea).

2.3 Dead-name markers — universal self-heal on hand-out

On any 42P01, parse all quoted t_\d+ names from the message into a deadTemporaryTables set on ExConnection. SQLTemporaryPool.getTable skips and removes dead matches before reuse (collect first, remove after the loop — no modification inside the iteration; re-fetch matchTables after removals since removing the last element drops the tables[fieldStruct] mapping). Marker lifecycle: cleared in a single removePoolTable helper used by every final-removal branch, cleared on re-CREATE of the same name (counter reuse after rollback), and cleared wholesale when the physical connection is replaced (restart). Limitation: this heals the pool, not already-handed-out SessionTable references — those are Part 1.

2.4 removeUnusedTemporaryTables: a dead entry gets stuck forever

truncateSession runs before iterator.remove(); if the table is already physically gone, truncate throws 42P01 and the entry stays in sessionTablesMap ⇒ every subsequent getTemporaryTable of that session fails at the same place. Fix: catch isTableDoesNotExist ⇒ remove the entry from pool + map (removePoolTable), continue.

---

Evidence summary (diagnostic build, anonymized)

Signal	Value
42P01 errors in sql.log	337 over ~18 h, in per-connection batches
"return without truncate (problem in transaction)" detector	1757 in 26 min
Restart pool-cleanup lists vs errored names	contiguous t_N ranges with gaps at exactly the errored names
Enriched 42P01 (batch A)	IN TRANSACTION : true, CONNECTION STARTED = 70 s before, on uncommitted-changes tables
Enriched 42P01 (batch B)	IN TRANSACTION : false, connection alive since last restart ⇒ rollback killed the tables
TABLE WAS DROPPED BEFORE / TABLE WAS REMOVED BEFORE / UPDATED RETURNED TABLE	87 / 24 / 3 over two days
NESTED APPLY / USER INTERACTION IN TRANSACTION / FORM CREATED IN TRANSACTION...	547 / 276 / 12 per day
percentRestartConnections	~5 (default 1) ⇒ 13–14 restarts/min across ~270 sessions

Suggested priority: Part 2 fixes (2.1–2.4) are low-risk and immediately cap each incident at ≤1 error per name; Part 1.2 is a contained fix; Part 1.1 needs a deeper race analysis by maintainers (and is also a silent-data-corruption risk, so arguably the most important long-term).