Skip to content

postgresql.md

Latest commit

 

History

History
831 lines (731 loc) · 53.4 KB

postgresql.md

File metadata and controls

831 lines (731 loc) · 53.4 KB
Raw

PostgreSQL

Table of Contents

Per-target schema_overrides can map one canonical namespace directory to a physical PostgreSQL schema. This keeps plans, tasks, and pull output canonical when physical schema names differ between targets. See Per-Target Schema Overrides for configuration and identifier rules.

SchemaBot can plan declarative PostgreSQL schemas and execute a deliberately narrow set of changes through the pg-sprite engine. This page describes the current support boundary. A change outside that boundary is not routed to a less safe executor: its plan is blocked.

PostgreSQL target support is separate from using PostgreSQL for SchemaBot's own state. See SchemaBot storage for that distinction.

Schema pull

schemabot pull prints the pulled PostgreSQL schema as readable SQL, or as the API response when JSON output is selected. A PostgreSQL schema is the SchemaBot namespace. With no namespace selection, pull discovers every schema that is not a server or known extension schema. That discovery is best-effort: an extension whose schema the reserved list does not name is pulled as if it were an application schema, so select the schema explicitly with --namespace when the database hosts extensions. Only ordinary and partitioned tables are exported; views, materialized views, and foreign tables are not, and partition children are not exported separately from their parent.

The pull is refused in full if any selected table cannot round-trip through the declarative format. This includes partitioned parents, foreign-key relationships, unlogged tables, explicit collations, and sequence defaults that cannot be represented safely, as well as tables carrying objects the format does not model at all: triggers, table and column comments, non-default relation options, and table inheritance. If any selected table is refused, no partial output is produced. Only basic catalog detail is supported on PostgreSQL; a request for detailed catalog output is rejected rather than answered with the basic shape.

schemabot pull --lint audits each pulled table for the schema-shape rules that have a PostgreSQL analog: a missing primary key or a key column whose type is not bigint or uuid, real / double precision / float(n) columns, a quoted table name that is not lowercase, and a btree index that another index or the primary key already covers. Every finding is a warning, and a clean audit returns an empty list for the namespace. Each pulled entry is audited as the create set the renderer emits: CREATE TABLE and CREATE INDEX statements, with validated RLS declarations when present. The audit checks table and index shape, not access policy correctness. Unsupported declarations fail the request rather than producing a partial audit. The rules and the MySQL-only rules they leave out are in lint and safety levels.

Row-level security

Pull includes row-level security (RLS) settings, policies, and policy comments when a table defines them. Tables without RLS settings or policies keep their ordinary table and index definitions; no extra flag is needed. A disabled table with policies still exports those policies and an explicit DISABLE setting.

A pulled RLS definition can be checked back into its namespace directory and planned. An unchanged definition produces no changes. Changes to policies, policy comments, or RLS settings become one reviewed operation per table. The existing unsafe-change consent gate applies because these changes affect who can access rows. No additional RLS flag is needed.

At execution, pg-sprite locks the table, regenerates the ordered SQL, and checks it against the reviewed operation before executing any of it. The entire policy replacement commits in one transaction, including settings and comments. A mismatch refuses execution and requires a new plan. This binds the SQL to execute, not every prior policy predicate: if concurrent edits still produce exactly the reviewed SQL, that SQL remains authorized.

RLS drift comparison retains the physical schema qualifier. Dispatching a reviewed RLS operation to a differently named physical schema requires a fresh plan and review on that target; ordinary table namespace mapping does not authorize this RLS remapping. The executor still verifies the exact ordered SQL under its lock.

Unchanged RLS definitions use a catalog comparison without an exclusive target lock or an ownership requirement. Planning a change and executing it take a bounded exclusive table lock. Keep policy updates small and expect a busy table to refuse rather than wait indefinitely. Creating a new table with an RLS declaration and combining structural changes with managed RLS remain unsupported. Direct RLS apply requests without the stored desired schema files are refused.

Existing table-only files remain supported on tables with live RLS. They manage columns and indexes without changing policies or RLS settings; older rollback baselines keep this behavior too. Omitting RLS clauses does not disable RLS or drop policies. Explicit RLS declarations use the atomic path above, which refuses mixed structural and RLS changes. A no-change result for a table-only file says nothing about whether its access policies match.

New plans for a namespace containing managed RLS tables remain rollback-incapable until complete RLS definitions can be applied during recovery. Pull still exports the full definition; rollback capture never strips access policies to claim support.

For example, an existing table can pull as:

CREATE TABLE "documents" (
    "id" bigint NOT NULL,
    "owner_id" bigint NOT NULL,
    CONSTRAINT "documents_pkey" PRIMARY KEY (id)
);

ALTER TABLE "documents" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "documents" NO FORCE ROW LEVEL SECURITY;

CREATE POLICY "readers" ON "documents"
    AS PERMISSIVE FOR SELECT TO PUBLIC
    USING ((owner_id = 1));

Planning this file against that same live definition returns NoChanges: true. Editing the policy to USING (true) produces an unsafe policy replacement for review. Applying it broadens access to every row allowed by the remaining policies and grants; review that access change before consenting.

