Postgres Locks and the Change Classifier

Every change between two Postgres schemas is classified before anything runs, by the lock it takes and whether it reads or rewrites the table, as the Postgres 18 reference states them. Where 14 to 17 differ, the rule says so.

Class	Means	Disruption
create	a new object; nothing that exists changes	`in-place`
metadata only	a catalog change under a brief lock; no row is read or written	`in-place`
validates under a weaker lock	every row is read to check a constraint, under a lock weaker than ACCESS EXCLUSIVE	`rolling`
needs CONCURRENTLY	an index build or drop that blocks writes unless it is CONCURRENTLY, which cannot run in a transaction block	`rolling`
ACCESS EXCLUSIVE rewrite or scan	the table is rewritten or read in full while reads and writes wait	`rolling`
expand and contract	no in-place change keeps old readers working; a plan refuses it	`replace`
drop	the object, and a table’s or sequence’s data, is gone	`destroy`

The disruption column is what chant lifecycle plan reports for each update. A changed path alone cannot always say which rule applies (a column’s type, a constraint, a view’s query, an enum’s labels), and those report unknown there; chant sql plan has both definitions and classifies them.

chant sql diff base.json head.json     # two chant build outputs, offline
chant sql plan prod schema.json        # a build output against the prod server

Both read the build’s dialect and exit 2 when a change can only be made as expand and contract. --json prints the changes, the hints, the refused changes and the migration Op suggestions as one document. chant sql diff compares a pull request’s base and head builds with no server. chant sql plan reads the server sql.profiles.<env> binds (else POSTGRES_URL), in the profile’s schemas when it lists them, otherwise in the default schema and every schema the build declares an object in. Where the normalization rules leave an expression different, it asks that server: the declared view, or the table’s column types, defaults, generated expressions and checks, are created as temporary objects in a transaction that is always rolled back, and read back with the same printers, so a view’s SELECT * and the server’s added parentheses are not reported as changes.

Statements for a migration file

chant sql diff base.json head.json --statements prints the statements that take the base schema to the head one, offline: each one the applier would send for the same change, after a comment naming its object, rule and class, and whether it has to run outside a transaction block. --json prints them as one document, which diffStatements(before, after) from @intentius/chant-lexicon-sql also returns, each statement with transactional set:

-- users (app.users): SQLPG201 metadata
ALTER TABLE app.users ADD COLUMN name text DEFAULT 'none' NOT NULL;

-- usersEmail (app.users_email_idx): SQLPG240 concurrently, outside a transaction
CREATE INDEX CONCURRENTLY users_email_idx ON app.users (email);

-- orders (app.orders): SQLPG205 made by PostgresMigrationOp, not a statement:
--   export const { op } = PostgresMigrationOp({ name: "migrate-app-orders-reference", env: "<env>", table: "app.orders", column: "reference" });

A column rename or a type change across kinds is never DDL: it is a step naming PostgresMigrationOp, one per column. Any other expand-and-contract change, and the refused table’s other changes, which the applier also holds back, are a manual step. A column or object drop is a statement marked destructive. Statements use the default schema for a bare name, as the applier sets search_path. The command exits 2 when a step is not a statement. What the applier asks the server is left out: its normalization of expressions the rules leave different, and an extension’s own comment.

Which major

Two rules change class across the supported majors: a STORED generated column’s expression (SQLPG212) is a rewrite from 17 and expand and contract before it, and a table’s access method (SQLPG226) is a rewrite from 15 and expand and contract before it.

chant sql diff classifies for the major the builds recorded (postgresMajor in the build output): the newer build’s, else the older build’s, else sql.postgresMajor, else 18. A diff between two revisions does not depend on today’s config.
chant sql plan classifies for the major the server runs (server_version_num), since the server is what takes the locks. When the build targets another major, the plan says so in a hint.

Identity and renames

An object is identified by its export name between two builds, and by its schema-qualified name against a server, which has no export names. Tables, views, materialized views, sequences and indexes share one namespace per schema, types and domains another, and schemas and extensions are matched by name. Functions and procedures share a namespace of their own and are told apart by their input parameter types, so an overload is another object; a trigger is matched by its name on its table.

A rename is declared where it happens. Before a CREATE, -- previously: <old name> says the object had another name; on a column’s line, it says the column did:

