CREATE TABLE IF NOT EXISTS Is Not a Schema Compatibility Check

A bootstrap script finishes successfully. Its requested table definition contains a primary key, a required payload, and an attempts counter defaulting to zero. That success can still leave the application facing a relation with a missing column, a different default, or no primary key. The operational question is what definition remained after the command.

The PostgreSQL 18 CREATE TABLE reference explicitly limits IF NOT EXISTS: an existing same-name relation produces a notice instead of the corresponding existence error, without any assurance that its definition resembles the requested one. This is an established command contract, not a newly introduced defect.

This playbook defines a narrower, reviewable acceptance test for jobs(job_id bigint PRIMARY KEY, payload text NOT NULL, attempts integer NOT NULL DEFAULT 0). Nine controlled object states exercise the same bootstrap request. An independently written manifest specifies the permitted relation kind, column representation, defaults, and primary-key fields. OID comparisons distinguish inspection of an existing object from assumptions about its name.

Execution status: NOT RUN. The commissioning review checked documentation but had no PostgreSQL client or server. No new PostgreSQL execution is claimed here. The source digest has been checked separately; all nine outcomes remain authored expectations. The goal is a bounded decision: accept the declared signature, commission a reviewed migration, or hold when the available evidence cannot support approval.

Define the schema postcondition hidden in bootstrap success

A release gate needs to identify the proposition it is evaluating. Three propositions commonly collapse into one green bootstrap step:

The distinction matters even when every deployment uses the same SQL file. Replaying a request is not evidence that a pre-existing object acquired the requested properties. For this exercise, compatibility requires an ordinary permanent table with exactly three admitted columns and the specified primary key.

Choose that requirement before running bootstrap. A team might deliberately permit extra columns or alternative defaults in another contract. This contract permits neither. Its strictness makes a mismatch reviewable; it does not establish that every different table would break every application.

For PostgreSQL schema compatibility validation, name the endpoint, database, schema, manifest revision, and inspected fields in the release record. A bare note saying “table exists” leaves the central question unanswered. Likewise, “schema checked” is too broad when only several catalog properties were compared.

Place this focused gate alongside database integration and migration foundations. The surrounding application design still needs an owner. Here, the DBA owns catalog interpretation, while the backend owner states the required interface.

The requested postcondition should survive review in one sentence: the selected relation matches this exact bounded manifest. Anything stronger needs additional evidence. A successful bootstrap is one input to that decision, never a substitute for writing down the decision itself.

Write an independent expected manifest

The expected manifest is an input, not a report generated from whichever database happens to answer. Author it from the reviewed application requirement before collecting catalog state.

For all nine cases, the requested column contract is identical:

The enclosing relation must have relkind="r" and persistence="p". Its primary-key array must contain one entry: columns ["job_id"], deferrable=false, initially_deferred=false, and validated=true. No generated identifier policy is implied by choosing bigint. The expectation deliberately leaves job_id without an identity marker or default.

JSON null here represents the absence of reconstructed default text. It is different from a string containing the word null. Keep the representation exact so a comparison does not quietly erase the difference between absent metadata and a declared expression.

A captured database is not its own specification

Copying the observed signature into expected would make the wrong object its own acceptance criterion. Creating an expected table and introspecting it can also couple the oracle to the same assumptions being tested. The supplied fixture instead embeds an independently authored JSON value.

Review that value with the application owner. Confirm column order, the required payload, the attempts default, and primary-key timing requirements. Preserve the approved manifest beside the migration requirement so a later reviewer can explain why each field belongs.

Do not absorb a separate PostgreSQL constraint rollout into this exercise. The manifest asks what bounded definition exists after bootstrap. Establishing other constraint states or validating a population requires its own contract.

The expected value excludes relation names and OIDs because those identify individual fixture objects. The definition expectation is shared; the observations must remain attached to their particular objects.

Build a guarded disposable PostgreSQL fixture

Use an explicitly selected, owned PostgreSQL 18 server and a disposable database named schema_contract_lab. The database must already exist when the fixture begins; the fixture creates only its owned schema and objects. Database naming alone does not establish ownership or authorize an endpoint.

After selecting the disposable host, port, and role through your normal connection settings, create the database separately:

psql -X -v ON_ERROR_STOP=1 -d postgres -c 'CREATE DATABASE schema_contract_lab;'

This setup command is also NOT RUN. If the name already exists, stop and establish its ownership and purpose. Do not suppress the error or drop an existing database to make setup proceed.

