retrofit
0.14.0
retrofit.sh

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-child PARTITION 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.
  • 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, and DeclareReversal, 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.

SplitTable moves 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 with INHERITS and a CHECK is partitioning too, never a split.

No operator covers these changes:

  • privileges and ownership: GRANT, REVOKE, default privileges and OWNER 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; MoveColumn moves only a column none of them depends on;
  • view options (security_invoker, security_barrier, check_option) set with ALTER VIEW … SET;
  • domains; CreateType covers 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, AGGREGATE and LANGUAGE; SET STORAGE, SET COMPRESSION, REINDEX and CLUSTER; 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.

Before 1.0, any release may change behavior; the release notes list what changed.

Published .