Skip to content

cli-output-examples.md

Latest commit

 

History

History
589 lines (529 loc) · 26.6 KB

cli-output-examples.md

File metadata and controls

589 lines (529 loc) · 26.6 KB
Raw

CLI output examples

Representative, real outputs for every shape the CLI produces: the plan report for each dry-run disposition, the execution verdict, the linter, pull, diff, and the offline capabilities matrix. Every human text rendering is display only and unpinned — including pull's, which has no JSON form yet — see the animated demos in demos/ for how it reads; where JSON exists it is the machine contract. The text reports color their diagnostic labels when stdout is a terminal (--color=auto|always|never; NO_COLOR and TERM=dumb disable auto-detection); the JSON and diff --sql outputs are never colored. All were captured verbatim from a real session against the compose database (make db-up, PostgreSQL 16) — $ marks the command, everything after it is the tool's output — with this schema:

CREATE TABLE users (id bigint PRIMARY KEY, email text);
CREATE TABLE events (id bigint, created date) PARTITION BY RANGE (created);

migrate exit codes follow the four-code ladder under Exit codes: the dry run exits 0 when every statement is executable and 2 when any would be refused — including a target table that does not exist, so a typo'd name cannot gate green — as defined with the diagnostic codes; CI can gate on the exit code without parsing JSON. The gate is refusals only: a destructive-but-executable change (DROP COLUMN) warns and exits 0 — a gate that must stop drops checks .statements[].destructive in the JSON report. Exit 2 covers every refusal cause; the exit code answers should this proceed, the report's typed reason and disposition answer why not. The contract is migrate's: lint exits 1 when a script has error-severity findings (warnings alone exit 0), and diff prints the plan and exits 2 when it contains a statement execution would refuse — a missing table is diff's greenfield case (the plan creates the table), not a refusal, unlike the dry run. Statement kinds migrate does not support (DROP INDEX, REINDEX, CREATE TABLE, and anything that is not ALTER TABLE or CREATE INDEX) emit the same refusal verdict on --dry-run as on apply — a verdict, not a plan report — and exit 2. The JSON report schema is plan-report.md.

Exit codes

The process status is the part of the contract a shell reads without JSON. Each code answers whether anything committed and whether the engine vouches for it as online-safe; the section headings below name the code each example exits with. The table is restated from the README, which is the canonical statement of the ladder; a wording fix lands there first.

Exit Meaning Anything committed? Online-safe?
0 Executed through an online-safe path; or a dry run, diff, or pull found nothing to refuse yes (dry run, diff, pull: nothing runs) yes
1 Failed: a PostgreSQL error after execution started (rolled back), or an operational or usage error before it — bad flags, an unreachable database, a mismatched --force acknowledgement no; a safer sequence that stopped mid-flight keeps its committed prefix, named in executed_sql not applicable
2 Refused: no online-safe path; the verdict names the typed reason and class no not applicable
3 Committed through the accepted-blocking passthrough, under bounded budgets, without an online-safety guarantee yes no

Refusals from every command — migrate, its dry run, diff, and pull — share exit 2, so a gate branches on the status without caring which subcommand produced it; exit 3 is migrate's alone, because no other command executes DDL, and only migrate --accept-blocking reaches the passthrough that produces it. Exit 2 means nothing committed: an optimistic attempt that exceeded its statement budget did run and was rolled back, and still exits 2. A gate that treats every non-zero status as failure is fail-closed for all three non-zero codes; a caller that deliberately permits the passthrough allows 3 explicitly. The rationale is in lock-budgeted-passthrough.md.

Codes used in these examples

Every diagnostic in the examples below carries one of these typed codes. Each is a one-line summary; the linked reference entry is authoritative.