Save the following complete source as schema_contract.sql. Its SHA-256, including the final newline, is d38fbb58c51a83e2e18b44374dd8032f403d381974befe534091e99fc0a886b1. Byte integrity was checked during preparation; SQL behavior remains NOT RUN.

The guards reject another database name, another server major version, or an existing ifne_fixture schema. All fixture objects are inside one explicit transaction. Preserve the existing-schema refusal exactly.

\set ON_ERROR_STOP on
\pset pager off
\echo CLIENT_VERSION :VERSION_NAME
BEGIN;
DO $guard$
BEGIN
  IF current_database() <> 'schema_contract_lab' THEN
    RAISE EXCEPTION 'Use only the disposable schema_contract_lab database';
  END IF;
  IF current_setting('server_version_num')::integer / 10000 <> 18 THEN
    RAISE EXCEPTION 'This unexecuted fixture targets PostgreSQL 18';
  END IF;
  IF EXISTS (SELECT FROM pg_catalog.pg_namespace WHERE nspname='ifne_fixture') THEN
    RAISE EXCEPTION 'Refusing an existing ifne_fixture schema';
  END IF;
END
$guard$;
CREATE SCHEMA ifne_fixture;
SET LOCAL search_path = pg_catalog;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
SELECT version(), current_database(), current_user, current_setting('search_path');
CREATE FUNCTION ifne_fixture.signature(target regclass)
RETURNS jsonb LANGUAGE sql STABLE SET search_path=pg_catalog AS $sig$
SELECT jsonb_build_object(
 'relkind', c.relkind::text, 'persistence', c.relpersistence::text,
 'columns', COALESCE((
   SELECT jsonb_agg(jsonb_build_object(
     'position', a.attnum, 'name', a.attname,
     'type', format_type(a.atttypid, a.atttypmod),
     'not_null', a.attnotnull, 'default', pg_get_expr(d.adbin, d.adrelid),
     'identity', a.attidentity::text, 'generated', a.attgenerated::text)
     ORDER BY a.attnum)
   FROM pg_attribute a LEFT JOIN pg_attrdef d
     ON d.adrelid=a.attrelid AND d.adnum=a.attnum
   WHERE a.attrelid=c.oid AND a.attnum>0 AND NOT a.attisdropped), '[]'::jsonb),
 'primary_key', COALESCE((
   SELECT jsonb_agg(jsonb_build_object(
     'columns', (SELECT jsonb_agg(a.attname ORDER BY k.ord)
       FROM unnest(p.conkey) WITH ORDINALITY AS k(attnum, ord)
       JOIN pg_attribute a ON a.attrelid=p.conrelid AND a.attnum=k.attnum),
     'deferrable', p.condeferrable, 'initially_deferred', p.condeferred,
     'validated', p.convalidated) ORDER BY p.oid)
   FROM pg_constraint p WHERE p.conrelid=c.oid AND p.contype='p'), '[]'::jsonb))
FROM pg_class c WHERE c.oid=target;
$sig$;
-- All pre-existing objects below belong to this disposable transaction.
CREATE TABLE ifne_fixture.matching (
  job_id bigint PRIMARY KEY, payload text NOT NULL, attempts integer NOT NULL DEFAULT 0);
CREATE TABLE ifne_fixture.missing_payload (
  job_id bigint PRIMARY KEY, attempts integer NOT NULL DEFAULT 0);
CREATE TABLE ifne_fixture.wrong_type (
  job_id integer PRIMARY KEY, payload text NOT NULL, attempts integer NOT NULL DEFAULT 0);
CREATE TABLE ifne_fixture.nullable_attempts (
  job_id bigint PRIMARY KEY, payload text NOT NULL, attempts integer DEFAULT 0);
CREATE TABLE ifne_fixture.wrong_default (
  job_id bigint PRIMARY KEY, payload text NOT NULL, attempts integer NOT NULL DEFAULT 42);
CREATE TABLE ifne_fixture.no_primary_key (
  job_id bigint NOT NULL, payload text NOT NULL, attempts integer NOT NULL DEFAULT 0);
CREATE TABLE ifne_fixture.extra_column (
  job_id bigint PRIMARY KEY, payload text NOT NULL, attempts integer NOT NULL DEFAULT 0,
  extra text);
CREATE VIEW ifne_fixture.wrong_kind AS
  SELECT 1::bigint AS job_id, 'example'::text AS payload, 0::integer AS attempts;
