A successful ALTER TABLE ... ATTACH PARTITION is an easy green signal to overread. It proves that PostgreSQL completed that statement. It does not, by itself, tell you how PostgreSQL established that the attached table’s preexisting rows satisfy the new partition bound.
For an ordinary table without sufficient existing constraints, PostgreSQL documents a full table scan to check those rows. When a valid CHECK constraint is strong enough to prove the desired partition constraint, PostgreSQL can avoid that ATTACH PARTITION validation scan. The operational distinction matters: in the second case, the row population is not trusted merely because ATTACH returned successfully; the valid constraint supplies the proof that makes another scan unnecessary. PostgreSQL separately documents that adding an ordinary CHECK constraint normally scans existing rows, and that a previously NOT VALID constraint requires a later validation scan before it becomes validated.
Which evidence establishes that every retained row satisfied the partition bound? The acceptance decision depends on which operation established that fact, which locks protected it, and what happened when the candidate contained a deliberately incompatible row.
The disposable fixture uses synthetic data, a pinned PostgreSQL version and independently authored expectations. The environment lacked psql, postgres and Docker, so every database outcome in the manifest and ledger is labeled EXPECTED / NOT EXECUTED.
Define the exact acceptance contract before trusting ATTACH
PostgreSQL 19.0’s ALTER TABLE documentation gives the core contract. For a regular table being attached, PostgreSQL performs a full table scan to ensure existing rows do not violate the new partition constraint. A valid CHECK constraint that permits only rows satisfying the desired partition constraint can make that scan unnecessary. A foreign table is an explicit exception: PostgreSQL says it does not verify all of its rows during attachment.
The acceptance contract for this playbook is deliberately narrower than “partitioning works.” For each ordinary-table case, evidence must identify the candidate relation, parent relation, exact bound, preexisting rows, relevant CHECK state, outcome of ATTACH, final partition membership, and any error. For the lock case, evidence must additionally show the parent and candidate relation locks from the backend performing ATTACH, plus external probes demonstrating which competing lock requests can and cannot proceed.
The central state contract is:
Claim | Evidence required | Failure meaning |
Candidate population is admissible | Synthetic input inventory plus successful validation path | REPAIR or HOLD |
Dirty preexisting row is detected | Rejected ATTACH for a known out-of-bound row | HOLD if the negative case unexpectedly attaches |
Matching valid CHECK is usable as proof | Constraint definition/state plus successful attachment under the documented optimization | RERUN if evidence is incomplete |
NOT VALID is not treated as equivalent proof | Dirty negative-control table with matching CHECK ... NOT VALID still rejected | HOLD on unexpected success |
Parent lock is bounded | ShareUpdateExclusiveLock observed for the attaching backend | HOLD on unexplained divergence |
Attached table lock is stronger | AccessExclusiveLock observed on candidate | HOLD on unexplained divergence |
Failed attachment leaves no false partition state | Catalog reconciliation after failure | REPAIR/HOLD |
Repair changes only the offending boundary | Before/after row evidence plus successful rerun | REPAIR until verified |
Rollback is understood | Rolled-back attachment absent from partition hierarchy | RERUN/HOLD if ambiguous |
The lock expectations come directly from PostgreSQL’s ATTACH PARTITION documentation: attachment takes SHARE UPDATE EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the table being attached; a default partition, when present, also receives ACCESS EXCLUSIVE. PostgreSQL’s explicit-locking table says SHARE UPDATE EXCLUSIVE conflicts with several schema-oriented modes but not ordinary ROW EXCLUSIVE, while ACCESS EXCLUSIVE conflicts with every table-level lock mode and is the only table lock that blocks a plain SELECT.
An unvalidated check matters when it is the proposed proof for attachment. Refonte Learning’s guide to a staged PostgreSQL NOT VALID CHECK rollout explains when VALIDATE CONSTRAINT establishes that preexisting rows satisfy the check.
Pin the runtime, build the disposable fixture, and record identity
PostgreSQL’s official versioning page lists November 19, 2026 as the release date for PostgreSQL 19. As of November 20, 2026, version 19.0 was the current release in that series. The fixture pins the server to 19.0.
Bind each observation to the server and client versions used in the run. Earlier supported branches also implement ATTACH PARTITION, but the documented claims here apply to PostgreSQL 19.
The runtime fingerprint must capture more than SELECT version(). At minimum retain server_version, server_version_num, psql --version, operating-system identity, database name, server start time, backend PID for each concurrent session, application_name, fixture source digest and timestamps. The harness below refuses to commission a server whose server_version_num is not 190000.
Runtime fingerprint pass condition: PASS only when the actual server reports 19.0/190000, the client version is retained, the harness digest is recorded, and all session PIDs used in lock evidence can be mapped to their declared application_name. Any other version is HOLD for version-specific review, not an automatic failure of PostgreSQL.
The fixture uses a temporary PostgreSQL cluster rather than shared application schemas. It requires PostgreSQL 19.0 client/server binaries on PATH. It does not require extensions, external data or production credentials.
#!/usr/bin/env bash
# attach_partition_lab.sh
set -euo pipefail
ROOT="$(mktemp -d "${TMPDIR:-/tmp}/pg-attach-lab.XXXXXX")"
PGDATA="$ROOT/data"
SOCK="$ROOT/socket"
EVIDENCE="$ROOT/evidence"
DB="attach_lab"
mkdir -p "$SOCK" "$EVIDENCE"
cleanup() {
if [[ -f "$PGDATA/postmaster.pid" ]]; then
pg_ctl -D "$PGDATA" -m immediate stop >/dev/null 2>&1 || true
fi
}
trap cleanup EXIT
sha256_file() {
if command -v sha256sum >/dev/null 2>&1; then
sha256sum "$1" | awk '{print $1}'
else
shasum -a 256 "$1" | awk '{print $1}'
fi
}
echo "harness_sha256=$(sha256_file "$0")" > "$EVIDENCE/run.meta"
psql --version >> "$EVIDENCE/run.meta"
initdb -D "$PGDATA" --no-locale --encoding=UTF8 >"$EVIDENCE/initdb.log"
pg_ctl -D "$PGDATA" \
-o "-k '$SOCK' -p 55432 -c listen_addresses='' -c log_min_messages=debug1" \
-l "$EVIDENCE/postgres.log" start
createdb -h "$SOCK" -p 55432 "$DB"
PSQL=(psql -X -v ON_ERROR_STOP=1 -h "$SOCK" -p 55432 -d "$DB")
"${PSQL[@]}" -Atqc "
SELECT 'server_version=' || current_setting('server_version');
SELECT 'server_version_num=' || current_setting('server_version_num');
SELECT 'database=' || current_database();
SELECT 'server_start=' || pg_postmaster_start_time();
" >> "$EVIDENCE/run.meta"
ACTUAL="$("${PSQL[@]}" -Atqc "SHOW server_version_num")"
if [[ "$ACTUAL" != "190000" ]]; then
echo "HOLD: expected server_version_num=190000, got $ACTUAL" >&2
exit 20
fi
"${PSQL[@]}" >"$EVIDENCE/setup.log" <<'SQL'
CREATE SCHEMA lab;
CREATE TABLE lab.events (
event_id bigint NOT NULL,
occurred_on date NOT NULL,
payload text NOT NULL
) PARTITION BY RANGE (occurred_on);
SQL
run_sql() {
local case_id="$1"
local file="$EVIDENCE/${case_id}.sql"
local out="$EVIDENCE/${case_id}.out"
cat >"$file"
set +e
"${PSQL[@]}" -v VERBOSITY=verbose -f "$file" >"$out" 2>&1
local rc=$?
set -e
printf '%s\t%s\n' "$case_id" "$rc" >> "$EVIDENCE/exit_codes.tsv"
}
# C01: valid rows, no proving CHECK. Docs say ATTACH performs validation scan.
run_sql C01 <<'SQL'
SET application_name='attach-lab/C01';
CREATE TABLE lab.c01 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c01 VALUES
(101, DATE '2026-01-01', 'lower-bound'),
(102, DATE '2026-01-31', 'inside');
ALTER TABLE lab.events
ATTACH PARTITION lab.c01
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
SELECT tableoid::regclass, event_id, occurred_on
FROM lab.events
WHERE occurred_on >= DATE '2026-01-01'
AND occurred_on < DATE '2026-02-01'
ORDER BY event_id;
ALTER TABLE lab.events DETACH PARTITION lab.c01;
DROP TABLE lab.c01;
SQL
# C02: dirty preexisting row must reject attachment.
run_sql C02 <<'SQL'
\set ON_ERROR_STOP off
SET application_name='attach-lab/C02';
CREATE TABLE lab.c02 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c02 VALUES
(201, DATE '2026-02-10', 'inside'),
(202, DATE '2026-03-01', 'upper-bound-outside');
ALTER TABLE lab.events
ATTACH PARTITION lab.c02
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
\echo attach_sqlstate=:SQLSTATE
SELECT EXISTS (
SELECT 1
FROM pg_inherits
WHERE inhparent='lab.events'::regclass
AND inhrelid='lab.c02'::regclass
) AS attached_after_failure;
SQL
# C03: validated CHECK matching the intended range.
run_sql C03 <<'SQL'
SET application_name='attach-lab/C03';
CREATE TABLE lab.c03 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c03 VALUES
(301, DATE '2026-03-01', 'lower-bound'),
(302, DATE '2026-03-20', 'inside');
ALTER TABLE lab.c03 ADD CONSTRAINT c03_bound
CHECK (occurred_on >= DATE '2026-03-01'
AND occurred_on < DATE '2026-04-01');
SELECT conname, convalidated, conenforced,
pg_get_constraintdef(oid, true)
FROM pg_constraint
WHERE conrelid='lab.c03'::regclass
ORDER BY conname;
ALTER TABLE lab.events
ATTACH PARTITION lab.c03
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
ALTER TABLE lab.events DETACH PARTITION lab.c03;
DROP TABLE lab.c03;
SQL
# C04: negative control. Dirty row predates matching NOT VALID CHECK.
run_sql C04 <<'SQL'
\set ON_ERROR_STOP off
SET application_name='attach-lab/C04';
CREATE TABLE lab.c04 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c04 VALUES
(401, DATE '2026-04-10', 'inside'),
(402, DATE '2026-05-01', 'outside');
ALTER TABLE lab.c04 ADD CONSTRAINT c04_bound
CHECK (occurred_on >= DATE '2026-04-01'
AND occurred_on < DATE '2026-05-01') NOT VALID;
SELECT conname, convalidated, conenforced
FROM pg_constraint
WHERE conrelid='lab.c04'::regclass
AND conname='c04_bound';
ALTER TABLE lab.events
ATTACH PARTITION lab.c04
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
\echo attach_sqlstate=:SQLSTATE
SELECT EXISTS (
SELECT 1 FROM pg_inherits
WHERE inhparent='lab.events'::regclass
AND inhrelid='lab.c04'::regclass
) AS attached_after_negative_control;
SQL
# C05 setup for the concurrent lock probe.
"${PSQL[@]}" <<'SQL'
CREATE TABLE lab.c05 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c05 VALUES (501, DATE '2026-05-10', 'inside');
ALTER TABLE lab.c05 ADD CONSTRAINT c05_bound
CHECK (occurred_on >= DATE '2026-05-01'
AND occurred_on < DATE '2026-06-01');
SQL
"${PSQL[@]}" >"$EVIDENCE/C05_session_A.out" 2>&1 <<'SQL' &
SET application_name='attach-lab/C05-A';
BEGIN;
ALTER TABLE lab.events
ATTACH PARTITION lab.c05
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
SELECT pg_backend_pid() AS attach_pid;
SELECT pg_sleep(8);
COMMIT;
SQL
A_PID=$!
sleep 1
"${PSQL[@]}" >"$EVIDENCE/C05_locks.out" <<'SQL'
SELECT a.pid, a.application_name,
l.relation::regclass AS relation,
l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE a.application_name='attach-lab/C05-A'
AND l.locktype='relation'
AND l.relation IN ('lab.events'::regclass, 'lab.c05'::regclass)
ORDER BY relation::text, mode;
SQL
set +e
"${PSQL[@]}" >"$EVIDENCE/C05_parent_access_share.out" 2>&1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout='500ms';
LOCK TABLE lab.events IN ACCESS SHARE MODE;
ROLLBACK;
SQL
echo -e "C05_parent_access_share\t$?" >> "$EVIDENCE/exit_codes.tsv"
"${PSQL[@]}" >"$EVIDENCE/C05_parent_share.out" 2>&1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout='500ms';
LOCK TABLE lab.events IN SHARE MODE;
ROLLBACK;
SQL
echo -e "C05_parent_share\t$?" >> "$EVIDENCE/exit_codes.tsv"
"${PSQL[@]}" >"$EVIDENCE/C05_child_access_share.out" 2>&1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout='500ms';
LOCK TABLE lab.c05 IN ACCESS SHARE MODE;
ROLLBACK;
SQL
echo -e "C05_child_access_share\t$?" >> "$EVIDENCE/exit_codes.tsv"
set -e
wait "$A_PID"
"${PSQL[@]}" <<'SQL'
ALTER TABLE lab.events DETACH PARTITION lab.c05;
DROP TABLE lab.c05;
SQL
# C06: default partition contains a row belonging to proposed new range.
run_sql C06 <<'SQL'
\set ON_ERROR_STOP off
SET application_name='attach-lab/C06';
CREATE TABLE lab.events_default PARTITION OF lab.events DEFAULT;
INSERT INTO lab.events VALUES (601, DATE '2026-06-15', 'currently-default');
CREATE TABLE lab.c06 (LIKE lab.events INCLUDING ALL);
ALTER TABLE lab.events
ATTACH PARTITION lab.c06
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
\echo attach_sqlstate=:SQLSTATE
SELECT tableoid::regclass, event_id, occurred_on
FROM lab.events WHERE event_id=601;
DROP TABLE lab.c06;
DROP TABLE lab.events_default;
SQL
# C07: smallest repair then rerun.
run_sql C07 <<'SQL'
SET application_name='attach-lab/C07';
CREATE TABLE lab.c07 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c07 VALUES
(701, DATE '2026-07-10', 'keep'),
(702, DATE '2026-08-01', 'remove-from-candidate');
DELETE FROM lab.c07
WHERE occurred_on >= DATE '2026-08-01';
ALTER TABLE lab.events
ATTACH PARTITION lab.c07
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
SELECT tableoid::regclass, event_id, occurred_on
FROM lab.events WHERE event_id=701;
ALTER TABLE lab.events DETACH PARTITION lab.c07;
DROP TABLE lab.c07;
SQL
# C08: rollback attachment, prove hierarchy absent, then safe rerun.
run_sql C08 <<'SQL'
SET application_name='attach-lab/C08';
CREATE TABLE lab.c08 (LIKE lab.events INCLUDING ALL);
INSERT INTO lab.c08 VALUES (801, DATE '2026-08-15', 'rollback-control');
BEGIN;
ALTER TABLE lab.events
ATTACH PARTITION lab.c08
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
ROLLBACK;
SELECT EXISTS (
SELECT 1 FROM pg_inherits
WHERE inhparent='lab.events'::regclass
AND inhrelid='lab.c08'::regclass
) AS attached_after_rollback;
ALTER TABLE lab.events
ATTACH PARTITION lab.c08
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
SELECT EXISTS (
SELECT 1 FROM pg_inherits
WHERE inhparent='lab.events'::regclass
AND inhrelid='lab.c08'::regclass
) AS attached_after_rerun;
ALTER TABLE lab.events DETACH PARTITION lab.c08;
DROP TABLE lab.c08;
SQL
"${PSQL[@]}" -Atqc "
SELECT count(*) AS remaining_parent_rows FROM lab.events;
" >"$EVIDENCE/final_state.out"
printf 'evidence_dir=%s\n' "$EVIDENCE"The harness is intentionally conservative about what counts as proof of a scan. A successful C01 does not empirically demonstrate heap scanning by itself; that behavior is the documented contract. Conversely, C02 and C04 are behavioural controls: if a known out-of-range preexisting row causes rejection, the acceptance path is demonstrably capable of detecting the dirty population. PostgreSQL’s source also contains an internal branch that skips queued partition validation when existing constraints imply the partition constraint, but that implementation detail does not extend the guarantee in the manual.
The same distinction applies to PostgreSQL IF NOT EXISTS schema validation. A successful DDL command alone cannot establish that a relation has the intended shape.
Author the expected manifest independently and keep a negative control
The expected manifest must not be generated from PostgreSQL output. Its purpose is to prevent the test harness from becoming its own oracle. Write it before commissioning, review the date bounds manually, and preserve it under source control separately from transcripts.
For range partitions, this fixture treats the FROM value as included and the TO value as excluded, consistent with PostgreSQL range-partition semantics illustrated in the official partitioning examples. The fixture deliberately places rows on both relevant boundaries so a one-day or inclusive-upper-bound mistake cannot disappear inside a large dataset.
manifest_version: 1
target_server_version_num: 190000
publication_status: EXPECTED / NOT EXECUTED
cases:
C01:
purpose: baseline validation path without proving CHECK
candidate: lab.c01
bound: "[2026-01-01, 2026-02-01)"
expected_attach: success
expected_population_valid: true
documented_scan_contract: full scan
status: EXPECTED / NOT EXECUTED
C02:
purpose: reject preexisting out-of-bound row
candidate: lab.c02
bound: "[2026-02-01, 2026-03-01)"
bad_row: "event_id=202, occurred_on=2026-03-01"
expected_attach: failure
expected_partition_membership_after_failure: false
status: EXPECTED / NOT EXECUTED
C03:
purpose: valid CHECK supplies proof for scan avoidance
candidate: lab.c03
bound: "[2026-03-01, 2026-04-01)"
expected_check_validated: true
expected_check_enforced: true
expected_attach: success
documented_attach_scan: avoidable
status: EXPECTED / NOT EXECUTED
C04:
purpose: negative control for NOT VALID
candidate: lab.c04
bound: "[2026-04-01, 2026-05-01)"
bad_row: "event_id=402, occurred_on=2026-05-01"
expected_check_validated: false
expected_attach: failure
expected_partition_membership_after_failure: false
status: EXPECTED / NOT EXECUTED
C05:
purpose: lock-scope proof
expected_parent_lock: ShareUpdateExclusiveLock
expected_candidate_lock: AccessExclusiveLock
expected_parent_access_share_probe: success
expected_parent_share_probe: lock timeout
expected_candidate_access_share_probe: lock timeout
status: EXPECTED / NOT EXECUTED
C06:
purpose: default-partition conflict
default_row: "event_id=601, occurred_on=2026-06-15"
proposed_bound: "[2026-06-01, 2026-07-01)"
expected_attach: failure
expected_row_remains_in_default: true
status: EXPECTED / NOT EXECUTED
C07:
purpose: smallest approved data repair
initial_bad_row: "event_id=702, occurred_on=2026-08-01"
repair: delete event_id=702 from candidate fixture
expected_attach_after_repair: success
expected_retained_row_visible_through_parent: true
status: EXPECTED / NOT EXECUTED
C08:
purpose: rollback and safe rerun
expected_membership_after_rollback: false
expected_membership_after_committed_rerun: true
status: EXPECTED / NOT EXECUTEDC04 is the critical negative control. PostgreSQL says the scan-avoidance optimization requires a valid CHECK constraint. PostgreSQL also distinguishes an unvalidated constraint from a validated one: VALIDATE CONSTRAINT scans the table to establish that no existing rows violate a previously NOT VALID constraint. Therefore a matching expression with convalidated=false must not be treated by the acceptance protocol as equivalent to validated population evidence.
That distinction is more important in PostgreSQL 19 because enforceability and validation are separate states. ALTER TABLE documentation states that a NOT VALID constraint can defer initial validation while subsequent row versions are still subject to the constraint, whereas NOT ENFORCED means the database does not check it for new or updated rows. The fixture uses NOT VALID, not NOT ENFORCED, because the negative control is specifically about missing legacy-population validation, not disabled enforcement.
Bounded negative-control pass condition: C04 passes only when the matching but unvalidated CHECK remains identifiable as unvalidated, ATTACH PARTITION rejects the known dirty row, and pg_inherits does not show lab.c04 attached afterward. Unexpected attachment is HOLD, not a candidate for reinterpretation.
The misleading success signal now has two legitimate explanations. In C01, successful attachment follows the documented candidate validation scan. In C03, successful attachment can follow without that scan because a valid constraint proves the partition condition. The final green state is similar; the evidence path is not. PostgreSQL’s constraint documentation also warns that CHECK expressions are assumed immutable for the same input row, which is one reason this fixture intentionally uses only simple immutable date comparisons rather than a mutable user-defined function.
Run the controlled comparison, preserve failures, and inspect boundaries
The comparison is meaningful only if cases change one relevant property at a time. C01 and C02 differ in population validity. C03 changes the proof path by adding a validated bound constraint. C04 keeps a matching predicate but removes validated status while retaining a dirty legacy row. C05 holds a successful attachment transaction open so lock-manager state can be observed deterministically after the statement and before commit.
The lock test deserves particular care. PostgreSQL documents that table-level locks are normally held until the transaction ends. Holding C05’s transaction after ATTACH is therefore a cleaner observation point than trying to race against the validation operation itself. The observer should find ShareUpdateExclusiveLock on lab.events and AccessExclusiveLock on lab.c05 for the recorded C05-A backend.
The external probes verify consequences rather than merely trusting lock labels. An ACCESS SHARE request on the parent is compatible with SHARE UPDATE EXCLUSIVE, so that probe is expected to succeed. A SHARE request conflicts with SHARE UPDATE EXCLUSIVE, so it should time out while C05-A holds the transaction. An ACCESS SHARE request on the candidate conflicts with its ACCESS EXCLUSIVE lock and should also time out. PostgreSQL’s lock-conflict matrix directly supports those expectations.
The parent’s SHARE UPDATE EXCLUSIVE lock permits ordinary ACCESS SHARE readers, but the candidate relation itself is held ACCESS EXCLUSIVE; a default partition involved in attachment is also documented as receiving ACCESS EXCLUSIVE. Query planning, other DDL and application access paths can introduce additional dependencies that this fixture does not model.
C06 tests another easily missed population. If the parent already has a default partition, creating a new explicit partition changes what the default is permitted to contain. PostgreSQL documents that the default partition must be checked for rows that would belong to the new partition, and that this scan can likewise be avoided by an appropriate constraint. Without such proof, the default partition is scanned under ACCESS EXCLUSIVE.
This matters because “the candidate table is empty” is not sufficient evidence that an attachment should succeed. In C06, the candidate is empty, but the default partition contains 2026-06-15, which falls inside the proposed June range. The correct expected outcome is rejection. A system that attached the empty candidate while silently leaving that row in the default partition would violate the documented partition-state contract.
Preserve failure evidence before cleanup. Each failed case should retain the complete psql transcript, VERBOSITY=verbose error fields, exit code, candidate inventory, catalog membership query and run metadata. PostgreSQL recommends using SQLSTATE rather than localized error text when programmatic error classification matters, because SQLSTATE is less likely to vary across releases and locales. The fixture therefore echoes :SQLSTATE for rejected cases instead of relying solely on a human-readable error string.
Do not delete C02 or C04 immediately after they fail. The rejected state is part of the evidence. Verify that the dirty row still exists and that the relation did not become a partition. Only after the evidence package is complete should the isolated fixture be repaired or destroyed.
PostgreSQL SKIP LOCKED fairness also takes a controlled comparison to test. A single successful command says little about how concurrent sessions affect a queue.
Boundary coverage remains intentionally small. This playbook does not claim to certify list, hash, expression-key or foreign-table attachment. The current ALTER TABLE documentation contains special cases for expression partition keys, list partitions that reject NULL, foreign tables and recursively attached partitioned tables. Those belong in separate manifests if they matter to a release.
Build the evidence ledger and reconcile database state, not exit codes
A useful ledger must preserve case identity from manifest through transcript. Do not summarize eight cases into “7/8 passed.” A failed negative control and a failed positive case have opposite meanings even though both produce nonzero exits.
The ledger records the expected outcomes for all eight cases. The PostgreSQL 19.0 fixture has yet to run.
Case | Expected database outcome | Required evidence | Observed | Status |
C01 | Attach succeeds; documented candidate scan path applies | transcript, row inventory, membership | none | EXPECTED / NOT EXECUTED |
C02 | Dirty row causes attach failure; no membership | SQLSTATE, dirty row, pg_inherits=false | none | EXPECTED / NOT EXECUTED |
C03 | Valid matching CHECK; attach succeeds; scan avoidable | constraint state/definition, transcript | none | EXPECTED / NOT EXECUTED |
C04 | Matching NOT VALID CHECK does not rescue dirty population | convalidated=false, failure, no membership | none | EXPECTED / NOT EXECUTED |
C05 | Parent ShareUpdateExclusive; child AccessExclusive | PID-bound pg_locks, three probes | none | EXPECTED / NOT EXECUTED |
C06 | Default-row conflict rejects new partition | default row, failure transcript, row location | none | EXPECTED / NOT EXECUTED |
C07 | Smallest repair followed by successful attach | before/after row evidence, membership | none | EXPECTED / NOT EXECUTED |
C08 | Rolled-back attach absent; committed rerun present | two pg_inherits observations | none | EXPECTED / NOT EXECUTED |
For C03, catalog evidence must include the exact relation identity, constraint definition, validation state and enforceability state. PostgreSQL 19 exposes validation and enforcement as distinct attributes in the constraint model; do not reduce them to “constraint exists.” The acceptance question is whether the bound predicate on the intended candidate was valid proof when attachment ran.
For C05, the PID relationship matters. A snapshot containing the right lock modes on the wrong backend is not evidence for the attachment. Preserve the attaching PID from the application-named session and join pg_locks to pg_stat_activity; this prevents unrelated lab activity from accidentally satisfying the expected lock pattern.
State reconciliation after every rejected case should answer three questions independently: does the candidate still contain the authored rows, is it listed as a child of the parent, and is the intended partition bound present anywhere in the hierarchy? The first guards against accidental data modification, the second against partial attachment assumptions, and the third against fixture contamination from an earlier case.
The evidence auditor should also look for missing evidence. A successful transcript without the manifest used for that run is incomplete. A lock dump without recorded PIDs is incomplete. An error without the post-failure hierarchy check is incomplete. A C03 transcript without the pre-attach constraint state cannot support the claim that an already-valid constraint supplied the proof.
Missing-evidence pass condition: every manifest case has exactly one evidence directory, runtime metadata matches the commissioned runtime, transcripts exist, expected catalog snapshots exist, and every failure case has a retained post-failure state check. Missing mandatory artifacts produce HOLD even if the SQL command itself appeared to behave correctly.
Backend identity also matters when inspecting PostgreSQL advisory locks after savepoint rollback. Those locks are application-defined and have their own lifetime. Evidence about them cannot establish which relation locks ATTACH PARTITION held.
Separate documented contract from observation, then repair and rerun safely
Use four labels when recording the evidence for each case.
Documented behavior is what PostgreSQL promises in the PostgreSQL 19 manual: regular-table attachment performs a full scan unless a valid sufficient constraint makes the validation scan unnecessary; attachment takes SHARE UPDATE EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the candidate; a default partition can require its own validation and lock.
Engineering inference is reasoning derived from those guarantees. For example, if C03’s exact validated range check is in place before attachment, then a second heap scan during ATTACH is not required for the documented validation objective. That is a bounded inference from the documented scan-avoidance mechanism, not a claim that PostgreSQL performed zero physical reads of the relation for every conceivable implementation reason.
Proposed experiment is the harness and expected manifest above. It is designed to distinguish clean population, dirty population, validated proof, unvalidated proof, default-partition conflict, lock scope, repair and rollback.
Reserve observed result for evidence produced by the harness on the pinned runtime. All eight cases are still EXPECTED / NOT EXECUTED.
That separation also protects against overclaiming CHECK semantics. PostgreSQL says a check is satisfied when its expression evaluates to true or null; a CHECK alone therefore does not necessarily prohibit null values. It also assumes check expressions are immutable for the same row. Those facts are why the fixture declares occurred_on NOT NULL and uses direct date comparisons.
Repair should target the smallest failing boundary. In C02, the partition definition is coherent and one authored row sits exactly on the exclusive upper bound. The fixture’s approved repair is to remove or relocate that synthetic row; it is not to widen the partition to hide the error. In a real release, deleting, correcting or moving business data requires its own authorization. This lab grants no such authorization.
Bounded-repair pass condition: retain the original failed transcript, identify the exact offending row with an independently written predicate, apply only the approved fixture repair, rerun the attachment, and then query through the parent to prove the retained row routes through the newly attached relation. If the repair changes the partition bound, parent schema or unrelated rows, classify it as a new case rather than silently relabeling the old one.
C07 models that protocol. Its bad row is 2026-08-01, exactly equal to the exclusive TO ('2026-08-01') boundary. After that synthetic row is removed from the candidate, the rerun is expected to attach the July range successfully. The rejected first attempt remains evidence; the successful rerun does not overwrite it.
Rollback is a separate control. PostgreSQL documents that table locks are normally retained until transaction end and released on rollback. C08 attaches inside an explicit transaction and rolls it back, then checks pg_inherits before executing a clean committed rerun.
The rollback gate is not “ROLLBACK returned successfully.” It is state reconciliation: after rollback the child must not be a partition; after the subsequent committed attachment it must be. Only then is retry behavior demonstrated for this isolated fixture.
PostgreSQL setval rollback validation illustrates why state must be checked before a retry. Sequence changes have different rollback semantics from partition DDL.
The operational owner should be named before running C07 or C08 outside a disposable lab. Ownership means authority to decide what happens to rejected rows, responsibility for retaining the failed-run evidence, and responsibility for approving the rerun. An anonymous “DB team” field is insufficient when remediation changes data.
Apply the ACCEPT, REPAIR, RERUN or HOLD gate
The decision is evidence-driven:
Condition | Decision | Required action |
All mandatory cases executed on the pinned version; expected positive cases attach; dirty controls reject; lock evidence matches; repaired case is reverified; ledger complete | ACCEPT | Publish bounded result with runtime and evidence identifiers |
A known candidate/default row violates the approved bound and the intended contract remains correct | REPAIR | Remediate only the authorized row/state, retain failure evidence, then verify again |
Harness interruption, lock-probe race, incomplete transcript, transient environment problem, or intentionally rolled-back control leaves the database contract unchanged | RERUN | Recreate clean disposable fixture and rerun the affected case |
Unexpected negative-control success, unexplained lock divergence, unsupported runtime, missing mandatory evidence, unknown production-data repair, or disagreement between manifest and requirement | HOLD | Do not infer acceptance; investigate before release |
The decision for this fixture is HOLD. All eight cases are EXPECTED / NOT EXECUTED. In particular, rejection of row 402 in C04 remains a prediction until a PostgreSQL 19.0 run produces the error and the post-failure catalog check.
Without a sufficient valid constraint, PostgreSQL documents that the ordinary candidate table is scanned during ATTACH PARTITION to check its existing rows. With a valid CHECK that proves the desired partition constraint, that attach-time scan can be avoided; the valid constraint is the evidence PostgreSQL relies on instead. A matching but NOT VALID constraint is not the same population proof.
For practitioners who want broader database-administration study around these operational skills, the Refonte Learning Database Administrator Essentials Program currently lists a three-month duration and 12–14 hours per week, with database design, advanced SQL/query optimization, backup and recovery, performance tuning, security, cloud database administration, migration and disaster recovery among its published competencies. Its FAQ names MySQL Workbench, Oracle SQL Developer, AWS RDS and monitoring solutions; the page advertises hands-on projects and personalized mentorship. It states that basic programming knowledge is recommended and separately lists working toward a bachelor’s or higher-level degree as an obligatory admission prerequisite. The page describes a “Potential Internship” and says successful completion provides a Training Certificate and Certificate of Internship.
