Writing an Upgrade Script

Each upgrade script is a hand-written delta file that re-runs the SQL changes made during one release cycle. Since every release so far has consisted of in-place CREATE OR REPLACE FUNCTION rewrites or purely additive CREATE FUNCTION / CREATE CAST statements, writing an upgrade script is usually a matter of copy-pasting the modified function bodies from sql/provsql.common.sql (or sql/provsql.14.sql) into a new file under sql/upgrades/:

/**
 * @file
 * @brief ProvSQL upgrade script: A.B.C → X.Y.Z
 *
 * <one-paragraph summary of SQL-surface changes>
 */

SET search_path TO provsql;

CREATE OR REPLACE FUNCTION ... ;
-- or CREATE FUNCTION, CREATE CAST, etc.

The script runs inside an implicit transaction (the whole ALTER EXTENSION UPDATE is one transaction), so any error rolls everything back. The script is executed once when transitioning from A.B.C to X.Y.Z; it does not have to be idempotent on its own.

Non-idempotent statements such as CREATE SCHEMA, CREATE TYPE (without IF NOT EXISTS), CREATE TABLE, CREATE OPERATOR, CREATE CAST, and CREATE AGGREGATE are allowed only if the upgrade is the first to introduce the corresponding object -- PostgreSQL will not re-run the script against an already-upgraded installation.

If a release genuinely introduces no SQL-surface change, the upgrade script still has to exist so that PostgreSQL can offer the update path, but it may be a no-op (just the header comment and a SET search_path). The release.sh script (see below) auto-generates such a no-op file when it detects no SQL diff since the previous tag.

The On-Disk mmap ABI

In-place upgrades only cover the SQL catalog state. ProvSQL's persistent circuit lives in four memory-mapped files (provsql_gates.mmap, provsql_wires.mmap, provsql_mapping.mmap, provsql_extra.mmap) that are pure plain-old-data dumps of C and |cpp| structs -- no format version header, no magic bytes, no schema.

An upgrade therefore requires that the binary layout of GateInformation (in MMappedCircuit.h), the gate_type enum (in provsql_utils.h), and the MMappedUUIDHashTable slot structure are all byte-compatible between the two versions. Since 1.0.0 these layouts have been deliberately frozen: zero commits to any of them. The block comments at the top of those files explicitly call this out.

If a future contribution has to touch the on-disk layout, the upgrade story has to change. Two reasonable options:

1. Bump the on-disk format version. Add a small format-version header to each of the four *.mmap files, write a migration pass that rewrites the old format to the new one at worker startup, and gate the new pass on detecting an old header. The upgrade script for that release also needs to mention the one-time migration cost.
2. Break upgrades for that release. Document it in the release notes and ship a hard failure at worker startup if the existing *.mmap files look like an old format -- safer than silently misreading the bytes. Users then go through DROP EXTENSION provsql CASCADE; CREATE EXTENSION provsql, losing only the circuit data (their base tables are unaffected).

Workflow During Development

Release-engineering for upgrades follows this rhythm:

• When a contributor adds or modifies SQL in provsql.common.sql or provsql.14.sql, they do not have to write a committed upgrade script themselves. The maintainer who cuts the next release is responsible for writing it. During the dev cycle, the Makefile auto-generates an empty dev-cycle upgrade script (see above) so that ALTER EXTENSION provsql UPDATE is structurally reachable to default_version; that file is purely a placeholder and does not replay the SQL deltas introduced during the cycle.
• When cutting a release, release.sh checks for sql/upgrades/provsql--<prev>--<new>.sql and refuses to proceed if it is missing unless git diff shows no SQL source changes since the previous tag (in which case it auto-generates a no-op script and commits it).
• When touching any mmap-serialised struct or enum, the contributor must either preserve binary compatibility (e.g., by appending a new gate_type enumerator at the end, never in the middle) or coordinate a format-version bump with the maintainer -- see the warning block at the top of provsql_utils.h and MMappedCircuit.h.

Automated Testing of the Upgrade Path

The upgrade chain is exercised end-to-end by a pg_regress test, test/sql/extension_upgrade.sql. Because the test is destructive (it DROPs the extension CASCADE to replay the upgrade from a clean 1.0.0 state), it must run strictly after every other test in the suite, including the schedule.14-only tests that follow schedule.common on PostgreSQL 14+. To guarantee that ordering regardless of which source schedule files are active, the Makefile.internal rule that assembles test/schedule appends a single test: extension_upgrade line after concatenating schedule.common and (where applicable) schedule.14:

test/schedule: $(wildcard test/schedule.*)
    cat test/schedule.common > test/schedule
    if [ $(PGVER_MAJOR) -ge 14 ]; then \
        cat test/schedule.14 >> test/schedule; \
    fi
    echo "test: extension_upgrade" >> test/schedule

so the final schedule always ends with extension_upgrade. The test is therefore not listed in either schedule.common or schedule.14 source files.

The test itself:

1. Drops the current provsql extension (CASCADE destroys all provenance-tracked state from preceding tests).
2. Installs the oldest supported version via CREATE EXTENSION provsql VERSION '1.0.0'. PostgreSQL picks up provsql--1.0.0.sql from the extensions directory -- a frozen install-script fixture generated at build time from sql/fixtures/provsql--1.0.0-common.sql and sql/fixtures/provsql--1.0.0-14.sql (themselves exact copies of the historical 1.0.0 source files, extracted via git show v1.0.0:sql/...).
3. Runs ALTER EXTENSION provsql UPDATE (no TO clause), so PostgreSQL advances the extension all the way to whatever the current default_version is -- walking the full chain of committed upgrade scripts under sql/upgrades/ and, on a development build, the auto-generated empty dev-cycle upgrade script described below.
4. Asserts that the post-upgrade extversion equals default_version (read from pg_available_extensions) via a boolean comparison, so the expected output never contains a hard-coded version string and the test stays correct as master advances.
5. Runs a tiny smoke query against the upgraded extension to confirm that the query rewriter, add_provenance, create_provenance_mapping, and a compiled semiring evaluator still work.

The test runs on every PostgreSQL version in the CI matrix (10 through 18), because it lives inside the standard pg_regress suite. It catches regressions in: committed upgrade scripts, the DATA wildcard in Makefile.internal that ships them, and the binary stability of the mmap format across versions.

ifneq ($(findstring -dev,$(EXTVERSION)),)
LATEST_RELEASE = $(shell git describe --tags --abbrev=0 --match 'v[0-9]*' \
                          2>/dev/null | sed 's/^v//')
ifneq ($(LATEST_RELEASE),)
DEV_UPGRADE = sql/$(EXTENSION)--$(LATEST_RELEASE)--$(EXTVERSION).sql
endif
endif

Manual Testing

To exercise the same upgrade path interactively:

# Install the current build (which now ships the 1.0.0 fixture
# and all upgrade scripts alongside the current install script).
make && sudo make install
sudo systemctl restart postgresql

# Install the extension at the old version and populate state.
psql <<'SQL'
CREATE DATABASE upgrade_test;
\c upgrade_test
CREATE EXTENSION provsql VERSION '1.0.0';
SELECT extversion FROM pg_extension WHERE extname='provsql';
-- ... exercise the extension, populate some provenance state ...
SQL

# Apply the upgrade chain.
psql -d upgrade_test -c "ALTER EXTENSION provsql UPDATE TO '1.2.1';"
psql -d upgrade_test -c "SELECT extversion FROM pg_extension WHERE extname='provsql';"
# ... verify the extension still works; mmap state is intact ...