# PostgreSQL What Ptah manages on PostgreSQL - schema objects, roles and grants, RLS, extensions, sequences, user-defined types, and version-dependent behavior. Source: https://docs.ptah.run/v0.25.0/databases/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](../../reference/go-annotations/). Commands that introspect this behavior require a live PostgreSQL database URL. ## Connecting Use a `postgres://` or `postgresql://` URL: ```bash 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](../../versioned/generate/#which-schemas-plan-and-generate-read) 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](../../concepts/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](../mysql/#what-the-connecting-account-needs) 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: ```text 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](#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](../../schema/sql/#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](#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: ```bash 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 `__key` or `
__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 `
__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](#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 `
__check` when its condition names exactly one column of the table, and `
_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 `CHECK`s 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, `
__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 `
__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 `
__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: `
__key`, or `
__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 `
_ibfk_`, which the [MySQL page](../mysql/) describes. The other engines keep Ptah's own name for an unnamed foreign key, `fk_
_`. 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 `
_check`, `
_check1` and on, past the names the column `CHECK`s take: a column `CHECK` over two columns takes `
_check`, and the first entry beside it takes `
_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: ```sql 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: ```sql -- 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: ```sql 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](#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.** `
__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: ```sql "pk" integer PRIMARY KEY CONSTRAINT "t_id_not_null" NOT NULL ``` The name the server gives, `
__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](../../atlas/strict-ce-mode/#plans-strict-mode-writes-as-atlas-ce-does). - **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: ```sql 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: ```sql -- 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](../../direct/plan-and-approve/#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. ```sql 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: ```sql 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_
_`: 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. ```sql 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](../distributed/#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: ```text 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](#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: ```text 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`: ```sql 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](#a-security-option-the-declaration-leaves-out). The Go annotations and YAML state them in `options` ([Go annotations](../../reference/go-annotations/#ptahschemaview), [YAML](../../reference/yaml-schema/#view-options-and-storage-parameters)). HCL states a view's options in the `security` block Atlas documents for them: ```hcl 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: ```text 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](../../direct/apply/#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](#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](#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: ```sql 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](../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`: ```text 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: ```sql -- 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. ```sql 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](../../extend/providers/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: ```text 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`: ```sql 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: ```sql 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 --to ` 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`: ```hcl permission { to = role.app for = function.purge_workspace args = ["uuid"] privileges = ["EXECUTE"] } ``` `ptah schema export --to hcl` writes `function.` or `procedure.` 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: ```sql 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: ```text 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 ```sql 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: ```bash # 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: ```text 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: ```text 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`. ```bash # 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](../../reference/configuration/#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](../../schema/sql/#row-level-security) 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](#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](https://github.com/stokaro/ptah/issues/4305)), a column type change under the view ([stokaro/ptah#4965](https://github.com/stokaro/ptah/issues/4965)), a change to a materialized view the view reads ([stokaro/ptah#4964](https://github.com/stokaro/ptah/issues/4964)), and a routine that uses the row type of such a view ([stokaro/ptah#4980](https://github.com/stokaro/ptah/issues/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](#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](../../versioned/lint/). 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](../../extend/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. ```go //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: ```sql -- 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: ```text 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](#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](../../reference/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](../../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: ```sql 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`](../../reference/lint-rules/), 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: ```sql 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](../../versioned/apply/). ### 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: ```text 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: ```console $ 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: ```text 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: ```text 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: ```console $ 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: ```go //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{} ``` ```hcl 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 ```sql 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: ```text 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. ```go //ptah:schema:hypertable table="readings" column="time" chunk_interval="1 day" if_not_exists="true" type ReadingsHypertable struct{} ``` ```hcl hypertable "readings" { schema = schema.public column = "time" chunk_interval = "1 day" if_not_exists = true } ``` which renders, directly after the table's `CREATE TABLE`, ```sql 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`](../../reference/lint-rules/). 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: ```text illustration 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. ```console $ 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: ```text 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](../../reference/go-annotations/). - Targeting CockroachDB or YugabyteDB instead: [CockroachDB and YugabyteDB](../distributed/). - Targeting Spanner's PostgreSQL interface: [Configure the Spanner provider](../../extend/providers/spanner/). - Gating destructive changes before they run: [Lint and gate unsafe SQL](../../versioned/lint/).