pg_automergev0.1.0

Versioning and upgrades

The extension version is the crate version (default_version = '@CARGO_VERSION@' in pg_automerge.control; both crates carry it), currently 0.1.0, the first release; pgrx generates the install script pg_automerge--X.Y.Z.sql for the build, and the Docker images' version labels and tags come from it (scripts/versions.sh). A release is a GitHub release with the tag v<version> (the release workflow checks it against Cargo.toml; see Releasing) and an entry in the Changelog.

Policy:

  • The current version's generated script is committed as sql/snapshots/pg_automerge--X.Y.Z.sql (cargo pgrx schema pg18 -o ...). Until the version is released it is regenerated with every SQL change; from its release on it is never edited again.
  • Any change to the SQL surface after a release bumps the version, adds that version's snapshot, and comes with a hand-written sql/pg_automerge--A--B.sql from the previous version (cargo pgrx install and package ship every sql/pg_automerge--*--*.sql, so the Docker images do too; the snapshots below sql/snapshots/ are not shipped). 0.1.0 has none yet: the images hold only its install script. C symbols that an older version's script references stay exported: the new library serves the old catalog from the moment it is installed until the ALTER EXTENSION .. UPDATE.
  • A library-only change (a bug fix, a faster path) needs no new version as long as no result changes; one that changes a result of an IMMUTABLE function (the jsonb view) is called out in the changelog with the REINDEX/rewrite it needs (see below).

Writing an upgrade script#

An upgrade script must leave exactly the catalog a fresh CREATE EXTENSION of the new version has: the same member objects, definitions, labels, symbols, comments, ACLs and dependencies. Anything else makes the databases that were updated differ from the ones created later, in ways that show up only much later (a dump that restores differently, a function that is STABLE in one and IMMUTABLE in the other). tests/upgrade.sh compares the two catalogs, and the way to keep them equal is to change as little as possible:

  • Only what changed. Diff the previous release's snapshot against the new install script and write exactly that difference; leave every unchanged object alone, so no dependent object (index, generated column, trigger, view) is touched and no REINDEX or rewrite is needed. A fix inside the library that returns the same results (as the deep-block fix does: the jsonb of every document is unchanged) needs no SQL at all.
  • New functions: plain CREATE FUNCTION. No older catalog has them, and a function of the same signature that someone put into the extension's schema makes the update fail ("already exists") rather than being replaced.
  • Redefining a function: CREATE OR REPLACE FUNCTION, with the same signature: it keeps the function's OID, so views and other objects that depend on it survive. That is safe in an extension script because PostgreSQL refuses to replace an object the extension does not own ("is not a member of extension"): a user's function of the same signature makes the update fail rather than being adopted.
  • Unqualified names, as in pgrx's install script, so the extension stays relocatable: ALTER EXTENSION .. UPDATE runs the script with search_path set to the extension's current schema and then pg_temp, with pg_catalog searched first, so jsonb and text are the built-in types and automerge the extension's own.
  • The script starts with the usual \echo .. \quit guard against running it with psql.

Then, in the same change: the snapshot of the new version, a case in tests/upgrade.sh for every object the script creates (a user's object of the same name in the extension's schema must make the update fail), and a Changelog entry that says whether the update needs more than ALTER EXTENSION pg_automerge UPDATE.

Upgrade tests#

  • tests/upgrade.sh (part of mise run test) installs every snapshot under a scratch version name (with the first hop of each of its version's upgrade paths, which must be installed byte for byte as in sql/; for the current version's snapshot, an empty script), creates what a deployed database has on it (a STORED generated doc::jsonb column, GIN and B-tree expression indexes, a view and a SQL function over the extension's functions, a view on automerge_spans, an automerge_notify() trigger, documents including rich text and a 2,000-level deep block), reads them all before the update (the old catalog on the new library), runs ALTER EXTENSION pg_automerge UPDATE, and compares the extension's catalog with a fresh CREATE EXTENSION (tests/catalog.sql: member objects and their comments, the members' dependencies, function definitions with their labels, symbols and ACLs, aggregates, types, casts, operators, the extension row). Then the documents' fingerprints (bytes, heads, the cast, the generated column, the views, the SQL function), the indexes (valid, used, and agreeing with a sequential scan), the trigger's notification, automerge_spans and automerge_memory_usage(). It repeats the update on an extension relocated with SET SCHEMA first (every member ends up in that schema). A snapshot of the current version is required and compared as is, so an SQL change without a new snapshot (and, after a release, a version bump) fails. With 0.1.0 as the only version, that is all it checks; from the next version on it runs the real update from every released version.
  • tests/docker.sh does the snapshot part again with the image's own scripts and library (see Docker image).
  • tests/docker_upgrade.sh (mise run docker-upgrade-test, not part of docker-test) upgrades a real deployment of the previous release (the newest snapshot older than the current version, or PG_AUTOMERGE_OLD_VERSION): a container of that release's image (PG_AUTOMERGE_OLD_IMAGE, a tag or an image ID, such as the published ghcr.io/theknarf/pg-automerge:0.1.0, or by default built from the release tag v<version> with that revision's own scripts/docker-build.sh --full, under a test tag; its install script must be that version's snapshot) creates tables with a generated doc::jsonb column, GIN and B-tree expression indexes, a view, a notify trigger and documents; it is stopped and the new image started on the same volume (init not re-run, still the old version, the same data, and the deep block stored through the generated column); ALTER EXTENSION pg_automerge UPDATE; then the same data, the indexes valid, used and agreeing with a sequential scan, the trigger's notification, automerge_spans (54000 on the deep block), the memory counters, the catalog equal to a fresh install's in the same container, and clean server logs. It follows the compose steps of Updating as written: the cluster's superuser is an app's own POSTGRES_USER, not postgres (so the cluster has no postgres role), the dump of step 1 and the ALTER EXTENSION and version check of step 5 are that page's commands (read from its MDX source, docker compose exec run as docker exec, <user>, <db> and <version> filled in), and a service with image: and a build: section with args: PG_AUTOMERGE_VERSION (a common app setup) fails to build with the old version (the Dockerfile's version guard) and builds after step 3's edit. While 0.1.0 is the current version there is no older snapshot, and it reports that it skipped. For the first upgrade: keep the 0.1.0 image (or publish it), bump the version, add the upgrade script and the new snapshot, mise run docker-build, then PG_AUTOMERGE_OLD_IMAGE=pg-automerge:0.1.0 mise run docker-upgrade-test.

Data compatibility:

  • Stored values are Automerge's save_nocompress() format, which any later Automerge must load. crates/pg_automerge_core/tests/fixtures/ automerge-<version>/ holds documents saved by each automerge version the extension shipped with (written by examples/gen_format_fixtures.rs; 0.12.0 now), and tests/format_fixtures.rs checks that they still load with the same heads and jsonb, and that normalizing them still gives the same bytes.
  • The automerge crate is pinned (=0.12.0). Upgrading it, or changing the jsonb mapping, must be called out in the release notes:
    • A different jsonb result for the same document changes what the IMMUTABLE cast returns, so expression indexes on doc::jsonb must be rebuilt (REINDEX) and stored generated columns rewritten (e.g. UPDATE t SET doc = doc), or they disagree with fresh computations.
    • A different canonical encoding (the format fixture test fails) means re-saved values differ from stored ones: doc::bytea of old rows stops matching new saves of the same history (heads do not change), and each row is re-encoded on its next write.
  • Text output (\x + hex of the stored bytes) is the dump format and is stable, so dump and restore works across versions.