PostgreSQL

PostgreSQL is Ptah’s primary first-party target, and this page is the map of what Ptah manages on it beyond portable table DDL. Every object family below is declared in your schema sources, flows through the full generate / compare / migrate / rollback lifecycle, and has its exact directive syntax in the Go annotation reference. Commands that introspect this behavior require a live PostgreSQL database URL.

Connecting

Use a postgres:// or postgresql:// URL:

ptah db read --db-url "postgres://user:pass@localhost:5432/app"

Commands that introspect PostgreSQL-family targets accept --schemas to scope reading to a comma-separated list of database schemas. Empty means the connection’s default schema for a command that only describes the database, such as db read. A command that compares the database with a desired schema, such as schema compare, schema drift, schema plan, schema apply, migrations plan or migrations generate, lets the URL decide instead: a URL that pins search_path is read at that schema, one that pins none across every schema, and the schemas the desired schema names are read too. A schema such a command reads only because the URL pins no search_path, and that the desired schema declares nothing in, has its tables planned for removal; the command warns on standard error once for each one, as Generate migrations shows.

On connection, Ptah reads the server version and selects the matching capability preset, so planning adapts to the concrete server — see Dialects and capabilities.

A foreign key may reference a table in another schema of the same database, such as REFERENCES crm.customers (id) on a table of public. A read of public keeps the name crm: db read and SQL output write REFERENCES "crm"."customers"("id"), HCL output writes table.crm.customers, and a schema file declaring the key compares equal to the database. The same holds on CockroachDB. The description does not hold crm.customers, so Ptah cannot check its columns or its key, and the server checks them when the statement runs. Where the read a command compares against covers crm too, as schema apply reads a URL naming no schema, a desired schema that declares nothing of crm asks for its tables to be dropped. Ptah refuses a key into such a schema by name rather than plan the drop: scope the run with search_path or declare the referenced table.

Foreign key columns may use different widths within PostgreSQL’s integer family (smallint, integer, bigint), or different varchar lengths. Ptah keeps the declared column types because PostgreSQL can compare these values without widening the columns. This rule does not permit arbitrary type pairs; PostgreSQL must have a suitable equality operator for the key.

What the connecting role needs

A read takes its tables, columns, constraints, views and grants from pg_catalog, which lists every object to every role. schema diff, schema compare, schema drift, schema inspect and every other command that reads a schema give the same answer for a role with no table privileges as for the object’s owner, so a comparison can run as a role that cannot read a single row. information_schema is not read here: it answers only for what the role holds a privilege on, and pg_read_all_data shows no constraint or grant through it. MySQL and MariaDB have no such catalog, so there the account matters.

The role needs CONNECT on the database and USAGE on a schema on its search_path. Ptah needs that schema to place the objects a name without a schema refers to, and PostgreSQL resolves a schema the role has no USAGE on to nothing. Such a connection is refused at exit 2, and the message names the privilege:

database URL selects schema "app", which does not exist in this database or holds no USAGE privilege for the connecting role

USAGE on the other schemas is not needed. A schema the role cannot use is read like any other. A search_path that lists one is the exception for spelling: PostgreSQL leaves it out of the role’s search path, so a view body names its tables with the schema, and the first schema the role can use becomes the default.

One fact needs more. A role’s password is in pg_catalog.pg_authid, which only a superuser can read by default. A comparison that has to decide whether to set a declared password is refused at exit 2 for any other role; see Role passwords.

Version-dependent behavior

PostgreSQL release lines differ in grammar that reaches generated SQL:

• Trigger modification uses single-statement CREATE OR REPLACE TRIGGER on PostgreSQL 14+; older lines get an explicit drop-and-create sequence.
• In-place ALTER COLUMN ... SET EXPRESSION for generated columns requires PostgreSQL 17+.
• A column list on ON DELETE SET NULL or ON DELETE SET DEFAULT requires PostgreSQL 15+. On an older line Ptah refuses the declaration rather than render an action that clears every column of the key; see Limit ON DELETE to some columns.
• NOT ENFORCED on a CHECK or a foreign key requires PostgreSQL 18+. On an older line Ptah refuses the declaration rather than render a constraint the server would check; see Constraint enforcement and the MATCH type.

Schema objects

The annotation grammar covers, per object family:

• Namespaces and infrastructure: schemas, extensions, functions.
• Types: enum types (CREATE TYPE ... AS ENUM), domains, composite types, and range types.
• Relations: tables, views, materialized views, triggers, and standalone sequences.
• Security: roles, grants, and row-level security policies.

Several of these are features Atlas keeps out of its open-source core; Ptah provides them as open, local, no-account capabilities. The sections below summarize behavior that affects how you plan changes.

How a declaration is compared with what the server stored

PostgreSQL does not keep the text that declared a type, a default or an expression. It stores what it parsed and prints that back, so a declaration and its own read-back rarely match as text:

Time defaults keep their SQL syntax: DEFAULT CURRENT_TIMESTAMP stays a keyword expression, and DEFAULT CURRENT_TIMESTAMP(3) keeps its precision. DEFAULT NULL means SQL null, the same as an omitted default. DEFAULT 'NULL' is a string value and compares as a different default.

Declared	Read back
varchar(10)[]	character varying(10)[]
DEFAULT '2020-01-01'::timestamp with time zone	'2020-01-01 00:00:00+00'::timestamp with time zone
DEFAULT 'x'::text::character varying	('x'::text)::character varying
CHECK (price >= 0) on a numeric column	(price >= (0)::numeric)
routine argument b text DEFAULT 'X'	b text DEFAULT 'X'::text
routine argument c numeric(10,2) = 1.5	c numeric DEFAULT 1.5
routine result SETOF public.items, with public on the search path	SETOF items
routine body BEGIN ATOMIC SELECT count(*) FROM v; END	BEGIN ATOMIC SELECT count(*) AS count FROM v; END, on several lines
routine f(x int) body BEGIN ATOMIC SELECT count(*) FROM t WHERE a > x; END	... FROM t WHERE (t.a > f.x); END

When a comparison has a connection, Ptah asks that server to spell each declared column type and default, CHECK, EXCLUDE element list and predicate, policy clause, index expression and predicate, trigger WHEN condition, domain, and routine argument list and result the way its catalog does. It creates a temporary object inside a transaction that is rolled back, reads the stored form, and compares like with like. A column is asked only when its default is declared or its type is not written the way the catalog reports it. A routine is asked when it declares arguments or a result and the database holds a routine of that name. Its temporary copy has the declared arguments and return type, and a body the server does not check, because the signature does not depend on the body.

A routine with a SQL-standard body, BEGIN ATOMIC ... END or RETURN ..., is asked with its body too, a procedure without arguments included. PostgreSQL keeps such a body parsed, and pg_get_function_sqlbody prints it in its own spelling, so the temporary copy carries the declared body and its stored form is compared with the one the database holds. The copy takes the routine’s own name, because the server qualifies a parameter with it, as f.x. A schema file may leave the language out of such a routine: the server records it in LANGUAGE sql, and the SQL schema reader does too.

• The body reads the tables and views the database holds. A body that reads one the plan has not created yet makes the server refuse the copy. A second copy with the body left out then answers for the arguments and the result, and the body is compared as written.
• A body that selects * is answered only while no table the database holds gains or loses a column, as on CockroachDB.
• YugabyteDB keeps a routine created in pg_temp past the rollback, so there the copy carries no body.

A result type keeps its schema in the read-back only where the session’s search path does not reach it: SETOF public.items reads back as SETOF items, and SETOF other.items keeps other.. Only the server knows which applies, so a result is not folded by removing its schema.

CockroachDB needs more than the signature, because it rewrites a routine when it stores it:

Declared	CockroachDB v26.3.2 stores
result SETOF public.items	pg_get_function_result items, with proretset true
result TABLE (a INT8, b STRING)	record, with proretset true
body SELECT * FROM public.items	SELECT public.items.id, public.items.title FROM f1.public.items;

Ptah reads SETOF back from proretset where the result leaves it out. CockroachDB refuses routines in pg_temp, so there the probe creates the declared routine itself, language and body included, in the current schema inside the rolled-back transaction, as the view probe does, and every routine the database holds by name is asked. The stored body can only match the catalog’s, never make a body that matched as written differ. A body that selects * is answered only while no table the database holds gains or loses a column: the stored expansion lists the columns the table had, and a routine over a table the plan widens is replaced so that its * covers the new column.

Ptah lowercases the words of an argument list and a return clause that are not quoted, as the server does. A string literal, a quoted name and a dollar-quoted string keep their case: an argument declared DEFAULT 'X' is created with 'X'.

The temporary table takes the name of the table the declaration is on, so an expression that names its own table, such as CHECK (clients.n > 0) or a policy subquery comparing with clients.id, resolves the way the server resolved it. The server stores that qualifier unqualified. The probe searches pg_temp last, so every other name in the expression, public.clients included, still reads the real table.

One pass through the server does not always reach the database’s spelling. PostgreSQL prints kind IN ('a', 'b') as kind::text = ANY ((ARRAY['a'::character varying, 'b'::character varying])::text[]), and stores that text, written back, as kind::text = ANY (ARRAY[('a'::character varying)::text, ...]). A history written from an inspected database holds the last form. When the declaration’s answer differs from the stored CHECK, Ptah asks the same server about both texts once more and compares the two answers. A check whose values changed is still a change.

The server asked is the one the other side was read from. schema apply asks the target. migrate diff and migrations generate --replay ask the dev database the migration directory was replayed on. Both compare on a session they hold for the whole run, the apply lock or the replay, and the probe transaction runs on that session while no transaction is open on it.

schema diff asks the server its --from side was read from, while that connection is still open: the --from database itself, or the dev database session the --from migration directory was replayed on, before the replay’s cleanup. It does so when --to is a schema file or another declaration, and when both sides are databases. A --from database answers without --dev-url.

When --from is a schema file and --to is a database or a migration directory, schema diff creates the file on the --dev-url database, reads it back, and compares what it read. The --from side then holds the server’s spelling too, as Atlas CE’s does. Two schema files are compared as written: they spell an expression the same way when they declare the same schema.

Two databases are not always spelled alike, even on one server version. The server prints kind IN ('a', 'b') as kind::text = ANY ((ARRAY['a'::character varying, 'b'::character varying])::text[]). A migration that writes that text back stores kind::text = ANY (ARRAY[('a'::character varying)::text, ...]). schema diff therefore asks the --from database how it spells each expression of the --to database. A migration directory compared with a database is still compared as the two servers printed it.

A declaration the server refuses is compared with Ptah’s own folding instead. So is a comparison on a session with a transaction open, where the rollback would discard the session’s work, and every comparison without a connection. A schema diff from a schema file to a database without --dev-url is one of those: the file’s expressions are compared as written, and a rewritten default, CHECK or policy is planned again. Pass --dev-url for that direction. A key column is NOT NULL on the file’s side either way, as the server holds it. ptah-compat schema diff refuses a schema file without --dev-url, as Atlas CE does, unless PTAH_ATLAS_DIFF_WITHOUT_DEV_URL=1 is set.

A view or materialized view body is compared by folding first. The server expands a * into the column list when it creates the view, so SELECT * FROM orders WHERE total > 100 reads back as SELECT id, total FROM orders WHERE (total > 100). Ptah expands each top-level * of a view that reads one table into that table’s declared columns before it compares, so the declaration and its read-back match. Because the declared columns are used, a view created before its table gained a column is replaced, and the new column appears in it. A * over a join, over a function or inside a subquery is compared as written, so such a view is replaced on every plan; list its columns instead.

A body the fold finds different is then asked of the server, when the database holds a view or materialized view of that name and the body selects no *. Ptah creates a view with the declared body inside the rolled-back transaction, reads pg_get_viewdef back and compares that with the catalog’s. The server’s answer can only match a body; it never makes a body the fold matched differ. This is what matches a view over a set-returning function, which the server stores with the function’s whole result as a column alias list:

Declared	Read back
SELECT id FROM public.all_items()	SELECT id FROM all_items() all_items(id, title, tags)
SELECT g FROM generate_series(1, 3) g	SELECT g FROM generate_series(1, 3) g(g)

The probe view is temporary on PostgreSQL and YugabyteDB. CockroachDB refuses temporary views by default, so there it is created in the current schema inside the same transaction. Either way it is dropped before the transaction rolls back, because YugabyteDB keeps a temporary view through the rollback and the session. A * is not asked, because the server expands it against the columns the database holds now, not the ones the desired tables declare.

Constraint enforcement and the MATCH type

PostgreSQL 18 keeps a CHECK or a foreign key declared NOT ENFORCED and does not check it. A foreign key declared MATCH FULL refuses a row whose key is partly null. Ptah reads both clauses from a SQL file, writes them when it renders the constraint, reads them back from pg_get_constraintdef, and compares them. A constraint whose enforcement or MATCH type changes is dropped and added again, because PostgreSQL 18.6 cannot change a CHECK’s enforcement in place.

CockroachDB and YugabyteDB keep MATCH FULL too, and neither takes NOT ENFORCED. The reader refuses MATCH PARTIAL, which PostgreSQL 18.6 answers with MATCH PARTIAL not yet implemented, and the renderer refuses NOT ENFORCED for PostgreSQL before 18.

A command that connects plans for the server it connects to. schema diff plans for the database it reads, or for the dev server it compares two files on; a docker://postgres/18 dev URL names that server in its tag. schema render, which connects to nothing, plans for PostgreSQL 17 unless --server-version 18 names the target:

ptah schema render --dialect postgres --server-version 18 --root-dir ./models

Unnamed constraints in a SQL file

PostgreSQL names a CHECK, a table-level UNIQUE, an EXCLUDE or a FOREIGN KEY that the SQL leaves unnamed. A SQL schema file read for PostgreSQL gives the constraint the same name, so the file compares equal to the database its own SQL built, and a plan from the file creates the constraint under that name.

The name is <table>_<columns>_key or <table>_<columns>_fkey, with the columns joined by underscores. The columns of a UNIQUE are every column of its index, the INCLUDE columns after the key columns, and a column named twice is numbered the second time. A name longer than 63 bytes is cut, from the longer of the table part and the columns part first, at a character boundary. A name already taken in the schema is numbered key1, fkey1 and on. For a UNIQUE, a table, view, sequence or index of that name counts as taken too. Measured on PostgreSQL 18.6:

Declared	Name
parent_id bigint REFERENCES parent(id) on child	child_parent_id_fkey
FOREIGN KEY (a, b) REFERENCES parent(id, k) on child	child_a_b_fkey
a second foreign key over p on twice	twice_p_fkey1
UNIQUE (a, b) on p	p_a_b_key
UNIQUE (x) INCLUDE (y) on i1	i1_x_y_key
UNIQUE (x, y) INCLUDE (z, x) on i3	i3_x_y_z_x1_key
UNIQUE (a) on q, beside an index named q_a_key	q_a_key1

An EXCLUDE is named <table>_<elements>_excl. An element that is a column takes the column’s name. An expression takes the name of the function it calls, of the column or type a cast names, or a word such as coalesce or case, and expr when it has none. A name an earlier element holds is numbered, and the constraint name is cut and numbered the way a UNIQUE name is, past tables, views, sequences and indexes as well. Measured on PostgreSQL 18.6:

Declared	Name
EXCLUDE USING gist (r WITH =, s WITH <>) on b	b_r_s_excl
EXCLUDE USING btree (lower(t) WITH =) on d	d_lower_excl
EXCLUDE USING btree ((r + 1) WITH =) on f	f_expr_excl
EXCLUDE USING gist (r WITH =, r WITH <>) on h	h_r_r1_excl