CREATE TABLE ifne_fixture.evidence (
  case_name text PRIMARY KEY, existed_before boolean, oid_preserved boolean,
  definition_unchanged boolean, compatible boolean, decision text,
  before_signature jsonb, after_signature jsonb);
DO $cases$
DECLARE
  expected jsonb := '{
  "relkind":"r", "persistence":"p", "columns":[
   {"position":1,"name":"job_id","type":"bigint","not_null":true,"default":null,"identity":"","generated":""},
   {"position":2,"name":"payload","type":"text","not_null":true,"default":null,"identity":"","generated":""},
   {"position":3,"name":"attempts","type":"integer","not_null":true,"default":"0","identity":"","generated":""}],
  "primary_key":[{"columns":["job_id"],"deferrable":false,"initially_deferred":false,"validated":true}]}'::jsonb;
  name text;
  before_oid oid;
  after_oid oid;
  before_state jsonb;
  after_state jsonb;
  compatible boolean;
  should_match boolean;
BEGIN
  FOREACH name IN ARRAY ARRAY['fresh','matching','missing_payload','wrong_type',
    'nullable_attempts','wrong_default','no_primary_key','extra_column','wrong_kind']
  LOOP
    before_oid := to_regclass(format('%I.%I','ifne_fixture',name));
    before_state := ifne_fixture.signature(before_oid::regclass);
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I.%I (
      job_id bigint PRIMARY KEY, payload text NOT NULL,
      attempts integer NOT NULL DEFAULT 0)', 'ifne_fixture',name);
    after_oid := to_regclass(format('%I.%I','ifne_fixture',name));
    after_state := ifne_fixture.signature(after_oid::regclass);
    compatible := after_state = expected;
    should_match := name IN ('fresh','matching');
    IF compatible IS DISTINCT FROM should_match THEN
      RAISE EXCEPTION 'Manifest expectation failed for %, actual %',name,after_state;
    END IF;
    IF before_oid IS NOT NULL AND
      (before_oid IS DISTINCT FROM after_oid OR before_state IS DISTINCT FROM after_state) THEN
      RAISE EXCEPTION 'Existing object unexpectedly changed for %',name;
    END IF;
    INSERT INTO ifne_fixture.evidence VALUES (
      name,before_oid IS NOT NULL,
      CASE WHEN before_oid IS NULL THEN NULL ELSE before_oid=after_oid END,
      CASE WHEN before_oid IS NULL THEN NULL ELSE before_state=after_state END,
      compatible,CASE WHEN compatible THEN 'ACCEPT_BOUNDED_MANIFEST'
                      ELSE 'MIGRATION_OR_REVIEW_REQUIRED' END,before_state,after_state);
  END LOOP;
END
$cases$;
TABLE ifne_fixture.evidence;
SELECT count(*) AS cases, count(*) FILTER (WHERE compatible) AS compatible_cases
FROM ifne_fixture.evidence;
-- Expected: nine cases; only fresh and matching compatible. NOT EXECUTED HERE.
ROLLBACK;
SELECT to_regnamespace('ifne_fixture') IS NULL AS fixture_rolled_back;

# On the authorized disposable connection, use fresh, writable output paths for every run.
# This wrapper refuses to overwrite existing output files.
# When psql launches and the status file is written, it captures the combined output and saves the process's exit status.
# A redirection failure can prevent psql from starting; failure to save the status also invalidates the evidence capture.
# Treat either condition as HOLD and preserve the shell error.
(
  set -o noclobber
  if psql -X -d schema_contract_lab -f schema_contract.sql > schema-contract-evidence.txt 2>&1; then
    lab_status=0
  else
    lab_status=$?
  fi
  printf '%s\n' "$lab_status" > schema-contract-exit-status.txt || exit 1
  exit "$lab_status"
)

The psql reference documents startup-file suppression with -X, script execution, ON_ERROR_STOP, client version variables, and distinct failure exit statuses. The fixture prints client identity and requests server identity; those are future runtime evidence, not tested versions in this article.

Ensure the lab connection exposes notices; the source does not set client_min_messages. A connection failure, timeout, or assertion failure requires HOLD. Reconcile the nine cases, two matches, and final rollback result before claiming any successful experiment.

Prove that repeated creation can leave a wrong object unchanged

The central negative control is an existing object that survives the requested bootstrap unchanged. A failed CREATE TABLE would answer a different question. Here, the expected lesson is that command success and definition mismatch can coexist.