Code Meaning
metadata-only A brief catalog-only change: at most a short ACCESS EXCLUSIVE lock, no table scan or rewrite. Runs as written.
safer-idiom The statement blocks as written but an equivalent online sequence exists; pg-sprite substitutes it. Each step commits on its own — the sequence is not transactionally equivalent to the original.
type-rewrite The column type change is not binary-coercible, so PostgreSQL rewrites the whole table under ACCESS EXCLUSIVE. Routed to the copy-and-swap backend.
rewrite-required The statement blocks as written and no online replacement could be constructed. Refused; split the change into separate online steps. The statement's guidance field names the typed manual path (Guidance vocabulary).
backend-unavailable The plan routes to a backend (online shadow-table copy with cutover) this build does not implement yet. Refused; nothing executes.
unsupported-partitioned-parent The routed plan builds an index concurrently but the target is a partitioned parent, where PostgreSQL cannot CREATE INDEX CONCURRENTLY. Refused.
unsupported-statement The planner knows no safe path for the statement (for example SET UNLOGGED, CLUSTER ON). Refused — the same typed reason the run path's refusal verdict carries.
table-not-found The target table does not exist, so classification fell back to zero facts; running without --dry-run would fail. The dry run exits 2 and the report carries table_exists: false.
destructive The change discards live data or structure — a dropped column, constraint, index, or NOT NULL (dropping a DEFAULT is not destructive). A warning alongside the routing decision, not a refusal; DROP TABLE never reaches classification, it refuses as unsupported-statement.
blocking-idiom Lint-only code: the submitted form blocks readers or writers and a safer native form exists; the finding's suggestion carries the safer SQL when the linter can construct it.

Refusal reasons

Every refusal verdict ("outcome": "refused", exit 2) carries exactly one of these typed reason tokens — the value automation switches on; prose belongs in detail. The set is closed and pinned by test (verdict.Reasons()).

Reason Meaning
unsupported-statement No safe path is known for the statement — only ALTER TABLE and CREATE INDEX reach classification — or a greenfield create plan carries a shape the create path refuses (PARTITION OF, INHERITS, LIKE, OF, IF NOT EXISTS, or a duplicate claimed relation name; the plan statement's cause names which — a create-shape vocabulary distinct from the verdict's budget cause below). These greenfield shapes refuse in the plan and are re-checked at apply.
index-statement Index maintenance (DROP INDEX, REINDEX) has a native safe idiom (CONCURRENTLY) and is never attempted; the verdict's safer_idiom names it.
not-native-safe-table-too-large The size guard skipped the optimistic attempt: the table exceeds the configured bound and the change is not provably metadata-only.
insufficient-privileges The connected role lacks the access the change needs; detail names the missing grant for preflight checks (see engine-role.md). RLS admission names the required table ownership or database CREATE privilege and its remedy; permission errors reported by PostgreSQL retain the server diagnostic, which may not identify an exact GRANT.
unsupported-partitioned-parent The routed plan builds an index on a partitioned parent, where PostgreSQL cannot CREATE INDEX CONCURRENTLY.
not-native-safe-budget-exceeded The optimistic attempt exceeded its lock or statement budget and was cancelled; the verdict's cause narrows which budget fired. The same cause field carries the partitioned-parent shape under unsupported-partitioned-parent; both are refusal causes, unrelated to the plan statement's create-shape cause.
not-native-safe-rewrite-required The submitted form blocks and must run as a safer native sequence, but none could be constructed.
backend-unavailable The change routes to an execution strategy this build does not implement (copy-and-swap).
destructive-change The desired-state plan discards live structure — a dropped column, constraint, index, or NOT NULL — and desired-state execution runs no destructive statement; run the drop deliberately instead (execution model).
plan-fingerprint-mismatch The plan recomputed at execution time does not carry the pinned fingerprint: the plan a reviewer approved is not the plan that would execute, so nothing runs (execution model).
create-collision The greenfield create plan's table name or a claimed index, constraint-index, or sequence name is occupied. Nothing runs; re-derive the plan against the live catalog to see what holds the name, then drop or rename the occupant, name a constraint's index explicitly, or for a sequence use an explicitly named sequence or a non-serial column — re-planning alone reproduces the refusal. Catalog absence checks handle existing occupants. Duplicate-name SQLSTATEs backstop races for explicit names; for server-chosen names, the probe narrows the race to the time-of-check window, and after the CREATE TABLE commits the executor reads the constraint-index and sequence names the table actually owns and compares them against the claimed first-choice names — a name taken inside the window makes the server pick a suffixed replacement, which surfaces as a typed create-name-mismatch failure at step 1 with the born table left in place for an operator to rename the relation or drop, then re-diff.

Two codes that look like refusals are not: create-name-mismatch and create-names-unverified are executor failure codes on a failed verdict (exit 1, failed_step 1), because the CREATE TABLE has already committed when they arise. The first means a claimed first-choice constraint-index or sequence name went to an occupant inside the probe's window and the server suffixed it; the second means the read of the names the table owns did not complete, so the claims are unproven, not failed. In both the born table is left in place — compare its names against the desired file, rename or drop, then re-diff.

