Database administrator comparing a PostgreSQL partition’s date bounds with an existing table’s CHECK constraint.

PostgreSQL Attached the Partition. Was Every Existing Row Validated?

Fri, Nov 20, 2026

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 EXECUTED

C04 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.