The script creates eight objects before entering its case loop. For each name, it resolves the relation, obtains a before signature, executes the same requested definition, resolves the name again, and obtains an after signature. The fresh case starts without an object. The other eight have an existing OID and a deliberately authored starting state.

Read the evidence by case_name. The output query has no ordering clause, so transcript position is not a case identifier. For fresh, the preservation fields and before signature should be null because there was no prior relation. For every pre-existing case, oid_preserved and definition_unchanged are expected to be true.

Those true values are compatible with compatible=false. For example, wrong_default is expected to retain default text "42" before and after bootstrap while failing the independent expectation of "0". The script should classify this as MIGRATION_OR_REVIEW_REQUIRED without changing that object.

Notice plus success does not imply compatibility

A PostgreSQL existing relation notice tells the operator that the requested name encountered an existing relation. It is useful evidence about the command path. It does not establish the payload column, the attempts default, or the primary key.

The strongest fixture comparison therefore joins three observations: an existing-object notice when visible, preserved object identity, and an unchanged mismatching signature. Each answers a different question. Suppressing the notice removes useful context without improving the definition.

The missing_payload case provides a complementary failure: the requested column is absent from both signatures. Its rejection should be explained by that recorded absence, not by an imagined application exception. The lab contains no application process, so it supplies no observed reader or writer failure.

Be precise about the field named definition_unchanged. It compares the bounded JSON projection, not every catalog property or dependency. The supplied code checks numeric OIDs internally but exports only their equality. Do not describe its transcript as a raw numeric OID ledger.

Finally, the diagnostic expects seven rejections. A successful laboratory run would show that these wrong objects were correctly rejected by the manifest comparison. It would not make their definitions acceptable. Until the server run exists, even that diagnostic result remains an expectation.

Bind inspection to the exact relation and its kind

Inspection starts with the intended qualified name. The fixture constructs it using format('%I.%I', 'ifne_fixture', name) and resolves it before reading catalog rows. It also pins the inspection search path to pg_catalog, while explicitly qualifying fixture objects.

PostgreSQL documents to_regclass as a name-to-relation lookup that returns null when a valid relation name is absent. The fixture uses that distinction for fresh; it then binds subsequent inspection to the returned object identity. See the system information function reference.

Every signature begins from pg_class c WHERE c.oid=target. Column and constraint observations then follow that relation identity. This avoids assembling a supposed table definition from unrelated objects that happen to share column or constraint names.

Object kind comes before column approval

The pg_class catalog reference distinguishes ordinary tables, views, partitioned tables, and other relation kinds; it separately records persistence. This manifest admits relkind='r' and relpersistence='p'. The view in wrong_kind cannot satisfy that table requirement.

Its projected columns may look plausible to a casual reader. That is not sufficient. Also avoid describing this view as a single-field negative control: its default, primary-key, and nullability metadata can differ alongside its kind.

The kind test is an initial boundary, not a complete architecture inventory. An ordinary-table kind does not establish the absence of inheritance or partition membership. Those features require the separate scope review described below.

An OID also belongs to the observed database and lifecycle. Compare it before and after the controlled command; do not put one installation's numeric OID into a portable expected manifest. The acceptance record needs both location and definition: which object was inspected, and what approved fields it contained.

If lookup or signature collection is incomplete, stop. Missing metadata cannot be treated as a conveniently empty set of differences.

The signature's empty-array defaults apply to column and key collections inside an identified relation. They do not turn an absent pg_class row into a matching object. The per-case IS DISTINCT FROM assertion is intended to expose an unexpected null comparison rather than allowing missing evidence to pass.

Compare columns without hiding representation differences

The column array is built from pg_attribute rows belonging to the selected relation. It excludes system attributes with attnum>0 and excludes dropped attributes with NOT attisdropped. The pg_attribute reference defines the column identifiers, type fields, not-null flag, and identity/generated markers used in that projection.

Each retained entry carries its actual attribute number and is aggregated in that order. This is intentionally stricter than comparing a set of names. missing_payload must fail even though its remaining columns are familiar. extra_column must fail because this manifest defines an exact admitted shape.

The query does not renumber surviving columns after excluding dropped ones. A relation with historical gaps in attribute numbers can therefore differ from this fresh three-position representation even when its visible names appear similar. That is a conservative mismatch requiring review, not permission to rebuild the table.