Migrate

The imperative front door: submit one DDL statement; pg-sprite classifies it, routes it, and either runs it, substitutes a safer online sequence, or refuses. --dry-run --json emits the plan report without executing.

Runs as written (metadata-only) — exit 0

A safe submitted form executes unchanged: exec_sql is the statement itself.

$ pg-sprite migrate --alter 'ALTER TABLE users ADD COLUMN note text' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "execute",
  "fingerprint": "sha256:653b46e2647e787478db2feb7115b8fe440e392115d465278fec0cfd33892484",
  "statements": [
    {
      "sql": "ALTER TABLE users ADD COLUMN note text",
      "destructive": false,
      "route": "native",
      "backend": "native",
      "disposition": "execute",
      "decisions": [
        {
          "operation": "ADD COLUMN note",
          "destructive": false,
          "route": "native",
          "reason": "metadata-only"
        }
      ],
      "exec_sql": [
        "ALTER TABLE users ADD COLUMN note text"
      ],
      "execution": "autocommit-each-step"
    }
  ]
}

Safer-sequence substitution (safer-idiom) — exit 0

The submitted ADD CONSTRAINT ... UNIQUE blocks as written, so pg-sprite plans the safer online sequence instead: the decision carries it in safer_sql, and exec_sql is what migrate would run.

$ pg-sprite migrate --alter 'ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email)' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "execute",
  "fingerprint": "sha256:e2b2a51466547f4469754c9296524844f868eb5a967aba1e290ed8aa7ee63996",
  "statements": [
    {
      "sql": "ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email)",
      "destructive": false,
      "route": "native",
      "backend": "native",
      "disposition": "execute",
      "decisions": [
        {
          "operation": "ADD CONSTRAINT users_email_key",
          "destructive": false,
          "route": "native",
          "reason": "safer-idiom",
          "safer_sql": [
            "CREATE UNIQUE INDEX CONCURRENTLY \"users_email_key\" ON \"users\" (\"email\")",
            "ALTER TABLE \"users\" ADD CONSTRAINT \"users_email_key\" UNIQUE USING INDEX \"users_email_key\""
          ],
          "safer_sql_execution": "autocommit-each-step"
        }
      ],
      "exec_sql": [
        "CREATE UNIQUE INDEX CONCURRENTLY \"users_email_key\" ON \"users\" (\"email\")",
        "ALTER TABLE \"users\" ADD CONSTRAINT \"users_email_key\" UNIQUE USING INDEX \"users_email_key\""
      ],
      "execution": "autocommit-each-step"
    }
  ]
}

Real execution

The same statement without --dry-run runs the substituted sequence for real. The verdict names what was executed and every step that committed (exit 0):

$ pg-sprite migrate --alter 'ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email)' --json
{
  "outcome": "executed-natively",
  "statement": "ALTER TABLE public.users ADD CONSTRAINT users_email_key UNIQUE (email)",
  "table": "public.users",
  "detail": "the submitted form blocks; pg-sprite ran the safer native sequence instead — all 2 steps committed",
  "executed_sql": [
    "CREATE UNIQUE INDEX CONCURRENTLY \"users_email_key\" ON \"public\".\"users\" (\"email\")",
    "ALTER TABLE \"public\".\"users\" ADD CONSTRAINT \"users_email_key\" UNIQUE USING INDEX \"users_email_key\""
  ]
}

An operator-accepted blocking refusal has a distinct marked outcome and exit 3; exit 0 remains exclusive to online-safe execution. --accept-blocking takes the schema-qualified table whose lock the operator accepts, must match the index's owning table exactly (a mismatch executes nothing and exits 1), and requires an explicit --statement-timeout; it cannot be combined with --force or --dry-run. The text rendering marks the outcome as a warning:

executed without online safety (accepted blocking refusal)
  table:     public.users
  refusal:   by-design / index-statement
  statement: DROP INDEX public.users_email_idx
  safer:     DROP INDEX CONCURRENTLY
  budgets:   lock 3s, statement 10m