An EXCLUDE is paired by its name and compared by its access method, written in any case, its elements and its WHERE clause. The server prints the elements and the clause back its own way: (lower(t)) WITH = as lower(t) WITH =, WHERE (s > 0) as WHERE ((s > 0)), and WHERE (n >= 0) over a numeric column as WHERE ((n >= (0)::numeric)). With a connection the declaration is spelled by the server first, as How a declaration is compared with what the server stored describes; without one the texts are compared as written.

Catalog inspection preserves parentheses inside quoted strings and identifiers in EXCLUDE elements and predicates. A malformed or unrepresented definition stops the read and names the constraint. INCLUDE, storage parameters, and an index tablespace on an EXCLUDE are not represented; inspection refuses those clauses.

One CREATE TABLE builds a single constraint for index constraints that share a key. For a primary key or a UNIQUE the key is the columns, the INCLUDE columns and NULLS NOT DISTINCT. For an EXCLUDE it is the access method, the elements and the WHERE clause. The primary key is kept first, then the first of the others, and a name the dropped constraint carries goes to the kept one when that one has none. A SQL schema file is read the same way. Measured on PostgreSQL 18.6:

Declared in one CREATE TABLE on t	Built
id int PRIMARY KEY, CONSTRAINT u UNIQUE (id)	the primary key, named u
UNIQUE (a), CONSTRAINT n UNIQUE (a)	n
EXCLUDE USING btree (r WITH =) twice	t_r_excl
UNIQUE (a), UNIQUE NULLS NOT DISTINCT (a)	t_a_key, t_a_key1

Declared in separate statements, a CREATE TABLE and an ALTER TABLE ... ADD, both constraints are built. A render writes the second one as an ALTER TABLE ... ADD CONSTRAINT after its table, so a database built from the render holds both as well. Elements and clauses are compared as the server’s lexer reads them: spacing and the case of an unquoted word do not matter, and an extra pair of parentheses does, so the server can build one constraint for a pair that Ptah reads as two. Deferral is part of the key too: UNIQUE (a) DEFERRABLE, UNIQUE (a) builds both constraints.

A column’s own UNIQUE is a key over that column alone, and it folds the same way into an equal primary key or UNIQUE of the same CREATE TABLE: a int UNIQUE, CONSTRAINT uq_a UNIQUE (a) builds uq_a alone, and id int PRIMARY KEY UNIQUE builds the primary key alone. A render keeps the column’s key and adds the other one after the table, or adds the column’s key after the table where the other one is the primary key. How the comparison reads the pair is described with the other UNIQUE rules below.

A CHECK is named <table>_<column>_check when its condition names exactly one column of the table, and <table>_check when it names none or more than one. It makes no difference whether the CHECK is written on the column or on the table. A string literal, a function, a type, a collation and a qualifier are not columns. The name is cut to 63 bytes the same way and numbered check1, check2 and on. A name counts as taken when another constraint in the schema holds it; a table or an index of that name does not. A column may carry more than one CHECK, and each is kept. Measured on PostgreSQL 18.6:

Declared	Name
CHECK (plan IN ('x','y')) on a	a_plan_check
CHECK (lo < hi) on c	c_check
a int CHECK (a IS NULL OR b IS NOT NULL) on e	e_check
CHECK (s COLLATE "C" > '') on co, which has a column C	co_s_check
a int CHECK (a > 0) CHECK (a < 10) on h2	h2_a_check, h2_a_check1

One CREATE TABLE names its constraints in the order PostgreSQL builds them, and a SQL schema file is read in the same order. The server adds the CHECKs first, then builds the primary key wherever the statement writes it, then every UNIQUE and EXCLUDE, a column’s own UNIQUE among them, and then the foreign keys. Within each group it follows the statement, a column’s constraints at the column’s place. A name the statement writes before a derived one numbers it, and a derived name takes no account of a name the statement writes later. Measured on PostgreSQL 18.6:

Declared in one CREATE TABLE on t	Names
CHECK (a > 0), a int CHECK (a < 10)	t_a_check for a > 0, t_a_check1 for a < 10
UNIQUE NULLS NOT DISTINCT (a), a int UNIQUE	t_a_key for the first, t_a_key1 for the column’s
CONSTRAINT t_x_key UNIQUE (y), x int UNIQUE	t_x_key, and t_x_key1 for the column’s
x int UNIQUE, CONSTRAINT t_x_key CHECK (y > 0)	t_x_key for the CHECK, t_x_key1 for the column’s

A column’s own UNIQUE that the server numbers this way is read as a UNIQUE on the table under that name. A render writes a column’s constraints before the table’s, so the key left on the column would take the first name there, and the constraint that holds it would be refused.

A written name that a constraint built earlier already holds is refused, as the server refuses it. The reader names both constraints in the error. Each of these fails on PostgreSQL 18.6, and the reader refuses it:

Declared in one CREATE TABLE on t	PostgreSQL answers
x int UNIQUE, y int, CONSTRAINT t_x_key UNIQUE (y)	relation "t_x_key" already exists
a int CHECK (a > 0), CONSTRAINT t_a_check CHECK (a < 10)	check constraint "t_a_check" already exists
x int CHECK (x > 0), CONSTRAINT t_x_check UNIQUE (id)	constraint "t_x_check" for relation "t" already exists
id int PRIMARY KEY, x int, CONSTRAINT t_pkey UNIQUE (x)	relation "t_pkey" already exists
CONSTRAINT d UNIQUE (a), CONSTRAINT d UNIQUE (b)	relation "d" already exists

A foreign key is compared by its name, whether the file declares it on its column or on its table. When the database holds the key under another name, the plan drops that key and adds the declared one, so the table ends with the keys a new database built from the file has. For a migration that names the key c_p_fk and a schema file that writes p_id bigint REFERENCES p(id) on c, the plan drops c_p_fk and adds c_p_id_fkey, as Atlas CE does.

A plan releases removed foreign keys before removing or replacing the keys they reference and before changing their columns. Replacement foreign keys are added after the referenced keys. This order also applies to reverse migrations and self-references.

A foreign key depends on one index of the table it references, and PostgreSQL refuses to drop that index while the foreign key exists. It does not move the foreign key to another key over the same columns. Ptah reads the index from pg_constraint.conindid. When a plan removes or replaces that key, it drops the foreign key, changes the key, and adds the foreign key back, which takes a lock on both tables and validates every row. A foreign key that depends on a key the plan keeps stays untouched. A foreign key in a schema the run leaves out is not read, so the server’s refusal stops that plan and its transaction undoes it.

A primary key the plan removes or replaces leaves ahead of the column changes when one of them needs it gone: a column of the key that stops being NOT NULL, which PostgreSQL refuses with column "id" is in a primary key, or a new column that declares PRIMARY KEY. Otherwise the key is replaced after them.

A declared primary key name over the columns the live key holds is a rename, on PostgreSQL and CockroachDB: the plan writes RENAME CONSTRAINT, so the key’s index is not rebuilt and a foreign key over it stays. A key that declares no name keeps the name it holds.

A column-level UNIQUE is compared by the name PostgreSQL gives it, <table>_<column>_key, numbered 1 and on where another relation or constraint of the schema already holds that name. When the database holds the key over the column under another name, the plan drops that key and adds <table>_<column>_key, as Atlas CE does. For a migration that names the key c_x_uq and a schema file that writes x int UNIQUE on c, the plan drops c_x_uq and adds c_x_key. Where a key the plan drops holds that name, the plan drops it before it adds the column’s key.

The column’s key is one key over the column alone. Every other key of the table, a second key over the column or a key over more columns that the column leads, is compared by its name, so a int UNIQUE, UNIQUE (a, b) matches the two keys it builds, and a key the file no longer declares is dropped.

A named UNIQUE over the column alone, written in the same CREATE TABLE, is the column’s key too: the server builds CONSTRAINT uq_a UNIQUE (a) beside a int UNIQUE as uq_a alone, and Ptah reads the file that way. Written in separate statements, as a int UNIQUE and a later ALTER TABLE c ADD CONSTRAINT uq_a UNIQUE (a), the two are two keys, c_a_key and uq_a. A unique index is an object apart the same way, so a int UNIQUE beside CREATE UNIQUE INDEX ux ON c (a) builds c_a_key and ux. In both cases a database that holds only the other key is planned c_a_key, as Atlas CE plans it.

A database is described the same way. Where it holds <table>_<column>_key and a second key over the column alone, as CREATE TABLE g2 (a int, UNIQUE (a)) followed by ALTER TABLE g2 ADD UNIQUE (a) builds, the description carries the column’s UNIQUE and the second key under its name, g2_a_key1.

A plan that adds a column-level UNIQUE to an existing column writes ADD CONSTRAINT under the name the comparison reads as the column’s key, as Atlas CE writes it: <table>_<column>_key, or <table>_<column>_key1 and on where another relation or constraint the file declares holds that name. Beside CREATE UNIQUE INDEX c_x_key ON c (y), a file that makes x UNIQUE on c plans ALTER TABLE c ADD CONSTRAINT c_x_key1 UNIQUE (x); under c_x_key, PostgreSQL 18.6 refuses the statement with relation "c_x_key" already exists.

The rollback of a plan that gives a column its UNIQUE drops the key the column took, under the same name. The rollback of a plan that drops a key adds the key back with the body the database held, and a column the rollback makes UNIQUE again or adds back takes no second key through its UNIQUE. Where a key the rollback adds back takes the name of the key a column holds, the rollback drops that column’s key first.

MySQL and MariaDB name an unnamed foreign key <table>_ibfk_<n>, which the MySQL page describes. The other engines keep Ptah’s own name for an unnamed foreign key, fk_<table>_<column>. MySQL and MariaDB name an unnamed CHECK by rules of their own, which the MySQL page describes too. A CHECK left unnamed in a file read for another engine stays unnamed. Two of them on one table are compared by their conditions, so a plan never merges one into the other.

A column check declared in YAML or a Go annotation without check_name is written without a name, so PostgreSQL names it by the rule above, and the comparison looks for that name. A table’s checks entries are named <table>_check, <table>_check1 and on, past the names the column CHECKs take: a column CHECK over two columns takes <table>_check, and the first entry beside it takes <table>_check1.

Unvalidated constraints

A CHECK or a foreign key added with NOT VALID checks new rows and leaves the rows already in the table unchecked. PostgreSQL, CockroachDB and YugabyteDB record it as not validated until VALIDATE CONSTRAINT checks those rows. A schema file declares it the way a migration adds it:

ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

The comparison reads the clause as a permission, not a requirement:

• A declaration with NOT VALID is satisfied by the constraint whether the server has validated it or not. A plan that adds the constraint adds it NOT VALID, so rows that break it do not stop the plan.
• A declaration without the clause asks for a validated constraint. Where the database holds the constraint NOT VALID, the plan runs ALTER TABLE ... VALIDATE CONSTRAINT rather than dropping and adding it. The validation fails if a row breaks the constraint.

NOT VALID inside a CREATE TABLE changes nothing, because the server records the constraint as validated. So a plan that creates the table adds a CHECK declared NOT VALID after it, where the clause is kept. VALIDATE CONSTRAINT in a schema file marks the constraint validated.

The Atlas HCL and Go annotation exports refuse a NOT VALID constraint, because neither format can write one. A rollback leaves a validated constraint validated: nothing can mark it NOT VALID again short of dropping it.

Allow a key another source declares to stay NOT VALID

An ORM’s schema export, such as Ent’s, writes every foreign key without NOT VALID, because it has no way to say it. When the migration history added a key NOT VALID on purpose, to validate it later after a data check, that export asks for the key validated, and the first diff plans VALIDATE CONSTRAINT. A line comment in another SQL file of the desired schema records the history’s choice without declaring the key again:

-- ptah:allow-not-valid CONSTRAINT items_owner_fk ON items

It is the same permission the NOT VALID clause gives, added to the key the export declares. The rest of the key stays the export’s: a change to its columns, its target or its ON DELETE action is still planned, and the key is then added again NOT VALID. A database that holds the key validated satisfies it too, so a directive left behind after a manual VALIDATE CONSTRAINT changes nothing. Remove the line to require the key validated again.

• The names are read the way the server reads them: quoted or folded, and cut to 63 bytes where it cuts a longer name.
• The table is named the way the documents name it, as COMMENT ON CONSTRAINT ... ON would name it in the same file: unqualified where they write it unqualified, qualified where they qualify it.
• The directive is read after every source of the desired schema is merged, so the key can come from another file, an external_schema program or another part of a composite_schema. ptah and ptah-compat read it the same way.
• A directive that reaches no CHECK or FOREIGN KEY of the merged schema is refused and names where it was written. So is a misspelled word of the ptah:allow family, such as ptah:allow-not-vaild, and a directive in a document of a dialect without NOT VALID.
• Only a comment that begins its line is a directive. The same text inside a string or a routine body is not one.
• A migration directory used as the desired schema is replayed, and a key its history adds NOT VALID is read back NOT VALID, which allows it already. A directive in a migration file is not read.

A file that drops a key and adds it again under its name with another definition is read as the later statement says, and the run warns about it: the copy goes on deciding the key after the export changes it. Where only NOT VALID differs, the warning names the directive that allows it instead.

The directive is a Ptah addition. Atlas reads it as a SQL comment: the pinned community binary v1.3.0 accepts a file that carries it, and has no equivalent setting.

Object comments

Ptah writes the comment of a view, a materialized view, a sequence, a domain, a composite, range or enum type, an extension, a function, a procedure, a trigger, a row-level security policy, an index, a schema and a role with COMMENT ON, reads it back and compares it, as it does a table’s and a column’s. The comment is written right after the statement that creates the object, and a changed comment is planned as one COMMENT ON for the object, not as a drop and a create. An index whose definition changed is dropped and created again, and the comment follows its CREATE INDEX.

A function or a procedure is named by the argument list the server records, so the statement addresses one overload. A trigger and a policy are named ON their table.

An object the plan writes again ends with the comment the declaration states. CREATE OR REPLACE keeps the comment a function, a procedure or a trigger had, so a removed comment is cleared after the replacement. ALTER POLICY also keeps its comment; a comment change gets a separate COMMENT ON POLICY. A materialized view, and a policy that needs DROP and CREATE, get their declared comments after creation. An enum that loses a value is renamed, created again and the old type dropped; each new object is written with the declared comment.

An extension’s comment is compared only when the declaration states one. CREATE EXTENSION gives every extension the comment its control file carries, so a declaration without a comment is not read as asking for none. The version of an extension follows the same rule, and so does a schema’s comment: PostgreSQL creates every database with public commented standard public schema, and an Atlas HCL document declares schema "public" {} before any table can name it, so a schema declared without a comment keeps the one it holds. The rule holds in the other direction too: schema diff --from such a document --to a database, with no --dev-url, reads the document’s public with the comment the server gives it.

The other engines of the family take fewer of these statements. Ptah writes and compares a comment only where the server stores it and reports it back, which each statement’s capability key records: view_comments, materialized_view_comments, sequence_comments, type_comments, domain_comments, extension_comments, function_comments, procedure_comments, trigger_comments, policy_comments, index_comments, schema_comments and role_comments. Where a key is false, the render names the comment it left out. CockroachDB accepts COMMENT ON TYPE and then reports no comment for the type, so its type_comments key is false. CockroachDB 26.3 stores a function’s and a procedure’s comment and refuses the statement for a materialized view, a trigger and a policy. CockroachDB stores an index’s and a schema’s comment and has no COMMENT ON ROLE.

Constraint comments