Type comparison uses format_type(a.atttypid, a.atttypmod), preserving the formatted SQL type and its modifier input. PostgreSQL documents both arguments; discarding the modifier would discard part of the declared type representation. The authored wrong_type case specifically tests integer against bigint.

Nullability is compared independently. nullable_attempts should fail while keeping the correct type and default. Empty identity and generated markers are also required; a similarly named generated column is outside the intended ordinary input-column contract.

One PostgreSQL 18 limitation needs explicit treatment: attnotnull can describe a possibly invalid not-null constraint. Comparing that flag does not establish validation of existing rows. The supplied projection does not audit not-null constraint validation state.

For these authored empty tables, the declared creation statements define the controlled setting. For an unfamiliar table, uncertain validation state belongs in HOLD. Keep the acceptance claim at the level actually inspected: exact column metadata in this admitted fixture, without promoting it to a data-quality result.

When reporting a difference, retain its position and field name. “Payload is absent” and “job_id has type integer” lead to different reviews. Reducing both to a red Boolean discards the information needed to assess whether the requirement, the collector, or the database should change.

Audit defaults with a deliberately conservative rule

A pg_attrdef default expression audit must associate the expression with the right relation and column. Joining by the text name attempts would be insufficient: every fixture table can contain that name.

The supplied left join uses both d.adrelid=a.attrelid and d.adnum=a.attnum. The pg_attrdef reference defines those identity fields and describes adbin as the stored expression representation. It also covers generation expressions, which is why the separate generated-column marker matters.

The signature reconstructs readable SQL using pg_get_expr(d.adbin, d.adrelid). The system information functions documentation describes this as decompilation of the catalog representation. It is not recovery of the exact source spelling originally submitted.

For this fixture, the expected attempts expression is the string "0". The wrong_default control supplies 42 instead. That simple distinction gives the reviewer a direct, independently authored reason to reject the resulting signature.

Textual defaults are a bounded comparison

Text equality is the rule for these simple defaults under the stated reference family and inspection settings. It is not a semantic equivalence engine. An expression with different casts, qualifications, function dependencies, or reconstruction can need review even when a person expects the same result.

Do not broaden acceptance by removing arbitrary parentheses or coercing every reconstructed expression through ad hoc replacements. A normalization policy changes the oracle. It needs a documented set of admitted expressions, counterexamples, review ownership, and new execution evidence.

Likewise, an identical function call string would not certify that all external dependencies or relevant behavior remained equivalent. This manifest does not inspect that layer. Complex defaults and uncertain equivalence therefore belong in HOLD, pending a suitably bounded review.

The recovery decision also needs a data boundary. Correcting the declaration and assessing stored attempts values are separate work items. The catalog comparison contains no evidence identifying which existing values came from a previous default, an explicit application value, or another write path.

For wrong_default, record the observed expression and the approved expression separately. Ask the schema owner to propose the declaration change and the data owner to assess affected records. Replaying bootstrap supplies neither assessment.

If a later run reconstructs even a simple default differently, retain that string with the runtime identity and inspection settings. The mismatch is evidence to investigate. A developer's expectation that two expressions evaluate alike does not replace a reviewed rule admitting both representations.

Validate the primary key as a behavior, not a name

The expected key is defined by its ordered columns and timing fields. It does not depend on an automatically chosen constraint name or the name of a supporting index.

The pg_constraint reference documents relation identity, constraint type, key-column numbers, deferrability, initial deferral, and validation. It also makes clear that constraint names need not be unique and that the catalog contains multiple constraint types. The fixture therefore filters conrelid=c.oid AND contype='p' deliberately.

For each admitted primary key, unnest(p.conkey) WITH ORDINALITY preserves the recorded key order. The join resolves those attribute numbers against the same constraint relation. Sorting column names alphabetically would erase an ordered declaration; this contract keeps it.

The required entry has columns=["job_id"], deferrable=false, initially_deferred=false, and validated=true. These are distinct fields in the expectation. A matching name cannot compensate for a different one.

For example, a deferrable key that starts in immediate mode would still differ from the required nondeferrable key. Comparing only the initial mode would hide that distinction. The supplied control set does not execute this additional variation; it follows from the declared comparison.

The no_primary_key control is particularly useful because job_id remains explicitly NOT NULL. Its expected rejection should therefore expose the missing primary-key entry without relying on a nullable identifier as the only signal.

