pg-sprite refuses a schema change at capability, ownership, design, environment, or internal-invariant boundaries. Some refusals mean there is no online-safety problem for the engine to solve and name the owner that should handle the work. This page explains the mechanics of each current refusal; the complete support matrix — every operation and object type, tiered as supported / planned / out of scope, with reasons — is capabilities.md. These are current capability boundaries, not escape hatches:
| Change | Current behavior |
|---|---|
DROP COLUMN, DROP CONSTRAINT, and ALTER COLUMN ... DROP NOT NULL on migrate --alter |
Refused with destructive-change (class by-design, exit 2) before any route runs the statement — native, safer sequence, --force, or copy-and-swap — and --dry-run ends in the same refusal verdict: each discards data or a guarantee the schema was providing, and pg-sprite does not infer that the loss is intended. --allow-destructive SCHEMA.TABLE (Options.AllowDestructive as a library caller) names the resolved table and runs the statement through every other gate as usual; its verdict carries allowed_destructive: true and the run logs one warning naming the table. A mismatched acknowledgement executes nothing and exits 1. It acknowledges only the loss: a forced type rewrite that also drops a column needs --force too. It cannot be combined with --accept-blocking or --desired, and it does not reach DROP INDEX, which keeps its own refusal below. A statement that drops a column and adds one of the same name back is refused with the alter-column-type-in-place guidance: ALTER COLUMN ... TYPE ... USING ... keeps the values the drop would discard. A planner refusal of a drop (replica-identity, dependent-view, cascading-drop) keeps its own reason, since no acknowledgement lifts it. |
| Index builds on a partitioned parent | PostgreSQL cannot build an index concurrently at the parent level. PostgreSQL supports a plain blocking build, but pg-sprite refuses it by policy because it takes ACCESS EXCLUSIVE; --force does not bypass this decision. migrate --accept-blocking does not reach this refusal yet either — it covers the single-relation DROP INDEX / REINDEX refusals — so an operator who chooses a maintenance-window blocking build runs it outside the CLI today; a library caller can run it through executor.ExecuteAcceptedBlocking, which marks the verdict as executed without online safety (lock-budgeted-passthrough.md). The partition-aware CREATE INDEX ON ONLY → per-partition CREATE INDEX CONCURRENTLY → ATTACH PARTITION flow is planned but not yet implemented. |
ADD CONSTRAINT ... USING INDEX on a partitioned parent |
PostgreSQL does not support adopting an existing index on a partitioned parent in any supported version. pg-sprite refuses before execution. |
ADD PRIMARY KEY / ADD UNIQUE with a storage parameter the online sequence cannot render |
The online sequence builds the key's index with CREATE UNIQUE INDEX CONCURRENTLY and carries the key's WITH (...) parameters onto it, each name quoted and each value a quoted literal. A parameter it cannot render faithfully, one whose value is not a plain word, number, or string, contains a backslash, or whose name is namespaced, would be dropped or changed, so no sequence is built. Running the statement as written builds the index under ACCESS EXCLUSIVE, so it is refused as rewrite-required with the unique-index-then-constraint guidance. Build the index concurrently with the parameter, then attach it with ADD CONSTRAINT ... USING INDEX. |
ADD FOREIGN KEY ... NOT VALID on a partitioned parent |
PostgreSQL does not support this before version 18, so pg-sprite refuses it on versions 14–17. It is supported on version 18 and later. |
DROP INDEX and REINDEX, plain or CONCURRENTLY |
migrate never runs index maintenance online-safely. The plain form is refused with index-statement and names its CONCURRENTLY idiom; the concurrent form is refused as already safe, so run it directly against the database. migrate --accept-blocking SCHEMA.TABLE runs a single-relation plain DROP INDEX, REINDEX INDEX, or REINDEX TABLE as-is under bounded budgets and exits 3 (lock-budgeted-passthrough.md). diff's hint to run a removed index through migrate --alter ends in this refusal; run DROP INDEX CONCURRENTLY directly. |
RENAME COLUMN and RENAME TO |
Without --force, refused with not-native-safe-rewrite-required, nothing executed: the catalog flip is instant, but every running application instance that still queries the old name breaks the moment it commits, and no safer sequence can be built from the statement alone because the dual-write and the read switch live in the application. The plan report, suggest, and the verdict carry the manual path as typed guidance — expand-contract-column for a column (add the new column, dual-write and backfill, switch reads, drop the old column as its own reviewed change) and coordinated-table-rename for a table (coordinate the rename with the deploy that adopts the new name, or keep the old name readable through a view until every instance has moved) — and lint reports the rename as an error. --force SCHEMA.TABLE runs the submitted rename as a blind bounded attempt under the usual budgets and size guard, for the operator who has coordinated the deploy; on a table above --max-table-size (default 1 GiB) the forced rename is refused as not-native-safe-table-too-large until that threshold is raised. |
ALTER INDEX ... RENAME TO on an index that backs a primary-key, unique, or exclusion constraint |
Refused with not-native-safe-rewrite-required, class by-design, guidance coordinated-constraint-rename, nothing executed: PostgreSQL renames the constraint with its index, and ON CONFLICT ON CONSTRAINT and error handling that name the old constraint break the moment it commits. --force does not apply to an index rename. A plain index rename runs. The refusal needs the catalog, so offline lint and suggest do not report it. |
ATTACH PARTITION |
Without --force, refused with not-native-safe-rewrite-required, nothing executed: the engine does not yet construct the validated CHECK that lets the attach skip its scan. Pre-prove the bound yourself, then run the attach directly. --force SCHEMA.TABLE runs the blocking ATTACH PARTITION as submitted under bounded budgets, taking the blocking lock. |
DETACH PARTITION and DETACH PARTITION ... CONCURRENTLY |
Without --force, refused with unsupported-statement before anything executes: the plain form is rewritten to CONCURRENTLY, and the executor does not drive that step because a cancelled concurrent detach leaves a detach-pending state it does not yet own recovering. Run DETACH PARTITION ... CONCURRENTLY directly. --force SCHEMA.TABLE runs a plain DETACH PARTITION as submitted under bounded budgets, taking the blocking lock; DETACH ... CONCURRENTLY stays refused under --force. migrate --dry-run ends in the same refusal and exit code 2, so it is a submit gate for a detach; suggest shows the CONCURRENTLY rewrite. |
| Dropping what a replica identity index is built on | REPLICA IDENTITY USING INDEX outlives its index, so dropping the index, the primary key or unique constraint that owns it, or a column it covers leaves the table with no usable identity, and every UPDATE and DELETE on it fails while a publication publishes them. The planner refuses such a drop with replica-identity, on the table and on what the drop reaches below it: a DROP COLUMN reaches partitions and inheritance children unless the statement says ONLY, and a DROP CONSTRAINT reaches partitions, whose indexes are attached under the parent's. The executor reads the identity again under the lock the drop takes, so an identity that moved onto the dropped object after planning still refuses the run before anything commits. migrate --accept-blocking makes the same check before a DROP INDEX. Point REPLICA IDENTITY at another unique index first, or DEFAULT if a usable primary key remains after the drop, then re-plan. |
| Retyping or dropping a column a view reads | PostgreSQL refuses ALTER COLUMN TYPE and DROP COLUMN on a column a view or materialized view reads, even for a type change it would relabel in place. Without ONLY, a type change reaches the column on every partition and inheritance child too, and a drop reaches every partition and each inheritance child that does not declare the column and inherits it only from tables the drop also reaches; a child that declares the column or also inherits it from a table outside the drop keeps its copy, while one that inherits it along two paths from the dropped table loses it. The planner reads the views on every copy the change reaches from the catalog and refuses the change with dependent-view, naming each view in the plan and in the verdict's dependent_views, instead of letting the statement fail at the server. DROP COLUMN … CASCADE, which would drop the views with the column, is refused with cascading-drop. pg-sprite does not drop and recreate the views for you. Drop or redefine them first, then re-plan. |
Dropping with CASCADE |
CASCADE on DROP COLUMN, DROP CONSTRAINT, or DROP INDEX also drops whatever depends on the target: a view that reads the column, or another table's foreign key on the constraint. Those objects are not in the statement, so the plan cannot show them and the table's lock does not cover them. The planner refuses every such drop with cascading-drop. Drop each dependent object as its own change, then resubmit without CASCADE; PostgreSQL then refuses the drop while anything still depends on the target. |
ADD COLUMN on a hot standby, or by a role without TEMPORARY |
Whether adding a column rewrites the table is the server's answer, read by adding the column to an empty temporary table. A server that accepts no writes, or a role whose TEMPORARY on the database was revoked, cannot create that table, so the plan fails with ErrAddColumnProbeUnavailable before anything runs. Plan against the primary, or grant TEMPORARY on the database to the engine's role. |
| Copy-and-swap | The copy-and-swap backend carries a general ALTER COLUMN TYPE (with or without USING): the changed shape is built as a shadow table, filled in chunks while writes stream through a logical replication slot, verified, and swapped in under the source's name. The run needs --free-disk (the preflight refuses copy-and-swap-disk-headroom when the shadow and its indexes would not fit) and either logical decoding (wal_level = logical, a free replication slot) or --block-writes SCHEMA.TABLE, which names the table whose writes it blocks until the swap; its preflight refuses table shapes it cannot run against — no primary key or usable replica identity, unlogged, partitioned, FORCE ROW LEVEL SECURITY, foreign keys in either direction, triggers, dependent views, membership in a publication, or a subscription on the database — each as a typed copy-and-swap-refused cause (refusal-classes.md). The other rewrite-class operations — volatile-default ADD COLUMN, ADD COLUMN of a domain with a CHECK or NOT NULL, GENERATED … STORED, SET TABLESPACE — route to the backend but it does not carry them yet; they refuse backend-unavailable. pg-sprite never falls through to a blocking rewrite. A run that stops short of the swap keeps its hold on the source until it is resumed or abandoned — the write fence of a --block-writes run (every write is refused 55006), or the replication slot of a live run, which pins WAL on the server — and the failed verdict's detail says which applies: a stop the next run clears (a lost connection, a cancelled run) resumes from the checkpoint row when the same statement is run again; a stop the data causes meets the same rows again, so it is not resumed: when the server rejects a row under the new type or a constraint (SQLSTATE class 22 or 23) the run abandons itself — drops the shadow, releases the fence or slot, deletes the checkpoint row — and the verdict is failed with code: data-fault, so correct the data or the statement and run it again from the beginning; when the shadow and source diverge at verification (or the abandon itself failed), the row and the hold stay and the run has to be started over: correct the data or the statement, then run it with --restart SCHEMA.TABLE (CopySwapOptions.Restart as a library caller), which drops the shadow, releases the fence or slot, deletes the checkpoint row, and runs the change fresh under the run's own lock (stopping, resuming, and starting over). |
ALTER COLUMN TYPE ... USING <expr> the copy cannot reproduce |
The copy-and-swap route (above) runs the retype, but the shadow builder admits a USING expression only when it can evaluate it again, identically, in the chunk copy, the verifier's recopy, and every change the catch-up flushes. Three rules, each refused as shadow-statement-conversion before any shadow is committed: the expression must be immutable by the server's own classification (no now(), random(), current_setting(), session-dependent casts such as text to timestamptz, or functions declared STABLE or VOLATILE; checked by trying it as a stored generated column's expression on the empty shadow inside the build's transaction, then rolling that attempt back), it must name only the column it retypes, bare and unqualified (no neighbouring column, no t.col, no whole-row t.*), and it must not retype the primary key, whose values every chunk range, watermark, and resume clear are expressed in. The expression is also refused when the deparser cannot prove it rendered the text faithfully. Rewrite the expression in terms of the retyped column alone and of IMMUTABLE functions, or retype without USING so the server's assignment cast converts the value; the refusal names the column, the expression, and that rewrite. The spellings most often refused, and what to write instead, are in USING expressions the copy reproduces below. |
| A schema change to the source table while a copy-and-swap runs | Unsupported: logical decoding does not stream DDL, and the copy does not lock it out — a chunk holds ACCESS SHARE on the source only for its own transaction, so ALTER TABLE on the source completes between chunks. The copy selects the source by column name, so what happens depends on whether every copied name still resolves. A copied column renamed or dropped stops the copy at its next chunk with the server's undefined_column error (SQLSTATE 42703), which is not a typed refusal; that chunk stays in flight and nothing from it reaches the shadow. A change that leaves every copied name resolving to a column of a compatible type — two columns swapping names, a column renamed and re-added under the old name, a widened type — is not detected by the copy, which completes, and after a swap or a re-add the rows copied since hold another column's values or the new column's default. Once the replication stream has decoded a change to the table, a later relation description that differs stops it with ErrSourceShapeChanged. None of these reaches the swap: the verifier compares source and shadow rows by column name, and the cutover gate re-fingerprints the source and refuses any changed column as cutover-schema-drift (refusal-classes.md), after which the shadow is dropped and the change re-planned. |
| A concurrent index build whose name is occupied by an invalid index, or whose table carries quarantined debris | The build refuses before running and never drops the occupant: an invalid entry is either abandoned debris or someone's build still in progress, and a drop by name cannot tell them apart. The refusal is typed by proof (invalid-index-abandoned, invalid-index-build-in-flight, invalid-index-other-table, invalid-index-not-droppable, invalid-index-builder-unobservable), and a table carrying an entry a previous recovery quarantined (pgsprite_abandoned_<oid>) but did not drop is refused the same way, so debris never goes quiet. The proven removal — executor.RebuildAbandonedIndex, or executor.DropAbandonedIndex to remove the leftover without running the build; both library-only today, no CLI verb — takes a bounded SHARE UPDATE EXCLUSIVE lock on the table (which any running concurrent index command holds), re-verifies the entry by OID, renames it to an identity-derived name and drops that, all within one shared budget; it refuses a visible in-flight build, another table's entry, or an entry the server will not drop concurrently (a partitioned table's index, an index partition, a constraint's index), and reports a lock budget rather than wait behind a build it cannot see. From the CLI, the runbook applies. |
| A copy-and-swap statement that drops a column and adds one of the same name | Like any drop, it runs only under --allow-destructive; without it the statement is refused with destructive-change and the alter-column-type-in-place guidance before the shadow build starts. Under the acknowledgement, the re-added column is a new column: every row holds its default (or NULL), as under a direct ALTER TABLE, and the dropped column's values are not copied into it (copy-and-swap D1). Three such re-adds have no default to take or are the key the copy is driven by, and the shadow build refuses them with shadow-statement-readd inside its own transaction, before any shadow, fence, or checkpoint row exists: re-adding the primary key column, re-adding a source identity column, and re-adding a column that refuses NULL (by its own NOT NULL or its domain's) with no default of its own or on its type. The remedy depends on which: a primary key is refused whatever the new column declares, so a default does not help — change the key in place with ALTER COLUMN TYPE; a source identity column is refused whatever the new column declares too — drop it in one schema change and add the new column in the next; any other column runs once the re-added column has a default (its own, or its domain's). A nullable, defaulted, or generated re-add builds and runs. A re-add declared as an identity or serial builds, copies, and then stops at the swap with cutover-swap-mismatch, leaving the shadow and the hold on the source for --restart, as any new identity or sequence-owned column added through copy-and-swap does. The refusal covers only these named re-add shapes: a new column the server cannot fill in other ways — a CHECK that rejects the column's default or NULL, a NOT NULL column added without a drop beside a retype, or the key replaced under a new name — still builds and stops at the first chunk or the copier's shape check, failing closed; a first-chunk stop is the server's class 23 error, a data fault, so the run abandons itself and the verdict is failed with code: data-fault. A run checkpointed by an earlier build for a drop-and-re-add statement no longer resumes (resume-row-mismatch, because the copy list it recorded names the re-added column); abandon it and run again rather than retrying the resume. |
The bar is the server's own classification, read by trying the expression as a stored
generated column's expression: IMMUTABLE, not STABLE. STABLE promises one answer
within a statement, not across the hours a copy takes or the sessions the catch-up opens.
Many conversions people write for a retype are refused not because they read the clock
but because the builtin behind them is STABLE — any cast whose text form depends on
DateStyle, TimeZone, or the enum's catalog entry is. The refusal arrives at the shadow
build, inside the run, after the plan has been reviewed: the plan cannot read volatility
off the statement's text, so a plan that reads clean can still stop at this gate.
| Refused spelling | Why the server refuses it | What to write |
|---|---|---|
enum_col::text |
enum_out is STABLE: the label comes from the catalog |
Retype to text without USING; the assignment cast converts the label |
date_col::text, timestamp_col::text, timestamptz_col::text |
date_out and friends are STABLE: the text follows DateStyle (and TimeZone) |
An IMMUTABLE wrapper of your own that fixes the format, e.g. to_char(d, 'YYYY-MM-DD') inside a function declared IMMUTABLE — to_char itself is STABLE |
text_col::date, text_col::timestamp, text_col::timestamptz |
The input functions are STABLE: parsing follows DateStyle and TimeZone |
A wrapper declared IMMUTABLE that parses one fixed format |
to_char(...), to_date(...), to_timestamp(text, text) |
Declared STABLE: they read lc_time and the session's style settings (to_timestamp(double precision) is IMMUTABLE and admitted) |
A wrapper declared IMMUTABLE around the one format you use |
convert_to(text_col, 'UTF8') |
Declared STABLE: the result depends on the database encoding |
A wrapper declared IMMUTABLE around the one encoding you use; decode(text_col, 'escape') is not a general substitute — it interprets backslash sequences, so it equals convert_to only for text that contains none |
now()::text, random(), current_setting(...), nextval(...), a subquery |
VOLATILE, or not an expression a generated column may hold |
No rewrite reproduces these; the value is not a function of the source row |
A wrapper function is your assertion, not the server's: declaring it IMMUTABLE is what
admits it, and the server never checks the body. It must exist, in the source schema or
public, before the shadow build, since the expression resolves on that path in the build,
the copy, the recopy, and every flush (CO-9).
The build records a fingerprint of what the expression's names resolved to — the functions,
operators, types, and collations by name across that path, with each function's body,
strictness, SET settings, and argument defaults, and each collation's locale — and the
copy, the verifier, the catch-up, and the swap's final drain recompute it inside each of their
transactions, and once more after each write and before its commit. A function or operator
created, replaced, or dropped on that path after the build refuses the run as
copy-conversion-drift, and a run resumed over such a change is refused the same way until
the change is undone. Every one of those write transactions runs READ COMMITTED, whatever
default_transaction_isolation the server or role sets, so a write that resolved through the
change is rolled back by the second check. One case gets past both checks: a change made and
undone again between them. The verifier's comparison against the source is what catches the
rows it reached, except in the swap's final drain, which no comparison follows. Make the change
before the build, or abandon the run with --restart and build again over the new catalog.
The fingerprint covers only the names the expression spells: the functions a wrapper calls in
turn, and the function behind a cast the expression performs (price::cents through
CREATE CAST), are not under a spelled name and are not pinned. Keeping those still for the
length of the run is the wrapper author's responsibility.
Two casts the server does call immutable still have more than one text rendering: a
float4 or float8 to text follows extra_float_digits, and a bytea to text
follows bytea_output. The copy, the verifier, and the catch-up each pin both settings
(3 and hex) in their own sessions, so such a conversion lands the same text from all
three whatever the database or role is configured with.
pg-sprite keeps state on each connection's PostgreSQL session: the execution bounds
(lock_timeout, statement_timeout) every statement runs under, and the per-table
advisory lock that keeps a second run off the same table. Each of its connections must
therefore stay on one session for its whole life. This is session affinity: every
statement sent on a connection runs on the same PostgreSQL backend, so a setting or lock
taken by one statement is still there for the next.
A direct connection always has it. A transaction-mode or statement-mode pooler does not:
it may hand the connection's next transaction to a different backend, one another client
may have just used, so pg-sprite's timeouts could silently vanish and two runs could both
believe they hold the same table lock. pg-sprite checks rather than assumes. When it opens
its pool it reads back the timeouts it set, and on the pool and again on the table lock's
own connection it takes a throwaway advisory lock, then confirms from a second connection
that the first still holds it and that the second cannot take it. A connection that drops
the timeouts is refused with ErrSessionSettingsDiscarded, and one that fails the lock
check with ErrNoSessionAffinity, before any schema change runs.
These checks guard against session loss they can observe; they cannot prove its absence. An idle transaction-mode pooler with spare backends can keep a connection on one backend for the length of the checks and move it later, so a clean start does not make a pooler path safe. Connect directly to the primary.
This constrains only pg-sprite's own connection. The application keeps whatever pooling it uses — a client-side pool, or a server-side pooler in session, transaction, or statement mode — and copy-and-swap reads the application's writes from the WAL through logical decoding, whichever pooler carried them.
| pg-sprite connects through | Support |
|---|---|
| A direct connection to the primary | Supported, and the recommended deployment. Every schema change path, copy-and-swap included, is tested this way. |
| Supabase's Supavisor session endpoint | Supported for the native schema changes listed in supabase.md. Copy-and-swap through it is unsupported. |
| PgBouncer, or any other server-side pooler, in session mode | Unsupported. Connect directly to the primary. |
| Any pooler in transaction mode | Unsupported. Connect directly to the primary. The startup checks usually refuse it before any work runs, with ErrSessionSettingsDiscarded when the pooler drops the timeouts or ErrNoSessionAffinity when it moves the connection; the tests pin ErrNoSessionAffinity for PgBouncer and Supavisor in transaction mode. A pooler that passes the checks is still unsupported. |
| Any pooler in statement mode | Unsupported. Connect directly to the primary. Opening the pool refuses it with ErrNoSessionAffinity: the pooler rejects the check's transaction, which the tests pin for PgBouncer in statement mode. |
The application's pooler and pg-sprite share the server's max_connections, which has
to cover the application's backends plus pg-sprite's pool, its dedicated sessions, and,
for copy-and-swap, its replication connections.
The declarative front door (desired-state files, diff, and schema export)
models one ordinary table plus its indexes. These are the boundaries of
that model today. Wherever an operation meets a boundary, it fails closed
with a typed refusal — never a silently wrong or incomplete result:
| Not modeled | Current behavior |
|---|---|
| Row-level security (RLS) settings and policies | pull exports settings, policies, roles, and policy comments; diff verifies unchanged definitions and shows review-only RLS deltas before refusing changes. The atomic executor, through the Go API or migrate --desired, supports RLS-only changes to existing supported tables. Go callers can preview and bind execution to the ordered generated SQL; this does not bind every earlier catalog value. Policy relation subqueries, mixed table changes, and greenfield RLS creation remain refused. Tables without RLS get no extra SQL; table-only desired files leave access control separately managed. See the RLS workflow and roadmap. |
| Foreign keys (either direction) | Planned, never executed declaratively. A desired file may declare a foreign key whose referenced table is unqualified and exists in the target schema; a schema-qualified reference is refused at parse (ErrQualifiedName) and a referenced table that does not exist fails the plan (ErrReferencedTableNotFound), and so does one the planning role cannot SELECT from (ErrReferencedTableNotReadable), because the scratch copy reads its shape. diff plans a declared foreign key that matches the live one as no change, and an added or undeclared live foreign key as an add-foreign-key or drop-foreign-key statement with the declarative-foreign-key refusal, so the change is reviewable but migrate --desired refuses the plan. A new table that declares a foreign key is refused by the create path with the foreign-key cause. Planning binds each reference to an empty scratch copy of the referenced live table, so it takes only ACCESS SHARE on the live table, the lock a plain SELECT takes, and never blocks its writes. Incoming foreign keys are invisible to a single-table diff, export refuses both a table that owns foreign keys and a table that other tables reference, and desired row-security files refuse foreign keys. Foreign-key DDL is still supported through migrate --alter (ADD FOREIGN KEY routes to the online NOT VALID + VALIDATE sequence). |
| Partitioned tables | Partitioned parents and their partitions are introspectable, and the statement front door supports in-place changes on them (see the partitioned-parent rows above), but they cannot be expressed in or exported to a desired file: the model captures the partition key and attachment only to refuse — it does not carry partition bounds or the parent/partition topology. A partitioning mismatch between live and desired is a typed diff refusal, never a zero diff. A desired file whose CREATE TABLE is PARTITION OF a parent is refused before planning runs anything (ErrDesiredPartitionOf), because building it would attach to the live parent under ACCESS EXCLUSIVE. migrate --desired reports it as an unsupported-statement refusal. |
Classic table inheritance (INHERITS) |
Both parents and children are refused during export. The model records classic inheritance edges in both directions only to fail closed: rendering a child would flatten inherited columns and rendering a parent would omit its children. Declarative partitions share pg_inherits but remain classified by relispartition and use the partition refusal above. A desired file whose CREATE TABLE declares INHERITS is refused before planning runs anything (ErrDesiredInherits), because building it would lock the live parent and add an inheritance edge to it. migrate --desired reports it as an unsupported-statement refusal. |
| Unlogged tables | Persistence is not managed: converging it (SET LOGGED / SET UNLOGGED) is a full table rewrite. Export refuses an unlogged table — a plain CREATE TABLE baseline would silently change its crash-safety and replication behavior — and a persistence mismatch between live and desired is a typed diff refusal, never a zero diff. |
| Table and column comments | Comments are metadata outside the table-shape model. Desired files and exports do not carry them; manage them with owner tooling. |
Storage parameters (fillfactor, etc.) |
Modeled, exported, and compared, but not converged. pull writes a table's storage parameters, its TOAST table's included, back as a WITH (...) clause on the CREATE TABLE, and a desired file that declares the live parameters plans its other changes normally. Boolean values compare by meaning, so a live off matches a declared false. Any other difference between live and desired is a typed diff refusal, never a zero diff: change the parameters with owner tooling (ALTER TABLE ... SET (...)), then declare them in the file. A table with no text-like column has no TOAST table and the server discards toast.* parameters on it, so a file that adds such a column together with a toast.* parameter refuses too. The reverse also happens: a table keeps its TOAST table after its last text-like column is dropped, so export refuses toast.* parameters on a table with a dropped column and no remaining unbounded text-like column (ErrUnrenderableToastOptions) rather than write a baseline whose build would discard them. That refusal is export's alone; a row-security apply builds nothing from the table and is not refused by it. A row-security file is compared like any other desired file, so on a tuned table it declares the live WITH (...) clause too. |
| Identity and serial sequence declarations | Modeled and compared, but not converged. An identity whose sequence is declared away from the defaults (START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CACHE, CYCLE) exports with its options clause, and a file that states the live declaration plans its other changes normally; any difference between live and desired is a typed diff refusal. Change the declaration with owner tooling (ALTER TABLE ... ALTER COLUMN ... SET ...), then state it in the file. A serial cannot state a declaration, so export refuses a serial whose sequence is declared otherwise (ErrUnrenderableSequenceOptions), including an integer serial whose column was widened to bigint while its sequence stayed integer. That refusal is export's alone: a policy-only row-security apply goes ahead on such a table, because it renders nothing from the table. A serial's declaration is not compared either, so a file that writes the serial shorthand plans no change against a live serial whose sequence was tuned; the tuning stays as it is. Widening an identity column's type carries its sequence along, so a file that widens the column and states the declaration converges in one plan. A column whose default calls more than one sequence it owns is refused at render (ErrMultipleOwnedSequences), not at introspection, so planning other changes on the table still works. |
| Column collations | An explicit COLLATE on a column is not managed: converging a collation delta rewrites the column and its indexes. Export refuses a collated column — a baseline without the clause would silently change sort order and index semantics — and a collation delta (including on an added column) is a typed diff refusal. |
| Non-table objects | Views, materialized views, standalone sequences, enums, domains, extensions, functions, and triggers are outside the model. A serial column's owned sequence is the one exception: it round-trips through the serial pseudo-types — and ownership is verified through the catalog (pg_depend), so a hand-written nextval default on a standalone sequence that merely carries the serial-style name refuses rather than exporting as serial and silently privatizing a shared sequence. A column, check constraint, or index may use an unmanaged type or function (an enum, a domain, a SQL function) that the table's own schema or public defines. Planning resolves names in the order live introspection does, the table's schema and then public, so the definition round-trips whether the file names the object bare or qualified with its schema. The object's own definition is not managed. |
| Multiple tables per file | A desired file is single-table scoped: exactly one CREATE TABLE plus CREATE INDEX statements on it. Multi-table schemas are managed as one file per table. |
| Live tables with no desired file | Not in diff's view: the diff is single-table scoped, so it never plans or executes a DROP TABLE, and the imperative front door refuses the statement as unsupported. The set of tables a schema should contain is the whole-schema owner's to enumerate and reconcile — see Deliberately operator-owned for the catalog query and its exclusions. |
| Greenfield table creation | Reachable through desired-state execution (migrate --desired or migrate.RunDesired): a plan whose table does not exist routes to the executor create path (executor.ExecuteCreate). Planning refuses every clause that binds to an existing object the absence proof does not cover (PARTITION OF, INHERITS, LIKE, OF, and REFERENCES), plus IF NOT EXISTS and duplicate claimed relation names. Apply re-checks the same shape rules before anything executes. CONCURRENTLY is refused upstream at desired-file parse (statement.ParseDesired); the create path's admission re-checks it as defense in depth. |
| Changed stored generated column expression | Planned, never executed. diff plans the change as one ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION AS (...) statement of kind set-generated-expression, refused as unsupported-statement: it recomputes every row under an ACCESS EXCLUSIVE lock, and no online path exists yet. Planning it this way, rather than as a drop and re-add of the column, leaves indexes on the column out of the plan. SET EXPRESSION needs PostgreSQL 17 or later; on older servers the plan still shows it, refused, and the only path is to drop and re-add the column, which also drops its indexes. A change to a generated column's type still fails the diff. |
| Virtual generated columns (PostgreSQL 18+) | Not modeled. A desired file cannot declare VIRTUAL, export refuses a live virtual column (ErrUnrenderableVirtual), and a desired file that declares the column any other way fails the diff, so a virtual expression is never converged as a column default. |
| Changed index or constraint definition | A redefinition diffs to drop-and-recreate, the drop is destructive, and desired-state execution refuses any plan containing a destructive statement — the whole plan, including the harmless recreate. Run the drop deliberately first (DROP INDEX CONCURRENTLY directly against the database; ALTER TABLE ... DROP CONSTRAINT through the imperative front door with --allow-destructive), then rerun — the remaining plan converges the recreate. One shape is not a redefinition: a new primary key or unique constraint whose name and index match a live unique index takes that index over with ADD CONSTRAINT ... USING INDEX, so the table keeps its guarantee and its index throughout. A primary key's columns are set NOT NULL online first. The statement names only the index, so execution reads the index again under the table's lock and refuses with adopted-index-drift, attaching nothing, if it was dropped, replaced under the same name, or left invalid since planning. A deferrable constraint takes the index over with its declared deferrability. An unfinished index build and a partitioned parent keep the drop and rebuild. A live constraint left NOT VALID with the desired definition is not a redefinition either: it plans as one VALIDATE CONSTRAINT, which a foreign key's plan shows refused with declarative-foreign-key. The statement names only the constraint, so execution reads it again under the validation's own lock and refuses with validated-constraint-drift, validating nothing, if it was dropped or replaced under the same name since planning. |
Desired-state execution (migrate --desired or migrate.RunDesired) feeds
every planned statement back through the same gates as the imperative
front door, so the outcome of an ordinary desired-file edit is the
composition of the model boundaries above with those gates. At a glance:
| Desired-file edit | Outcome today |
|---|---|
| A desired file whose table does not exist yet | Converges — the greenfield create path verifies the table name and every relation name the desired file states (explicit index names and first-choice constraint-index and column-sequence names) are free and the role holds CREATE on the schema, then runs the CREATE TABLE and index builds as brief bounded steps. Names the server invents rather than names the desired file states are outside this coverage. An occupied claimed name is a typed create-collision refusal before anything runs — 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. 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 (a read of the owned names that does not complete is the same step-1 failure as create-names-unverified, never a pass). Unsupported create shapes (PARTITION OF, INHERITS, LIKE, OF, IF NOT EXISTS) and duplicate claimed names refuse at plan time and are re-checked at apply. |
| Add a column | Converges. Runs as a bounded attempt of the submitted form, so the table-size guard applies (below). |
Widen a column type (varchar(50) → varchar(255)) |
Converges — the same bounded attempt, under the same size guard. |
| Add an index | Converges via CREATE INDEX CONCURRENTLY. Not size-guarded: long online work on a large table is the pattern's purpose. |
| Rebuild an index left invalid by an unfinished concurrent build | Plans as the same create-index change — the invalid entry, though it carries the desired name and definition, does not deliver the index, and the diff never emits a drop for it. Execution then meets the occupied name and stops by proof: a failed outcome carrying invalid-index-abandoned (the invalid-index row above); remove the leftover per the runbook, or, as a library caller, run the create through executor.RebuildAbandonedIndex, and rerun. A build still in progress looks the same to the diff (indisvalid stays false until the build's final wait ends) and plans the same create; execution then stops with invalid-index-build-in-flight rather than build twice. |
| Remove or redefine an index that is currently invalid | The diff leaves the invalid entry alone: it never emits a drop for an entry that may be someone's build still running, so diff shows no change for a removed index and only the create-index for a redefined one (execution then meets the occupied name as in the row above). This also means the _ccnew leftover of an interrupted REINDEX ... CONCURRENTLY, which no desired file names, does not block the table's other changes; clear it per the runbook. On a partitioned parent, where an invalid index means unattached partition indexes rather than an unfinished build, indexes are compared by name and definition alone. |
Add a constraint (UNIQUE, CHECK); SET NOT NULL |
Converges via the safer online sequence; not size-guarded either. |
Relax a NOT NULL |
Refused, whole plan: dropping NOT NULL discards the same guarantee its constraint form would, so it is destructive. Run it deliberately through the imperative front door — it executes natively there once --allow-destructive names the table — then rerun. |
| Change an index or constraint definition | Refused, whole plan — the drop-and-recreate row above. |
Narrow a column type (varchar(255) → varchar(50)) |
Converges through copy-and-swap: the plan's disposition is execute with the statement routed to the copy-and-swap backend, and desired-state execution runs it with the same preflight gates and flags as migrate --alter (the Copy-and-swap row above). The route's preflight is read at admission, before any statement runs, so a table or server the route refuses — no --free-disk, no logical decoding without --block-writes SCHEMA.TABLE, a trigger, a dependent view — refuses the whole plan with nothing executed, native statements included. The dry run and diff do not read the preflight — the plan report says execute on the route alone, and whether the route can run here, now, with these flags, is answered when the plan is executed. A plan that retypes two columns runs two copy-and-swap passes on the table, one per statement; the second replaces the done row the first leaves behind. |
| A bounded-attempt edit on a table above the size threshold | Refused at that statement (not-native-safe-table-too-large): with the default 1 GiB threshold, adding a column to a larger table refuses until the threshold is raised. |
A destructive refusal is all-or-nothing: one destructive statement refuses the whole plan, and the non-destructive statements beside it do not run — the refusal detail says how many were skipped.
The size threshold is policy, not capability: Options.MaxTableSizeBytes
(the CLI's --max-table-size) defaults to 1 GiB, and the guard covers only
the blind bounded attempt of a submitted form — planner-proven online
sequences are exempt. On a table you operate deliberately, raising the
threshold is the sanctioned way to converge the bounded-attempt edits; the
refusal means pg-sprite cannot prove the change is instant at that size,
not that the change is unsafe. Why the guard covers exactly that path —
and why the wrong-guess cost scales with table size — is
optimistic-attempt.md.