$ pg-sprite migrate --alter 'DROP INDEX public.users_email_idx' --accept-blocking public.users --lock-timeout 3s --statement-timeout 10m --json
{
  "outcome": "executed-without-online-safety",
  "reason": "index-statement",
  "class": "by-design",
  "statement": "DROP INDEX public.users_email_idx",
  "table": "public.users",
  "safer_idiom": "DROP INDEX CONCURRENTLY",
  "blocking_passthrough": true,
  "lock_timeout": "3s",
  "statement_timeout": "10m"
}

Refused: no online rewrite exists (rewrite-required) — exit 2

The column and its constraint arrive in one statement, so no online substitution can be constructed; the change must be rewritten as separate online steps. No exec_sql is offered. The guidance field names the typed manual path — here add-column-then-constraint: add the plain column first, then build the constraint as a separate, named ADD CONSTRAINT with its online pattern (see the suggest report's Guidance vocabulary).

$ pg-sprite migrate --alter 'ALTER TABLE users ADD COLUMN nickname text UNIQUE' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "rewrite-required",
  "fingerprint": "sha256:9773ff32c62b04e97bacb0ae85cf0ad528164c7baafc33db66d8adf20d7a5674",
  "statements": [
    {
      "sql": "ALTER TABLE users ADD COLUMN nickname text UNIQUE",
      "destructive": false,
      "route": "native",
      "backend": "native",
      "disposition": "rewrite-required",
      "decisions": [
        {
          "operation": "ADD COLUMN nickname",
          "destructive": false,
          "route": "native",
          "reason": "safer-idiom"
        }
      ],
      "guidance": "add-column-then-constraint"
    }
  ]
}

Refused: needs a backend this build lacks (backend-unavailable) — exit 2

A genuine table rewrite routes to the copy-and-swap backend, which is not implemented yet.

$ pg-sprite migrate --alter 'ALTER TABLE users ALTER COLUMN id TYPE text' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "unavailable",
  "fingerprint": "sha256:fb4836fa89efab4280be1680b8c3181fb902ec67131ae5959df3d5e872f1b3c5",
  "statements": [
    {
      "sql": "ALTER TABLE users ALTER COLUMN id TYPE text",
      "destructive": false,
      "route": "copy-and-swap",
      "backend": "copy-and-swap",
      "disposition": "unavailable",
      "decisions": [
        {
          "operation": "ALTER COLUMN id TYPE text",
          "destructive": false,
          "route": "copy-and-swap",
          "reason": "type-rewrite"
        }
      ]
    }
  ]
}

Refused: target facts forbid the plan (unsupported-partitioned-parent) — exit 2

The routed plan builds an index concurrently, but the target is a partitioned parent — PostgreSQL cannot CREATE INDEX CONCURRENTLY there. The refusal cause is the report-level reason.

$ pg-sprite migrate --alter 'CREATE INDEX events_created_idx ON events (created)' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "events",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "refuse",
  "reason": "unsupported-partitioned-parent",
  "class": "capability-boundary",
  "fingerprint": "sha256:e0cebea56d6c5577722d17be16442b06303a817af0c009913d746fd3d1c379e0",
  "statements": [
    {
      "sql": "CREATE INDEX events_created_idx ON events USING btree (created)",
      "destructive": false,
      "route": "native",
      "disposition": "refuse",
      "reason": "unsupported-partitioned-parent",
      "class": "capability-boundary",
      "blocking_passthrough_eligible": false,
      "decisions": [
        {
          "operation": "CREATE INDEX events_created_idx",
          "destructive": false,
          "route": "native",
          "reason": "safer-idiom"
        }
      ]
    }
  ]
}

Executable but destructive (destructive) — exit 0

A DROP COLUMN routes to execute — the destructive flag is surfaced for the reviewer or orchestrator to gate on; migrate itself does not block it.

$ pg-sprite migrate --alter 'ALTER TABLE users DROP COLUMN email' --dry-run --json
{
  "format_version": 5,
  "source": "alter",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "execute",
  "fingerprint": "sha256:77caa837ce1750b23eaaf9f988ede933f624c8c4c6de1dc2fee7780bf64a4c1f",
  "statements": [
    {
      "sql": "ALTER TABLE users DROP email",
      "destructive": true,
      "route": "native",
      "backend": "native",
      "disposition": "execute",
      "decisions": [
        {
          "operation": "DROP COLUMN email",
          "destructive": true,
          "route": "native",
          "reason": "metadata-only"
        }
      ],
      "exec_sql": [
        "ALTER TABLE users DROP email"
      ],
      "execution": "autocommit-each-step"
    }
  ]
}