This is still a selected catalog contract. It does not perform duplicate-write probes, inspect all supporting-index properties, or validate query plans. Other constraint types, and fields such as broader enforcement or temporal-constraint metadata, are outside the supplied projection.

For review, retain the empty or differing primary-key array alongside the expected entry. The migration owner needs the exact missing requirement. A note saying “key problem” is insufficient for assessing application assumptions or planning any subsequent data checks.

A catalog comparison can identify this bounded mismatch without choosing how to repair it. That separation keeps a useful diagnostic from becoming an unreviewed schema mutation.

Keep unsupported schema features outside acceptance

The signature has intentionally limited coverage. It does not automatically discover everything that might affect an application. A table can equal the JSON expectation while carrying additional properties that this query never reads.

That matters when moving from authored fixture objects to an unfamiliar environment. The controlled creation statements establish what was introduced in the lab. An unknown relation requires an admission review before this comparison can support even its limited operational purpose.

Keep the omitted layers explicit:

The script contains no general unsupported-feature detector. Its SQL decision column chooses between the bounded manifest result and a mismatch. HOLD is the additional review decision for unsupported scope or insufficient evidence; it is not an undocumented branch already implemented in the fixture.

Structural scope is not a production-readiness claim

An exact signature match is a conservative sufficient test for equality of the declared projection. It is not a complete validator for every relation that might be compatible with an application.

The reverse limitation matters too: some mismatches may be acceptable under a differently approved contract. Historical attribute positions or an extra column can require review without proving a specific runtime failure. Rejecting this manifest does not identify every business consequence.

Before approval, ask whether an excluded property is relevant to the proposed release. If its presence or behavior is unknown, hold that broader approval and assign the missing assessment. Do not label a security policy, a trigger, or inherited behavior certified merely because the column array matched.

There is no concurrent-DDL safety protocol here. The owned experiment assumes its controlled object lifecycle; deployment coordination remains outside the evidence being offered.

Turn catalog differences into migration work

A manifest difference should become a specific change request with a schema owner, an application owner, and a data assessment where appropriate. The comparison identifies the disputed requirement. It does not supply an automatically safe migration.

Attach the actual signature and the approved expectation to each request. “Rerun bootstrap” is not a migration description. It does not explain how a missing field becomes available or what happens to records that conflict with a proposed requirement.

Compatibility testing must follow the proposed change. The backend owner should identify affected readers and writers, including which application versions must coexist. The data owner should approve any interpretation of stored values. The DBA should review the implementation and recovery boundary.

Keep row-level PostgreSQL write validation as a separate workstream when a repair touches records. A structural comparison cannot certify the cardinality, targeting, or meaning of those writes.

Sometimes the reviewed outcome is a revised requirement rather than a database change. That requires a new manifest revision with an explicit rationale. Never turn the current observation into the expected value merely to clear the gate.

Once the proposed change is qualified in an authorized environment, repeat the appropriate evidence collection. The original mismatch remains part of the history; a later match documents a later state.

Preserve evidence before rollback or repair

Export evidence before the owned transaction is rolled back. The evidence table is itself a fixture object, so its catalog lifetime is not an archival mechanism.

Keep the four evidence classes distinct:

A run record should retain source bytes and digest, manifest revision, connection identity, client/server versions, command, notices, both output streams, process status, and raw before/after signatures. The printed server identity must come from that run; the PostgreSQL 18 documentation selector is not an installed patch version.

The supplied source compares OIDs but records only oid_preserved. If raw numeric before/after OIDs are required for an audit, HOLD that evidence requirement until a separately versioned instrumentation change has its own digest and actual rerun. Do not claim numbers were archived by this listing.

Require nine distinct case names, exactly two compatible cases, and the expected preservation flags. The aggregate counts and fixture_rolled_back result are printed, not separately asserted. A zero process status therefore remains insufficient without output reconciliation.

The table output follows the case loop. An assertion can stop processing before the accumulated signatures are printed. A partial failure log is therefore not a completed evidence ledger.

The expected final schema-absence result is true. If an error stops processing earlier, do not claim the explicit rollback statement or final query ran. Preserve the failure and verify the disposable cleanup state separately.

Keep DDL transaction behavior in another database outside this PostgreSQL conclusion. Here, ROLLBACK_LAB refers only to the owned experiment. It neither supplies production data recovery nor proves that an unrelated migration can be reversed.

Choose bounded acceptance, migration or hold

