Kinds of operators
The kinds of change an operator records, and the two commands that write ops from SQL.
Operators by category
The operator catalog is grouped by what kind of change the op expresses. Operators lists every operator with its category and which commands handle it. The categories:
- Schema:
CreateSchema. Root element. - Table:
CreateTable,DropTable,RenameTable,CopyTable,ExtractTable/JoinTable(column-axis split/merge),InlineTable/UnjoinTable(fold an extracted child back in, take a join apart),SplitTable/MergeTable(row-axis split/merge),MoveColumn,SetReplicaIdentity,SetTableComment, the reference-data ops (SetReferenceData,WitnessReferenceData), and the declarative partitioning ops below.- Partitioning:
CreatePartition(born-as-childPARTITION OF),AttachPartition/DetachPartition(subordinate an existing table, emancipate one back),PartitionTable/DepartitionTable(identity-preserving convert between a plain heap and a partitioned parent). These are Table-category ops, not a category of their own: partitioning is a physical storage attribute of a table that persists across the change, so the op acts on a table the way a rename or a comment does.
- Partitioning:
- Column:
AddColumn,DropColumn,RenameColumn,CastColumn,CopyColumn,SetDefault/DropDefault, identity ops (AddIdentity,DropIdentity,SetIdentityGeneration,SetIdentityOptions),RenameOwnedSequence, NOT NULL ops (AddNotNull,DropNotNull),DropGeneratedExpression,SetColumnComment. - Constraint:
AddPrimaryKey,ChangePrimaryKey,DropPrimaryKey,AddForeignKey,DropForeignKey,AddUnique,DropUnique,AddCheck,DropCheck,AddExclude,DropExclude,RenameConstraint,ValidateConstraint,SetConstraintDeferrable,SetConstraintInitiallyDeferred,SetConstraintEnforced,SetConstraintInherit,SetConstraintComment. - Index:
CreateIndex,DropIndex,RenameIndex. - View:
CreateView,ReplaceView,RenameView,DropView, and the materialized-view ops (CreateMaterializedView,ReplaceMaterializedView,RenameMaterializedView,DropMaterializedView). - Type:
CreateType,DropType,RenameType,AddEnumValue,RenameEnumValue. - Policy:
CreatePolicy,ReplacePolicy,RenamePolicy,DropPolicy,SetRowLevelSecurity. - Function:
CreateFunction,ReplaceFunction,RenameFunction,DropFunction,SetFunctionComment. - Trigger:
CreateTrigger,RenameTrigger,DropTrigger,EnableTrigger,DisableTrigger. - Extension:
CreateExtension,DropExtension(under the schema-root category). - Raw:
RunRawScript(DO $$ … $$;escape hatch). - Log:
Seal, which pins log integrity, andDeclareReversal, which declares how a sealed raw script is undone. Neither is a schema operation.
The change operators (Rename, Cast, Extract/Join, Split/Merge, the partition converts, Copy, Move) are the ones a migration tool can’t express. Create/Add/Drop exist to bootstrap and trim the schema; the change operators are what drives architectural evolution.
The five data-motion change operators
(CopyTable, ExtractTable, JoinTable, SplitTable,
MergeTable) are authored via op add and replay/render
correctly through snapshot. Two verbs also recognize a subset:
drift offers Copy / Extract / Join / Merge (plus the
partition converts) as questions from an end-state diff, and import mints a solid-match ExtractTable from a
migration script. SplitTable is recognized by
neither from end-state, because its per-row predicate leaves no schema
residue; author it with op add. The limit is the information a schema
holds, not a missing capability: drift compares one cut with one
schema, and an operator whose meaning is in its rows, not in the shape
it leaves, cannot be read back from the shape. See
retrofit drift and
retrofit import for the recognizer
details.
ExtractTable splits a table along its columns, and whether any live
column is left unassigned decides what happens to the source. With
columns left, the source survives in place holding them (the survivor
arm; ElementID says what it keeps). With every live
column assigned, the source is consumed. Each new table is one of two
kinds. One that copies the source’s primary key takes every source row,
and a foreign key ties it back to the table that holds that key; the
foreign key has no ON DELETE action, so a row of that table cannot be
deleted while a child row refers to it. A lookup is keyed by columns it
shares with the source and holds one row per distinct non-NULL key
value, so a source row whose key column is NULL loses its moved values.
retrofit op info ExtractTable lists every refusal, and Record a
table split or merge yourself
gives the recipe.
SplitTablemoves rows into new tables, each with its own identity, and keeps its source. PostgreSQL partitioning is a different change: the five partitioning operators (CreatePartition,AttachPartition,DetachPartition,PartitionTable,DepartitionTable) keep the parent table’s identity and change how its rows are stored. A child table defined withINHERITSand aCHECKis partitioning too, never a split.
No operator covers these changes:
- privileges and ownership:
GRANT,REVOKE, default privileges andOWNER TO; - making a table logged or unlogged (
SET LOGGED,SET UNLOGGED), or changing its access method; - moving a column that a key, constraint or index depends on;
MoveColumnmoves only a column none of them depends on; - view options (
security_invoker,security_barrier,check_option) set withALTER VIEW … SET; - domains;
CreateTypecovers enums and composite types; - a sequence no column owns; a column’s identity or serial sequence is part of the column;
- reordering a table’s columns, or removing an enum value, which PostgreSQL has no statement for;
- the cluster, storage and language surface: databases, tablespaces,
foreign data wrappers and collations;
CREATE OPERATOR,CAST,AGGREGATEandLANGUAGE;SET STORAGE,SET COMPRESSION,REINDEXandCLUSTER;REFRESH MATERIALIZED VIEW, which changes a view’s rows and no catalog state; and typed tables (OF,NOT OF).
Authoring ops: import vs drift
Two ingress verbs, opposite postures. import is imperative:
it reads a .sql source and appends one op per recognized DDL
statement, with no inference. drift is declarative: it
reads a .sql source as the database’s current state and emits
the ops that converge the oplog to that state.
Rows take different paths too. import sets every INSERT,
UPDATE, DELETE and MERGE it reads aside in a data file, as the
changes a script states; drift captures only the INSERT rows of the
tables declared as reference data, as part of the state.
A pg_dump --schema-only is a state, not a list of changes: import
does not append one to an oplog that already has ops, since that would
record every object a second time (--force starts the oplog over
instead), and drift is the command that reads it.
Decision rule: “Have I authored operations I want recorded?” →
import. “Has the database moved without me, and I want the oplog
to catch up?” → drift. See Import existing SQL and
retrofit drift for the full per-verb behavior.