retrofit
0.15.0-wip.89
retrofit.sh

These docs are for 0.15.0-wip.89, a WIP, and may describe what no release does yet. The newest release is 0.14.0: this page for 0.14.0.

PostgreSQL versions

This page says which PostgreSQL majors this build supports, what it reads and writes at each one, and what the engine version governs. Only the major is modeled. Each guide states its own verb’s row and links here.

Supported majors

MajorTested on a live serverReads its pg_dumpWrites SQL it runsReads its new syntaxWrites its new syntax
15YesYesYesYesYes
16YesYesYesYesYes
17 (default)YesYesYesYesYes
18YesYesYesNoNo

This build reads and writes PostgreSQL 17 syntax at every engine version. PostgreSQL 18’s own syntax (VIRTUAL generated columns, NOT ENFORCED constraints, WITHOUT OVERLAPS and PERIOD keys, and ALTER CONSTRAINT … [NO] INHERIT) is not available: op add and op revise refuse it with syntax-not-available at every engine version, a verb that reads a log holding it refuses with the same code, and import and drift exit 1 with parse-error on it.

17 is the value init writes to [project] engine_version, and the one a schema resolves to when nothing names another. Any other value refuses with unknown-engine-version. The value is a bare major: 14, 19, 18.1, 018, v18 and 18 all refuse.

The engine version is set by any of:

  • schema create --engine-version <major> or import --engine-version <major>, which set a new schema’s pin and emit target together;
  • schema engine <major>, which moves an existing schema’s pin and emit target (--config-only moves the target alone);
  • engine_version under [project] (the value a new schema takes when no flag names one) or under a [[schema]] row (that schema’s emit target);
  • --using=postgres:<major> on snapshot or migrate, which sets the emit target for that run.

What the engine version governs

Each schema carries two values.

  • The pin is the oplog’s # engine-version: <major> header: the major production runs. op add refuses an op the pin’s major does not take: below 18, a DropForeignKey whose reverse re-adds a partitioned table’s key NOT VALID refuses with reverse-policy-partitioned-fk.
  • The emit target is the major snapshot and migrate write SQL for. It resolves from --using=postgres:<major>, then the schema’s [[schema]] engine_version, then [project] engine_version, then 17. The snapshot banner names it (engine_version=<major>) and every migrate file opens with -- engine: postgres <major>.

The target may not lag the pin: a schema whose [[schema]] engine_version is below its oplog’s header fails at config load with engine-version-target-below-oplog.

No command connects to a database, so neither value is read from a server. The header line states the major the script was written for; PostgreSQL at that major decides whether its DDL is valid.

Per verb

Every verb does the same at each supported major, and snapshot and migrate emit the same SQL at each; only the banner and the header line name the major. The exceptions are a NOT VALID foreign key on a partitioned table, a partition’s columns at ATTACH, and the identity ops on a partition’s column. The known gaps for 15 and 16 also apply.

Only 18 accepts a NOT VALID foreign key on a partitioned table: migrate renders it at 18, and 15 to 17 refuse it with migrate-blocked-on-partitioned-fk-not-valid.

PostgreSQL’s ATTACH PARTITION takes a different set of partition columns at each major, and the AttachPartition column check follows the pin:

Partition column151617 and 18
Its own identityAttaches, keptAttaches, keptRefused
STORED generated under a plain parent columnAttaches, keptRefusedRefused
Generated with its own expression, not its parent’sRefusedAttaches, keptAttaches, kept

import and op add refuse a refused shape with attach-partition-columns-mismatch. snapshot and migrate at an emit target other than the pin refuse an ATTACH the pin accepts and the target does not, with unsupported-on-engine-version, and schema engine refuses to move the pin to such a major. retrofit migrate lists each case.

On a 15 or 16 pin, AddIdentity, DropIdentity, SetIdentityGeneration and SetIdentityOptions on a partition’s column (<partition>.<column>) alter that partition’s own identity in place: op add records the op with partition=, and migrate renders it as ALTER TABLE <partition> ALTER COLUMN …. The identity’s sequence options (START WITH, CACHE, …) stay with the partition through the ATTACH and the DETACH. drift plans one for a partition that lost its own identity, holds it under another generation or with other sequence options, and import writes one for such a statement after the ATTACH. From 17 on, op add refuses them on a partition’s column with column-op-on-partition, naming the partitioned root, and migrate at a 17 or 18 target refuses one in the window it emits, forward or as a --down inverse, with unsupported-on-engine-version.

The PostgreSQL 18 syntax this build refuses with syntax-not-available is the SetConstraintEnforced and SetConstraintInherit operators and these parameter values: generated_kind=virtual on AddColumn; enforced=false on AddForeignKey and AddCheck; period=true on AddForeignKey; and without_overlaps=true on AddPrimaryKey, AddUnique and ChangePrimaryKey. op info marks each as not available. validate, op seal, identify, snapshot and migrate refuse a log that holds one, and drift refuses to append to one; revise each op to its PostgreSQL 17 value (generated_kind=stored, enforced=true, period=false, without_overlaps=false), or regenerate the log without it.

Known gaps

15 and 16:

  • RenameOwnedSequence on the sequence of a partition’s own identity refuses with column-op-on-partition, though PostgreSQL renames it. The hint names the workaround: DetachPartition the partition, run RenameOwnedSequence on it, then AttachPartition it again.

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

Published .