RLS comparison and rendering use pg-sprite's validated model. Policy roles and qualified helper functions must already exist. Unsupported policy dependencies still refuse the entire pull rather than returning an incomplete definition. pull --lint validates the declaration and audits table and index shape; it does not judge whether a policy grants the right access.

Supported changes

The apply path normally executes a plan one statement at a time. Each statement runs as its own task and its own PostgreSQL transaction. An RLS replacement runs as one task and one atomic transaction, as described above. A second exception is a greenfield create set: its table and indexes run as one task through a pg-sprite sequence, with each sequence step in its own bounded transaction. A failed set leaves its committed prefix on the target, and the next plan reconciles that live catalog. Every statement must satisfy all of these conditions:

  • pg-sprite classifies the statement for native execution.
  • The statement is a kind the shape check admits: an ALTER TABLE shape accepted by pg-sprite's privilege-tier check, a greenfield CREATE TABLE (see Greenfield tables), or a CREATE INDEX (see Index builds). Statement kinds outside that set fail closed.
  • For a change to an existing table, the target is an ordinary or partitioned table. Native ALTER TABLE and blocking CREATE INDEX statements also require its measured on-disk size to be no more than the configured native-apply ceiling (1 GiB by default; see postgres.native_safe_table_size_limit_bytes in configuration). For a partitioned parent, the measurement includes its complete partition tree. The ceiling bounds work that scales with existing data, so it does not apply to a CREATE TABLE or CREATE INDEX CONCURRENTLY.
  • The target role passes the privilege preflight for the planned statement. A greenfield create is checked against the schema — the role needs CREATE on the target schema — because no table exists to state facts about.
  • A CREATE INDEX CONCURRENTLY build is bounded by postgres.concurrent_index_max_duration, which defaults to 24 hours, set as the build's server-side statement_timeout. The bound starts when the build starts; the session setup before it runs under a separate headroom sized for setup as slow as the server's own limits allow, so the build gets the full configured bound. Setup that outruns the headroom shortens the build by the excess, since the apply as a whole still ends at the bound plus the headroom. The catalog reads pg-sprite runs in the build's own session ahead of the build share its statement_timeout, so the bound ends one of those too and the failure names the option all the same.

The common supported case is a metadata-only ALTER TABLE, such as adding a nullable column:

ALTER TABLE public.users ADD COLUMN email text;

The planner, rather than a fixed list in SchemaBot, decides whether a particular ALTER TABLE can use the native path. Review the plan result for the decision on the exact target schema. Even a native-routed statement is bounded at apply time: each attempt has a 3-second lock budget and a 30-second statement budget, with bounded retries for lock contention. A statement that exceeds the execution budget is not allowed to continue unbounded.

While a statement runs, PostgreSQL progress metadata carries two phase vocabularies in one flat map. phase is SchemaBot's own record of the apply: preflight for as long as the apply runs, then its terminal outcome (completed, failed, or refused). server_phase is the phase column of PostgreSQL's pg_stat_progress_create_index view, verbatim (for example building index: scanning table or index validation: scanning index), and is present only while PostgreSQL publishes a progress row for a concurrent index build. The metadata also carries step, steps_total, and the sanitized statement for the statement in flight; executor_operation, pg-sprite's class for the step (admitting, optimistic, brief, validate-constraint, concurrent-index-build); and attempt. Whenever a progress row is published, all six counters — blocks_done, blocks_total, tuples_done, tuples_total, lockers_done, lockers_total — are present, zeros included: a zero is a reading, and an absent key means PostgreSQL has not published a row. PostgreSQL scopes block and tuple counters to phases, but a completed phase's values can persist into the next phase. The completed heap scan's block counters remain visible while live tuples are sorted, so that sorting phase's zero-width band absorbs the carry-over and reports its start.

Table progress during a concurrent index build is a whole-build estimate, not a phase ratio. Each server phase owns a fixed band of the 0–100 scale in PostgreSQL's documented phase order, a phase with block or tuple counters interpolates within its band, and a waiting or sorting phase reports the band's start. The estimate stays below 100 until the apply completes and never moves backwards within a build; a poll that finds no progress row — between phases, or while the view cannot be read — keeps the last derived percent.

A multi-statement plan — several tables changed, or one declarative edit that the planner expands into several steps — is not applied atomically. The statements execute in order, each committing or failing in its own transaction, so a mid-plan failure leaves the earlier statements committed and the schema partially changed. Keep a PostgreSQL change to a single statement when a partially applied plan would be unsafe to leave in place.

Index builds

