DEFAULT reconciliation cannot attach the window a live writer is inserting into, and partition_data raises on it

[AlexeyShalaev]

Measured on 1.5.0 against PostgreSQL 17.11: a plain events table with two million rows over the last year, swapped under a new parent as its DEFAULT partition the way guide/partition-existing-table.md says, with one writer inserting now() rows through events every 5 ms the whole time. Nothing else is running.

The first tick plans CREATE events__2026_09 / __2026_10 / __2026_11 (create_ahead). The two future months attach; the current month never does:

--- 3. the first tick: this month and two ahead, this month's rows move out of DEFAULT
Failed to attach after reconciliation retries
...
sqlalchemy.exc.IntegrityError: ... CheckViolationError: updated partition constraint for default partition "events_legacy" would be violated by some row
[SQL: ALTER TABLE "public"."events" ATTACH PARTITION "public"."events__2026_09" FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')]
Attach failed after reconciliation; returned rows to DEFAULT partition
    created 0, issues 0, error 'IntegrityError: ...'
    live rows: 0 in this month's partition, 87 left in DEFAULT
    writer: 19 inserts, p50 0.5 ms, longest 604 ms, 2 over 100 ms

The cause is the shape of _attach_with_reconcile (aio/services/execution.py, and the sync twin): the attach fails on the DEFAULT conflict, the window's rows are moved out of DEFAULT in their own transaction, the attach is retried in another. Between the move's commit and the attach's scan the writer has inserted one more row for the window, so the scan finds it and the attach fails again, DEFAULT_CONFLICT_MAX_RETRIES times in a row, and the rows are moved back. Under any steady write rate the current month cannot be attached, which is the one window every live table is writing into, and the state after the tick is the state before it: every insert keeps landing in DEFAULT. The run reports error set and issues empty; each retry costs the writer a full scan of the two-million-row DEFAULT under ACCESS EXCLUSIVE (the 604 ms above).

partition_data reaches the same window last and does worse: it fills the detached events__2026_09, fails the attach the same way, moves the rows back, and then raises the IntegrityError out of partition_data instead of reporting a move issue with complete=False, which is what the guide promises for a window it cannot handle. The eleven older months drained fine before that (lowest count 29764 of 129764 on the oldest month while it was mid-move, as the guide warns).

What I think the fix is: do the move and the attach in one transaction, under the lock the attach takes anyway. ATTACH already needs ACCESS EXCLUSIVE on the DEFAULT sibling for its scan, so BEGIN; LOCK TABLE <default> IN ACCESS EXCLUSIVE MODE; DELETE ... RETURNING / INSERT; ALTER TABLE ... ATTACH PARTITION ...; COMMIT holds the same lock for the move plus the scan and cannot lose the race; a writer waits for that transaction and is then routed to the new partition. The rows also stop needing a compensating move-back for this path, since a failed attach rolls the move back with it. For a large window the move itself can be long, so it may be worth draining most of the window in batches first (which partition_data already does) and taking the locked move-and-attach only for what is left, so the exclusive window is a batch and a scan, not the whole month. partition_data should report an attach it still cannot do as a move issue and return complete=False, never raise. The DEFAULT reconciliation section of concepts/execution.md and the guide's numbered list follow.

Lab: migrate_lab.py in https://github.com/bedrock-python/bedrock-python.github.io/tree/docs/production-python-series/docs/blog/lab/2026-09-07-partition-existing-table (the writer is Load._writer; steps 3 and 4 are the ones above).

[AlexeyShalaev]

Reproduced on master with the lab, postgres:17-alpine, 2M rows, the writer running: step 3 prints created 0, issues 0, error 'IntegrityError: ... updated partition constraint for default partition "events_legacy" would be violated by some row' and leaves the current month unattached, and step 4 ends with that same IntegrityError coming out of partition_data, so the run never reaches the drain's end.

The cause

The one you name. _attach_with_reconcile moves the window's rows out of DEFAULT in reconcile_default_rows' own transaction and retries the ATTACH in another. That move holds SHARE ROW EXCLUSIVE on the DEFAULT partition, so writers queue behind it — and are let in the moment it commits, which is exactly the gap before the ATTACH takes its ACCESS EXCLUSIVE and scans. One row is enough. With DEFAULT_CONFLICT_MAX_RETRIES = 2 the window then buys two full scans of a two-million-row DEFAULT under ACCESS EXCLUSIVE and a compensating move-back, and converges on nothing.

The design

The shape you propose, put where the library already puts a lock-and-then-several-statements step (the drop path).

A new repository operation, reconcile_and_attach, doing in one transaction: LOCK TABLE ONLY <parent> IN SHARE UPDATE EXCLUSIVE MODE, LOCK TABLE <partition> and LOCK TABLE <default> IN ACCESS EXCLUSIVE MODE — the locks ATTACH takes anyway, parent first, so a concurrent ATTACH on the same parent queues there rather than deadlocking against this one — then the same DELETE … RETURNING / INSERT the reconcile already uses, then ALTER TABLE … ATTACH PARTITION, then the identity re-checks and the orphan-marker clearing the plain attach does. Nothing can put a row into DEFAULT between the move and the scan, because nothing has been able to touch DEFAULT since before the move. And a failed attach rolls the move back with it, so this path needs no compensating move-back at all.

_attach_with_reconcile keeps its shape. Attempt 1 is still the plain, cheap attach_partition. On a DEFAULT conflict it still does the bulk reconcile_default_rows exactly as today — under SHARE ROW EXCLUSIVE, which leaves readers of DEFAULT alone — and attempt 2 is reconcile_and_attach, which takes only what the writer added in the meantime, plus the scan. That is your "drain most of it in batches first, take the locked move-and-attach only for what is left": the exclusive window is a tail and a scan, not a month. partition_data gets it for nothing, since its fill has already drained the window in batches and the tail is whatever landed during the last one.

The second half of the report is the other half of the fix: a window that still cannot be attached has to be an issue, not an exception. On the last retry the executor re-raises the driver's IntegrityError today; it will raise PartitionTopologyError with default_holds_rows instead — which apply() already records in result.issues and goes on from, which is what the failures table in concepts/execution.md has always promised — and partition_data will catch it and return a move issue with complete=False rather than let it out. That also covers the one case the lock cannot reach: a foreign DEFAULT partition, which LOCK TABLE refuses outright.

What I rejected

• More retries. You are right that no retry count converges against a steady writer, and each one costs a full scan of DEFAULT under ACCESS EXCLUSIVE.
• Moving the whole window inside the locked transaction, with no bulk pre-drain. Simpler, but it holds ACCESS EXCLUSIVE on DEFAULT for the length of a month's rows, which blocks readers of DEFAULT too — where today's SHARE ROW EXCLUSIVE bulk move leaves them alone.
• Going straight to the locked form instead of trying the plain attach first. It would save the one failed scan, but it would put an extra statement and a heavier lock on every attach against a table that has a DEFAULT partition, including the common case where DEFAULT holds nothing for the window and the plain attach just works.

reconcile_and_attach is an addition to the PartitionRepository protocol, so an implementation written from scratch gains one method; subclasses of PostgresPartitionRepository are untouched. Both mirrors get the change and the same test — an integration test with a writer inserting into the window's hot end while the attach runs, which fails on master and passes with this. concepts/execution.md, guide/partition-existing-table.md and agents.md follow.

[AlexeyShalaev]

Shipped in #65.

The move and the attach now share a transaction. A new repository operation, reconcile_and_attach, takes EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the partition and on the DEFAULT sibling, moves what is left of the window, and attaches. Nothing can put a row into DEFAULT between the move and the scan, and a failed attach rolls the move back with it — so no compensating move-back on that path either. The bulk of the window still moves first in the reconcile's own transaction, under the lighter SHARE ROW EXCLUSIVE that leaves readers alone, so what the exclusive lock covers is a tail and a scan, not a month. That is your "drain most of it in batches first, take the locked move-and-attach only for what is left"; partition_data gets it for nothing, since its fill has already done the batches.

One thing came out different from the sketch, and it is worth knowing about. I first took SHARE UPDATE EXCLUSIVE on the parent — what ATTACH itself takes — and the new test failed, but on the writer's insert rather than on the attach:

CheckViolation: new row for relation "events_legacy" violates partition constraint

An INSERT picks its partition from the set it saw when it took ROW EXCLUSIVE on the parent. SHARE UPDATE EXCLUSIVE does not conflict with that, so an insert can route to DEFAULT, queue on the lock the attach holds over it, and come out of the wait still aimed at DEFAULT — where the constraint the attach just narrowed rejects it. It does not re-route; the write is lost. That is what a plain ATTACH does to a live writer today, and it only became visible here because the attach finally succeeds. EXCLUSIVE on the parent — one level up, conflicting with ROW EXCLUSIVE but not with ACCESS SHARE — makes the insert wait before it chooses, so it re-plans and lands in the new partition with its statement unchanged, while readers of the other partitions carry on. It is written up in design/postgresql-semantics.md.

And the second half of your report: a window that still cannot be attached is now a default_holds_rows finding rather than the driver's exception. apply() records it in result.issues and the run goes on — what the failures table promised all along — and partition_data returns a move issue with complete=False instead of raising. That also covers the one case the lock cannot reach: a foreign DEFAULT partition, which LOCK TABLE refuses outright.

Your lab, unmodified, against the branch:

--- 3. the first tick: this month and two ahead, this month's rows move out of DEFAULT
    created 3, issues 0, error None, 806 ms
    live rows: 85 in this month's partition, 0 left in DEFAULT
    writer: 43 inserts, p50 0.5 ms, longest 424 ms, 2 over 100 ms
--- 4. the drain: partition_data in batches of 50,000, oldest month first
    5 calls, 47 batches, 1,964,338 rows moved into 12 partitions in 10.6 s
    writer: 1480 inserts, p50 0.7 ms, longest 43 ms, 0 over 100 ms

No writer errors in either step, no issues, nothing raised. The tests that hold this are integration tests in both mirrors with a writer inserting into the window while the attach runs — a task in the aio suite, a thread in tests/integration/sync/test_concurrency.py. Against master's library sources all four fail with the CheckViolation you reported; against the branch they pass.

Nothing to change on your side. The PartitionRepository protocol gains reconcile_and_attach, so only an implementation written from scratch (rather than a subclass of PostgresPartitionRepository) has one method to add; guide/extending.md describes its contract.

One more thing the lab turned up on the way, unrelated to this: step 6 dies on DROP TABLE events_legacy because BIGSERIAL leaves the sequence owned by the old table while every partition's id default draws from it. ALTER SEQUENCE events_id_seq OWNED BY events.id before the drop, and it goes through with the ids intact — CASCADE takes the sequence and every default with it, and the next insert fails on NOT NULL. That is now a note in the guide's last step.

[AlexeyShalaev]

Shipped in #65, which is merged. The lab's step 3 now attaches the current month on the first tick with a live writer inserting into it, and partition_data no longer raises on that window. A release goes out shortly; I will re-measure the lab against it.