A table constraint’s comment is written with COMMENT ON CONSTRAINT ... ON its table, right after the statement that adds the constraint. Ptah reads it back and compares it, and a changed comment is planned as one COMMENT ON CONSTRAINT, not as a drop and an add. A constraint whose definition changed is dropped and added again, and the statement that adds it writes the declared comment. A rollback that adds back a dropped CHECK, UNIQUE or foreign key constraint writes the comment it had.

Only a constraint the declaration states as a constraint is compared. A primary key from a primary-key column, a column’s check or foreign key, and a table’s list of checks have no place for a comment, so the comparison leaves the comment the database holds on them alone. An unnamed constraint gets no comment, because the statement has to name it.

The constraint_comments key records where the server stores the comment and reports it back. PostgreSQL, CockroachDB and YugabyteDB do.

A NOT NULL name another column holds

PostgreSQL 18 keeps a NOT NULL as a named constraint, and RENAME COLUMN leaves the name as it was. So display_name can hold t_full_name_not_null in one database while full_name holds it in another, and no two constraints of a table can share a name.

The plan keeps its order, with the removal last, because a view or a policy may read the old column until an earlier statement moves it to the new one. It moves the name instead, and the interim name leaves with the old column:

ALTER TABLE "t" RENAME CONSTRAINT "t_full_name_not_null" TO "t_full_name_not_null__ptah_old";
-- POSTGRES: ADD COLUMN "display_name" fails if "t" holds any row; the column is NOT NULL and declares no default to fill it with.
ALTER TABLE "t" ADD COLUMN "display_name" text CONSTRAINT "t_full_name_not_null" NOT NULL;
ALTER TABLE "t" DROP COLUMN "full_name";

A column whose NOT NULL the plan renames holds its name in the same way until that rename runs, and a column the plan makes nullable holds it until DROP NOT NULL runs. Each goes through the interim name too when another statement takes the name first. The name the server would give a column left to it counts as taken as well, so SET NOT NULL does not land on a numbered name while a column the plan removes holds the plain one.

A rollback gives a column the plan made nullable the name it held, by the statement in Making a column NOT NULL. A name the server would give needs no statement of its own.

ptah-compat writes one ALTER TABLE for the drop and the addition where nothing depends on their order. PostgreSQL runs the drops of a statement first, so that statement needs no rename. PostgreSQL 17 and earlier keep no such name, so a plan between two of them has nothing to move.

A NOT NULL name on PostgreSQL 17

PostgreSQL 17 accepts CONSTRAINT c NOT NULL and stores no name, and a read of PostgreSQL 18 names every NOT NULL. So schema diff --from <17> --to <18> sees a name on every column, most of which nobody wrote.

• The name the server gives. <table>_<column>_not_null, cut to 63 bytes as the server cuts it, asks for nothing. The plan writes NOT NULL, and the next plan reads synced.
• Any other name on a column the plan adds or creates is refused, because the target would lose it. A name the server numbered, such as t_c_not_null1, is refused too: the catalog does not say who wrote a name, so it reads as written.
• Any other name on a column that exists. The plan writes a WARNING comment in place of a statement, and the next plan writes it again.

From PostgreSQL 18 toward PostgreSQL 17 the desired side names nothing. An added column takes the name the server gives it, and a name the left side holds stays.

A NOT NULL name on a primary key column

A primary key column’s NOT NULL holds a name on PostgreSQL 18 as any other does, so RENAME COLUMN id TO pk leaves pk holding t_id_not_null, and id int CONSTRAINT nn NOT NULL PRIMARY KEY holds nn. A plan that adds such a column or creates its table writes the name, and ptah db read prints it:

"pk" integer PRIMARY KEY CONSTRAINT "t_id_not_null" NOT NULL

The name the server gives, <table>_<column>_not_null, stays out of the statement. A target that keeps no such name refuses any other name here too, because it would lose it.

Making a column NOT NULL

A plan that makes an existing column NOT NULL writes ALTER COLUMN ... SET NOT NULL. PostgreSQL checks every row when the statement runs, and fails it with SQLSTATE 23502 if a row holds NULL.

• The column declares a default. The plan fills the NULL rows with that default first, then sets NOT NULL. The value is one the schema states. The safety report lists the fill as a warning, because it rewrites rows, and the SET NOT NULL after it as safe. Atlas CE fails this change on a NULL row, so here Ptah is deliberately more permissive, with the author’s own value. Under PTAH_ATLAS_STRICT_COMPAT=1, ptah-compat writes no fill and the statement fails as it does in Atlas CE; see Strict CE mode.
• The column declares no default. Nothing is filled. The plan carries a comment saying the statement fails on a NULL row, and the safety report lists it as a warning. Update those rows in a migration of their own first, or declare a default.

A declared name that is not the one the server would give takes the same steps and a different statement, because SET NOT NULL leaves the name to the server:

ALTER TABLE "t" ADD CONSTRAINT "nn" NOT NULL "a";

It fails on a NULL row as SET NOT NULL does, with the same SQLSTATE, and the safety report and lint rule PG303 read it the same way. PostgreSQL 18 added the statement, and a target that keeps no name writes SET NOT NULL with a WARNING comment. A rename after SET NOT NULL would have to guess the name the server picked: it picks t_a_not_null1 where another constraint holds t_a_not_null, and the rename would then move that other constraint’s name.

A plan that adds a NOT NULL column with no default writes the statement as it is, because it is valid on an empty table. On a table that holds a row it fails with SQLSTATE 23502, so a comment says so, on every verb that writes a plan and on CockroachDB and YugabyteDB too:

-- POSTGRES: ADD COLUMN "required" fails if "t" holds any row; the column is NOT NULL and declares no default to fill it with.
ALTER TABLE "t" ADD COLUMN "required" text NOT NULL;

A declared default, an identity, a serial and a generated column give the row a value, so they get no comment. A column of a type somebody created gets none either, apart from an enum: a domain can declare a default of its own, which fills the row, and the plan cannot read it.

A PostgreSQL identity column is implicitly NOT NULL. SQL desired-schema sources preserve that property for both GENERATED ALWAYS and GENERATED BY DEFAULT AS IDENTITY, even when the declaration omits NOT NULL.

A column’s identity is compared with its declaration, on PostgreSQL and CockroachDB. A column that loses one takes DROP IDENTITY, and a change of generation takes SET GENERATED. A column that gains one takes ADD GENERATED, then a setval that moves the sequence past the highest value the table holds, so the next insert does not collide with a row. A SERIAL column and an identity generated BY DEFAULT compare equal: each draws from a sequence and takes a value an insert names. An identity generated ALWAYS refuses that value, so against a SERIAL column the plan replaces one with the other, in both directions.

A plan rates these by what an insert does next, and no row is lost by any of them. DROP IDENTITY is destructive, because the server drops the sequence with it, as DROP SEQUENCE is. That holds where the plan replaces the identity with a SERIAL column as well, since the statement reads the same. DROP DEFAULT, SET GENERATED and ADD GENERATED ALWAYS are warnings. See how a column change is rated.

A numeric column that declares no precision holds any number. So numeric declared numeric(10,2) is a change rated as a narrowing, and numeric(10,2) declared numeric is a change rated safe. char and char(1) are one type, double precision and float are one type, and real and float(24) are one type, while a bare bpchar has no limit.

A float or a number under a type with a lower capacity is a narrowing, and most of them lose a value without a word. double precision declared real reads 1.23456789012 back as 1.2345679, numeric(10,2) declared integer rounds 1.50 to 2, and bigint declared double precision reads 9007199254740993 back as 9.007199254740992e+15.

An interval column is read with the fields it keeps. interval year to month clears days and time of day from every value, so declaring it over an interval column is rated as a narrowing, and a schema file may spell it as pg_dump does.

Unlogged tables

A table declared unlogged = true renders CREATE UNLOGGED TABLE. Its writes skip the write-ahead log, which makes them faster and costs durability: the server truncates the table after a crash, and replication does not carry it. Use it for a cache or a staging table, not for data that has to survive.

SQL uses CREATE UNLOGGED TABLE; HCL uses the table attribute unlogged. Go annotations use platform.postgres.unlogged="true" on the table directive. Go and HCL exports preserve the declaration. Embedders attach a pgtable.DesiredPersistence facet to the table. Complete source coverage treats an absent setting as logged; incomplete catalog coverage leaves the comparison undecided.

ptah db read reports the property back, so a schema file built from a live database describes an unlogged table as one.

CockroachDB and YugabyteDB reach Ptah over the same wire protocol and neither creates an unlogged table: YugabyteDB takes the keyword and creates an ordinary table. A declaration carrying the flag renders an ordinary table there, and the render reports the flag as a property it left out.

A table that changes between logged and unlogged is planned as ALTER TABLE ... SET UNLOGGED or SET LOGGED, and a rollback writes the opposite. PostgreSQL rewrites the table to do it, under a lock that blocks readers and writers, and the PG307 lint rule reports the statement in a migration that contains one.

Table partition keys

SQL declarations support PARTITION BY RANGE, LIST and HASH. Keys retain column order, quoted column names, expressions, collation and operator class. HCL uses a table partition block with type and either columns or ordered by blocks. A by block takes column or expr, plus optional collate and ops strings. Go table annotations use platform.postgres.partition_by="RANGE (tenant)"; YAML uses partition_by in the table’s postgres platform group. YugabyteDB uses its own platform group.

The reader captures the server’s partition key. Connected comparison normalizes expressions and default key modifiers through a temporary table in a rolled-back transaction. This accounts for implicit casts without changing the source SQL. A probe that the server refuses leaves the comparison undecided.

An existing partition key cannot change in place. Changing an ordinary table to a partitioned table, removing partitioning, or changing its method or key requires a reviewed manual partition cutover. Ptah refuses these changes by name and refuses to rebuild a partitioned table without a partition-tree strategy. An unlogged partitioned table is refused. Complete source coverage distinguishes an ordinary table from a partitioned one; unknown coverage establishes neither.

Table partitions

A SQL schema file declares a partition with CREATE TABLE ... PARTITION OF. Every bound form reads: FOR VALUES FROM ... TO, FOR VALUES IN, FOR VALUES WITH (MODULUS ..., REMAINDER ...) and DEFAULT. The partition and its parent may be in different schemas and may be quoted. A partition may be partitioned itself, with PARTITION BY after its bound.

CREATE TABLE events (
  id bigint NOT NULL,
  happened_at timestamptz NOT NULL,
  PRIMARY KEY (id, happened_at)
) PARTITION BY RANGE (happened_at);

CREATE TABLE events_2026 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE archive.events_rest PARTITION OF events DEFAULT;

A partition’s columns are its parent’s, so a comparison reads none on the partition. A column added to the parent is planned on the parent alone. What the server copies onto a partition from its parent is the parent’s to change, and no plan names it: the copy of a primary key, a unique key, a foreign key, a check, an index and a row trigger. An object the partition holds for itself is compared as usual: a table comment, an index, a trigger, a grant, row security, and a constraint declared with ALTER TABLE.

The reader takes a partition’s parent from pg_inherits and its bound from pg_get_expr, which prints values in the server’s spelling. Connected comparison normalizes a declared bound through a temporary copy of the parent in a rolled-back transaction, so '2026-01-01' on a timestamptz key and the catalog’s '2026-01-01 00:00:00+00' compare equal. A bound with no offset means an instant in the session’s time zone, and a session in another zone reads it as another bound. A bound computed by a volatile function, such as now(), stores a value that never matches its declaration again. Without a connection, a bound spelled differently stays undecided.

A partition the document leaves out is a table the plan drops, with its rows. Plans mark it destructive and the destructive gates hold it as they hold any DROP TABLE. A schema inspect --format sql writes CREATE TABLE ... PARTITION OF, and that document rebuilds the tree on an empty database.

A table that moves in its tree is detached and attached. This covers a partition with another bound or another parent, a partition the document declares as an ordinary table, and an ordinary table it declares as a partition. PostgreSQL has no statement that changes a bound in place, so a bound change detaches the partition and attaches it again under the same parent:

ALTER TABLE "events" DETACH PARTITION "e1";
ALTER TABLE "events" ATTACH PARTITION "e1" FOR VALUES FROM ('2026-01-01') TO ('2028-01-01');

The table keeps its rows. Both statements run in the plan’s transaction, so a move that fails leaves the tree as it was. DETACH ... CONCURRENTLY cannot run in a transaction, and Ptah does not write it. Every detachment of a plan runs before every attachment, so two partitions can trade bounds. A detachment runs before the table’s other changes, which a partition refuses, and an attachment runs after them.

A plan rates a statement by what a reader of the parent sees. A DETACH that takes the table out of its parent, to no parent or to another one, is destructive: the partition’s rows stay in the table and leave what that parent returns, so the destructive gates hold it. Every other statement of a move is a warning, a bound change under the same parent included, because its ATTACH returns the rows in the same transaction. Both statements lock the parent table. ATTACH reads every row of the partition to check it against the bound, and reads a default partition too when the parent has one. A row outside the declared bound makes the server refuse the attachment, and the transaction rolls the move back. A rollback moves the table back to the parent and bound it held.

A document that keeps a partition and leaves out a parent the database holds is refused, because dropping a partitioned table drops its partitions.

Some forms are refused because the declaration has no place for them:

• A column or constraint list, PARTITION OF events (...). Declare a default on the parent, and a constraint with ALTER TABLE.
• WITH (...), USING and other clauses after the bound.
• ALTER TABLE ... ADD COLUMN on a partition, which the server refuses too.
• COMMENT ON COLUMN on a partition, whose columns the declaration does not hold. An export leaves such a comment out.

A dump reads as the same declaration. pg_dump writes each partition as an ordinary CREATE TABLE with its columns, then ALTER TABLE [ONLY] parent ATTACH PARTITION partition bound, a copy of each of the parent’s keys and indexes on the partition, and ALTER INDEX parent_index ATTACH PARTITION copy for each copy:

• The attached table becomes a partition, as PARTITION OF declares one. Its columns have to be its parent’s, with the same type, nullability and default, as the server requires; another column, or one that differs, is refused by name. The reader then drops them, because a partition takes its columns from its parent.
• An attached copy leaves the declarations, and the parent’s key or index stays. The copy has to be an index of a partition of the parent index’s table.
• CREATE INDEX ... ON ONLY parent creates the parent’s index alone. Every partition the same file declares has to attach a copy of it, or the server leaves the index invalid, and the reader refuses the file.
• The \restrict and \unrestrict lines around a dump, which pg_dump writes from its August 2025 minor releases on, 17.6 and 18 among them, are psql commands that declare nothing, and the reader skips them. Any other psql command is refused.

YugabyteDB reads a dump the same way, and ALTER INDEX ... RENAME TO as PostgreSQL does.

HCL, DBML, YAML and Go annotations have no spelling for a partition. A source in one of them compares the table as it declares it and claims nothing about its parent or bound. schema inspect and ptah introspect describe a partition in such a format as an ordinary table with its columns, and report that they left the rest out.

YugabyteDB takes the same statement and reads the same way. On every other target the reader refuses CREATE TABLE ... PARTITION OF by name, whatever the server would answer: MySQL, MariaDB and CockroachDB answer it with a syntax error.

A plan that removes a partitioned table and a partition of it drops the partition first, and a table that inherits another before that table. The reader takes these relationships from pg_inherits. A DROP TABLE of a partitioned table also removes a partition the run leaves out, because PostgreSQL drops the partitions with their parent without CASCADE. A table that inherits one the plan removes and that the run leaves out stops the drop at the server’s error.

Function changes