Use the complete case record to choose a decision. Every row below is an unexecuted expectation, not an observed PostgreSQL result. All eight pre-existing objects are expected to retain their OIDs and bounded signatures.

The mismatch decisions identify work to review; they do not authorize a particular alteration. An unknown view may also leave a broader release on HOLD while its owner and dependencies are established.

Acceptance requires a complete successful run, correct per-case outcomes, preserved existing-object checks, raw signature review, aggregate reconciliation, and the final cleanup result. It approves the declared projection only. Application tests, data assessment, access checks, and any relevant omitted layer retain their own status.

Never repair an unknown table by dropping it

Do not replace the existing-schema guard with DROP SCHEMA CASCADE. Do not respond to a production mismatch by dropping and recreating the relation under the expected name.

The discrepancy may involve retained data, dependent applications, or an object whose purpose has not been established. Destructive work requires a separate authorized migration decision and an appropriate recovery assessment. A failed manifest is evidence to preserve.

Likewise, suppressing the notice or ignoring the mismatch changes no requirement. The release reviewer must name what is accepted, what requires migration, and what remains unknown. A passing diagnostic of the seven intended negatives is useful only when those negatives remain rejected.

Keep bootstrap and migration responsibilities separate

A bootstrap step requests the initial object. A migration changes a known state through a reviewed transition. A manifest gate evaluates selected properties at an evidence checkpoint. Keeping those responsibilities explicit makes a repeated deployment easier to review.

In CI, version the expected manifest with the application requirement and retain the fixture source digest. The proposed test should first be qualified on the guarded PostgreSQL 18 environment. Until that happens, mark the database integration layer NOT RUN, even if text checks or other build steps pass.

Separate the intended matching controls from the negative controls in review. Both fresh and matching are expected to match, while each remaining case has a defined reason for rejection. This prevents an always-true comparison, an empty result, or a broken collector from appearing useful merely because the job completed.

Assign ownership explicitly. The backend owner approves the needed shape and compatibility assumptions. The DBA reviews catalog interpretation and the proposed schema change. The data owner approves record-level assessment. The release reviewer reconciles evidence and records the scope of acceptance.

Keep index-plan qualification as a separate gate. The primary-key manifest does not establish whether another index serves a prepared query or whether an application's workload meets its performance requirement.

A different PostgreSQL major version needs a reviewed rerun. Do not remove the version guard to make an unsupported environment produce a convenient result. Record the exact server and client versions actually used, and keep evidence from different revisions distinguishable.

When documentation, versioned source, and execution disagree, preserve the conflicting material and HOLD the disputed conclusion. An unexpected signature may expose an incorrect expectation, an environment difference, or a collector defect. None should be silently normalized away.

Finally, do not mark a migration applied because bootstrap was replayed. The migration record must identify the intended transition and its independently verified outcome. The manifest remains useful precisely because it asks about the resulting state without pretending to be the migration implementation.

Develop broader database administration practice

This exercise develops a habit that extends beyond a particular SQL statement: write the required postcondition, select evidence that can answer it, and stop the conclusion at the edge of that evidence.

For a DBA, that means connecting catalog interpretation to schema ownership and recovery planning. For a backend engineer, it means expressing an application dependency precisely enough to review. For a migration reviewer, it means distinguishing a command's status from the state a release needs.

The practice task remains concrete. Explain why matching and wrong_default can both preserve their objects while receiving different decisions. Explain why the view is not admitted. Identify which omitted properties would require another assessment. Then describe what evidence a real PostgreSQL run still needs to produce.

Refonte Learning's Database Administrator Essentials page lists a three-month program requiring 12–14 hours per week. Its stated areas include database design, SQL optimization, backup and recovery, migration and integration, security, and monitoring. These are relevant foundations for the broader responsibilities surrounding schema review.

The admission text requires working toward a bachelor's or higher-level degree. The page describes a potential internship and a Training Certificate plus Certificate of Internship upon successful completion. Those descriptions should be read with the current admission and completion conditions.

The immediate engineering deliverable is still an owned decision: a versioned expectation, complete evidence, a precise mismatch when present, and a named person responsible for the next action. Broader study supports that discipline; the laboratory's own execution and release review must still be completed.

Evaluate this particular exercise through its source and retained database evidence. A general program description supplies context for further learning, while qualification of the proposed fixture remains the responsibility of its operator and reviewer.

For structured work in database design, SQL, migration practice and recovery, review Database Administrator Essentials and its current admission details.