A CREATE INDEX against an existing table plans as a native statement. The choice between a blocking and a concurrent build is pg-sprite's, not the author's: its planner constructs CREATE INDEX CONCURRENTLY as the safer form of a plain CREATE INDEX, and the plan surfaces that concurrent form for review. A statement already written with CONCURRENTLY surfaces as authored. The apply runs the reviewed build through pg-sprite's dedicated concurrent index-build executor — outside a transaction block, with lock_timeout disabled and statement_timeout set to postgres.concurrent_index_max_duration (24 hours unless configured), so the server's own timer is what ends an over-long build — and reports completion only once the catalog shows the index valid. A build that fails part-way leaves an invalid index; the stored failure names it and is retryable, and when the bound is what ended the build it names the option to raise. A build that runs past the bound and provably leaves nothing behind is refused permanently, naming the option, because an identical retry would spend the same bound again. The native-apply size ceiling does not gate this path: the build is bounded by postgres.concurrent_index_max_duration instead, and a build the bound ended or an invalid-index outcome is reported as such rather than as a table-size verdict. Losing the driver pod mid-build does not stop the statement: the server keeps building until it finishes, fails, or its statement_timeout ends it, so the recovery re-plan on the next drive meets that build's outcome — a valid index it has nothing left to build, a build still in flight (the apply fails retryable, naming the index and the backend building it, until that build ends), or a failed build's invalid leftover. The next drive recovers that leftover itself: when the build finds an invalid index under the requested name, or quarantined on the table by an interrupted recovery, that pg-sprite can prove abandoned — on the target table, with no backend building it, or with a builder the engine role cannot see through the progress view — it runs pg-sprite's recovery, which removes the entry under a lock-held proof of abandonment and then builds, so a change interrupted mid-build converges without an operator dropping anything. The recovery runs as one caller-owned envelope — proof, drops, then the build — under the engine's own deadline of the same configured bound, with no server-side statement timeout, since a served build would spend the bound once on the drops and again on the build. The lock is the proof: a build still holding the table, visible or not, stops the recovery at its lock budget and the apply fails retryable, still naming the index. A recovery that cannot prove the entry unchanged through to its removal fails retryable the same way; one that finds the connection pool a session short is refused permanently, since a retry sees the same pool. A concurrent build against a partitioned parent is refused permanently, because PostgreSQL cannot build parent-level indexes concurrently.

Indexes declared together with a new table take a different path: they run as plain, non-concurrent steps inside the greenfield create set, because the table has no readers yet (see Greenfield tables).

Control operations

Cancel is supported for an existing table's concurrent index build. Plain DDL is never cancellable: each statement commits or fails on its own, so a cancel of one is declined as a typed unsupported operation and the durable request resolves terminally.

A cancel of a running build signals the build's backend through the apply's pg-sprite progress tracker. When that signal cannot reach a running statement — the build has not started, has already returned, or its backend cannot be observed or signalled by the engine role — the engine cancels the apply's own context instead, which ends whatever statement the apply is on. Membership in pg_signal_backend lets the signal land on the build backend directly; without it the cancel still takes effect through the apply's context.

The cancel is answered from the outcome the apply settles on, never from the signal alone. When a cancelled initial or recovery build leaves its own invalid index, the drive removes it through pg-sprite's abandonment proof before settling the apply. That cleanup is detached from the already-cancelled apply context and runs under its own bound of seconds, kept well inside the cancel's settle wait so that a slow cleanup cannot turn a cancel that is about to succeed into a not-settled answer; it cannot spend another full build bound. The summary reports what pg-sprite's report proves: the index was removed only when an entry was dropped; an entry the server refuses to drop concurrently is named under its quarantine name for an operator to remove; an entry already gone is reported as such. Cleanup failure does not change the terminal cancelled outcome. When pg-sprite exposes the leftover's identity, the summary and server log name it; otherwise the summary names both the original and quarantine-name possibilities and gives the catalog query needed to identify it. The terminal apply does not retry cleanup: the entry remains until an operator follows the invalid-index recovery guidance. The full cleanup error stays in server logs. A signal that races the build's completion may find the index already valid: that apply is reported as already completed, not cancelled, and the durable request reconciles to the completed outcome. A cancel that arrives after the apply has already failed is accepted over the failure, carrying the failure detail. A cancel whose build has not settled within the engine's wait is reported as still running, and the next attempt waits for it again.

On shutdown the engine halts its running concurrent index builds the same way — by cancelling their contexts — so the process does not exit with a build running on the target under a lease nobody renews. A halt is not an operator cancel: the apply records the build's failure, stays active, and the next driver to claim it recovers the leftover index before its own build. Plain DDL is left to finish; a halt whose budget expires while such a statement is still running reports that the target may still be held.

Stop remains unsupported. A concurrent build has no resumable midpoint, so a stop would be a permanent cancel under a misleading name; non-concurrent statements likewise have no engine phase to pause.

Greenfield tables

A CREATE TABLE for a table that does not exist on the target plans as a native statement and executes through pg-sprite's create path. The two facts the create depends on are proved in the session that executes, not at plan time, because absence or privilege at plan time proves nothing about apply time: the role holds CREATE on the target schema, and the name is free. A target configured with table_owner runs each create step with SET LOCAL ROLE, so the owner's default privileges apply to the table and its serial sequences. The connected engine role needs membership in that role (GRANT app_owner TO engine), and the owner needs USAGE and CREATE on the schema. Without table_owner, the connected role owns new tables as before. The owner is checked at plan time as the role the create will run as: missing membership blocks the create step with the GRANT that provides it, and an owner the target has no role for blocks it with no grant to print.

table_owner is one role per target, deliberately scalar. A per-namespace map mirroring schema_overrides is a later extension for a target whose schemas have different owners; no such target exists today.