A function the database does not have yet is planned as CREATE FUNCTION, and a new procedure as CREATE PROCEDURE, so a plan says whether it creates a routine or replaces one. If a routine of that name and those argument types appears before the plan runs, the statement fails with SQLSTATE 42723 instead of overwriting it. The function Ptah writes for a trigger with a body follows the trigger: a new trigger creates it, and a changed trigger replaces it.

That function’s name is ptah_trigger_<table>_<name>: the table and the trigger name are each folded to lower case, every run of characters outside [a-z0-9_] collapsed to one _, and every _ that remains doubled before the two parts join on a single _. The doubling keeps the join unambiguous – a table a_b with trigger c and a table a with trigger b_c render as ptah_trigger_a__b_c and ptah_trigger_a_b__c rather than the same name. A table or trigger name that contains an underscore therefore renders under a different generated name; a database already holding that trigger’s function under the old name keeps it, unreferenced, until someone drops it by hand.

A changed function is planned as CREATE OR REPLACE FUNCTION where the server accepts that: a new body, language, security context, volatility, planner property, or setting. Views, policies, and triggers that call the function stay in place.

A changed return type or parameter list cannot be applied that way, so Ptah plans DROP FUNCTION followed by the create, in the migration and in its rollback. PostgreSQL refuses a changed return type with cannot change return type of existing function and a renamed parameter with cannot change name of input parameter (both SQLSTATE 42P13). A changed parameter type it accepts, and creates a second overload: without the drop, a declaration of one routine leaves two behind.

PostgreSQL discards type modifiers in routine arguments and results. RETURNS varchar(20) therefore compares equal to the catalog’s character varying, and numeric(10,2) to numeric. Ptah ignores these modifiers when comparing routines and keeps the authored declaration when rendering a real change. Column lengths and numeric precision still affect stored values and remain part of column comparison.

The drop names the function’s argument list, so an overloaded name loses only the overload that changed. A function that takes no arguments is named with an empty list, f(), which is what selects it among the overloads of its name; the same holds for a function the schema stops declaring, and for a procedure. A schema may declare several overloads of one name, as pg_dump writes them. Each is a routine of its own, told apart by its kind and its input argument types, as PostgreSQL tells them apart: f(a int) and f(a int, b text) are two functions, and f(b integer) is f(a int) declared again. The drop does not use CASCADE: when a view, policy, or trigger uses the function, the server refuses the drop with SQLSTATE 2BP01 and the migration stops, instead of removing an object the schema still declares. Measured on PostgreSQL 18.

The rollback of a routine the schema stops declaring creates it again as the database held it, with its settings, its properties, its comment and its privileges. PostgreSQL lets PUBLIC execute a new routine, so one whose privilege PUBLIC lost has it revoked again, and the privileges of every other role are granted again, grant option included. A plan whose removed routine the read of the database does not hold refuses the rollback rather than creating the routine without them.

A function with OUT or INOUT arguments may leave RETURNS out. PostgreSQL then records the type those arguments imply, and Ptah compares the declaration against that type: the type of the one such argument, or record for two or more. f(a integer, OUT b text) returns text, and f(a integer, OUT b integer, OUT c text) returns record.

A routine or DO body in a SQL schema file may be written in any quoting PostgreSQL takes: '...' with doubled quotes, E'...' with backslash escapes, or a dollar quote. Ptah reads the body as the server does, with the quoting undone, and writes it between dollar quotes the body does not contain: $$, or $ptah$ when the body holds $$.

Triggers

A SQL schema file declares a trigger with the statement pg_dump writes, and every form PostgreSQL 18 accepts is read: the events INSERT, UPDATE, UPDATE OF a column list, DELETE and TRUNCATE, joined by OR; FOR EACH ROW or FOR EACH STATEMENT, with EACH optional; a WHEN condition; and REFERENCING OLD TABLE and NEW TABLE for transition tables.

CREATE TRIGGER orders_touch BEFORE UPDATE OF total, status OR INSERT ON orders
  FOR EACH ROW WHEN (NEW.total > 0) EXECUTE FUNCTION touch_order();
CREATE TRIGGER orders_audit AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS before_rows NEW TABLE AS after_rows
  FOR EACH STATEMENT EXECUTE FUNCTION audit_orders();

The server keeps the events as flags and reports them in one order, INSERT, DELETE, UPDATE, TRUNCATE, so the first trigger reads back as INSERT OR UPDATE OF total, status. Ptah compares event lists in that order, so the order they are declared in does not matter. The columns of UPDATE OF keep their declared order on the server, and a different order is a different declaration. An unquoted column name is compared the way the server folds it, so UPDATE OF Total matches the column total.

The server does not keep the WHEN text either, and pg_get_expr cannot print it, because the condition reads both OLD and NEW. Ptah reads it from pg_get_triggerdef, which prints WHEN ((new.total > 0)) for the trigger above, and compares it the way a CHECK is compared: with a connection the declared condition is created on a temporary copy of the table and read back, and without one it is folded. CockroachDB 25.4 has no pg_get_triggerdef, so there a trigger is read without its condition; see Trigger conditions.

A change to the events, the condition or the transition tables is planned as CREATE OR REPLACE TRIGGER. The Go annotation //ptah:schema:trigger and a YAML trigger take the same clauses as when, old_table and new_table. Other targets refuse what they cannot create rather than render a different trigger. MySQL and MariaDB take one event and none of these clauses. SQLite takes one event, which may be UPDATE OF. SQL Server takes an event list without UPDATE OF, and Oracle takes both. None of them takes TRUNCATE, transition tables, or a PostgreSQL WHEN condition; SQLite and Oracle keep a WHEN of their own grammar in the trigger body.

schema inspect in HCL writes each event as an attribute of the timing block, so INSERT OR UPDATE is insert = true and update = true. It leaves out a trigger with UPDATE OF columns, a condition or transition tables, and says so, because the HCL trigger block has no attribute for them.

Changing a column type under a view

PostgreSQL refuses ALTER COLUMN ... TYPE on a column a view or materialized view reads, whether or not that view changes:

ERROR:  cannot alter type of a column used by a view or rule

When a plan retypes a column, Ptah drops the declared views and materialized views that read its table, directly or through another view, before the column step. It creates them again after it, in dependency order. A live view the declaration no longer holds is dropped at the same point, because the removal step comes after the column step. The rollback migration does the same in the other direction. See What a view rebuild keeps.

Changing a column to or from an enum

A column that becomes an enum is changed with ALTER COLUMN ... TYPE status USING s::text::status. The cast goes through text because every type a column can leave casts to text, and no cast leads from one enum to another. An array of an enum casts through text[], USING s::text[]::status[], and a domain over an enum takes the same cast as its enum. PostgreSQL has no automatic cast from the old default to the enum either, so a column with a default loses it before the TYPE clause and takes the declared one back after, if it declares one.

A column that leaves an enum, an array of one or a domain over one takes its declared default again after the TYPE clause. PostgreSQL converts the stored default to the new type and keeps the old type inside it, ('a'::status)::text, and refuses to drop the enum while that default uses it:

ERROR:  cannot drop type status because other objects depend on it
DETAIL:  default value for column s of table t depends on type status

The default is set again whenever the old type is not one of PostgreSQL’s own, which covers a composite or range type too.

Changing a materialized view

PostgreSQL has no in-place change for a materialized view, so a changed body is a DROP MATERIALIZED VIEW and a CREATE MATERIALIZED VIEW. The drop also removes every view and materialized view that reads it. Ptah drops the declared readers first, each after the objects that read it, and creates them again after the materialized view, in dependency order. A reader the declaration no longer holds is dropped at the same point. A view the plan adds that reads the materialized view is created after it, and so is a function the plan adds whose result type is such a view. The rollback migration does the same in the other direction.

Both rebuilds find a reader by the names its body uses, each part quoted or not: "reporting"."order_totals", "reporting".order_totals and reporting.order_totals all read the materialized view reporting.order_totals. A name with a schema reads only that schema’s object. A name without one reads an object of that name in any schema of the connection’s search_path, which is where PostgreSQL looks it up, and an object declared without a schema is in the default schema, the first schema of the path that exists. With public the path, a view that reads item_skus is not rebuilt when reporting.item_skus changes. A comparison made without a connection does not know the path, and a name without a schema then reads an object of that name in every schema.

The order a plan creates views in reads names the same way, but never guesses a schema: under search_path=app, a view that reads app.zbase is created after a zbase declared without a schema.

A materialized view declared WITH NO DATA is compared by its query, because the server does not store the clause. An unchanged declaration plans nothing. A plan that creates the view again creates it WITH NO DATA, so it is empty until a REFRESH MATERIALIZED VIEW. The read reports a view the server holds without rows, so the rollback migration creates such a view WITH NO DATA too.

View options and storage parameters

A view takes the options security_invoker and security_barrier, and a materialized view takes storage parameters such as fillfactor and autovacuum_enabled. A SQL schema file declares them in the WITH list before AS:

CREATE VIEW order_report WITH (security_invoker = true, security_barrier) AS
  SELECT id, amount FROM orders;

CREATE MATERIALIZED VIEW order_totals WITH (fillfactor = 70) AS
  SELECT sum(amount) AS total FROM orders;

With security_invoker, PostgreSQL checks privileges and row-level security policies as the user who queries the view, not as the view’s owner. PostgreSQL 15 and later have it; 13 and 14 refuse it with unrecognized parameter "security_invoker".

The declaration states every option the object holds. The comparison reads pg_class.reloptions and plans a change when the two differ, also for an option the database holds and the declaration leaves out: a materialized view declared without fillfactor is a materialized view without it, as a view declared without WITH CHECK OPTION is a view without a check option. A security option the view holds on is the exception, and refused. The Go annotations and YAML state them in options (Go annotations, YAML). HCL states a view’s options in the security block Atlas documents for them:

view "order_report" {
  schema = schema.public
  as     = "SELECT id, amount FROM orders"
  security {
    invoker = true
    barrier = true
  }
}

An HCL materialized block has no attribute for storage parameters, as Atlas’s has none. So an HCL schema states nothing about them: the comparison keeps the ones the database holds, and a materialized view the plan creates again gets them back. To change them, declare the view in SQL, Go or YAML.

A value is compared by what it means, not by how it is spelled. The server stores a value as written, so a database that holds security_invoker=on or fillfactor=070 matches a declaration of true or 70.

A change to the options alone keeps the object. The plan writes ALTER VIEW ... SET (...) for each option declared and ALTER VIEW ... RESET (...) for an option held off and left out, or ALTER MATERIALIZED VIEW ... SET and RESET for storage parameters, so a view keeps what depends on it and a materialized view keeps its rows. Where the plan writes the object anyway, as a CREATE OR REPLACE VIEW for a changed body or a CREATE after a drop, that statement states the declared options. The rollback migration gives back the options the database held.

Ptah refuses check_option in the list, because a view declares it with WITH CHECK OPTION, and a toast. option, which PostgreSQL keeps on the TOAST table rather than on the object and drops without a word when there is none.

The other engines of the PostgreSQL family differ:

• CockroachDB v26.2 and later take security_invoker and answer a syntax error to security_barrier and to storage parameters. v25.4 refuses both view options.
• YugabyteDB takes both kinds. It records a materialized view’s storage parameters, and its ALTER MATERIALIZED VIEW ... SET answers with a notice that it ignores them, since its storage layer does not use them. YugabyteDB 2024.2 refuses security_invoker, as PostgreSQL 14 does.

Every other target refuses a view or a materialized view that declares options.

A security option the declaration leaves out

When a view holds security_invoker or security_barrier with a true value and the declaration does not state that option, the plan refuses before the server changes. Removing the option fails no statement and widens what the view’s readers see, and a view altered by hand holds an option no schema states:

unsupported feature: view my_docs holds security_invoker=true and the schema does not state it. Without it the view runs with its owner's rights, not the rights of the user who queries it. Declare security_invoker=true to keep it, or security_invoker=false to remove it. An HCL schema states these options in the view's security block, as invoker and barrier

Declare the option to keep it, and the plan leaves the view alone. Declare it false to remove it, as security_invoker = false in the WITH list or options, or invoker = false in the security block. That plan runs as usual, and the reader’s rows change as they would for any other removal. The declaration may leave out an option held false, because removing it changes nothing a reader sees. A storage parameter plans as declared.

The refusal covers each statement that would lose the option: ALTER VIEW ... RESET, a CREATE OR REPLACE VIEW for a changed body, which clears what it does not state, and a drop and create. It is the same on schema plan, schema apply, schema compare and migrations generate, and on ptah-compat schema apply, schema diff and migrate diff. schema drift plans nothing, so it reports the difference under views_modified and does not refuse. A rollback migration is not refused either: a down file that removes an option its up file added restores the earlier state.

Under --json, schema plan and schema apply report the refusal as outcome: refused with the code view-security-option and a view_options list. See Refusal codes.

PostgreSQL 18, CockroachDB v26.3 and YugabyteDB 2026.1 give the same answer for a reader the row-level security policy limits to one row of two: one row through a security_invoker view, two once the option is gone. A materialized view cannot hold these options: PostgreSQL and YugabyteDB answer unrecognized parameter, and CockroachDB answers a syntax error.

What a view rebuild keeps

Three changes drop a view or a materialized view and create it again: a column type change under it, a change to a materialized view, and a view change CREATE OR REPLACE VIEW cannot make, such as a column that moves. Each object that depends on a dropped one goes with it:

• the views and materialized views that read it, directly or through another view;
• the routines whose parameters or result use its row type, such as RETURNS SETOF v or f(r v), the routines whose SQL-standard body, BEGIN ATOMIC ... END or RETURN, reads it, and the views that call such a routine.

The plan drops each of them by name before the column step, after everything that depends on it in the database, so no drop needs CASCADE for a dependent the schema declares. It creates them again after the view step, in dependency order, from the declaration. The drop order reads the definitions the database holds, and the creation order reads the declaration. So when two changed views swap which one reads the other, the plan first drops the one that reads the other in the database, and first creates the one the other reads in the declaration. Right after each object it creates again, it writes:

• the declared indexes of a materialized view, so REFRESH MATERIALIZED VIEW CONCURRENTLY finds its unique index once the migration commits;
• the declared triggers of a view, such as an INSTEAD OF trigger;
• the privileges the database held on the object, column privileges included, also those of a role the schema does not manage. A routine that PUBLIC could not execute has that privilege revoked again, since a new routine is executable by PUBLIC;
• the options its declaration states, in the WITH list of the CREATE; see View options and storage parameters.

A comment is part of each CREATE, and a routine keeps its language, volatility, SECURITY DEFINER, STRICT, LEAKPROOF, PARALLEL and SET settings, because it is created from its declaration. A routine is dropped without CASCADE.

A view the plan changes with CREATE OR REPLACE VIEW is not dropped. When its definition in the database reads a dropped object and its new one does not, the plan replaces it before the drops; when only its new one does, after the object is back. The view keeps its policies and privileges either way. Every CREATE OR REPLACE VIEW Ptah writes states the options the view’s declaration holds, because the replace sets the options it states and clears the others.

What a rebuild does not keep:

• the owner. The new object belongs to the role that runs the migration, since Ptah does not describe ownership. What the old owner of a view held as its owner is not granted back;
• an object that depends on a dropped one and that the schema does not declare, such as a view in a schema the run leaves out. The drop has no CASCADE, so the server stops the migration at the drop and names the object, rather than removing it. The plan drops a policy of a removed table before a view or column it reads, so the later drop also needs no CASCADE;
• a materialized view’s rows and tablespace. CREATE MATERIALIZED VIEW runs the query again and puts the view in the default tablespace.

The plan refuses a rebuild that drops an object a row-level security policy reads or calls, a view or a routine, and names the policy; see Row-level security.

