Executing DDL¶

How the plan becomes statements: what runs in which transaction, what locks are taken, what an interruption leaves behind. All of it was measured on PostgreSQL 15 and 17 (details in PostgreSQL semantics).

One statement, one transaction¶

Every statement the executor runs commits on its own. A partition is created in one transaction and attached in another; a detach and the drop that follows it are separate statements. There is no long transaction wrapping a run, because DETACH … CONCURRENTLY cannot run inside a transaction block at all, and because a run that creates three partitions and fails on the fourth should keep the three.

Two steps are exceptions, and both for the same reason: a lock has to span more than one statement or the thing it protects can change underneath. A drop takes its lock, revalidates, drains and drops in one transaction; the last step of DEFAULT reconciliation moves the window's remaining rows and attaches in one transaction. ATTACH, unlike DETACH … CONCURRENTLY, may run inside a transaction block.

The consequence for callers: pass the service an engine, never a session you are using elsewhere.

Create standalone, attach last¶

A partition is never created with CREATE TABLE … PARTITION OF. That statement takes ACCESS EXCLUSIVE on the parent and stalls every writer routing through it. Instead:

1. CREATE TABLE child (LIKE parent INCLUDING ALL EXCLUDING IDENTITY) [PARTITION BY …] — a standalone table, ACCESS SHARE on the live parent only. Identity columns are excluded because a partition may not carry one; the parent's identity propagates on attach.
2. Its own subtree is built the same way and attached inside it, deepest first.
3. ALTER TABLE parent ATTACH PARTITION child FOR VALUES … — SHARE UPDATE EXCLUSIVE on the parent, ACCESS EXCLUSIVE on the child (and on a DEFAULT sibling, which is scanned for rows the new partition would claim).

Until step 3 commits the child is invisible to row routing. An interruption anywhere before it leaves a detached table no writer can reach — never a live branch that rejects part of its keyspace. The next run finds the table, completes its subtree, and attaches it.

  step 1            step 2                     step 3
  CREATE branch     CREATE + ATTACH buckets    ATTACH branch to parent
  (standalone)      (inside the branch)        (goes live, whole)

A lost race with another worker — the name already exists and it is attached with the planned bounds — is benign. The same name attached with other bounds is a conflict, reported as name_unusable.

DEFAULT reconciliation¶

When a RANGE partition is attached and the parent's DEFAULT partition holds rows that belong to the new window, PostgreSQL refuses the attach (23514). The executor:

1. moves those rows from DEFAULT into the new partition, naming columns on both sides (ATTACH matches by name, so physical column order may differ) and leaving rows with a NULL trailing key where PostgreSQL routes them. This is one statement under SHARE ROW EXCLUSIVE on DEFAULT: writers wait, readers do not;
2. moves whatever arrived in the meantime and attaches, in one transaction, under EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the partition and on DEFAULT — taken in that order, and taken before the move.

Step 2 is what makes the window a live writer is inserting into attachable at all. Two transactions cannot do it: the move commits, DEFAULT is free again, and the next insert for the window lands in it before ATTACH scans — under any steady write rate that never converges, and every attempt is a full scan of DEFAULT under ACCESS EXCLUSIVE. Sharing one lock closes the gap. Step 1 is what keeps that exclusive window short: the bulk of the month moves before the heavy lock is taken, so what step 2 covers is a tail and a scan.

The parent's lock is one level above what ATTACH itself takes, and it is there for the writer. An INSERT picks its partition from the set it saw when it took ROW EXCLUSIVE on the parent: one that got that far and then queued on DEFAULT's lock would come out of the wait still aimed at DEFAULT and be rejected by the constraint the attach just narrowed — a lost write, and what a plain ATTACH does to a live writer. Blocking at the parent instead means no insert is ever mid-routing while the partition set changes: the writer waits, re-plans, and lands in the new partition, its statement unchanged. EXCLUSIVE does not conflict with ACCESS SHARE, so readers of the other partitions carry on.

If the attach still fails, the rows step 1 moved are returned to DEFAULT rather than left in a table no query can see — step 2's own move rolls back with the attach, and needs no compensation. A window that still cannot be attached is reported as default_holds_rows, an issue like any other: the run goes on, and partition_data returns it rather than raising. For a nested branch the moved rows are routed onward into its leaves. A DEFAULT sibling holding rows for a hash or list member is reported rather than moved: only a RANGE window can be selected by its key. A foreign DEFAULT partition cannot be locked at all, so there the race stays and the report is all there is.

Detach¶

DetachMode.AUTO runs DETACH … CONCURRENTLY on an autocommit connection — SHARE UPDATE EXCLUSIVE on the parent, readers and writers untouched — and falls back to the blocking form when PostgreSQL refuses the concurrent one, which it does when a DEFAULT partition exists. The marker is written before the detach, and with the plan's OID the detach fails closed, after the before_detach hooks ran: the blocking form checks identity and attachment inside its own transaction and re-checks after the statement, so a swapped-in relation rolls everything back — marker included; the concurrent form pins the relation (a holder connection takes ACCESS SHARE and verifies the OID under it) while it is checked and marked, and releases the pin only once that statement's own backend is queued for the partition's lock — from there a swap can only make the statement fail, never redirect it. A statement that has still not got that far when the DDL timeout expires is cancelled and waited for before the pin goes, so a late DETACH can never fire at a name that changed hands in the meantime. A foreign relation cannot be pinned and uses the transactional blocking form instead. Either way a swap gets a PlanStaleError, not a detached replacement.

A DETACH CONCURRENTLY interrupted mid-way (a statement timeout, a killed connection) leaves the partition in a pending state: still attached in the catalog, invisible through the parent, rejecting its own rows. The next maintain() call reports it as detach_pending, completes it with DETACH … FINALIZE first (reason detach_finalize), and re-plans under the same lock: the finalized table comes back as an orphan that the same call re-attaches — its window still wanted, its data intact — or retires under the drop policy.

Detaching a branch keeps its subtree intact and readable through the branch.

Drop¶

A drop runs in one transaction: SET lock_timeout, LOCK TABLE … IN ACCESS EXCLUSIVE MODE, revalidate OID, attachment and marker, drain the remaining rows when the caller asked for it (drain_into — unpartition's guarantee, with the moved count reported), drop the partition's own foreign keys, then DROP TABLE — DROP FOREIGN TABLE for a foreign leaf, which cannot be locked and carries no constraints. Dropping a partitioned branch takes its children with it; CASCADE is never used. Lock contention is retried with exponential backoff (drop_lock_timeout_ms, drop_max_retries, drop_retry_delay, drop_max_backoff on the repository).

Lock levels, measured¶

CREATE TABLE … PARTITION OF — ACCESS EXCLUSIVE on the parent — is the one statement the library never issues against a live parent. Each operation reports the heaviest lock it takes on op.capabilities.

Failures¶

PartitionMaintainer.run_maintenance_safe() catches everything, cancellation included, and reports it on result.error.

What an interruption leaves behind¶

Because attach is last and the marker is written first, a run cut off at any point leaves one of a small number of states, each of which the next run converges:

Hooks¶

Eight hooks fire around create, attach, detach and drop, once per lifecycle unit — the partition under the root, never per leaf of its subtree — and once per member of a root HASH or LIST. A before_* hook that raises aborts that operation (the partition comes back on the next run); an after_* hook that raises is logged and re-raised — the operation has already happened, but the run aborts unless continue_on_error. Hooks decide nothing about which partitions come and go; they react. See Archive before dropping.