A name needed by the create set that is already occupied is a permanent refusal. The occupant is a relation of any kind — table, view, index, or sequence — or a standalone type (an enum, domain, range, or shell type; every table owns a composite type of its name, so these collide too). Re-plan first: a lost race for the table name resolves on the next plan, which sees the occupant and diffs against it. A collision that recurs after a re-plan is a schema-file problem — an explicitly named constraint or index claims a name another relation holds (an unnamed constraint picks a free index name on its own), or a serial column's auto-named sequence lands on a standalone type of that name — so drop or rename the occupant, or give the constraint, index, or sequence another name. A target schema that does not exist is also a permanent refusal.

The create path refuses shapes whose outcome it cannot prove, each permanently: IF NOT EXISTS (its no-op outcome cannot be proven), CREATE TABLE ... PARTITION OF attaching a partition to a live parent, and a statement that claims the same relation name twice.

A schema file may declare a new table together with CREATE INDEX statements on that table. SchemaBot plans and applies this greenfield create set as one change and one task. pg-sprite executes the table and indexes as an ordered sequence; because the table has no readers yet, index steps use plain non-CONCURRENTLY builds. Each step commits in its own bounded transaction, so a failure can leave the table and earlier indexes committed. Such a failure is never retried as the same set: its detail names the failed step and the committed table, then ends on the refusal's own remedy, or on a re-plan to reconcile that live prefix when the refusal has none.

Partitioned tables

The target preflight accepts ordinary tables, leaf partitions, and partitioned parents. pg-sprite also defines specific refusals for parent-level index builds, index adoption, and NOT VALID foreign keys on PostgreSQL versions before 18. The SchemaBot PostgreSQL apply path's table-change ladder invokes that partition-specific admission check immediately before execution; a CREATE TABLE returns from the optimistic path before the ladder and does not reach it. Unsupported changes to a partitioned parent therefore fail with pg-sprite's typed refusal rather than a raw server error; they remain outside the supported SchemaBot envelope even if a plan is otherwise shown as native.

Blocked plans

SchemaBot blocks every planner verdict that is not a native execution verdict. This includes changes that require copy-and-swap, require a rewrite before they can be planned safely, are refused by pg-sprite, or carry an unrecognized planner contract or verdict. A native verdict is also blocked when its statement shape is outside the ALTER TABLE, CREATE TABLE, and CREATE INDEX set accepted by the apply path's shape check. The admitted set is defined by the pinned pg-sprite version, not by SchemaBot: when a pg-sprite upgrade widens the shapes its engine executes, SchemaBot's plan and apply gates widen with the pin, with no SchemaBot code change. That shape check admits CREATE INDEX, so an index build against an existing table is not blocked at plan time; it executes as described in Index builds. It is blocked only when pg-sprite could not construct the concurrent form of a plain CREATE INDEX, since running the submitted form would falsify the plan's own verdict.

The plan comment lists the table and SchemaBot's reason for the refusal. When the create path refuses a statement's shape, that reason names the offending clause and how to remove or work around it. For a PostgreSQL plan, the plan Check Run concludes unsuccessfully, and an apply attempt is rejected before any apply or task is queued. Rewrite the declarative change into an eligible form or use a separately reviewed operational process; flags do not override an engine-blocked verdict.

The engine does not execute DROP TABLE or other statement kinds outside its admitted set. That includes tables that exist on the target but that no schema file in the namespace declares. The common case is onboarding an existing database, where every table nobody has written a file for yet is undeclared; the same happens when a file is deleted from the PR, or when an imperative tool leaves a table behind during coexistence. A namespace is owned whole: the schema files declare the set of tables, not just each table's shape, so the only convergence for a table with no file is a drop. The planner surfaces each undeclared table as a blocked, destructive DROP TABLE change: the engine will never run the drop, so the one choice left is whether to report the divergence, and hiding it would turn a target that does not match its declaration into a passing check. The plan and the schema pull enumerate the namespace's tables through the same pg-sprite query, so a pulled baseline cannot leave behind a table that the next plan then blocks on. Deleting every schema file in one namespace keeps that namespace in the plan and surfaces every live table it contains as one of these blocked drops. Because the check fails for the whole database while any change is blocked, an undeclared table holds up every other change to that database until it is resolved — including changes in other namespaces, since every namespace at the PR head is reconciled on every plan. Plan an onboarding accordingly: declare the existing tables first, or list namespaces that stay outside SchemaBot under ignore_namespaces.

Two remedies clear it. Declare the table in a schema file to bring it under management — the file need only match the live table for the drop to disappear from the plan. Or drop the table through a separately reviewed operational process. schemabot pull and schemabot onboard write the files for the tables the plan holds accountable, so onboarding an existing database is a pull, not hand-writing every file; the pull refuses a namespace as a whole when any of its tables carries objects the format cannot represent (triggers, table or column comments, relation options, inheritance, partitioning, or foreign keys on either side), and those tables need a hand-written file or a reviewed drop before the rest can be pulled. CREATE UNLOGGED TABLE, PARTITION BY and a child of INHERITS flattened to its own columns are all accepted in a hand-written file.