The drops and creates run inside the migration. Each DROP and CREATE takes an ACCESS EXCLUSIVE lock on its object, so queries against a rebuilt view or materialized view wait until the migration commits, and CREATE MATERIALIZED VIEW computes the rows inside the migration, which takes as long as the view’s query. In a migration that runs without a transaction, other sessions see each statement as it commits: a rebuilt object is missing between its drop and its create, and REFRESH MATERIALIZED VIEW CONCURRENTLY fails until the unique index is back.

The plan check rates a DROP VIEW the plan follows with a CREATE VIEW of the same view a warning, as it rates a CREATE OR REPLACE VIEW: the server stops the drop if anything the plan does not rebuild reads the view. It rates a DROP VIEW ... CASCADE, which the plan writes only where it relies on it, and every DROP MATERIALIZED VIEW destructive although the plan creates the object again, so --check-destructive refuses those plans.

Materialized view refresh

Ptah does not refresh materialized views, and a declaration cannot ask it to. A refresh_strategy attribute is refused when the schema is parsed, on every dialect and in every frontend.

That is a boundary rather than a missing feature. REFRESH MATERIALIZED VIEW is a statement someone runs, and CONCURRENTLY is an option of that statement; neither is state the database holds, so nothing Ptah reads back could report a refresh policy or diff one. What Ptah does manage keeps the view current on its own terms: CREATE MATERIALIZED VIEW populates the view, and a changed body is reconciled as a DROP and a CREATE that populates it again, unless the declaration says WITH NO DATA. Measured on PostgreSQL 18.

The case that is left over is a view no schema change touched, going stale because its source data moved. Schema reconciliation cannot observe that, so a refresh emitted there would be an unbounded data operation attached to a migration on a relationship Ptah inferred. Issue it yourself, from whatever already knows when the data changed:

REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.user_counts;

CONCURRENTLY needs a unique index on the view, or the server answers cannot refresh materialized view "public.mv" concurrently.

ClickHouse is a different matter: an ordinary materialized view there is maintained by inserts into its source, and the scheduled form the server does own, REFRESH EVERY|AFTER, is engine-native DDL and is managed — see ClickHouse.

Roles and grants

//ptah:schema:role and //ptah:schema:grant declare roles and their privileges next to your entities. Ptah emits CREATE ROLE for new roles, ALTER ROLE for attribute changes, and GRANT/REVOKE as declared grants change. Grants target a table, a schema, a sequence, a function or a procedure; a view and a materialized view take the grants of a table, and both are read back. Grants are compared per individual privilege, so a privilege="SELECT,INSERT" list round-trips cleanly through introspection. A grant on a sequence is described with on_sequence, so the description compares equal to the database it was read from. That holds for the sequence a SERIAL or identity column owns too, such as GRANT USAGE ON SEQUENCE items_id_seq TO app: Ptah reads it back, plans it only when the database lacks it, and revokes it from a role the schema manages when the declaration leaves it out.

A plan refuses a grant on a table, a view or a materialized view it removes, with --schemas as without it: declare the object, or leave the grant out. --schemas compares the grants on every relation in a selected schema, including one Ptah does not read, such as a foreign table or the revision table. So a run with the flag grants and revokes there as a run without it does.

Grants between two databases

schema diff with a database as --to reads every grant that database holds, so a grant only --from holds is a difference, whoever holds it. The diff prints its REVOKE, for a role --to does not name and for PUBLIC, and the other direction prints the GRANT. A schema file as --to says nothing about a role it does not declare, so a grant to such a role stays out of the plan.

PUBLIC’s EXECUTE on a routine is not revoked to match a database that holds it by default, because PostgreSQL stores no ACL until an object’s first GRANT or REVOKE.

The same first statement writes out what a table’s, a view’s, a sequence’s or a schema’s owner holds, so relacl being NULL says nothing about the owner. A read reports what the owner holds apart from the grants, and a comparison plans nothing for it in either direction. A schema file that revokes one of those privileges plans the REVOKE, and one that grants it asks for what the owner already holds.

An owner that revoked a privilege from itself holds less, and that is a difference. Between two databases where the same role owns the object, one direction prints the REVOKE and the other prints the GRANT that gives it back. Ptah describes no ownership, so where another role owns the object on the other side, nothing is planned for either owner. The privileges of a server’s own release are the ones compared: PostgreSQL 16 has no MAINTAIN, so a difference in it against a later release plans nothing.

Owner privileges a schema file does not manage

Ownership and the owner’s privileges are separate. An owner may revoke its own INSERT on a table and still alter, drop or grant on it, because ownership is not an ACL entry. A schema file manages only the privileges it names:

• a GRANT ... TO the owner is reconciled, so a privilege the owner revoked is granted back;
• a REVOKE ... FROM the owner is reconciled, so the privilege stays revoked;
• a privilege the file names neither way is not managed, and Ptah plans nothing for it.

Declaring the table or its owner role does not make Ptah give the owner back what PostgreSQL gives a new table’s owner. A file that leaves the owner’s privileges out says nothing about them, and restoring them would overwrite a revoke somebody made on purpose.

What the owner lacks is still reported. Against a table whose owner ran REVOKE INSERT, UPDATE ON orders FROM app_owner, a file that declares orders and names no privilege of app_owner gets a synced plan and one line on standard error, from schema plan, schema apply and schema compare:

UNMANAGED: table orders: owner app_owner lacks INSERT, UPDATE, which PostgreSQL gives an owner by default; the schema does not manage them, so nothing is planned

The line is a diagnostic, not drift: it changes neither the plan nor the exit code. It reports the owner’s own ACL entry, so a privilege the owner reaches through PUBLIC or through a role it belongs to is still listed as missing. schema security reports the same object as OWN02 from the database alone, without a schema file, so it cannot tell a revoke a file declares from an accident.

To end the report, decide what the owner should hold and say it:

-- the revoke was an accident: give the privileges back and keep them managed
GRANT INSERT, UPDATE ON orders TO app_owner;

-- the revoke was deliberate: keep it managed
REVOKE INSERT, UPDATE ON orders FROM app_owner;

Either line in the schema file makes the privilege managed, and the next plan reconciles it. Running the GRANT once by hand ends the report too, without managing the privilege. OWN02 is a warning, so it fails schema security only with --fail-on any; there is no switch that hides one finding.

Role passwords

A role’s password may be a password or a hash PostgreSQL takes as one, such as a SCRAM-SHA-256$... string. Ptah never prints either. A statement that sets it names an environment variable where the password would stand: PTAH_SECRET_PASSWORD_ followed by the role’s name in upper case, with every character other than a letter or a digit written as an underscore.

CREATE ROLE "app" WITH LOGIN PASSWORD $PTAH_SECRET_PASSWORD_APP NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION;

That is what schema render, schema plan, schema compare, schema apply and its --dry-run and --json output, a saved plan, migrations plan and a generated migration file show, and what an error quotes. Ptah writes the password in only when it sends the statement:

• schema apply that plans from the schema and applies in one run sends the declared password and needs no variable;
• schema apply --plan, migrations up and a replay read the variable the statement names, so export it on the machine that runs them. A variable that is not set refuses the statement before anything is sent, and schema apply --dry-run refuses it too.

PostgreSQL refuses the statement as written, so a migration file run without Ptah sets no other password. Ptah writes a value only after PASSWORD in CREATE ROLE or ALTER ROLE, and refuses a statement that names such a variable anywhere else. Two roles whose names give one variable, such as App and app, are refused.

A password written as a reference, $PTAH_SECRET_ and a name in upper case such as $PTAH_SECRET_APP_PASSWORD, names that variable instead of holding a value. The statements name it, and every run reads it from the environment, so the schema holds no password at all. A SQL schema read from a migration file Ptah generated keeps the reference it holds. The same rules hold on YDB, where a user’s password is named the same way.

Whether a role has a password is in pg_catalog.pg_authid. A comparison that declares a password for a role that exists needs SELECT on it, which a superuser holds. For any other connecting role the comparison cannot tell a missing password from a set one, so it is refused at exit 2 rather than planning nothing:

cannot compare password for role "app": current password state is unknown: the read could not establish it, which on PostgreSQL and YugabyteDB takes SELECT on pg_catalog.pg_authid

A plan that removes a table revokes the privileges of the roles the schema manages and leaves the others to DROP TABLE. The rollback of that plan creates the table again and grants every privilege the database held on it, column privileges included, whichever role held it. The owner’s privileges are left out: the owner of the new table holds them without a grant.

Such a grant belongs to the column, not to the sequence’s name. Renaming a table or a column leaves its sequence’s name as it was, so a table created as items and renamed to products keeps items_id_seq, while its BIGSERIAL column creates products_id_seq on an empty database. Ptah matches a grant written with either name to the column. A description from schema inspect names the sequence the column creates, so it replays on an empty database, and a plan names the sequence the database holds.

A function or procedure is named with its argument types, because PostgreSQL tells overloads apart by them: on_function="purge_workspace(uuid)" in Go, GRANT EXECUTE ON FUNCTION purge_workspace(uuid) TO app in a SQL schema file. A name without the types is refused. The types are matched against the catalog without parameter names or OUT arguments, so purge_workspace(uuid) and purge_workspace(p_id uuid) name the same function.

On CockroachDB, grants are read from information_schema on every release line, because v25.4 and v26.2 leave the catalog’s ACL columns empty. The read leaves out privileges nobody granted: the built-in admin role and the root user hold ALL on every object and cannot lose it, and an owner holds ALL on what it owns. CockroachDB records no grantor, so a described grant names none.

A GRANT may name several objects and roles. Ptah records every object-role pair, including column privileges and WITH GRANT OPTION:

GRANT SELECT ON users, tenants TO app_reader, app_operator;
GRANT UPDATE (name) ON users, tenants TO app_operator;

For function or procedure lists, name the argument types of each routine so its overload is unambiguous. Role creation inside a DO block is not a schema declaration; declare roles with CREATE ROLE, or create them in your versioned migrations before a grant uses them.

Revoking privileges nobody granted

PostgreSQL gives some privileges without a GRANT: every role can execute a new function through PUBLIC, and ALTER DEFAULT PRIVILEGES hands a role its privileges on each new table. A revoke declaration says such a privilege must be absent, whoever holds it, the object’s owner included:

