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. 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.

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

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 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 only when it takes arguments 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 arguments do not depend on the body.

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.

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. 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 or directories both hold the server’s spelling already.

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, not by asking the server. 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 or inside a subquery is compared as written, so such a view is replaced on every plan; list its columns instead.

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.

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 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.

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.

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 and a row-level security policy 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.

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. A materialized view and a policy are dropped and created again, and 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.

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 and policy_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. The Spanner PostgreSQL interface refuses every COMMENT ON.

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. The Spanner PostgreSQL interface refuses the statement, so there the render names the comment it left out.

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 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.

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.

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

YugabyteDB accepts the keyword as well. CockroachDB and Spanner reach Ptah over the same wire protocol and neither creates an unlogged table, so a declaration carrying the flag renders an ordinary table there.

Changing a table between logged and unlogged rewrites it under a lock that blocks readers and writers. Ptah does not plan that change, and the PG307 lint rule reports it in a migration that contains one.

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.

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.

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. 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, and grants are compared per individual privilege, so a privilege="SELECT,INSERT" list round-trips cleanly through introspection. A grant on a standalone sequence is described with on_sequence, so the description compares equal to the database it was read from.

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:

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.

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.

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: 1 default privilege is not described, because no schema source can
declare one set FOR ALL ROLES; a description applied to another database does
not carry it: TABLES in public for all roles.

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 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, grant introspection reports privileges somebody granted rather than the built-in privileges an owner holds by default. A relation whose pg_class.relacl is null has had no GRANT run against it, and its owner’s implicit privileges are no longer emitted as GRANT statements; replaying CREATE TABLE gives the new owner exactly those privileges again.

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.

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. A policy whose kind changes is dropped and created again, because PostgreSQL cannot alter a policy’s kind in place.

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. Ptah renders the clause you wrote, so a policy declared without TO keeps rendering without one. 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 can still be created when your schema declares it, but it is never dropped 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 between schemas is detected but currently refused before SQL is emitted, because Ptah does not yet plan ALTER EXTENSION ... SET SCHEMA. Creation, removal, and identical-placement comparisons remain supported.

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

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 standalone sequence — declared with //ptah:schema:sequence, typically to share one number generator across tables — is a first-class object with the full lifecycle.

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, 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 CHECK, 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 base-type or nullability change has no in-place ALTER, so it is emitted as a non-CASCADE drop and recreate; if a column still uses the domain, the drop fails loudly instead of dropping the column.
• Range types are matched by name only; a changed range is dropped and recreated.
• 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.
• 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.
• A domain, composite or range type that an extension owns is not described. CREATE EXTENSION creates those types, so they cannot be created or dropped independently, and describing one as a user type made the description declare something the extension already makes — replaying it failed with type "lo" already exists. Ownership is read from pg_depend, so a type of your own named close to an extension’s is unaffected. The same rule has always applied to extension-owned functions.

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

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:

//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

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

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 IF EXISTS "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 two changes are refused before anything is applied:

$ 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.

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.

Next steps

• Declaring these objects in Go sources: Go annotation reference.
• Targeting CockroachDB, YugabyteDB, or Spanner instead: CockroachDB, YugabyteDB, and Spanner.
• Gating destructive changes before they run: Lint and gate unsafe SQL.