CREATE TABLE app.orders (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  memo text   -- previously: note
)

Without the hint, a column dropped and another added with the same type in the same place are reported as a drop and an add, with a hint asking whether it is a rename. A constraint left unnamed is matched by what it says, not by the name Postgres gave it, and an index on a table the same plan creates is part of the create.

An object another tool keeps is never proposed for a drop: an ORM’s or migration runner’s revision table with its sequence and indexes, and, with sql.provider set, the provider’s own schemas and extensions. The plan names each in a hint and leaves it alone. Any other object in the schemas the plan reads that the build does not declare is a drop (SQLPG270); the applier drops it only when told to prune and only when it carries this project’s marker.

After the plan

The plan only reports. postgresApply makes every change that is not expand and contract, grouping the statements into transactions by class and running CONCURRENTLY builds outside them. A column rename (SQLPG205) or a type change across kinds (SQLPG208) runs as PostgresMigrationOp, and both commands print the declaration to start from for each such column (migrationOps in --json): see Migrating a Column. The other expand-and-contract changes (a NOT NULL column with no default, a view losing or reordering columns, partitioning, a rename of a table, an enum label removed) have no Op yet and are made by hand.

The rules

Id	Class	Change	Restriction
SQLPG200	create	Create an object	A new object is created; nothing existing changes. A table created in the same plan as its indexes needs no CONCURRENTLY. Postgres 18 docs
SQLPG201	metadata only	Add a column	ADD COLUMN with no default, or with a non-volatile default, changes only the catalog under ACCESS EXCLUSIVE: the default is stored once and read for existing rows (since 11, so in every supported major). A NOT NULL column needs that default. Postgres 18 docs
SQLPG202	ACCESS EXCLUSIVE rewrite or scan	Add a column whose value has to be computed for every row	ADD COLUMN with a volatile default (clock_timestamp(), random(), gen_random_uuid(), nextval()), a STORED generated column or an identity column rewrites the whole table under ACCESS EXCLUSIVE. A VIRTUAL generated column (18) does not. Postgres 18 docs
SQLPG203	EXPAND AND CONTRACT	Add a NOT NULL column with no default	ADD COLUMN … NOT NULL with no default fails on a table that has rows. Add it nullable, backfill it, then set NOT NULL. Postgres 18 docs
SQLPG204	metadata only	Drop a column	DROP COLUMN only hides the column in the catalog under ACCESS EXCLUSIVE; its space is reclaimed as rows are rewritten. The data is gone. Postgres 18 docs
SQLPG205	EXPAND AND CONTRACT	Rename a column	RENAME COLUMN is a catalog change, but every reader still using the old name fails the moment it runs: add the new column, write both, move readers, drop the old. Postgres 18 docs
SQLPG206	metadata only	Change a column’s type without a rewrite	ALTER COLUMN TYPE to a binary-coercible type (varchar(n) to a longer varchar or to text, numeric(p,s) to a wider precision or to unconstrained numeric, cidr to inet) needs no rewrite; indexes on the column may still be rebuilt. ACCESS EXCLUSIVE, briefly. Postgres 18 docs
SQLPG207	ACCESS EXCLUSIVE rewrite or scan	Change a column’s type with a rewrite	ALTER COLUMN TYPE that is not binary-coercible (integer to bigint, a shorter varchar, real to double precision) rewrites the table and rebuilds its indexes under ACCESS EXCLUSIVE. Postgres 18 docs
SQLPG208	EXPAND AND CONTRACT	Change a column’s type across kinds	A type change between kinds (text to integer, a type to an enum) needs a USING expression and breaks readers that expect the old type: add the new column, backfill, move readers, drop the old. Postgres 18 docs
SQLPG209	metadata only	Change a column’s default	SET DEFAULT and DROP DEFAULT change only the catalog; existing rows keep their values. Postgres 18 docs
SQLPG210	validates under a weaker lock	Set NOT NULL	SET NOT NULL alone reads the whole table under ACCESS EXCLUSIVE, unless a valid CHECK (column IS NOT NULL) constraint already proves it (since 12). So it is made as four statements: that check added NOT VALID, validated under SHARE UPDATE EXCLUSIVE while writes go on, SET NOT NULL, which the check proves without a scan, and the check dropped. Rows where the column is NULL fail the validation; the first statement’s pre-check counts them. Postgres 18 docs
SQLPG211	metadata only	Drop NOT NULL	DROP NOT NULL changes only the catalog. Postgres 18 docs
SQLPG212	ACCESS EXCLUSIVE rewrite or scan	Change a generated column’s expression	ALTER COLUMN SET EXPRESSION (17 and later) rewrites a STORED generated column under ACCESS EXCLUSIVE; on 14 to 16 the column has to be dropped and added. A VIRTUAL column (18) changes only the catalog. Postgres 18 docs
SQLPG213	metadata only	Change a column’s identity	ADD, SET and DROP IDENTITY change the column’s sequence and the catalog, not the rows. Postgres 18 docs
SQLPG214	ACCESS EXCLUSIVE rewrite or scan	Change a column’s collation	A collation change is ALTER COLUMN TYPE with COLLATE: the column’s indexes are rebuilt under ACCESS EXCLUSIVE. Postgres 18 docs
SQLPG215	metadata only	Change a column’s storage or compression	SET STORAGE and SET COMPRESSION apply to values written afterwards; existing values are not rewritten. Postgres 18 docs
SQLPG216	metadata only	Change a comment	COMMENT ON changes only the catalog. Postgres 18 docs
SQLPG217	metadata only	Add a constraint NOT VALID	ADD CONSTRAINT … NOT VALID checks new rows only and reads none of the existing ones; a foreign key takes SHARE ROW EXCLUSIVE on both tables, briefly. Validate it as a separate step. Postgres 18 docs
SQLPG218	validates under a weaker lock	Add a CHECK constraint	ADD CONSTRAINT … CHECK alone reads every row under ACCESS EXCLUSIVE. On a table that exists it is made as two statements: the check added NOT VALID (SQLPG217), which reads no rows, then VALIDATE CONSTRAINT (SQLPG220) in a transaction of its own, which reads them under SHARE UPDATE EXCLUSIVE while writes go on. Rows the check refuses fail the validation; the first statement’s pre-check counts them. Postgres 18 docs
SQLPG219	validates under a weaker lock	Add a foreign key	ADD FOREIGN KEY alone reads every row under SHARE ROW EXCLUSIVE on both tables, which blocks writes to them for the scan. On a table that exists it is made as two statements: the key added NOT VALID (SQLPG217), which takes that lock briefly and reads no rows, then VALIDATE CONSTRAINT (SQLPG220) in a transaction of its own, which reads them under SHARE UPDATE EXCLUSIVE while writes go on. Orphan rows fail the validation; the first statement’s pre-check counts them. Postgres 18 docs
SQLPG220	validates under a weaker lock	Validate a constraint	VALIDATE CONSTRAINT reads every row under SHARE UPDATE EXCLUSIVE, so reads and writes go on (ROW SHARE on a foreign key’s referenced table). Postgres 18 docs
SQLPG221	needs CONCURRENTLY	Add a primary key or unique constraint	ADD PRIMARY KEY or UNIQUE builds its index under ACCESS EXCLUSIVE. Build the unique index CONCURRENTLY first, then ADD CONSTRAINT … USING INDEX, which changes only the catalog. Postgres 18 docs
SQLPG222	ACCESS EXCLUSIVE rewrite or scan	Add an exclusion constraint	ADD CONSTRAINT … EXCLUDE builds its index under ACCESS EXCLUSIVE; there is no concurrent form. Postgres 18 docs
SQLPG223	metadata only	Drop a constraint	DROP CONSTRAINT changes the catalog under ACCESS EXCLUSIVE, briefly; a primary key’s or unique constraint’s index goes with it. Postgres 18 docs
SQLPG224	metadata only	Change storage parameters	SET and RESET of storage parameters change the catalog; fillfactor and the autovacuum parameters take only SHARE UPDATE EXCLUSIVE. Rows are not rewritten; a new fillfactor applies to pages written afterwards. Postgres 18 docs
SQLPG225	ACCESS EXCLUSIVE rewrite or scan	Make a table LOGGED or UNLOGGED	SET LOGGED and SET UNLOGGED rewrite the table under ACCESS EXCLUSIVE. Postgres 18 docs
SQLPG226	ACCESS EXCLUSIVE rewrite or scan	Change a table’s access method or tablespace	SET ACCESS METHOD (15 and later) and SET TABLESPACE rewrite the table under ACCESS EXCLUSIVE. Postgres 18 docs
SQLPG227	EXPAND AND CONTRACT	Change partitioning or inheritance	A table’s partition key, its parent or bound, INHERITS and OF type cannot be altered in place: a new table is filled and swapped in. DETACH PARTITION CONCURRENTLY (14 and later) and ATTACH PARTITION, which takes SHARE UPDATE EXCLUSIVE on the parent, are the steps. Postgres 18 docs
SQLPG228	EXPAND AND CONTRACT	Rename an object or move it to another schema	RENAME and SET SCHEMA are catalog changes, but every reader still using the old name fails at once: create the new name (a view over the old object works), move readers, drop the old. Postgres 18 docs
SQLPG229	metadata only	Rename an index	ALTER INDEX … RENAME changes only the catalog; nothing reads an index by name. Postgres 18 docs
SQLPG240	needs CONCURRENTLY	Create an index CONCURRENTLY	CREATE INDEX CONCURRENTLY builds the index without blocking writes (SHARE UPDATE EXCLUSIVE), in two scans, and cannot run inside a transaction block; a failed build leaves an INVALID index to drop. Postgres 18 docs
SQLPG241	needs CONCURRENTLY	Create an index without CONCURRENTLY	CREATE INDEX on an existing table takes SHARE, which blocks every write to the table until the build ends. Declare it CONCURRENTLY. Postgres 18 docs
SQLPG242	needs CONCURRENTLY	Drop an index	DROP INDEX takes ACCESS EXCLUSIVE on the table; DROP INDEX CONCURRENTLY waits for running queries instead, and cannot run inside a transaction block. Postgres 18 docs
SQLPG243	needs CONCURRENTLY	Change an index	An index’s definition cannot be altered: it is dropped and created, both CONCURRENTLY to keep writes going (SQLPG240, SQLPG242). Postgres 18 docs
SQLPG250	metadata only	Change a view’s query, keeping its columns	CREATE OR REPLACE VIEW replaces the query under ACCESS EXCLUSIVE on the view when the new query keeps the old columns, by name, in order, and only appends new ones. Postgres 18 docs
SQLPG251	EXPAND AND CONTRACT	Change a view’s columns	A view whose columns are removed, renamed or reordered cannot be replaced: it is dropped and created, and every view and reader on top of it with it. Postgres 18 docs
SQLPG252	ACCESS EXCLUSIVE rewrite or scan	Change a materialized view’s query	A materialized view’s query cannot be altered: it is dropped and created, and its rows are computed again, with every view on top of it dropped too. Postgres 18 docs
SQLPG253	metadata only	Change a view’s options	ALTER VIEW … SET (check_option, security_barrier, security_invoker) changes only the catalog. Postgres 18 docs
SQLPG260	metadata only	Add an enum label	ALTER TYPE … ADD VALUE changes only the catalog; since 12 it can run in a transaction block, but the new label cannot be used in that transaction. Postgres 18 docs
SQLPG261	EXPAND AND CONTRACT	Remove or reorder enum labels	An enum label cannot be removed and labels cannot be reordered in place: a new type is created, columns are moved to it, and the old one is dropped. Postgres 18 docs
SQLPG262	validates under a weaker lock	Add a domain constraint	ALTER DOMAIN ADD CONSTRAINT (and SET NOT NULL) checks every column of the domain’s type in every table; ADD CONSTRAINT … NOT VALID skips existing rows and VALIDATE CONSTRAINT checks them later. Postgres 18 docs
SQLPG263	metadata only	Change a domain’s default or drop its constraint	ALTER DOMAIN SET DEFAULT, DROP NOT NULL and DROP CONSTRAINT change only the catalog. Postgres 18 docs
SQLPG264	EXPAND AND CONTRACT	Change a domain’s data type	A domain’s underlying type cannot be altered: a new domain is created and columns are moved to it. Postgres 18 docs
SQLPG265	metadata only	Change a sequence’s options	ALTER SEQUENCE changes the sequence’s own row; tables are not touched. A sequence’s AS type bounds its values. Postgres 18 docs
SQLPG266	metadata only	Change an extension’s version or schema	ALTER EXTENSION UPDATE runs the extension’s update script, which may itself alter objects; SET SCHEMA moves its objects. Postgres 18 docs
SQLPG267	metadata only	Change a schema’s owner	ALTER SCHEMA … OWNER TO changes only the catalog. Postgres 18 docs
SQLPG268	EXPAND AND CONTRACT	Change an object’s kind	A view and a materialized view, or a table and either, are different kinds of object: the old one is dropped and the new one created. Postgres 18 docs
SQLPG280	metadata only	Replace a function’s or procedure’s definition	CREATE OR REPLACE FUNCTION (or PROCEDURE) replaces the body and attributes in the catalog; it locks no table, and a call already running finishes with the old definition. The parameter types, the result and the input parameters’ names have to stay the same. Postgres 18 docs
SQLPG281	metadata only	Drop and create a function or procedure	CREATE OR REPLACE refuses a change of the result type, the output parameters, an input parameter’s name, a removed parameter default or the routine’s kind: the routine is dropped and created in one transaction. Nothing on the server depends on it (a view, a trigger, a column default would make the DROP fail). Postgres 18 docs
SQLPG282	EXPAND AND CONTRACT	Change a function’s result or parameters while other objects depend on it	DROP FUNCTION refuses while a view, a trigger, a column default or another routine depends on the function, and CREATE OR REPLACE cannot make this change. Create the new definition under a new name (or signature), move what depends on it, then drop the old. Postgres 18 docs
SQLPG283	metadata only	Create a trigger on an existing table	CREATE TRIGGER takes SHARE ROW EXCLUSIVE on its table: writes and other schema changes to the table wait until the transaction commits, but no row is read. The applier’s lock_timeout bounds the wait behind long-running transactions. Postgres 18 docs
SQLPG284	metadata only	Change a trigger	CREATE OR REPLACE TRIGGER (14 and later) replaces the trigger under SHARE ROW EXCLUSIVE on its table. A constraint trigger has no OR REPLACE, and a trigger moved to another table is another trigger: each is dropped (ACCESS EXCLUSIVE, briefly) and created in one transaction. Postgres 18 docs
SQLPG285	drop	Drop a trigger	DROP TRIGGER takes ACCESS EXCLUSIVE on its table, briefly; no row is read or lost, and the table’s writes no longer fire it. Postgres 18 docs
SQLPG290	metadata only	Create a policy on an existing table	CREATE POLICY takes ACCESS EXCLUSIVE on its table, briefly; no row is read. Where row-level security is on, the rows each role sees change when the transaction commits. Postgres 18 docs
SQLPG291	metadata only	Change a policy	ALTER POLICY changes its roles, USING and WITH CHECK under ACCESS EXCLUSIVE on the table, briefly. Its command, AS RESTRICTIVE and table cannot be altered, nor an expression taken away: the policy is dropped and created in one transaction, so no session sees the table without it. Postgres 18 docs
SQLPG292	drop	Drop a policy	DROP POLICY takes ACCESS EXCLUSIVE on its table, briefly; the rows it let a role see are hidden from that role at once, or, if it was the last policy, every non-owner sees none. Postgres 18 docs
SQLPG293	metadata only	Turn row-level security on or off, or force it on the owner	ALTER TABLE … ENABLE, DISABLE, FORCE or NO FORCE ROW LEVEL SECURITY changes only the catalog, under ACCESS EXCLUSIVE, briefly. With it on and no policy that lets a role in, that role sees no rows; FORCE subjects the table’s owner too. Postgres 18 docs
SQLPG294	metadata only	Change a role’s attributes	ALTER ROLE changes the role in the cluster’s catalog, for every database on the server, at once; sessions already open keep the attributes they started with until they reconnect. Postgres 18 docs
SQLPG296	metadata only	Grant a privilege	GRANT adds to the object’s privileges in the catalog; no row is read and the role can use it from its next statement. Postgres 18 docs
SQLPG297	metadata only	Revoke a privilege	REVOKE removes from the object’s privileges in the catalog. The role loses it at once: its next statement that needs it fails, so move every reader off it first. Postgres 18 docs
SQLPG298	metadata only	Change default privileges	ALTER DEFAULT PRIVILEGES changes what objects created afterwards are given; objects that exist keep their privileges. Postgres 18 docs
SQLPG270	drop	Drop an object	The object and, for a table, a materialized view or a sequence, its data are gone. Postgres 18 docs