REVOKE ALL ON FUNCTION purge_workspace(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION purge_workspace(uuid) TO app;
REVOKE INSERT, UPDATE, DELETE ON plugin_signatures FROM app;

In Go the same declaration is //ptah:schema:revoke, in HCL a revoke block, and in YAML a revokes entry. Ptah plans the REVOKE whenever the database holds the privilege, including the implicit EXECUTE a function’s default ACL gives PUBLIC, and also in the same plan that creates the table or function, since the privilege arrives with the object. A revoke of a privilege the role does not hold changes nothing on the server, so the statement is safe either way.

PostgreSQL also creates every database with schema public, on which PUBLIC holds USAGE. schema diff --from <file> --to <database> with no --dev-url reads the file as the database a server builds from it, so the file’s side holds that privilege unless the file grants or revokes it itself. A file with REVOKE USAGE ON SCHEMA public FROM PUBLIC diffed to a database that holds the privilege plans the GRANT. PostgreSQL 14 and earlier give PUBLIC CREATE on public as well. The file’s side is read by the dialect, not by the release, so against those releases the diff plans GRANT CREATE ON SCHEMA "public" TO PUBLIC. A --dev-url on the same release compares the file with what that release creates.

ALL, in a grant or in ALTER DEFAULT PRIVILEGES, is compared with what the server reports, which is one row per privilege. A role holds ALL on a table when it holds SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES and TRIGGER. MAINTAIN, which PostgreSQL 17 added to ALL, is not required: the comparison does not know the server version, and PostgreSQL 16 has no MAINTAIN to report. A REVOKE of everything ALL names on a table the plan creates is planned as REVOKE ALL, because PostgreSQL 16 refuses the word MAINTAIN.

A SQL schema file is read as a script. GRANT and REVOKE compose in statement order, and the later statement about one privilege of one role on one object wins. Go annotations, HCL and YAML have no statement order, so declaring the same privilege both granted and revoked is refused.

In HCL a function or procedure is named by for and its argument types by the Ptah attribute args:

permission {
  to         = role.app
  for        = function.purge_workspace
  args       = ["uuid"]
  privileges = ["EXECUTE"]
}

ptah schema export --to hcl writes function.<name> or procedure.<name> where the document declares one routine under that name, and a quoted name otherwise, because the Atlas community CLI evaluates the reference before it ignores the block. In YAML a grant or revoke names the routine as the Go annotations do, on_function: purge_workspace(uuid).

ALTER DEFAULT PRIVILEGES ... REVOKE composes the same way with the ALTER DEFAULT PRIVILEGES ... GRANT statements before it. A revoke with no grant before it says the default privilege is absent, and Ptah revokes it wherever the database holds it for that grantor, schema, object type and grantee. In Go the same declaration is the revoked attribute of //ptah:schema:defaultprivilege, in HCL the revoked attribute of a default_privilege block, and in YAML the revoked key of a default_privileges entry.

For ptah-compat schema commands, excluding a schema, grantor, or grantee also excludes its default privileges. With --include, schema-scoped defaults remain only when --schema explicitly selects their schema; global defaults leave the selection. A retained default privilege keeps its declared schema and both named roles, even when no table selector retains them. Diagnostics about unread ACLs remain visible and do not add role declarations.

Global default privileges

A default privilege set without IN SCHEMA is the global default. It applies in every schema of the database, and each schema source declares it by leaving the schema out:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner GRANT USAGE ON SCHEMAS TO app_reader;

A schema-scoped default is only ever added to the global one, and the global one starts from the built-in default: the owner holds every privilege on the objects it creates, and PUBLIC can execute a new function and use a new type. A global revoke can take a built-in privilege away, so Ptah compares a global default with the built-in one:

• a declared global grant of a built-in privilege, such as EXECUTE on functions to PUBLIC, is already held unless the database revoked it;
• a declared global revoke of a built-in privilege is planned wherever the database still has it, a database with no default privileges included;
• a built-in privilege the database revoked and the declaration does not is granted back, when the grantor is a role the declaration manages or the declaration names that default.

SCHEMAS, and LARGE OBJECTS on PostgreSQL 18 and later, exist only in the global form, so a declaration that gives either one a schema is refused.

Reading a live database describes the schema-scoped defaults of each schema the read covers, the connection’s default schema included, and every global default whichever schemas the read covers. A global default is described as its difference from the built-in one.

The description leaves out a CockroachDB default set FOR ALL ROLES, which names no role, so no schema source can declare it. A description applied to another database therefore does not carry it, and ptah db read and schema inspect name each one on stderr, by object class, schema and grantor:

note: default privileges for ALL ROLES on TABLES in schema public are not fully described: FOR ALL ROLES has no named grantor in a schema declaration.

The SQL that ptah db read prints has a comment in place of each one. Go and HCL exports preserve unread ACL scopes as notdescribed directives. Reading an exported source keeps those scopes unmanaged. A known named role’s ACL remains independent of an unread FOR ALL ROLES ACL. Unknown catalog object classes retain their names in the diagnostic and the source limit.

ptah db drop-all revokes a CockroachDB FOR ALL ROLES default in the schemas it cleans, spelled FOR ALL ROLES.

CockroachDB’s SHOW DEFAULT PRIVILEGES lists privileges PostgreSQL does not have, and which of them ALL covers depends on the release. When a global default takes some of the owner’s own privileges away and leaves the rest, the read cannot say which, so it leaves the owner’s part out of the description and names it in the same note. REVOKE ALL and GRANT ALL converge from there whatever the owner holds, and a comparison plans one of them when the declaration asks for it, or GRANT ALL when the declaration says nothing about the owner of a role it manages. Any other change to the owner’s part is withheld and reported as undecided, so on CockroachDB a declared revoke of part of the owner’s own privileges is applied once and reported as undecided after that.

CockroachDB v26.2 refuses every read of pg_default_acl once a default privilege names a role whose name needs quoting, such as one with a dash. On such a database ptah db read and schema inspect describe no default privilege and say so in a note on stderr, and a comparison neither plans a declared default nor revokes one it could not read. ptah db drop-all asks CockroachDB’s SHOW DEFAULT PRIVILEGES for the defaults it revokes, so it cleans such a schema too.

A privilege can be limited to columns of a table: GRANT UPDATE (state, decided_at) ON proposals TO app. Each column is compared on its own against pg_attribute.attacl, and a column privilege and the table privilege of the same name are two privileges, as they are in the catalog. A REVOKE of the table privilege also takes it off every column, as PostgreSQL does, so

REVOKE UPDATE ON proposals FROM app;
GRANT UPDATE (state, decided_at) ON proposals TO app;

leaves app able to update those two columns and no other, and Ptah plans the REVOKE before the GRANT. In Go the column list is the columns attribute of //ptah:schema:grant and //ptah:schema:revoke, and in HCL the columns attribute of a permission or revoke block.

Some forms are refused rather than approximated, each with a message that names the form:

• a statement whose privileges name different column lists, such as GRANT SELECT (a), UPDATE (a, b) ON t: write one statement per list;
• a column REVOKE of a privilege the file grants on the whole table, which covers every column and which a column revoke does not take away;
• ON ALL TABLES IN SCHEMA, which applies to whatever objects exist when it runs;
• REVOKE ... CASCADE, REVOKE ... GRANTED BY and a list of grantees;
• REVOKE GRANT OPTION FOR, on an object or in ALTER DEFAULT PRIVILEGES, on a privilege the same file does not grant;
• a REVOKE of one privilege after GRANT ALL on a table, because the members of a table’s ALL depend on the server version. Name the privileges in the GRANT instead.

A privilege on a function or procedure is modeled for PostgreSQL only; the other renderers refuse the statement by name. Column privileges are modeled for PostgreSQL only too.

New-role SQL fails closed when the role already exists, so later comments and grants cannot be applied to a role with unverified security attributes. Role descriptions are applied with COMMENT ON ROLE after successful creation.

Ordering is dependency-aware: roles are created before the functions and policies that reference them, and grants are emitted after the roles and target objects exist.

A function or procedure is created as early as its definition allows. PostgreSQL resolves a routine’s parameter and return types when the routine is created, and a LANGUAGE sql body too, including a SQL-standard RETURN or BEGIN ATOMIC body. So a routine follows the types, tables, views and columns its definition names when the same plan creates them, and a view that calls the routine follows it. A PL/pgSQL body is read when the routine first runs, so it does not hold the routine back. A routine that names nothing the plan creates comes first, where a domain, a column default, a policy or a trigger can call it. ptah schema render places routines by the same rule, treating everything it declares as created.

A name counts with or without its schema: a table declared as app.orders is named by app.orders and by orders, and one declared without a schema is also named by public.orders. CockroachDB and YugabyteDB resolve a bare name in public too, and the same rule holds there.

A column default or a CHECK that calls a LANGUAGE sql routine reading another new table gets the one order PostgreSQL accepts: the table the routine reads, then the routine, then the table that calls it. The same holds for a column added to an existing table. A routine that reads the very table whose default or CHECK calls it has no such order, and PostgreSQL refuses it in either order; write that routine in PL/pgSQL.

Reading a live database describes only the roles the schemas being read actually use, because a PostgreSQL role belongs to the cluster rather than to one database. A role counts as used when it holds a privilege on a relation, a column or a routine in those schemas or on one of the schemas themselves, when it granted one, when a default privilege in them names it, or when a row-level security policy on a table in them applies to it. A role that merely exists elsewhere on the server is not part of the schema being described, so it is left out — of ptah db read and of ptah-compat schema inspect alike.

The rule is exact in both directions: a description defines a role when some other statement in it names that role, and not otherwise. Ownership alone does not qualify, because Ptah describes no ownership — it writes no OWNER TO and no CREATE SCHEMA ... AUTHORIZATION — so an owner would be created and then never referred to.

For the same reason, a description states privileges somebody granted rather than the privileges an owner holds by default, whether pg_class.relacl is NULL or spells them out: replaying CREATE TABLE gives the new owner exactly those privileges again. A privilege the owner revoked from itself is described as a REVOKE, and the owner is a role such a description names.

Leaving a role out of a description does not mean Ptah thinks it is missing. Which roles a schema uses and which roles Ptah manages on the server are separate questions, and comparison asks the second one: a managed role that already exists anywhere in the cluster is never planned as a CREATE ROLE, whether or not the schema you are reading refers to it. Declare such a role in your entities and Ptah still applies ALTER ROLE when its attributes drift, so comparison is unchanged in both directions. Roles outside the described scope are used for that comparison only — they are never written to any output.

Scoping the description does cost one thing, and it is not comparison: a description no longer reproduces a cluster’s ungranted roles somewhere else. Measured on PostgreSQL 17.10, on a database holding one table and roles nothing grants anything to, ptah db read went from four CREATE ROLE statements to none, and ptah-compat schema apply --dry-run against an empty database in a second cluster went from planning three of them to planning none. Copying one cluster’s roles into another is something you could do before, so it stays available on the same commands rather than being removed:

# Describe every role Ptah manages on the server, not only the ones in scope.
PTAH_POSTGRES_INSPECT_ALL_ROLES=1 ptah db read --db-url "$PG_URL"
PTAH_POSTGRES_INSPECT_ALL_ROLES=1 ptah-compat schema inspect --url "$PG_URL"

The variable widens the description only. Both reads still happen, so comparison sees exactly the same set of existing roles either way and turning it on can never plan a CREATE ROLE for a role that is already there. It is an environment variable rather than a flag because ptah-compat registers exactly the flags the Atlas community CLI registers. Reserved roles stay out of the widened read too, for the reason the next paragraph gives.

You are never left to infer the omission. When a read leaves roles out, Ptah says so on standard error, alongside the schema on standard output:

note: 4 roles Ptah manages on this server are not described, because nothing in the inspected schemas refers to them; comparison still treats them as present, so none of them is planned as a CREATE ROLE. Set PTAH_POSTGRES_INSPECT_ALL_ROLES=1 to describe every role Ptah manages.

The note reports a count and never the names: on a shared instance those names belong to other tenants, which is half the reason the description is scoped at all.

A scoped description is also a replayable one, which is the point of scoping it at all. Feed ptah-compat schema inspect output straight back in against a clean sibling database on the same server and it materializes at exit 0, and the document that comes back is the one that went in. The roles a scoped description names are precisely the roles the server already has — they hold privileges on the inspected tables — so the dev database is not given them again; they are left exactly as the server has them, never altered, and named on standard error. A role the server does not have is still created there, so the same document also materializes on a server that has never seen it.

Reserved roles sit outside that rule in both directions. Ptah manages neither the pg_ roles nor the bootstrap postgres superuser: it never describes them and never compares them, so a declaration naming one would be compared against nothing and planned as a CREATE ROLE the server refuses. Ptah refuses the declaration instead, before anything is compared or planned:

Error: compare database schema: invalid schema diff: desired schema declares reserved PostgreSQL role "pg_monitor" (PostgreSQL reserves the "pg_" prefix for system roles and refuses CREATE ROLE at SQLSTATE 42939); Ptah manages reserved roles in neither direction, so the declaration is compared against nothing and would be planned as a CREATE ROLE the server refuses; rename the role, or set PTAH_ALLOW_RESERVED_ROLE_NAMES=1 to plan it anyway

The two names fail for different reasons and both are covered: the superuser because it already exists (role "postgres" already exists, SQLSTATE 42710), and a pg_ name because the prefix is reserved (role name "pg_monitor" is reserved, SQLSTATE 42939). The prefix is matched literally, so pgbouncer, pgadmin and pgpool are ordinary roles and are planned as before.

A grant to a pg_ role is read and compared like any other, so GRANT SELECT ON t TO pg_monitor is planned once. A source cannot declare the role, so Ptah never revokes a grant it holds. The rights pg_database_owner holds on public, the schema it owns on a stock database, are not read as grants, because no statement declared them.

One thing the refusal costs, so it stays reachable rather than being removed: on a cluster bootstrapped under another name, CREATE ROLE "postgres" succeeds. Measured on PostgreSQL 17.10, a cluster whose superuser is admin accepts the statement and the role appears in pg_roles.

# Plan a declared reserved role anyway, as Ptah did before the refusal existed.
PTAH_ALLOW_RESERVED_ROLE_NAMES=1 ptah-compat schema apply --dry-run --url "$PG_URL" --to file://schema.hcl --dev-url "$PG_DEV_URL"

The variable changes only whether Ptah refuses first or the server does. The reads are untouched, so a pg_ name still fails at the server whatever you set it to. It is an environment variable rather than a flag because ptah-compat registers exactly the flags the Atlas community CLI registers.

Both variables on this page are booleans and read the same way: unset selects the default described above, a valid boolean is honored, and anything else fails the command before it reads or compares anything — including on a run that declares no reserved role and leaves no role out, which is what makes a typo visible before the day it would have mattered. See Boolean environment variables.

Caution

Ptah never drops a role automatically. A role that disappears from the desired schema stays in the database, because roles may be shared with DBAs, infrastructure, or other applications; remove one manually with DROP ROLE when you are certain it is safe. REVOKE is narrower: Ptah revokes only privileges attached to roles that are still declared in the desired schema, and privileges a revoke declaration names.

Do not put plaintext passwords in password attributes. Ptah recognizes common encrypted formats (MD5, SCRAM-SHA-256, bcrypt, SHA-256/512) and adds a warning comment to generated SQL when a value looks like a plaintext password.

Row-level security

//ptah:schema:rls:enable switches RLS on for a table and //ptah:schema:rls:policy declares each policy, including the roles it applies to and its USING/WITH CHECK expressions. Policies are created after the roles they reference, so a role and the policy that uses it can land in the same migration.

Enablement is compared against pg_class.relrowsecurity and planned in both directions: a table your schema declares that the database has row-level security off for gets ALTER TABLE ... ENABLE ROW LEVEL SECURITY, and a table the database secures that your schema does not declare gets the matching DISABLE. A table that is being dropped is not disabled first. A table that declares a policy without declaring enablement is enabled with the table, because CREATE POLICY on a table whose row-level security is off protects nothing.

Two attributes select the stronger posture of each pair, and both are read back from the catalog so a comparison can see them. force="true" on the enablement adds ALTER TABLE ... FORCE ROW LEVEL SECURITY, read back from pg_class.relforcerowsecurity; without it the table’s owner reads and writes past every policy on it. as="restrictive" on a policy renders AS RESTRICTIVE, read back from pg_policy.polpermissive. Permissive policies are OR-ed with each other and restrictive ones AND-ed over the result, so a row passes when some permissive policy admits it and every restrictive one does. PERMISSIVE is the server’s default and stays out of the rendered statement. A SQL schema file writes the same two flags as ALTER TABLE ... FORCE ROW LEVEL SECURITY and CREATE POLICY ... AS RESTRICTIVE.

FORCE is compared on its own as well. A table that stays enabled while your schema adds or drops FORCE gets ALTER TABLE ... FORCE ROW LEVEL SECURITY or ALTER TABLE ... NO FORCE ROW LEVEL SECURITY. FORCE survives DISABLE, so a table that is enabled again without it also gets NO FORCE.

The server rewrites a policy’s clauses when it stores them, so a comparison needs the server’s spelling of what you declared. Ptah asks by creating the policy on a temporary copy of the table, in a transaction it rolls back. CockroachDB refuses that, as can a read-only session. A clause there is compared by what it means: a type annotation, current_user written with or without (), the table’s own name as a prefix, and the parentheses around a condition do not count as a change, and a different column, constant, operator, grouping or operand order does. A clause this cannot reconcile, such as n = 1 + 1, which CockroachDB stores as n = (1:::INT8 + 1:::INT8), plans ALTER POLICY on every run. Declaring it as the server stores it plans nothing.

Changes to a policy’s roles, or additions and edits of its USING and WITH CHECK clauses, use ALTER POLICY. This step needs no transaction and preserves the policy’s identity and comment. A comment change gets its own COMMENT ON POLICY, including IS NULL to clear it. Changing the command or the permissive/restrictive composition, or removing either clause, requires DROP and CREATE in one transaction.

A migration creates a policy after the views, the role changes and the enablement it may name. It drops a policy your schema no longer declares ahead of its column changes, because PostgreSQL refuses ALTER COLUMN ... TYPE on a column a policy names (cannot alter type of a column used in a policy definition). A restrictive policy replaced by another waits until the new one is created, so the table never admits more than it does at either end, unless it names a column the plan retypes.

A policy that names a column the plan retypes is rebuilt around that change, whether your schema keeps it, changes it or removes it, and whether it sits on that table or on a table that reads it: the plan drops it ahead of the column step and creates it from your schema after, with its comment. The test reads the names in its expressions. An expression that reads the column through the whole row of the table names no column, so the server’s refusal stands there. The DROP and the CREATE run inside the migration. In a migration that runs without a transaction, other sessions see each statement as it commits, as they do for a rebuilt view: between the two the table has no such policy, so a permissive policy admits fewer rows than either end, and a restrictive one admits more.

Two other changes run before a policy is touched, and PostgreSQL refuses them while a policy uses the object they change:

• a function change the server cannot make in place, which is a drop and a create (see Function changes);
• a domain or composite type that is dropped and created again (cannot drop type ... because other objects depend on it).

A migration that combines one of them with that policy stops at that statement. Inside a transaction, nothing the migration holds is applied; a migration that runs without one keeps the statements that ran before it. Change the policy in one migration and the function or the type in the next. Measured on PostgreSQL 17.

A plan that drops a view or a materialized view a policy reads is refused, and so is one that drops a routine a policy calls. The drop would remove the policy, or, without CASCADE, the server would refuse the drop, and the plan does not create the policy again. That covers a view change CREATE OR REPLACE VIEW cannot make (stokaro/ptah#4305), a column type change under the view (stokaro/ptah#4965), a change to a materialized view the view reads (stokaro/ptah#4964), and a routine that uses the row type of such a view (stokaro/ptah#4980). The error names the policy and the change the drop is for. Change the policy so it no longer reads the view or calls the routine in one migration, and make the change in the next. A view the plan replaces in place is not dropped and does not cause a refusal; see What a view rebuild keeps.

A policy reads a view by the names in its expressions, as a rebuild reads one. A name with a schema reads only that schema’s object. A name without one may read the view in any schema of the connection’s search_path, because PostgreSQL prints a policy’s expression without the schema of every relation the path reaches. With public the path, a policy that reads reporting.item_skus does not stop a plan that rebuilds item_skus, and a policy that reads item_skus does not stop a plan that rebuilds reporting.item_skus. Under search_path=reporting,public, a policy that reads item_skus stops a plan that rebuilds either of them, since it may read both: a path that names more schemas refuses more plans. A server that does not report its search path is read as one that names every schema.

Both ways of taking the protection away are lint findings at error severity: DISABLE ROW LEVEL SECURITY is DS109 and NO FORCE ROW LEVEL SECURITY is DS111P. ptah migrations up refuses a pending migration that holds either statement until you rerun it with --allow-destructive, and plan and generate --check-destructive refuse a plan that disables row-level security or removes FORCE. See Lint and gate unsafe SQL.

A policy with no TO clause applies to PUBLIC, and the catalog reports it that way, so an omitted TO, TO PUBLIC and TO public compare as one policy. The comparison reads the role list as a set: the order of the roles and the spacing between them do not count, and a list that names PUBLIC beside other roles is PUBLIC, since every role is a member of it. A declaration that names a role where the database has PUBLIC, or the other way round, is still a change. CREATE POLICY leaves an omitted TO clause out. ALTER POLICY writes TO PUBLIC for it, because omitting TO there keeps the existing role list. CockroachDB and YugabyteDB report roles the same way.

Which table a policy belongs to is decided under the target’s identifier rules rather than by spelling, so a policy declared on orders and a table created as public.orders are one table and the enablement is emitted once. Matching them as plain strings left the CREATE POLICY in the plan without its ENABLE, and the migration reported success while the policy sat inert on an unprotected table.

A policy name is scoped to its table rather than to the schema, which is what PostgreSQL itself enforces: CREATE POLICY tenant_isolation succeeds on two tables in one schema and is refused only when repeated on the same table. Ptah identifies each policy by its table and its name together, so two tables can each carry a tenant_isolation policy and both are rendered, compared, and migrated independently.

The owning table is that table’s identity rather than the string you spelled it with. A table declared without a schema is reached both as orders and as public.orders, and PostgreSQL treats those as one table: declaring p on each spelling is one policy declared twice, and the second CREATE POLICY is refused with policy "p" for table "orders" already exists. Ptah keeps the first declaration and renders it once. Two tables of the same name in different schemas — tenanta.orders and tenantb.orders — remain two tables, and a policy name on each remains two policies.

Letter case is the other spelling that reaches one table, and a SQL schema file follows PostgreSQL’s rule for it. Read with --dialect postgres, every unquoted name folds to lower case the way the server folds it: tables, columns, indexes, constraints, policies and the table any statement names. A quoted name keeps its case. So CREATE TABLE Orders declares orders, and ALTER TABLE ORDERS ENABLE ROW LEVEL SECURITY and CREATE POLICY p ON orders name that same table. A database the file created directly, through psql or another tool, compares equal to the file.

CREATE TABLE "Orders" declares a different table, Orders. A schema that declares only "Orders" and then writes ON orders names a relation it does not declare, and the render keeps that name rather than guessing: it reproduces PostgreSQL’s own relation "orders" does not exist instead of moving the policy onto Orders. Declaring both orders and "Orders" gives two tables, and a policy on each is two policies.

Extensions

//ptah:schema:extension manages CREATE EXTENSION. Some extensions are pre-installed and should not be migration-managed, so Ptah keeps an ignore list, with plpgsql ignored by default. An ignored extension is neither created nor dropped, even when your schema declares it, and never appears in a diff. Embedders can replace or extend the ignore list through the Go API — see Reusable components.

Set the annotation’s schema attribute, or the equivalent HCL/YAML field, to install an extension outside the default schema. Ptah creates the schema first and renders CREATE EXTENSION ... WITH SCHEMA ...; live inspection preserves that placement, so a second comparison is synced. Moving an existing extension to another schema plans ALTER EXTENSION ... SET SCHEMA, after creating that schema when it is new. An extension the server marks as not relocatable, such as plpgsql, is refused before SQL is emitted, because the server refuses the move too. A declared version the extension does not have plans ALTER EXTENSION ... UPDATE TO; a lower one is refused, because an extension updates in one direction only. For the same reason a generated migration’s down file does not take the version back: it says, in a Recovery limit comment, which version the extension keeps.

//ptah:schema:extension name="pgcrypto" schema="extensions" if_not_exists="true"
type PostgreSQLExtensions struct{}

An extension declaration that names no target reaches every target. A target outside the PostgreSQL family leaves it out and says so in one line, in a render, in a plan and beside a schema apply result:

-- MYSQL: PostgreSQL extension pgcrypto is not supported by this target; skipped.

Add dialects="postgres,yugabytedb" to a Go annotation to keep an extension off the other targets. YAML and HCL declarations carry no scope.

A scope is the author naming targets, so it is held to what they can host. A Go declaration whose dialects names a target outside the PostgreSQL family is refused, and so is a CREATE EXTENSION in a SQL document read for one:

CREATE EXTENSION pg_trgm: an extension is a PostgreSQL object, and mysql refuses the statement

MySQL refuses the statement too, and an Atlas-compatible schema apply runs the document on the dev database first, where it fails the same way.

Two Go packages, or a Go package and a YAML file, may each declare the same extension. Two equal declarations are one extension, and two that differ in any attribute are refused, naming the extension. A SQL schema directory runs its files in order, as the server would. A later file may declare an extension an earlier file declared only with IF NOT EXISTS and the same definition, IF NOT EXISTS included; any other second declaration is refused. COMMENT ON EXTENSION in a later file sets the comment of the extension an earlier file created.

Standalone sequences

PostgreSQL creates an implicit sequence for every SERIAL column and identity column; you do not declare those, and Ptah’s introspection deliberately excludes them, so a plain SERIAL column never produces a spurious diff. A column the database holds as SERIAL and the declaration writes as a plain type is a difference: the plan drops its default and its sequence. A grant on one is still read and compared; see Roles and grants. A standalone sequence — declared with //ptah:schema:sequence, typically to share one number generator across tables — is a first-class object with the full lifecycle.

A plain column the declaration writes as SERIAL is a difference too. The column takes a sequence, the default and the owner, then a setval that moves the sequence past the highest value the table holds, so the next insert does not collide with a row. On CockroachDB, where a SERIAL column draws from unique_rowid(), it takes that default.

Ordering matters twice: a sequence consumed by a column default is created before the table that uses it, while an owned_by="table.column" association requires the table to exist, so Ptah emits it as a separate ALTER SEQUENCE ... OWNED BY after table creation — the same ordering pg_dump uses. Options you leave unset follow PostgreSQL defaults and are never reported as drift.

User-defined types

Domains, composite types, and range types are declared with //ptah:schema:domain, :composite, and :range. They are created after extensions and enums but before tables, except for a type over a table’s row type (see below), and their drops are classified as destructive by the safety gate. Reconciliation is deliberately conservative:

• A domain’s check and default are compared through the server. PostgreSQL rewrites both on read-back, so against a live database Ptah asks the server to normalize the declaration and compares that with the catalog. A changed CHECK plans ALTER DOMAIN ... DROP CONSTRAINT and ADD CONSTRAINT ... CHECK, which names the new constraint so the rollback can drop it, and a changed DEFAULT plans ALTER DOMAIN ... SET DEFAULT. A declaration that leaves either out does not remove the one the database holds.
• A domain nullability change uses ALTER DOMAIN ... SET NOT NULL or DROP NOT NULL. A base-type change uses a non-CASCADE drop and recreation. PostgreSQL refuses the drop while a column uses the domain. A domain’s NOT NULL belongs to the domain: a column typed by it reads as NOT NULL only when the column states it.
• A composite type’s attributes are compared in order, by the name the catalog stores, so a quoted "N" and n are two attributes.
• Range types match by schema and name. Ptah compares the subtype, comment, and optional attributes that the declaration states or explicitly clears. A comment-only change uses COMMENT ON TYPE. Other changes recreate the range. SQL, HCL and Go annotations preserve quoted references and their schemas.
• Go and HCL exports mark unknown range coverage as unmanaged. Reading an export does not authorize removal of ranges the source did not inspect.
• Within the group the three kinds are ordered by what their definitions name, not by kind: CREATE TYPE addr AS (...) precedes CREATE DOMAIN d AS addr, and CREATE DOMAIN qty AS integer precedes a composite with a qty field. Both directions occur, and PostgreSQL has no forward declaration for a type.
• A type may name a table’s row type: CREATE TYPE wrap AS (r t, n int), a domain over a table’s row type, or a type that names such a type. When the same plan creates the table, Ptah creates the type after the tables and columns. When the same plan drops the table, Ptah drops the type before the tables, and a removed table or routine that uses the type before it, so the table’s drop does not rely on CASCADE. A render and a rollback are planned the same way.
• Ptah refuses a plan or a render that no order serves, and names the type, the relation and the object that uses the type: a table, a column or a routine the plan creates that uses a type over a created table’s row type, a type over the row type of a view the plan creates, and such a type that has to be dropped and created again.
• The drops a recreation emits are ordered by the shape the database holds, which is a different graph from the one above and does not have to agree with it. A DROP runs against the current schema, so only a reference that schema carries can block it; when the change is what moves the reference, the create order and the drop order are not mirror images. Both orders come out of the same plan.
• Extension-owned domains, composites and ranges are omitted from the schema. CREATE EXTENSION creates them. Declaring them independently causes a duplicate-object error. Ptah reads ownership from pg_depend; a similar name does not make a user type part of an extension. Extension-owned functions follow the same rule.
• Extension-owned tables, views, materialized views and sequences are omitted too, such as a view of pg_stat_statements or PostGIS’s spatial_ref_sys, along with their indexes, constraints, triggers and policies. The server refuses to drop them while the extension stands. Grants on them are still compared.

Unique indexes and access methods

Of PostgreSQL’s built-in access methods, only btree builds a unique index. A unique index declared with hash, gin, gist, brin or spgist in any schema source, such as CREATE UNIQUE INDEX ... USING hash in a SQL file, is refused when Ptah renders or plans the schema, and the refusal names the index. Applied, PostgreSQL would refuse the statement with access method "hash" does not support unique indexes.

Statements that run outside a transaction

PostgreSQL refuses some statements inside a transaction block whatever else the block holds: VACUUM, REINDEX ... CONCURRENTLY, REINDEX of a schema, a database or the system catalogs, CREATE DATABASE and DROP DATABASE, ALTER DATABASE ... SET TABLESPACE, ALTER SYSTEM, CREATE TABLESPACE and DROP TABLESPACE, and DISCARD ALL. A migration that holds one runs in a file marked -- +ptah no_transaction (-- atlas:txmode none in an Atlas directory). In a file the migrator wraps in a transaction, the migrator refuses the file before any of its statements runs and names the statement, rather than leaving PostgreSQL to refuse it part way through. A golang-migrate file whose one statement is among them runs without a transaction, as golang-migrate sends it. CLUSTER without a table is refused too, and is not recognized here: only the statement as a whole tells it from CLUSTER of a table.

Concurrent index creation

CREATE INDEX CONCURRENTLY avoids blocking writes but cannot run inside a transaction block. With diff.concurrent_index: true in ptah.yaml (Configuration), migration generation emits CONCURRENTLY for new indexes on populated tables and pairs the file with -- +ptah no_transaction so the migrator runs it outside a transaction. The Atlas-compatible migrate diff (in the ptah-compat binary) with diff.concurrent_index.create in atlas.hcl tags such files with the Atlas -- atlas:txmode none directive instead (Atlas migrate commands); the migrator honors both directives.

A declared CONCURRENTLY is honored

The paragraph above is the heuristic: Ptah choosing CONCURRENTLY for you. A desired state can also ask for it directly, and that request is carried through to the plan:

CREATE INDEX CONCURRENTLY idx_widget_a ON widget (a);

ptah schema diff and ptah schema apply plan that index with CONCURRENTLY, on a PostgreSQL-family target that has the capability, without any diff.concurrent_index setting. The apply path refuses the statement inside a transaction with an actionable message rather than failing at the server, so --tx-mode none is the answer it names.

Two limits bound it, and they are the same two the heuristic uses:

• A partitioned parent is excluded, not refused. PostgreSQL has no concurrent index form for relkind 'p', and refusing would leave a project with a partitioned table unable to plan an index change at all.
• diff.concurrent_index.create = false turns it off. An explicit false is an instruction, and a description does not overrule it. Leaving the setting out is not the same answer: it leaves the description in charge.

The heuristic’s “table already holds rows” test does not apply here. That test exists because the heuristic is guessing what you would have wanted; a declaration is not guessing, so an index declared concurrent on an empty table is still built concurrently.

What “populated” means when the database cannot say

The decision reads pg_class.reltuples and pg_stat_all_tables.n_live_tup, and PostgreSQL reports no row statistics at all in more situations than it reports zero rows: reltuples is -1 until something vacuums or analyzes the relation, and n_live_tup reads 0 after the cumulative counters are reset — which happens on a crash-recovery restart, on pg_stat_reset(), and for a table restored into a fresh cluster. A table holding millions of rows reports exactly what an empty one reports.

Missing statistics alone would be a poor test, because reltuples = -1 is also the state of every table that has never had a row inserted — so reading it as “row count unknown” would put every freshly created table into a non-transactional migration of its own. Ptah asks the file system instead: pg_relation_size reports the table’s main fork, which is not reset by an analyze, by a counter reset, or by a restore.

• No statistics and no storage is an empty table. It gets the plain, transactional CREATE INDEX.
• No statistics but storage in use is a row count Ptah genuinely does not know, and it counts as populated — the non-blocking build. Failing the other way was the wrong direction: a blocking CREATE INDEX immediately after a bulk load or a restore held writes for the length of the scan.

Running ANALYZE on the table before generating gives the decision a real number in either case. A table that does not exist in the database yet is a separate case and stays transactional — the migration creates it, so it starts empty.

Partitioned parents

PostgreSQL has no concurrent index statement for a declaratively partitioned parent: both CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY are refused on relkind = 'p' with SQLSTATE 0A000, and they are refused at execution time — after the migration file, its checksum and its commit exist.

Ptah reads the partitioned flag from the catalog (information_schema reports a partitioned parent as an ordinary BASE TABLE, so it cannot carry this) and handles the two cases differently:

• The populated-table heuristic excludes a partitioned parent and generates the plain, transactional statement. Nothing asked for a concurrent build, and the plain form is legal SQL that ptah migrations lint still reports as a blocking build: as PG108, which names the sequence below, when the same migration creates the parent or --dev-url shows it partitioned, and as PG101 otherwise.
• An explicit diff.concurrent_index.create / diff.concurrent_index.drop fails generation before any file is written, naming the index and the partitioned table. Silently downgrading an explicit request would hand a project that asked for a non-blocking build a blocking one without saying so.

To build a partitioned index without locking every partition at once, use the documented sequence by hand: CREATE INDEX ... ON ONLY the parent, then CREATE INDEX CONCURRENTLY on each partition, then ALTER INDEX ... ATTACH PARTITION. The parent index stays indisvalid = false until every partition is attached, and Ptah’s migration guards recognize that shape rather than treating it as failed-build residue. The lint reports none of these steps: ON ONLY on a partitioned parent creates the parent’s index alone and reads no row, measured at 11 ms beside a 5.3 s build of the whole index on PostgreSQL 18.6.

The index copies a partitioned parent creates

Creating an index on a partitioned parent makes PostgreSQL create one copy of it on every partition, under a name the server chooses (events_2026_tenant_idx). Those copies are attached to the parent index and cannot be dropped on their own:

DROP INDEX "events_2026_tenant_idx";
-- ERROR:  cannot drop index events_2026_tenant_idx because index idx_events_tenant requires it

A desired state written against the parent never names them, so a comparison that reads them as ordinary indexes plans exactly that refused statement — and only on the second generate, because the copies do not exist until the first migration has been applied. Ptah reads the attachment from pg_inherits and plans neither a create nor a drop for an attached copy; the parent index is where the plan acts.

The attachment is the test, not the name and not the table. An index created on a partition directly is still managed, including one that happens to carry the name PostgreSQL would have generated for a copy, and a copy attached under a name of your own choosing is still left alone.

Concurrent index removal

DROP INDEX CONCURRENTLY is the matching non-blocking removal, and it carries the same restriction: it cannot run inside a transaction block.

The rollback of a concurrent index build always uses it where the target supports it, so the down file undoes a non-blocking build without taking the write lock the build avoided. For the up direction it is opt-in: diff.concurrent_index_drop: true in ptah.yaml, or diff.concurrent_index.drop = true in atlas.hcl, requests it for standalone index removals. An index that is dropped and recreated under the same identity is a redefinition rather than a standalone removal and keeps the blocking drop the planner pairs with the rebuild.

Migration locking

Migration runs serialize through a session-level advisory lock (pg_advisory_lock, lock name ptah_migrate), so two concurrent ptah migrations up runs against one database cannot interleave. Timeout flags for the lock are on Apply migrations.

Transaction poolers

A PostgreSQL advisory lock belongs to a session. A transaction pooler such as PgBouncer in pool_mode = transaction hands a client whichever backend is free between transactions, so two clients can be given the same backend — and there the lock is reentrant and answers “acquired” to both.

That does not weaken the lock. It removes it, and it fails open: both runs believe they hold it. Measured through PgBouncer 1.25.2 in transaction mode, two independent client connections and one key:

first handle         acquired=true  backend=113
second handle        acquired=true  backend=113
first handle again   acquired=true  backend=113

So Ptah checks, rather than trusts. After taking the lock it asks a second connection for the same one; if that succeeds, the lock excludes nothing and the command refuses before it changes anything:

$ ptah migrations up --db-url "postgres://…@pgbouncer:6432/app"
error: postgres advisory lock "ptah_migrate" excludes nothing on this connection:
a second connection took the same lock, which is what a transaction-pooling proxy
such as PgBouncer produces — it hands both clients one backend session, where the
lock is reentrant. …

The check asks the property rather than identifying the proxy, so it holds for any topology that shares backends, named or not, and costs one query against a direct server.

Point schema-mutating commands at the database directly. Reads through a pooler are unaffected — one server read both ways gives the same description, and a live test asserts it — it is the lock that cannot survive one. Where the risk is understood and accepted — a single deploy job that no other run can race — PTAH_ALLOW_UNVERIFIED_MIGRATION_LOCK=1 skips the refusal. It does not skip the lock, only the proof that the lock excludes anybody.

A transaction-scoped advisory lock does survive a pooler, because it is held to the end of a transaction and a pooler keeps one backend for the whole of one. Measured through PgBouncer 1.25.2 in transaction mode, two concurrent transactions and one key:

first transaction    pg_try_advisory_xact_lock = true   backend 79
second transaction   pg_try_advisory_xact_lock = false  backend 82

It is not what Ptah’s migration lock uses, because that lock is held across planning and applying rather than inside one transaction. The property is pinned by a live test so it is a measured fact rather than a plan.

search_path in the URL

A ?search_path= on a PostgreSQL URL is sent as a startup parameter, and PgBouncer refuses the connection outright rather than ignoring it:

FATAL: unsupported startup parameter: search_path (SQLSTATE 08P01)

Nothing about the schema is wrong there, so Ptah reconnects without the parameter and carries the selection itself. The server still resolves it — set_config('search_path', …, is_local => true) inside a transaction that is rolled back — so PostgreSQL’s own rule decides, including a list, a $user entry, and a schema the connected role may not use. A transaction is what makes that safe through a pooler: the setting dies with the transaction, and a pooler keeps one backend for the whole of one.

Measured on PostgreSQL 17 behind PgBouncer 1.25.2 in transaction mode, one server reached two ways, the pooled answer beside the direct one for the same URL:

search_path=	schema selected
app	app
app,public	app
nosuch,public	public
nosuch	refused: database URL selects schema "nosuch", which does not exist in this database or holds no USAGE privilege for the connecting role

The refusal is carried too. A URL naming a schema the database does not have is rejected rather than folded back to public, because a caller who named a schema and silently got a different one is the failure Ptah’s realm cleanup turns into a dropped schema.

Only the schema selection is carried. Every other startup parameter is the operator’s, and running a command without one would be running it under settings nobody asked for, so it stays a failure that names the parameter:

$ ptah schema inspect --db-url "postgres://…@pgbouncer:6432/app?statement_timeout=5000"
error: connect to --url: failed to ping database: the database URL carries the
startup parameter "statement_timeout", which the server or the proxy in front of
it refuses: server error: FATAL: unsupported startup parameter:
statement_timeout (SQLSTATE 08P01)

The proxy’s own FATAL is kept, so nothing is taken on trust. Configuring the proxy to pass a parameter through (PgBouncer: track_extra_parameters) is the other way out, and it is the only way out for a parameter Ptah does not own.

TimescaleDB

TimescaleDB is PostgreSQL with an extension installed. Ptah manages both of the objects it adds — hypertables and continuous aggregates — and the two sections below say what each declaration carries.

Continuous aggregates

A TimescaleDB continuous aggregate is a view to PostgreSQL: pg_class reports relkind = 'v', and a reader that asks only PostgreSQL describes it as one. Ptah reads it, declares it and plans it as itself.

Describing one as a view is wrong in both directions, and both were measured on TimescaleDB 2.29.2 / PostgreSQL 17.11:

• a plan that dropped it emitted DROP VIEW, and the server answered cannot drop continuous aggregate using DROP VIEW, hinting at DROP MATERIALIZED VIEW. The plan could not apply, and the next run reported the same pending change.
• a plan that created it emitted CREATE VIEW with the body pg_get_viewdef answers, which is not the body anybody wrote: TimescaleDB rewrites the definition to select from the materialization hypertable, so the emitted view named a relation in a schema the extension owns.

So a continuous aggregate is read from timescaledb_information.continuous_aggregates — which keeps the SELECT as it was written — and is left out of the view list.

Declare one over the hypertable it materializes:

//ptah:schema:continuousaggregate name="readings_hourly" body="SELECT time_bucket('1 hour', time) AS bucket, device, avg(temperature) AS avg_temp FROM readings GROUP BY bucket, device"
type ReadingsHourly struct{}

continuous_aggregate "readings_hourly" {
  schema = schema.public
  as     = "SELECT time_bucket('1 hour', time) AS bucket, device, avg(temperature) AS avg_temp FROM readings GROUP BY bucket, device"
}

which renders

CREATE MATERIALIZED VIEW "public"."readings_hourly" WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket, device, avg(temperature) AS avg_temp FROM readings GROUP BY bucket, device
WITH NO DATA;

WITH NO DATA is not a preference. Creating an aggregate with data materializes the whole history the hypertable holds, which is an unbounded amount of work a schema change must not start on its own; the first refresh is an operation an operator runs. It is also what makes the statement usable inside a transaction at all — without it the server answers CREATE MATERIALIZED VIEW ... WITH DATA cannot run inside a transaction block.

materialized_only is optional, and an omitted one takes the server’s own default rather than false. The default is not a constant across TimescaleDB versions — measured on 2.29.2, an aggregate created without the option is reported with it true — so a declaration that did not choose is not compared against it.

The create runs after create_hypertable, because the extension checks: over an ordinary table, WITH (timescaledb.continuous) answers invalid continuous aggregate view.

A changed body is a drop and a create

There is no CREATE OR REPLACE MATERIALIZED VIEW — measured, it is syntax error at or near "MATERIALIZED" — so a changed declaration is planned as a DROP MATERIALIZED VIEW followed by the create, in that order. The drop discards the materialization, and the aggregate starts empty again.

The body is compared through the server

TimescaleDB rewrites the definition before storing it, and the rewrite is not a formatting difference. Measured on 2.29.2:

declared                          stored
time_bucket('1 hour', time)    -> time_bucket('01:00:00'::interval, "time")
GROUP BY bucket, sensor        -> GROUP BY (time_bucket('01:00:00'::interval, "time")), sensor

The GROUP BY key written by its output name comes back as the whole expression that name stood for, so no textual fold can decide whether two spellings are the same aggregate. A comparison that holds a connection asks the server instead: the declaration is created under a probe name inside a transaction, its stored definition is read back, and the transaction is rolled back — the aggregate, its materialization hypertable and everything else it made go with it.

Where there is no connection — ptah schema render, a comparison between two files — the body stays uncompared, and only the options are. Reporting a difference between two spellings of one SELECT would drop and recreate the aggregate on every run, and each drop discards the materialization it exists to keep.

A declared view, materialized view or table whose name a continuous aggregate already occupies is refused before anything is compared, naming the aggregate and the hypertable it materializes. The server’s own answer at apply time would be relation "…" already exists, halfway through a script.

Hypertables

A hypertable is an ordinary table partitioned on a range dimension, and nothing in an ordinary catalog can tell you that it is one. Measured on TimescaleDB 2.29.2 / PostgreSQL 17.11, after create_hypertable('conditions', by_range('time')): pg_class reports relkind = 'r', pg_depend reports no extension ownership for the table, and the index the call created carries the same deptype an ordinary user index does. The extension’s own catalog is the only evidence there is.

Declare one beside the table it partitions. The settings belong to that table, so the annotation has to sit in the Go file that declares the table, and the block in an HCL schema that declares it – the files of a schema directory count as one schema. One that names a table declared anywhere else is refused, and so is a name without a schema that two schemas declare a table under.

//ptah:schema:hypertable table="readings" column="time" chunk_interval="1 day" if_not_exists="true"
type ReadingsHypertable struct{}

hypertable "readings" {
  schema         = schema.public
  column         = "time"
  chunk_interval = "1 day"
  if_not_exists  = true
}

which renders, directly after the table’s CREATE TABLE,

SELECT create_hypertable('"public"."readings"', by_range('time', INTERVAL '1 day'), if_not_exists => TRUE, create_default_indexes => FALSE);

The table name inside the literal keeps the quotes the CREATE TABLE gave it. The server reads the literal as a name and folds an unquoted one to lower case, so a table declared as Readings would otherwise not be found.

A table that already exists and becomes a hypertable gets the same call after the statements that create or change its columns.

chunk_interval is optional and is kept in the server’s own spelling, because that is what timescaledb_information.dimensions reports back: an omitted one takes TimescaleDB’s default, which is 7 days for a timestamptz column.

create_default_indexes => FALSE is not a preference. The call creates an index on the dimension unless told not to, and nothing declared it — measured on 2.29.2, the next comparison planned DROP INDEX "readings_time_idx", Ptah dropping an index the server had made a moment earlier, on every apply. Suppressing it keeps the description the whole truth about which indexes exist; declare an index on the dimension if you want one.

The call runs after the table exists and before anything writes rows. Measured on the same server, create_hypertable against a missing relation answers relation "conditions" does not exist, and against a table holding one row it answers table "loaded" is not empty, hinting at migrate_data => true. Ptah never sends migrate_data: it rewrites the whole table, which is a decision an operator makes rather than one a migration takes on their behalf.

Building an index on a hypertable

The usual way to build an index without blocking writes does not work here. Measured on TimescaleDB 2.30.1 / PostgreSQL 18.6, on a hypertable of three populated chunks:

• CREATE INDEX CONCURRENTLY is refused: hypertables do not support concurrent index creation.
• A plain CREATE INDEX takes a SHARE lock on the hypertable and on every chunk for the whole build, so an INSERT waits until it finishes.
• CREATE INDEX ... WITH (timescaledb.transaction_per_chunk) builds one chunk at a time and locks only that chunk. An INSERT into another chunk, or into a new one, goes through. It cannot run inside a transaction block, so it belongs in a migration marked no_transaction (-- atlas:txmode none in an Atlas directory).
• A per-chunk build that is interrupted leaves the hypertable’s index with indisvalid = false, and the chunks it had not reached without one. A rerun with IF NOT EXISTS skips the name and leaves both as they are, so drop the index before you run the migration again.

ptah migrations lint and ptah-compat migrate lint report a plain build as PG101. With --dev-url the replay shows which tables are hypertables, and on one the finding names the per-chunk build instead of CONCURRENTLY. The per-chunk build is not reported as a blocking build, but in a migration that runs inside a transaction it is reported as PG103P, the way PG103 reports CONCURRENTLY there.

On a server with the extension, ptah migrations up refuses such a migration before it sends a statement, and names the line and the marker:

error: error running migrations: failed to apply migration 2: 0000000002_kind.up.sql cannot run inside a transaction: line 1 CREATE INDEX ... WITH (timescaledb.transaction_per_chunk) commits one transaction per chunk and is refused inside a transaction block; mark the file `-- +ptah no_transaction`, or move the per-chunk build into a migration of its own
SQL: CREATE INDEX events_kind_idx ON events (kind) WITH (timescaledb.transaction_per_chunk)

What cannot be undone

TimescaleDB has no drop_hypertable — measured, the call answers function drop_hypertable(unknown) does not exist — and no call repartitions an existing hypertable. So a hypertable the declaration leaves ordinary, and a changed partitioning column, are refused before anything is applied. A changed chunk interval is refused too: Ptah does not plan a change to an existing hypertable’s partitioning. A table the declaration no longer holds is another matter: DROP TABLE removes its hypertable and chunks with it, so the drop is planned as for any other table.

$ ptah schema apply --db-url "$TIMESCALE_URL" --to file://schema.hcl
error: unsupported feature: readings is a hypertable and the desired schema does
not declare one; TimescaleDB has no statement that turns a hypertable back into
an ordinary table, so this needs an explicit migration that drops and recreates it

Planning nothing would be worse. The table stays partitioned, the description says otherwise, and an operator reading “no changes” believes the two agree.

One range dimension

A second dimension is a separate call — add_dimension with by_hash — and no declaration carries one. A table partitioned on two is described with the first, and the read says so:

note: 1 hypertable is described with the first partitioning dimension only,
because a declaration carries one range dimension and a second is a separate
call; replaying this description partitions on less than the server does, and a
diff between the two reports no difference: conditions (on time and 1 more
dimension).

A hypertable on one dimension is named nowhere, because the description is complete for it.

The capability follows the connection

TimescaleDB is PostgreSQL with an extension installed, not a dialect or a version of its own, and it puts no token in version(). So no preset sets the hypertables or continuous_aggregates capability: what decides both is pg_extension, read once when the connection opens. A PostgreSQL target without the extension skips the statement and says so in the plan rather than failing at apply time on function create_hypertable(unknown, unknown) does not exist. A target outside the PostgreSQL family refuses a hypertable or a continuous aggregate before it renders anything.

Offline, the declaration is the evidence. ptah schema render has no connection to ask, so a document that declares the timescaledb extension alongside a hypertable renders both — an extension the same script installs is installed by the time the call runs. A document that declares the hypertable and not the extension gets the skip comment instead, because nothing in it says the target has the function.

The same rule reaches an apply that adds the extension in the same plan: the connection was opened before the extension existed, so its answer is about the past.

Only HCL and a Go schema can declare a hypertable or a continuous aggregate. A YAML or .sql document records that it could not say so, and the comparison then withholds the removal its silence would otherwise mean.

A declared hypertable or continuous aggregate needs the timescaledb extension, so the comparison does not drop the extension while the schema declares one. This holds when the schema names no extension at all, as a Go schema without an extension annotation does. It does not install the extension either: declare it, for example with //ptah:schema:extension name="timescaledb" if_not_exists="true", when Ptah should create it.

Next steps

• Declaring these objects in Go sources: Go annotation reference.
• Targeting CockroachDB or YugabyteDB instead: CockroachDB and YugabyteDB.
• Targeting Spanner’s PostgreSQL interface: Configure the Spanner provider.
• Gating destructive changes before they run: Lint and gate unsafe SQL.