# 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.11.4/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. 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. ## 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` | When a comparison has a connection, Ptah asks that server to spell each declared column type and default, CHECK, `EXCLUDE` element list and predicate, policy clause, index expression and predicate, trigger WHEN condition, domain, and routine argument list the way its catalog does. It creates a temporary object inside a transaction that is rolled back, reads the stored form, and compares like with like. A column is asked only when its default is declared or its type is not written the way the catalog reports it. A routine is asked only when it takes arguments and the database holds a routine of that name. Its temporary copy has the declared arguments and return type, and a body the server does not check, because the arguments do not depend on the body. Ptah lowercases the words of an argument list and a return clause that are not quoted, as the server does. A string literal, a quoted name and a dollar-quoted string keep their case: an argument declared `DEFAULT 'X'` is created with `'X'`. The temporary table takes the name of the table the declaration is on, so an expression that names its own table, such as `CHECK (clients.n > 0)` or a policy subquery comparing with `clients.id`, resolves the way the server resolved it. The server stores that qualifier unqualified. The probe searches `pg_temp` last, so every other name in the expression, `public.clients` included, still reads the real table. The server asked is the one the other side was read from. `schema apply` asks the target. `migrate diff` and `migrations generate --replay` ask the dev database the migration directory was replayed on. Both compare on a session they hold for the whole run, the apply lock or the replay, and the probe transaction runs on that session while no transaction is open on it. `schema diff` asks the server its `--from` side was read from, while that connection is still open: the `--from` database itself, or the dev database session the `--from` migration directory was replayed on, before the replay's cleanup. It does so when `--to` is a schema file or another declaration. A `--from` database answers without `--dev-url`. When `--from` is a schema file and `--to` is a database or a migration directory, `schema diff` creates the file on the `--dev-url` database, reads it back, and compares what it read. The `--from` side then holds the server's spelling too, as Atlas CE's does. Two schema files are compared as written: they spell an expression the same way when they declare the same schema. Two databases or directories both hold the server's spelling already. A declaration the server refuses is compared with Ptah's own folding instead. So is a comparison on a session with a transaction open, where the rollback would discard the session's work, and every comparison without a connection. A `schema diff` from a schema file to a database without `--dev-url` is one of those: the file's expressions are compared as written, and a rewritten default, CHECK or policy is planned again. Pass `--dev-url` for that direction. A key column is NOT NULL on the file's side either way, as the server holds it. `ptah-compat schema diff` refuses a schema file without `--dev-url`, as Atlas CE does, unless `PTAH_ATLAS_DIFF_WITHOUT_DEV_URL=1` is set. A view or materialized view body is compared by folding, not by asking the server. The server expands a `*` into the column list when it creates the view, so `SELECT * FROM orders WHERE total > 100` reads back as `SELECT id, total FROM orders WHERE (total > 100)`. Ptah expands each top-level `*` of a view that reads one table into that table's declared columns before it compares, so the declaration and its read-back match. Because the declared columns are used, a view created before its table gained a column is replaced, and the new column appears in it. A `*` over a join or inside a subquery is compared as written, so such a view is replaced on every plan; list its columns instead. ## Constraint enforcement and the MATCH type PostgreSQL 18 keeps a `CHECK` or a foreign key declared `NOT ENFORCED` and does not check it. A foreign key declared `MATCH FULL` refuses a row whose key is partly null. Ptah reads both clauses from a SQL file, writes them when it renders the constraint, reads them back from `pg_get_constraintdef`, and compares them. A constraint whose enforcement or MATCH type changes is dropped and added again, because PostgreSQL 18.6 cannot change a `CHECK`'s enforcement in place. CockroachDB and YugabyteDB keep `MATCH FULL` too, and neither takes `NOT ENFORCED`. The reader refuses `MATCH PARTIAL`, which PostgreSQL 18.6 answers with `MATCH PARTIAL not yet implemented`, and the renderer refuses `NOT ENFORCED` for PostgreSQL before 18. A command that connects plans for the server it connects to. `schema diff` plans for the database it reads, or for the dev server it compares two files on; a `docker://postgres/18` dev URL names that server in its tag. `schema render`, which connects to nothing, plans for PostgreSQL 17 unless `--server-version 18` names the target: ```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. 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 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`. 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. ## Object comments Ptah writes the comment of a view, a materialized view, a sequence, a domain, a composite, range or enum type, an extension, a function, a procedure, a trigger and a row-level security policy with `COMMENT ON`, reads it back and compares it, as it does a table's and a column's. The comment is written right after the statement that creates the object, and a changed comment is planned as one `COMMENT ON` for the object, not as a drop and a create. A function or a procedure is named by the argument list the server records, so the statement addresses one overload. A trigger and a policy are named `ON` their table. An object the plan writes again ends with the comment the declaration states. `CREATE OR REPLACE` keeps the comment a function, a procedure or a trigger had, so a removed comment is cleared after the replacement. A materialized view and a policy are dropped and created again, and an enum that loses a value is renamed, created again and the old type dropped; each new object is written with the declared comment. An extension's comment is compared only when the declaration states one. `CREATE EXTENSION` gives every extension the comment its control file carries, so a declaration without a comment is not read as asking for none. The version of an extension follows the same rule. The other engines of the family take fewer of these statements. Ptah writes and compares a comment only where the server stores it and reports it back, which each statement's capability key records: `view_comments`, `materialized_view_comments`, `sequence_comments`, `type_comments`, `domain_comments`, `extension_comments`, `function_comments`, `procedure_comments`, `trigger_comments` and `policy_comments`. Where a key is false, the render names the comment it left out. CockroachDB accepts `COMMENT ON TYPE` and then reports no comment for the type, so its `type_comments` key is false. CockroachDB 26.3 stores a function's and a procedure's comment and refuses the statement for a materialized view, a trigger and a policy. The Spanner PostgreSQL interface refuses every `COMMENT ON`. ### Constraint comments A table constraint's comment is written with `COMMENT ON CONSTRAINT ... ON` its table, right after the statement that adds the constraint. Ptah reads it back and compares it, and a changed comment is planned as one `COMMENT ON CONSTRAINT`, not as a drop and an add. A constraint whose definition changed is dropped and added again, and the statement that adds it writes the declared comment. A rollback that adds back a dropped CHECK, UNIQUE or foreign key constraint writes the comment it had. Only a constraint the declaration states as a constraint is compared. A primary key from a primary-key column, a column's check or foreign key, and a table's list of checks have no place for a comment, so the comparison leaves the comment the database holds on them alone. An unnamed constraint gets no comment, because the statement has to name it. The `constraint_comments` key records where the server stores the comment and reports it back. PostgreSQL, CockroachDB and YugabyteDB do. The Spanner PostgreSQL interface refuses the statement, so there the render names the comment it left out. ## Making a column NOT NULL A plan that makes an existing column `NOT NULL` writes `ALTER COLUMN ... SET NOT NULL`. PostgreSQL checks every row when the statement runs, and fails it with SQLSTATE 23502 if a row holds `NULL`. - **The column declares a default.** The plan fills the `NULL` rows with that default first, then sets `NOT NULL`. The value is one the schema states. The safety report lists the fill as a warning, because it rewrites rows, and the `SET NOT NULL` after it as safe. Atlas CE fails this change on a `NULL` row, so here Ptah is deliberately more permissive, with the author's own value. Under `PTAH_ATLAS_STRICT_COMPAT=1`, `ptah-compat` writes no fill and the statement fails as it does in Atlas CE; see [Strict CE mode](../../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 PostgreSQL identity column is implicitly `NOT NULL`. SQL desired-schema sources preserve that property for both `GENERATED ALWAYS` and `GENERATED BY DEFAULT AS IDENTITY`, even when the declaration omits `NOT NULL`. ## Unlogged tables A table declared `unlogged = true` renders `CREATE UNLOGGED TABLE`. Its writes skip the write-ahead log, which makes them faster and costs durability: the server truncates the table after a crash, and replication does not carry it. Use it for a cache or a staging table, not for data that has to survive. `ptah db read` reports the property back, so a schema file built from a live database describes an unlogged table as one. YugabyteDB accepts the keyword as well. CockroachDB and Spanner reach Ptah over the same wire protocol and neither creates an unlogged table, so a declaration carrying the flag renders an ordinary table there. Changing a table between logged and unlogged rewrites it under a lock that blocks readers and writers. Ptah does not plan that change, and the `PG307` lint rule reports it in a migration that contains one. ## Function changes A function the database does not have yet is planned as `CREATE FUNCTION`, and a new procedure as `CREATE PROCEDURE`, so a plan says whether it creates a routine or replaces one. If a routine of that name and those argument types appears before the plan runs, the statement fails with SQLSTATE 42723 instead of overwriting it. The function Ptah writes for a trigger with a body follows the trigger: a new trigger creates it, and a changed trigger replaces it. That function's name is `ptah_trigger_
_`: the table and the trigger name are each folded to lower case, every run of characters outside `[a-z0-9_]` collapsed to one `_`, and every `_` that remains doubled before the two parts join on a single `_`. The doubling keeps the join unambiguous -- a table `a_b` with trigger `c` and a table `a` with trigger `b_c` render as `ptah_trigger_a__b_c` and `ptah_trigger_a_b__c` rather than the same name. A table or trigger name that contains an underscore therefore renders under a different generated name; a database already holding that trigger's function under the old name keeps it, unreferenced, until someone drops it by hand. A changed function is planned as `CREATE OR REPLACE FUNCTION` where the server accepts that: a new body, language, security context, volatility, planner property, or setting. Views, policies, and triggers that call the function stay in place. A changed return type or parameter list cannot be applied that way, so Ptah plans `DROP FUNCTION` followed by the create, in the migration and in its rollback. PostgreSQL refuses a changed return type with `cannot change return type of existing function` and a renamed parameter with `cannot change name of input parameter` (both SQLSTATE 42P13). A changed parameter *type* it accepts, and creates a second overload: without the drop, a declaration of one routine leaves two behind. PostgreSQL discards type modifiers in routine arguments and results. `RETURNS varchar(20)` therefore compares equal to the catalog's `character varying`, and `numeric(10,2)` to `numeric`. Ptah ignores these modifiers when comparing routines and keeps the authored declaration when rendering a real change. Column lengths and numeric precision still affect stored values and remain part of column comparison. The drop names the function's argument list, so an overloaded name loses only the overload that changed. A function that takes no arguments is named with an empty list, `f()`, which is what selects it among the overloads of its name; the same holds for a function the schema stops declaring, and for a procedure. A schema may declare several overloads of one name, as `pg_dump` writes them. Each is a routine of its own, told apart by its kind and its input argument types, as PostgreSQL tells them apart: `f(a int)` and `f(a int, b text)` are two functions, and `f(b integer)` is `f(a int)` declared again. The drop does not use `CASCADE`: when a view, policy, or trigger uses the function, the server refuses the drop with SQLSTATE 2BP01 and the migration stops, instead of removing an object the schema still declares. Measured on PostgreSQL 18. A function with `OUT` or `INOUT` arguments may leave `RETURNS` out. PostgreSQL then records the type those arguments imply, and Ptah compares the declaration against that type: the type of the one such argument, or `record` for two or more. `f(a integer, OUT b text)` returns `text`, and `f(a integer, OUT b integer, OUT c text)` returns `record`. A routine or `DO` body in a SQL schema file may be written in any quoting PostgreSQL takes: `'...'` with doubled quotes, `E'...'` with backslash escapes, or a dollar quote. Ptah reads the body as the server does, with the quoting undone, and writes it between dollar quotes the body does not contain: `$$`, or `$ptah$` when the body holds `$$`. ## Triggers A SQL schema file declares a trigger with the statement `pg_dump` writes, and every form PostgreSQL 18 accepts is read: the events `INSERT`, `UPDATE`, `UPDATE OF` a column list, `DELETE` and `TRUNCATE`, joined by `OR`; `FOR EACH ROW` or `FOR EACH STATEMENT`, with `EACH` optional; a `WHEN` condition; and `REFERENCING OLD TABLE` and `NEW TABLE` for transition tables. ```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. ## Materialized view refresh Ptah does not refresh materialized views, and a declaration cannot ask it to. A `refresh_strategy` attribute is refused when the schema is parsed, on every dialect and in every frontend. That is a boundary rather than a missing feature. `REFRESH MATERIALIZED VIEW` is a statement someone runs, and `CONCURRENTLY` is an option of that statement; neither is state the database holds, so nothing Ptah reads back could report a refresh policy or diff one. What Ptah does manage keeps the view current on its own terms: `CREATE MATERIALIZED VIEW` populates the view, and a changed body is reconciled as a `DROP` and a `CREATE` that populates it again. Measured on PostgreSQL 18. The case that is left over is a view no schema change touched, going stale because its **source data** moved. Schema reconciliation cannot observe that, so a refresh emitted there would be an unbounded data operation attached to a migration on a relationship Ptah inferred. Issue it yourself, from whatever already knows when the data changed: ```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, and grants are compared per individual privilege, so a `privilege="SELECT,INSERT"` list round-trips cleanly through introspection. A grant on a standalone sequence is described with `on_sequence`, so the description compares equal to the database it was read from. A function or procedure is named with its argument types, because PostgreSQL tells overloads apart by them: `on_function="purge_workspace(uuid)"` in Go, `GRANT EXECUTE ON FUNCTION purge_workspace(uuid) TO app` in a SQL schema file. A name without the types is refused. The types are matched against the catalog without parameter names or `OUT` arguments, so `purge_workspace(uuid)` and `purge_workspace(p_id uuid)` name the same function. On CockroachDB, grants are read from `information_schema` on every release line, because v25.4 and v26.2 leave the catalog's ACL columns empty. The read leaves out privileges nobody granted: the built-in `admin` role and the `root` user hold `ALL` on every object and cannot lose it, and an owner holds `ALL` on what it owns. CockroachDB records no grantor, so a described grant names none. A `GRANT` may name several objects and roles. Ptah records every object-role pair, including column privileges and `WITH GRANT OPTION`: ```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: ```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. `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. #### 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: 1 default privilege is not described, because no schema source can declare one set FOR ALL ROLES; a description applied to another database does not carry it: TABLES in public for all roles. ``` `ptah db drop-all` revokes a CockroachDB `FOR ALL ROLES` default in the schemas it cleans, spelled `FOR ALL ROLES`. CockroachDB's `SHOW DEFAULT PRIVILEGES` lists privileges PostgreSQL does not have, and which of them `ALL` covers depends on the release. When a global default takes some of the owner's own privileges away and leaves the rest, the read cannot say which, so it leaves the owner's part out of the description and names it in the same note. `REVOKE ALL` and `GRANT ALL` converge from there whatever the owner holds, and a comparison plans one of them when the declaration asks for it, or `GRANT ALL` when the declaration says nothing about the owner of a role it manages. Any other change to the owner's part is withheld and reported as undecided, so on CockroachDB a declared revoke of part of the owner's own privileges is applied once and reported as undecided after that. CockroachDB v26.2 refuses every read of `pg_default_acl` once a default privilege names a role whose name needs quoting, such as one with a dash. On such a database `ptah db read` and `schema inspect` describe no default privilege and say so in a note on stderr, and a comparison neither plans a declared default nor revokes one it could not read. `ptah db drop-all` asks CockroachDB's `SHOW DEFAULT PRIVILEGES` for the defaults it revokes, so it cleans such a schema too. A privilege can be limited to columns of a table: `GRANT UPDATE (state, decided_at) ON proposals TO app`. Each column is compared on its own against `pg_attribute.attacl`, and a column privilege and the table privilege of the same name are two privileges, as they are in the catalog. A `REVOKE` of the table privilege also takes it off every column, as PostgreSQL does, so ```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 column default or a `CHECK` that calls a `LANGUAGE sql` routine reading another new table gets the one order PostgreSQL accepts: the table the routine reads, then the routine, then the table that calls it. The same holds for a column added to an existing table. A routine that reads the very table whose default or `CHECK` calls it has no such order, and PostgreSQL refuses it in either order; write that routine in PL/pgSQL. Reading a live database describes only the roles the schemas being read actually use, because a PostgreSQL role belongs to the cluster rather than to one database. A role counts as used when it holds a privilege on a relation, a column or a routine in those schemas or on one of the schemas themselves, when it granted one, when a default privilege in them names it, or when a row-level security policy on a table in them applies to it. A role that merely exists elsewhere on the server is not part of the schema being described, so it is left out — of `ptah db read` and of `ptah-compat schema inspect` alike. The rule is exact in both directions: a description defines a role when some other statement in it names that role, and not otherwise. Ownership alone does not qualify, because Ptah describes no ownership — it writes no `OWNER TO` and no `CREATE SCHEMA ... AUTHORIZATION` — so an owner would be created and then never referred to. For the same reason, grant introspection reports privileges somebody granted rather than the built-in privileges an owner holds by default. A relation whose `pg_class.relacl` is null has had no `GRANT` run against it, and its owner's implicit privileges are no longer emitted as `GRANT` statements; replaying `CREATE TABLE` gives the new owner exactly those privileges again. Leaving a role out of a description does not mean Ptah thinks it is missing. Which roles a schema uses and which roles Ptah manages on the server are separate questions, and comparison asks the second one: a managed role that already exists anywhere in the cluster is never planned as a `CREATE ROLE`, whether or not the schema you are reading refers to it. Declare such a role in your entities and Ptah still applies `ALTER ROLE` when its attributes drift, so comparison is unchanged in both directions. Roles outside the described scope are used for that comparison only — they are never written to any output. Scoping the description does cost one thing, and it is not comparison: a description no longer reproduces a cluster's ungranted roles somewhere else. Measured on PostgreSQL 17.10, on a database holding one table and roles nothing grants anything to, `ptah db read` went from four `CREATE ROLE` statements to none, and `ptah-compat schema apply --dry-run` against an empty database in a **second** cluster went from planning three of them to planning none. Copying one cluster's roles into another is something you could do before, so it stays available on the same commands rather than being removed: ```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. 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`. A policy whose kind changes is dropped and created again, because PostgreSQL cannot alter a policy's kind in place. Both ways of taking the protection away are lint findings at error severity: `DISABLE ROW LEVEL SECURITY` is `DS109` and `NO FORCE ROW LEVEL SECURITY` is `DS111P`. `ptah migrations up` refuses a pending migration that holds either statement until you rerun it with `--allow-destructive`, and `plan` and `generate --check-destructive` refuse a plan that disables row-level security or removes FORCE. See [Lint and gate unsafe SQL](../../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. Ptah renders the clause you wrote, so a policy declared without `TO` keeps rendering without one. CockroachDB and YugabyteDB report roles the same way. Which table a policy belongs to is decided under the target's identifier rules rather than by spelling, so a policy declared on `orders` and a table created as `public.orders` are one table and the enablement is emitted once. Matching them as plain strings left the `CREATE POLICY` in the plan without its `ENABLE`, and the migration reported success while the policy sat inert on an unprotected table. A policy name is scoped to its table rather than to the schema, which is what PostgreSQL itself enforces: `CREATE POLICY tenant_isolation` succeeds on two tables in one schema and is refused only when repeated on the same table. Ptah identifies each policy by its table and its name together, so two tables can each carry a `tenant_isolation` policy and both are rendered, compared, and migrated independently. The owning table is that table's identity rather than the string you spelled it with. A table declared without a schema is reached both as `orders` and as `public.orders`, and PostgreSQL treats those as one table: declaring `p` on each spelling is one policy declared twice, and the second `CREATE POLICY` is refused with `policy "p" for table "orders" already exists`. Ptah keeps the first declaration and renders it once. Two tables of the same name in different schemas — `tenanta.orders` and `tenantb.orders` — remain two tables, and a policy name on each remains two policies. Letter case is the other spelling that reaches one table, and a SQL schema file follows PostgreSQL's rule for it. Read with `--dialect postgres`, every unquoted name folds to lower case the way the server folds it: tables, columns, indexes, constraints, policies and the table any statement names. A quoted name keeps its case. So `CREATE TABLE Orders` declares `orders`, and `ALTER TABLE ORDERS ENABLE ROW LEVEL SECURITY` and `CREATE POLICY p ON orders` name that same table. A database the file created directly, through `psql` or another tool, compares equal to the file. `CREATE TABLE "Orders"` declares a different table, `Orders`. A schema that declares only `"Orders"` and then writes `ON orders` names a relation it does not declare, and the render keeps that name rather than guessing: it reproduces PostgreSQL's own `relation "orders" does not exist` instead of moving the policy onto `Orders`. Declaring both `orders` and `"Orders"` gives two tables, and a policy on each is two policies. ## Extensions `//ptah:schema:extension` manages `CREATE EXTENSION`. Some extensions are pre-installed and should not be migration-managed, so Ptah keeps an ignore list, with `plpgsql` ignored by default. An ignored extension can still be created when your schema declares it, but it is never dropped and never appears in a diff. Embedders can replace or extend the ignore list through the Go API — see [Reusable components](../../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 between schemas is detected but currently refused before SQL is emitted, because Ptah does not yet plan `ALTER EXTENSION ... SET SCHEMA`. Creation, removal, and identical-placement comparisons remain supported. ```go //ptah:schema:extension name="pgcrypto" schema="extensions" if_not_exists="true" type PostgreSQLExtensions struct{} ``` ## Standalone sequences PostgreSQL creates an implicit sequence for every `SERIAL` column and identity column; you do not declare those, and Ptah's introspection deliberately excludes them, so a plain `SERIAL` column never produces a spurious diff. A *standalone* sequence — declared with `//ptah:schema:sequence`, typically to share one number generator across tables — is a first-class object with the full lifecycle. Ordering matters twice: a sequence consumed by a column default is created before the table that uses it, while an `owned_by="table.column"` association requires the table to exist, so Ptah emits it as a separate `ALTER SEQUENCE ... OWNED BY` after table creation — the same ordering `pg_dump` uses. Options you leave unset follow PostgreSQL defaults and are never reported as drift. ## User-defined types Domains, composite types, and range types are declared with `//ptah:schema:domain`, `:composite`, and `:range`. They are created after extensions and enums but before tables, and their drops are classified as destructive by the safety gate. Reconciliation is deliberately conservative: - A domain's `check` and `default` are compared through the server. PostgreSQL rewrites both on read-back, so against a live database Ptah asks the server to normalize the declaration and compares that with the catalog. A changed `CHECK` plans `ALTER DOMAIN ... DROP CONSTRAINT` and `ADD CHECK`, and a changed `DEFAULT` plans `ALTER DOMAIN ... SET DEFAULT`. A declaration that leaves either out does not remove the one the database holds. - A domain base-type or nullability change has no in-place `ALTER`, so it is emitted as a non-`CASCADE` drop and recreate; if a column still uses the domain, the drop fails loudly instead of dropping the column. - Range types are matched by name only; a changed range is dropped and recreated. - Within the group the three kinds are ordered by what their definitions name, not by kind: `CREATE TYPE addr AS (...)` precedes `CREATE DOMAIN d AS addr`, and `CREATE DOMAIN qty AS integer` precedes a composite with a `qty` field. Both directions occur, and PostgreSQL has no forward declaration for a type. - The drops a recreation emits are ordered by the shape the **database holds**, which is a different graph from the one above and does not have to agree with it. A `DROP` runs against the current schema, so only a reference that schema carries can block it; when the change is what moves the reference, the create order and the drop order are not mirror images. Both orders come out of the same plan. - A domain, composite or range type that an **extension** owns is not described. `CREATE EXTENSION` creates those types, so they cannot be created or dropped independently, and describing one as a user type made the description declare something the extension already makes — replaying it failed with `type "lo" already exists`. Ownership is read from `pg_depend`, so a type of your own named close to an extension's is unaffected. The same rule has always applied to extension-owned functions. ## Concurrent index creation `CREATE INDEX CONCURRENTLY` avoids blocking writes but cannot run inside a transaction block. With `diff.concurrent_index: true` in `ptah.yaml` ([Configuration](../../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` | 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: ```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 ```sql SELECT create_hypertable('public.readings', by_range('time', INTERVAL '1 day'), if_not_exists => TRUE, create_default_indexes => FALSE); ``` `chunk_interval` is optional and is kept in the server's own spelling, because that is what `timescaledb_information.dimensions` reports back: an omitted one takes TimescaleDB's default, which is 7 days for a `timestamptz` column. **`create_default_indexes => FALSE` is not a preference.** The call creates an index on the dimension unless told not to, and nothing declared it — measured on 2.29.2, the next comparison planned `DROP INDEX IF EXISTS "readings_time_idx"`, Ptah dropping an index the server had made a moment earlier, on every apply. Suppressing it keeps the description the whole truth about which indexes exist; declare an index on the dimension if you want one. The call runs after the table exists and before anything writes rows. Measured on the same server, `create_hypertable` against a missing relation answers `relation "conditions" does not exist`, and against a table holding one row it answers `table "loaded" is not empty`, hinting at `migrate_data => true`. **Ptah never sends `migrate_data`**: it rewrites the whole table, which is a decision an operator makes rather than one a migration takes on their behalf. #### Building an index on a hypertable The usual way to build an index without blocking writes does not work here. Measured on TimescaleDB 2.30.1 / PostgreSQL 18.6, on a hypertable of three populated chunks: - `CREATE INDEX CONCURRENTLY` is refused: `hypertables do not support concurrent index creation`. - A plain `CREATE INDEX` takes a `SHARE` lock on the hypertable and on every chunk for the whole build, so an `INSERT` waits until it finishes. - `CREATE INDEX ... WITH (timescaledb.transaction_per_chunk)` builds one chunk at a time and locks only that chunk. An `INSERT` into another chunk, or into a new one, goes through. It cannot run inside a transaction block, so it belongs in a migration marked `no_transaction` (`-- atlas:txmode none` in an Atlas directory). - A per-chunk build that is interrupted leaves the hypertable's index with `indisvalid = false`, and the chunks it had not reached without one. A rerun with `IF NOT EXISTS` skips the name and leaves both as they are, so drop the index before you run the migration again. `ptah migrations lint` and `ptah-compat migrate lint` report a plain build as [`PG101`](../../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 two changes are refused before anything is applied: ```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`. **Offline, the declaration is the evidence.** `ptah schema render` has no connection to ask, so a document that declares the `timescaledb` extension alongside a hypertable renders both — an extension the same script installs is installed by the time the call runs. A document that declares the hypertable and not the extension gets the skip comment instead, because nothing in it says the target has the function. The same rule reaches an apply that adds the extension in the same plan: the connection was opened before the extension existed, so its answer is about the past. Only HCL and a Go schema can declare a hypertable or a continuous aggregate. A YAML or `.sql` document records that it could not say so, and the comparison then withholds the removal its silence would otherwise mean. ## Next steps - Declaring these objects in Go sources: [Go annotation reference](../../reference/go-annotations/). - Targeting CockroachDB, YugabyteDB, or Spanner instead: [CockroachDB, YugabyteDB, and Spanner](../distributed/). - Gating destructive changes before they run: [Lint and gate unsafe SQL](../../versioned/lint/).