Foreign keys are planned but not applied. A file may declare a foreign key whose referenced table is unqualified and already exists on the target in the same namespace; a file that creates the referenced table in the same plan does not count, so leave the foreign key out of the file, apply the referenced table first, then run the ADD CONSTRAINT outside SchemaBot and add the foreign key back to the file. A declared foreign key that matches the live one plans no change. One the file adds plans as an ADD CONSTRAINT that names the referenced table with its namespace, and a live one the file omits plans as a destructive DROP CONSTRAINT; both are shown for review and blocked, because SchemaBot does not apply foreign-key changes on PostgreSQL yet. A blocked step blocks the whole plan. A dropped foreign key is usually one the file left out: declare it in the file to keep it, which plans no change, or run the DROP CONSTRAINT outside SchemaBot and re-plan to remove it. A foreign key into another namespace cannot be declared, so when the table carries one, the blocked step names it and the plan stays blocked until it is removed. An added one may sit on a column the same plan adds, so leave it out of the file to apply the rest of the plan first, then run the ADD CONSTRAINT outside SchemaBot and add the foreign key back to the file. A new table that declares a foreign key is blocked as a whole: leave the foreign key out of the file to create the table, then add it the same way. Planning copies each referenced table's shape, so the planning role needs SELECT on it; it never blocks writes to it.

The pull refuses to render a table with a foreign key on either side, because it would describe the table incompletely, so such a table needs a hand-written file. One that owns foreign keys is declared with them; one that other tables reference is declared without them, since the constraints live on the referencing tables, not in its own definition. A table that owns a foreign key into another namespace cannot be declared at all: remove that foreign key or drop the table through a separately reviewed process. A drop of a referenced table has to take the referencing constraints with it. The plan names the constraints on both sides, so both sides of one relationship explain themselves.

