VACUUM FULL, CLUSTER, and REPACK block on other sessions' temp tables

First seen: 2026-03-24 15:35:39+00:00 · Messages: 14 · Participants: 5

Latest Update

2026-05-06 · opus 4.7

VACUUM FULL / CLUSTER / REPACK blocking on other sessions' temp tables

The Core Problem

PostgreSQL's database-wide maintenance commands — VACUUM FULL, CLUSTER (without an argument), and the newly-introduced REPACK — iterate over all tables in the database that the caller is eligible to process, acquiring AccessExclusiveLock on each one in turn. The list of target relations is assembled by scanning pg_class (via get_all_vacuum_rels() for VACUUM, and get_tables_to_repack() for REPACK).

The bug Jim Jones reported is a consequence of an architectural gap in those selection routines: temporary tables owned by other backends are visible in pg_class (they live in per-backend pg_temp_N schemas that any session can see in the catalog), so they get swept into the target list. When the maintenance command then tries to lock them, it blocks indefinitely on locks held by the owning backend — even though the current session could not meaningfully operate on those relations anyway (they are inaccessible as data: only the owning backend can read/write a temp table's buffers, and the relfilenode lives in the owning backend's temp-buffer pool).

The reproducer is minimal: session 1 creates a temp table and takes any lock on it; session 2's REPACK/VACUUM FULL/CLUSTER hangs on AccessExclusiveLock acquisition for that OID. Prior to the fix this was strictly a correctness/UX problem — a routine maintenance command on an unrelated permanent schema could be held hostage by any idle session that happened to hold a lock on a temp table.

Why It Matters Architecturally

Three things make this more than a cosmetic issue:

  1. Temp tables are private by contract. Another session cannot even read the pages (the buffers are in localbuf.c, not shared buffers). Attempting to VACUUM FULL one is semantically meaningless; the right behavior is to skip, exactly as autovacuum already does for other-session temp relations.
  2. The blocking is on AccessExclusiveLock. This is the strongest lock mode; even a LOCK TABLE ... IN SHARE MODE in the owning session is enough to stall the maintenance command. In practice this means any long-running transaction in any session that has ever touched a temp table can freeze database-wide maintenance.
  3. The asymmetry with autovacuum. Autovacuum already filters other-session temp relations out of its worklist; interactive VACUUM FULL / CLUSTER did not. The fix restores parity.

The Fix and the Subtle API Choice

Jim's v1 patch filtered with isOtherTempNamespace(class->relnamespace). Chao Li pointed out a subtlety that drove the final shape of the patch: isOtherTempNamespace() is explicitly marked obsolete in its header comment, which recommends RELATION_IS_OTHER_TEMP() instead. But RELATION_IS_OTHER_TEMP takes a Relation and checks rd_islocaltemp, a flag set in RelationBuildDesc(). In the selection code we only have a Form_pg_class (the catalog row) — no open relcache entry yet — so we cannot use rd_islocaltemp.

The key insight is the distinction between these three namespace predicates:

And critically, rd_islocaltemp is set precisely via isTempOrTempToastNamespace() in RelationInitPhysicalAddr, not via isOtherTempNamespace(). The comment in relcache.c explains why: a pg_class entry can be left over from a crashed backend whose ProcNumber we now reuse. That stale entry must not be treated as local, even though GetTempNamespaceProcNumber() would return MyProcNumber. So the correct pre-relcache check is:

relpersistence == RELPERSISTENCE_TEMP && !isTempOrTempToastNamespace(relnamespace)

This is what v2/v3 adopt for the get_all_vacuum_rels() and the pg_class-scan branch of get_tables_to_repack(). In the usingindex path of get_tables_to_repack() there is no Form_pg_class available up front — only an index's indrelid. Without relpersistence to pre-filter, one cannot use !isTempOrTempToastNamespace(nsp) alone, because that would also skip permanent tables (everything that isn't in one's own temp schema). Hence the conjunction:

if (!isTempOrTempToastNamespace(nsp) && isAnyTempNamespace(nsp))
    /* skip */

This is logically what isOtherTempNamespace() computes; Jim inlined it to avoid the obsolete wrapper.

Syscache Efficiency Concern

Zsolt Parragi noted that the usingindex branch already does a SearchSysCacheExists1 right before the new code, and then the fix calls get_rel_namespace() (another syscache lookup) plus two namespace predicates (each touching catalogs on first use). v3 consolidates this by doing a single SearchSysCache1(RELOID, ...) and reading relpersistence + relnamespace out of the returned tuple, matching the style of the sibling branch. This is a minor improvement — the path is not hot — but it keeps the code uniform.

Testing and Committer Judgment

Jim added a TAP test in 0002 exercising all three commands. Chao and Antonin Houska pushed back that the selection loops already have unattested branches (e.g. the relkind filter directly above) and cited prior hackers discussions where gratuitous test additions were discouraged because of aggregate test-suite runtime.

Álvaro Herrera, committing the fix, agreed with the skeptics: he pushed 0001 but declined 0002, noting the test cost is "three full-database scans … plus one extra initdb," which is disproportionate for a branch whose behavior is simple to reason about. This is a reasonable tradeoff call — isolation tests for lock-contention scenarios are expensive, and the fix is small and self-evident.

Backpatch Decision

Álvaro explicitly chose not to backpatch despite the bug existing since at least PG14. His reasoning is worth noting: no user had ever complained, and backpatching behavior-changing fixes into stable branches risks destabilizing deployments that may have adapted to or worked around the current behavior. He left the door open ("reconsider after this month's minors") if anyone shows up with a strong need. This is consistent with project policy: silent long-standing misbehavior that is easily worked around (don't run global VACUUM FULL while temp tables are locked) generally does not meet the bar for backpatch.

Implications