Lint

The offline linter needs no database: it flags blocking idioms in a DDL file (or stdin) with the safer form suggested for each finding (schema: lint-report.md). Given:

-- /tmp/changes.sql
CREATE INDEX users_email_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

Blocking idioms flagged (blocking-idiom) — exit 0

$ pg-sprite lint /tmp/changes.sql --json
{
  "format_version": 1,
  "postgres_versions": "14-18",
  "findings": [
    {
      "statement": 1,
      "line": 1,
      "column": 1,
      "sql": "CREATE INDEX users_email_idx ON users (email)",
      "operation": "CREATE INDEX users_email_idx",
      "code": "blocking-idiom",
      "severity": "warning",
      "reason": "safer-idiom",
      "suggestion": [
        "CREATE INDEX CONCURRENTLY users_email_idx ON users USING btree (email)"
      ],
      "suggestion_execution": "autocommit-each-step"
    },
    {
      "statement": 2,
      "line": 2,
      "column": 1,
      "sql": "ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email)",
      "operation": "ADD CONSTRAINT users_email_key",
      "code": "blocking-idiom",
      "severity": "warning",
      "reason": "safer-idiom",
      "suggestion": [
        "CREATE UNIQUE INDEX CONCURRENTLY \"users_email_key\" ON \"users\" (\"email\")",
        "ALTER TABLE \"users\" ADD CONSTRAINT \"users_email_key\" UNIQUE USING INDEX \"users_email_key\""
      ],
      "suggestion_execution": "autocommit-each-step"
    }
  ],
  "errors": 0,
  "warnings": 2
}

Pull

pull exports every renderable table in a schema as a separate desired-state file. It currently emits a text-only per-table report rather than JSON. This output was captured from the compose database after loading demo/seed.sql:

Export a desired-state file per table — exit 0

$ pg-sprite pull --schema public --out /tmp/pg-sprite-pull-example
PULLED  orders -> /tmp/pg-sprite-pull-example/orders.sql
PULLED  users -> /tmp/pg-sprite-pull-example/users.sql
Summary: 2 pulled, 0 refused, 0 errors

See pull.md for the refusal and exit-code contract and the zero-change diff verification loop.

Diff

The declarative front door: point diff at a reviewed desired-state file and it emits the classified statements that converge the live table onto it, as the same plan report ("source": "diff"). Given the live users table above (with the unique constraint from the execution example) and:

-- /tmp/users.sql
CREATE TABLE users (
    id bigint PRIMARY KEY,
    email text,
    nickname text,
    CONSTRAINT users_email_key UNIQUE (email)
);

Converge to the desired state (metadata-only) — exit 0

$ pg-sprite diff --desired /tmp/users.sql --json
{
  "format_version": 5,
  "source": "diff",
  "schema": "public",
  "table": "users",
  "server_version": "16.14 (Debian 16.14-1.pgdg13+1)",
  "table_exists": true,
  "disposition": "execute",
  "fingerprint": "sha256:4c349e89a66fed63dd07f693dae62e356695f2a586306cfd5a153e6c1efcd9f9",
  "statements": [
    {
      "sql": "ALTER TABLE public.users ADD COLUMN nickname text",
      "kind": "add-column",
      "destructive": false,
      "route": "native",
      "backend": "native",
      "disposition": "execute",
      "decisions": [
        {
          "operation": "ADD COLUMN nickname",
          "destructive": false,
          "route": "native",
          "reason": "metadata-only"
        }
      ],
      "exec_sql": [
        "ALTER TABLE public.users ADD COLUMN nickname text"
      ],
      "execution": "autocommit-each-step"
    }
  ]
}

Capabilities

capabilities needs no database: it prints the embedded support matrix that generates capabilities.md, stamped with the same release version pg-sprite --version reports, so a consumer can pin a build and query the matrix with jq (recipes in capabilities-contract.md). The text form is a display-only table; the JSON is the contract. The output is long, so this example shows its opening lines:

Embedded support matrix with the binary version — exit 0

$ pg-sprite capabilities --json | head
{
  "version": "dev",
  "capabilities": [
    {
      "id": "add-column-no-default-or-constant-default",
      "area": "column_changes",
      "operation": "`ADD COLUMN` (no default, or constant default)",
      "tier": "t1",
      "status_mark": "✅",
      "engine_path": "native_as_is",