Archive tables named <table>_archive_YYYY[_MM[_DD]] with no schema file are exempt from the verdict, as they are in the MySQL engine's view of a live schema: an archive is a retired copy kept outside declarative schema files. An archive that a file explicitly declares is managed like any other declared table. That naming convention is the only naming-based per-table exemption — a leading underscore means nothing on PostgreSQL. ignore_tables entries, exact names and patterns, also withhold live tables; a file declaring an ignored table is refused as a contradiction. ignore_namespaces is the per-namespace exemption. The plan discloses the tables it exempted, by namespace, in the PR comment and in schemabot plan and schemabot apply output, so a reviewer can tell an archive the verdict skipped from a table it found declared. The underlying table set is pg-sprite's own view of which tables its declarative model manages; SchemaBot adds only that naming policy exemption. Tables whose definition lives elsewhere are not enumerated: a partition is declared through its parent's PARTITION BY and follows the parent's verdict wherever the parent lives, and extension-owned tables (such as PostGIS's spatial_ref_sys) belong to their extension — no file can declare them and the server refuses to drop them. An inheritance child and an unlogged table are ordinary tables with definitions of their own, and each needs its own file.

The enumeration covers ordinary and partitioned tables only. Undeclared views, materialized views, and foreign tables are outside the declarative format and are not reported. Because namespaces are derived from the schema files present in the PR, deleting the last file of a namespace removes that namespace from the plan altogether: its tables are not enumerated and the plan reports no change for it, so remove a namespace's final table through a separately reviewed process rather than by deleting its file.

SchemaBot's own catalog reads — the undeclared-table enumeration and the schema pull — name every catalog relation, function and operator with an explicit pg_catalog. qualification, so a target whose search_path lists a user schema ahead of pg_catalog cannot shadow the catalog and turn either into a silently wrong answer. pg-sprite's introspection pins its own transaction-local search_path; its preflight reads run on the shared connection pool with the session's search_path and are the library's surface.

Apply-time refusals

Planning establishes the statement shape. Apply time re-checks facts that can change or that depend on the target:

  • A table larger than the configured ceiling is refused permanently for native ALTER TABLE and blocking CREATE INDEX. This is SchemaBot's native-apply ceiling, not a PostgreSQL limit. It defaults to 1 GiB and is set with postgres.native_safe_table_size_limit_bytes; see configuration for the trade-offs of raising it. The ceiling gates work whose cost scales with an existing table. A CREATE TABLE is exempt, and CREATE INDEX CONCURRENTLY is admitted under the postgres.concurrent_index_max_duration bound instead; a build the bound ended and invalid-index outcomes are reported as such rather than as a size verdict.
  • For a change to an existing table: a missing target, or an object that is not an ordinary or partitioned table, is refused permanently.
  • For a CREATE TABLE: a relation of any kind already occupying the name, a missing target schema, a duplicate name inside the statement, IF NOT EXISTS, and PARTITION OF against a live parent are each refused permanently, with a reason directing a fix or a re-plan.
  • After a CREATE TABLE commits, pg-sprite reads back the constraint-index and sequence names the table owns. A table that owns a suffixed name in place of one the schema file claims — an occupant took the first choice between the catalog probe and the statement — is refused permanently. The reason names as many of the missing and owned names as it has room for beside the table name and counts the rest, so the remedy stays visible on the narrowest CLI surface; the table is left standing for the operator to rename the relation or drop, then re-plan. A read-back that does not complete is also refused permanently, even though pg-sprite marks it retryable: SchemaBot's retry re-runs the whole plan, whose CREATE TABLE has already committed, so it could only collide with the table this apply created. The reason directs the operator to compare the table's names against the schema file, then re-plan.
  • Insufficient privileges are refused permanently before DDL runs. The stored failure includes the provisioning GRANT derived by pg-sprite.
  • Exhausting the 30-second statement budget is a permanent native-safety refusal. Exhausting the lock budget is retryable after contention clears.
  • Every apply other than a concurrent index build runs under a fixed five-minute client-side ceiling. It is not configurable: it exists to guarantee a terminal progress state when a dial hangs or a connection is black-holed, not to bound legitimate work, which the statement and lock budgets above already bound. An apply the ceiling ends is recorded as a retryable operational failure.
  • A concurrent index build runs under the postgres.concurrent_index_max_duration bound (24 hours by default), set as the build's server-side statement_timeout, rather than that fixed ceiling. A build that finds an invalid index already under the requested name or quarantined on the table that pg-sprite proves abandoned — a failed build's leftover on the target table with no backend building it, one whose builder the engine role cannot observe, or quarantine debris an interrupted recovery left — recovers it inside the same apply: the proven entry is removed and the index built inside one caller-owned envelope under the engine's own deadline of the same bound. A build that leaves an invalid index behind — including one the bound's timer cancelled — or finds one another backend is visibly still building fails as a retryable operational failure naming the index and the next step; the invalid index, not the cause that produced it, is the outcome the retry acts on, and the next drive recovers the leftover as abandoned. When the bound is what cancelled the build, the detail also names postgres.concurrent_index_max_duration, since the retry rebuilds under the same bound and the operator's step is to raise it first. A recovery that fails before its build — the proof lock lost to a build still holding the table, or the entry changed between two verification points — fails retryable with the same naming; one that finds the pool a session short is refused permanently, as below. An invalid index under the requested name that this change can never clear — one on a different table, or one that backs a constraint or belongs to a partitioned table — is refused permanently with the same naming, as is a build that ran past the bound and provably left nothing, whose refusal names postgres.concurrent_index_max_duration as the option to raise. A parent-level index build on a partitioned table is refused permanently, and so is a target connection pool too small to hold the build's sessions at once — the recovery holds one more session than the build, so a pool sized to the build alone refuses the recovery — since the pool is sized by the target DSN and a retry sees the same pool.
  • Other operational failures are recorded as retryable when no create-set prefix committed and expose a sanitized message; connection and server details remain in server logs. A create-set failure after the table commits is not retryable and directs the operator to re-plan against the live schema.

A permanent refusal before execution leaves the target unchanged. A refusal from a later create-set step can leave its earlier steps committed; it is never retried as the stale set, and its detail ends on the refusal's own remedy, falling back to a re-plan only when the refusal carries none.

Unsupported workflow features

PostgreSQL targets do not support:

  • pending drops;
  • deferred cutover (--defer-cutover);
  • stop, start, cutover, revert, skip-revert, or volume controls;
  • cancel of anything other than a concurrent index build (see Control operations).

Each PostgreSQL DDL statement on this path runs as its own transaction that commits or fails. A stop request, or a cancel of plain DDL, is stored through the normal durable control path, but the engine resolves it as a typed, terminal unsupported-operation decline; it does not report a successful stop or keep retrying the request. The apply continues to its own completion or failure. If an in-flight plain DDL statement must be interrupted, find its backend in pg_stat_activity and use PostgreSQL's pg_cancel_backend through an operator-controlled database session; the apply records that as a failure the next drive retries.

Pending drops is a MySQL/Spirit quarantine and cleaner. PostgreSQL plans do not gain that behavior from enabling the server-level pending_drops block. PostgreSQL DDL also has no deferred table-swap phase, so deferred cutover is rejected before an apply is queued.

Engine capability mapping

The PostgreSQL engine implements the required engine.Engine interface and only the optional capabilities listed as implemented below. This table maps the interface contract to the behavior described in Control operations, Unsupported workflow features, and Failure and recovery. TestOptionalCapabilitySet in pkg/engine/postgres/postgres_test.go records one verdict per optional interface and fails when package engine declares one this table does not classify; TestCapabilityMappingMatchesEngine beside it reads this table and fails when an optional interface's row states a verdict the engine does not hold, or when an engine.Engine method has no row. The text of the Why and Where columns is not checked by any test.

A declined operation is refused at one of two layers, and the table says which. Typed decline at drive time means the request is durably recorded and acknowledged by the API, then resolved terminally by the driver when the engine returns a typed unsupported-operation error; the operator sees the decline in the apply's control history. Refused at intake means the API rejects the request before any engine is consulted, with a 409 naming the engine the operation belongs to; nothing durable is recorded and the engine's own decline exists only to satisfy the interface, because the driver calls it solely from a state this engine never reports.

Interface / method PostgreSQL behavior Why Where
engine.Engine.Name Implemented Selects the engine with the stable postgres identifier. pkg/engine/postgres/postgres.go
engine.Engine.Plan Implemented Plans supported PostgreSQL DDL against the live catalog and blocks statements without a safe native route. pkg/engine/postgres/postgres.go
engine.Engine.Apply Implemented Starts bounded native DDL or a greenfield create sequence in this process; a concurrent index build runs outside a transaction as PostgreSQL requires. pkg/engine/postgres/apply.go
engine.Engine.Progress Implemented Reads the apply record held by this engine instance and augments a running concurrent index build from PostgreSQL's progress view. pkg/engine/postgres/apply.go
engine.Engine.Stop Typed decline at drive time A concurrent index build has no resumable midpoint, while other statements commit or fail on their own; the durable request resolves terminally and never settles the apply as stopped or cancelled. pkg/engine/postgres/postgres.go, pkg/tern/local_control.go
engine.Engine.Cancel Implemented for concurrent index builds; typed decline for plain DDL PostgreSQL can cancel the build backend or its apply context, but plain DDL has no separately managed work to cancel safely. pkg/engine/postgres/cancel.go
engine.Engine.Start Typed decline at drive time Stop cannot create a stopped engine phase, so there is nothing to resume. pkg/engine/postgres/postgres.go, pkg/tern/local_control.go
engine.Engine.Cutover Typed decline at drive time DDL changes the target directly; there is no deferred table swap to trigger. pkg/engine/postgres/postgres.go, pkg/tern/local_control.go
engine.Engine.Revert Refused at intake; typed decline never reached Each successful statement commits directly and creates no revert window, so the API refuses the request before any engine is consulted and the driver never enters the state from which it would call the engine. pkg/api/control_handlers.go, pkg/engine/postgres/postgres.go
engine.Engine.SkipRevert Refused at intake; typed decline never reached With no revert window, every committed statement is already permanent; refused at the same intake gate as revert. pkg/api/control_handlers.go, pkg/engine/postgres/postgres.go
engine.Drainer.DrainContext Implemented Recovery must wait for in-process apply goroutines before re-planning, then discard their instance-local progress. The wait ends with the drive's context, leaving the work tracked, so a drive whose claim is gone does not sit behind it. pkg/engine/postgres/postgres.go
engine.ShutdownHalter.HaltForShutdown Implemented Shutdown cancels in-process concurrent index builds so they do not keep target resources after this instance stops renewing its lease; bounded plain DDL is allowed to finish. pkg/engine/postgres/postgres.go
engine.OwnedWorkHalter.HaltWorkOwnedBy Implemented A drive handing its apply back cancels the concurrent index builds it started, and only those, and waits for its plain DDL to finish; another drive's applies on the same engine are left running. pkg/engine/postgres/postgres.go
engine.DeferredCutoverSignalChecker.DeferredCutoverSignalExists Not applicable; not implemented Direct DDL has no deferred cutover gate or durable table-swap signal. pkg/engine/engine.go, pkg/engine/postgres/postgres.go
engine.ExternallyAuthoritativeProgress.ProgressIsExternallyAuthoritative Not applicable; not implemented Progress is held in the engine instance's in-memory apply map, not in an external service that every instance can query authoritatively. pkg/engine/postgres/apply.go
engine.SynchronousWorkRegistration.RegistersWorkSynchronously Implemented; returns true Apply claims the tracked progress entry before returning and has no remote provisioning phase. pkg/engine/postgres/postgres.go
engine.CancelledArtifactReleaser.ReleaseCancelledArtifacts Not applicable; not implemented PostgreSQL does not create copy tables owned by this engine; cancellation attempts to remove any invalid concurrent index before settling, and a removal that fails is named in the terminal summary for operator follow-up rather than deferred to a generic artifact release. pkg/engine/postgres/cancel.go, pkg/engine/postgres/apply.go
engine.CancelledResumeStateReclaimer.ReclaimCancelledResumeState Not applicable; not implemented Each statement runs in this process against the target directly; nothing is set up in a remote service ahead of the work that owns its teardown, so a cancel that lands between drives finds no stored resume state naming work to reclaim. pkg/engine/engine.go, pkg/engine/postgres/apply.go
engine.ControlResumeValidator.ValidateControlResumeState Not applicable; not implemented Control requests use ResumeState.MigrationContext only as the in-memory apply key and have no opaque, operation-specific remote state to validate. pkg/engine/postgres/apply.go, pkg/engine/postgres/cancel.go
engine.ShardKeyedPlanning.PlansEachShard Not applicable; not implemented A PostgreSQL target is not sharded, so its tasks carry no shard and the plan never needs to speak for one. pkg/engine/engine.go
engine.TargetDrainer.DrainTargetContext Not applicable; not implemented The PostgreSQL engine tracks its in-flight work as a whole, so a drive waits for all of it through DrainContext. pkg/engine/engine.go

Failure and recovery

An accepted apply is stored before a driver executes it. If SchemaBot restarts after queueing but before execution, a new instance claims the pending work and applies it. The engine drains any in-flight background statement before a recovery path re-plans, then clears its in-memory progress so stale state from one apply cannot be reported for another.

This recovery guarantee does not make the DDL resumable within a statement or create-set task. Each statement or sequence step is one PostgreSQL transaction: it commits or fails, and recovery re-plans against the live target before further work. A privilege refusal is a permanent failed task, includes the required provisioning advice, and leaves the target unchanged.

Rollback captures the same live table set the forward plan manages. Tables ignore_tables withholds, by exact case-sensitive name or by pattern, are excluded before introspection, as are archive-named tables with no schema file. An explicitly declared archive remains managed and keeps its original definition, so rollback can reverse its changes. A declared archive the forward plan creates has no original to keep: the rollback re-plan finds it live with no file and, like any archive-named table without one, leaves it in place and discloses it as exempt rather than dropping it. Pull still exports archive-named tables; these capture exclusions do not change pull behavior or permit a table to be both ignored and declared.

Capture is complete for that managed set or the plan is rollback-incapable; an unrenderable ignored table or undeclared archive does not prevent capture. Managed RLS tables still prevent capture until recovery can restore their complete access rules alongside sibling tables. Capture never strips policies to mark a plan rollback-capable, and a catalog read failure or cancellation still ends planning. RV-7 requires complete originals before rollback can proceed; missing originals in older plans are never reconstructed from the current target.

Configuration and credentials

Select PostgreSQL in the repository's schemabot.yaml:

database: app
type: postgres

The server must configure that database environment as a local target with a PostgreSQL DSN. The DSN may be a PostgreSQL URL or keyword/value string and may use the same env:, file:, or secretsmanager: reference forms as other SchemaBot secrets. Planning and applying both require DSN credentials and verify the connection before use.

TLS posture depends on which of the two DSN configuration paths supplies the DSN. On the raw-DSN path, SchemaBot uses the DSN as given: an explicit sslmode is honored; when the first host in the DSN is an RDS host and sslmode is absent, sslmode=require is added; a non-RDS host with no explicit sslmode connects under the driver's default, sslmode=prefer, which falls back to an unencrypted connection when TLS fails. On this path, set sslmode explicitly for multi-host DSNs and for any non-RDS host that must not connect in plaintext.

The assembled path — dsn_from with ca_ref, described in configuration — is fail-closed instead: every assembled DSN carries sslmode=verify-full, weaker modes are rejected at config load, and the server certificate is verified against the named CA bundle (the embedded AWS RDS bundle or a mounted PEM file), never the ambient trust store. A pinned file: CA bundle also requires a certificate-verifying sslmode: paired with a mode that does not verify the server certificate, the engine refuses every apply before execution, because the pinned roots would never be consulted.

For apply, the target role must be able to connect to the database, use the target schema, and act as the table owner for in-place ALTER TABLE. A greenfield CREATE TABLE instead requires CREATE on the target schema. SchemaBot checks the tier required by the exact planned statement and refuses before execution with specific provisioning advice when the role is insufficient.

Planning requires a strictly stronger grant than the apply-time set above. pg-sprite's planner introspects the desired schema by creating a scratch schema inside a transaction it then rolls back, so the planning role also needs CREATE on the target database, and the connection must be read-write rather than a hot standby. Nothing persists — the transaction is rolled back — but a role provisioned with only the apply-time privileges fails at plan time with a permission error on the database. A schema file that declares a foreign key also needs SELECT on each referenced table, because planning copies that table's shape.

Supabase compatibility tests

The Supabase adapter tests run against the pinned Supabase PostgreSQL 17.6.1.136 image, using its non-superuser postgres role. They need Docker, not a hosted project or API keys. They run in CI's per-package integration shard and with make test-integration.

Case What passing proves
Add a nullable column Pull, plan, apply, and replan converge without losing rows or table grants
Add an index The adapter executes a concurrent index build and converges to the desired schema
Table with RLS Pull retains access rules and an unchanged plan converges; a direct native column addition preserves row isolation
Copy and swap The plan is blocked and leaves the table unchanged

Run just these cases locally:

docker pull supabase/postgres:17.6.1.136@sha256:f371b5f3f2ac0a05703f33d6e6134515fb2498cab708fb948a0aeb7481467c00
go test -tags=integration -run '^TestSupabaseEngine$' -count=1 ./pkg/engine/postgres

Successful output ends with ok for github.com/block/schemabot/pkg/engine/postgres. A passing refusal case proves rejection, not support for applying that change.

This is a small adapter suite, not a claim of hosted Supabase support. It does not exercise Supavisor, the Data API, Auth service, Realtime, or hosted TLS. The Supabase fixture test covers pull and comparison; the separate atomic RLS integration tests cover policy replacement. The fixture RLS test also calls the native apply path directly and checks visibility with the image's authenticated role and bootstrap auth.uid() function. It does not test the full plan-to-apply workflow for an RLS table or HTTP authentication.

Keep the test and CI image pins in sync when updating the fixture, and rerun these cases before changing the tested version here.

SchemaBot storage

storage.dialect: postgres selects PostgreSQL for SchemaBot's internal plans, applies, tasks, and other control-plane state:

storage:
  dialect: postgres
  dsn: "env:SCHEMABOT_DSN"

This setting does not enable PostgreSQL target execution. Target execution is selected independently by type: postgres in the repository and server database configuration. Conversely, PostgreSQL targets do not require SchemaBot's own storage database to use PostgreSQL. See Configuration → Storage Dialect for the storage DSN and bootstrap behavior.

The storage DSN must reach an endpoint that gives each connection its own PostgreSQL session, because SchemaBot coordinates its instances with session-scoped advisory locks. Startup refuses a transaction-mode pooled connection string rather than run without that coordination. See Configuration → PostgreSQL storage needs a session-per-connection endpoint.