Contents

• pg_statecharts
  • Installing
  • Versions
  • Backups
  • Migrating to 0.1.0
    • Fresh install
    • From 0.0.0, the Rust build
    • From a sqitch deployment
    • What the upgrade changes
  • Versions and the semver extension
    • Installing semver afterwards
  • Layout
  • Tests

pg_statecharts

Statecharts (hierarchical state machines) in PostgreSQL, packaged as an extension.

This is the half you install in production: the tables that hold statechart definitions and running machines, and the functions that drive them. The tooling that turns .scxml files into migrations lives in a separate extension, pg_statecharts_dev, so that nothing able to read and write files on the database host has to be installed on a production server.

Installing

The extension is pure SQL. There is nothing to compile, so one set of files works on every PostgreSQL version, architecture and operating system, and installing is just copying two files into PostgreSQL’s extension directory.

./install.sh
# or, for a PostgreSQL that is not first on your PATH:
./install.sh /usr/lib/postgresql/18/bin/pg_config

install.sh needs nothing but a shell and pg_config. If you would rather use PGXS, make install does the same thing and produces the same files.

On Debian and Ubuntu there is a package per PostgreSQL major version on the releases page. It contains only this extension, so it is the one to put on production servers:

sudo dpkg -i pg-statecharts-18_0.1.0.deb

The releases also carry a tarball with both extensions, whose top-level install.sh installs just this one unless given --dev.

Then, in the database:

create extension pg_statecharts cascade;

cascade is there to pull in ltree, the only dependency. It ships with PostgreSQL as part of the standard contrib modules, so there is nothing to fetch or build. cascade installs it into your current default schema, and the extension expects to find it in public: two of its triggers name public.ltree outright, because they run during pg_restore, which searches no schema at all (see Backups), and the rest rely on public being on the search_path.

Versions

Tested against PostgreSQL 16, 17 and 18.

Backups

pg_dump leaves out the tables of an extension unless the extension asks for them to be included. pg_statecharts does, for every table and sequence in the fsm schema but one, so a plain pg_dump of your database contains the statecharts, the machines and their current states, and pg_restore puts them back with the sequences continuing where they left off.

The one exception is fsm.state_machine_event, the inbox queue, which stays out of the dump along with its sequence. An event is handled in the same transaction that inserts it, so a committed row has already been applied and the state it produced is in the backup; what remains is a debugging log that grows much faster than it is ever read. Leaving it out also avoids a hazard: the insert trigger fires while pg_restore loads rows, so an event that did somehow sit unhandled in the dump would be handled again on the restored database, moving machines that were dumped in a settled state and running their on_entry and on_exit callbacks a second time. A restored database starts with an empty event queue.

0.0.0 did not. A backup taken under 0.0.0 holds every application row with its state_machine_id and none of the machines those ids point at. Restoring one leaves the fsm tables empty and the identity sequences starting over from 1, so the next machines created collide with the ids the application tables still carry, typically as a unique index violation on a state_machine_id column. There is no repairing such a dump; take a fresh backup as soon as the upgrade has been applied.

pg_restore loads the rows of fsm.state through the same trigger that fills in parent_path on a normal insert, and that trigger needs the parent row to already be there. pg_dump writes rows in the order they sit in the table, which is insertion order unless a parent state was updated after its children were created. If a restore ever fails on fsm.state with Invalid parent_id, restore in sections and load the data with triggers off:

pg_restore -d mydb --section=pre-data backup.dump
pg_restore -d mydb --section=data --disable-triggers backup.dump
pg_restore -d mydb --section=post-data backup.dump

The registration only exists in the catalog of the database the extension was created or upgraded in. A database that already had 0.1.0 installed from before the registration was added has to drop extension pg_statecharts cascade and create it again to pick it up, which also drops the fsm data, so that only makes sense on a development database.

Migrating to 0.1.0

There are three places a database can be starting from, and the ending point is the same for all of them: the extension at 0.1.0, with the version column on the built-in fsm.semver domain and the semver extension gone.

Starting point	What to run
Nothing installed	create extension pg_statecharts cascade;
0.0.0, the Rust build	alter extension pg_statecharts update;
Deployed from deploy/ with sqitch	create extension pg_statecharts version 'sqitch'; then alter extension pg_statecharts update;

Both upgrades have to be run by a superuser, even though superuser = false in the control file lets a plain user install the extension from scratch. The update removes the catalogued dependency on the semver extension, and there is no supported statement for dropping an extension-to-extension dependency edge, so it deletes the pg_depend row directly; drop extension semver below it needs to own that extension or be a superuser too.

In every case the new extension files have to be installed first, as described under Installing. The sqitch upgrade is covered by CI: a job deploys deploy/ with sqitch, adopts it, upgrades it and checks the result owns exactly the objects a fresh install does. The 0.0.0 path is not exercised any more – the last database on 0.0.0 has been migrated – but the upgrade script is still shipped, and the sqitch path runs through it.

Fresh install

Nothing to migrate. create extension pg_statecharts cascade creates the fsm schema and everything in it. Statecharts then come from .scxml files through pg_statecharts_dev, either imported directly or as generated sqitch migrations.

From 0.0.0, the Rust build

Install the new files as above and then:

alter extension pg_statecharts update;

On Debian the 0.0.0 package was called pg-statecharts, and the new pg-statecharts-<PG> package declares that it replaces it, so a plain dpkg -i removes the old package for you. That also removes the Rust shared library, so the 0.0.0 functions stop working the moment the new package is installed. Run the alter extension right after.

From a sqitch deployment

Before there was an extension, this repository was deployed by running sqitch against its deploy/ directory. A database set up that way has every object already, so it cannot create extension in the normal way. Instead, a version named sqitch adopts the existing objects into the extension without creating or changing anything, and the update then takes the same path as from 0.0.0, because 0.0.0 was generated from deploy/:

create extension pg_statecharts version 'sqitch';
alter extension pg_statecharts update;

The semver extension has to still be installed on the server while this runs, because converting the stored versions reads them through its output function. Afterwards it can be uninstalled.

Do not put those two statements verbatim into a sqitch change. The sqitch version only adopts, so on a database that has no legacy schema, which is what every freshly created development database is, it fails with schema "fsm" does not exist and rolls back. The same change has to run everywhere, so make it decide:

do $migrate$
begin
  if exists (select 1 from pg_namespace where nspname = 'fsm')
     and not exists (select 1 from pg_extension where extname = 'pg_statecharts') then
    -- deployed from deploy/ with sqitch: adopt the existing objects
    create extension pg_statecharts version 'sqitch';
  else
    -- fresh database, or the extension is already installed
    create extension if not exists pg_statecharts cascade;
  end if;
  alter extension pg_statecharts update;
end
$migrate$;

This is one change that works from all three starting points. On a legacy database it adopts and upgrades. On a fresh one it installs 0.1.0 and the update is a no-op. On a 0.0.0 install it upgrades. Running it again does nothing. CI runs this exact block against a sqitch deployment and against an empty database, twice each, and checks both end up owning the same objects.

Two things change for the project that owns the database:

• sqitch must not revert the fsm changes any more. The extension protects the objects it owns, so most of those reverts fail. The dangerous ones are the reworks of function/handle_machine_events, whose revert scripts are create or replace function and go through, swapping an old body into a function the extension now owns. Leave the fsm changes in the plan as history.

• Migrations generated by the Haskell SDK need a one-time rewrite. The SDK writes versions as casts, 1.0::semver or '1.5.1'::semver, in deploy, verify and revert scripts alike. That type no longer exists once the upgrade has dropped the semver extension, so fresh deploys, verifies and reverts of those changes fail. No shim can rescue a cast to a missing type. Replace the casts with fsm.to_semver('1.0') and fsm.to_semver('1.5.1') in the existing files; sqitch identifies a change by its plan entry, not by the script’s contents. From the sqitch project directory:

  git grep -l '::semver' -- deploy revert verify | xargs sed -i -E \
    -e "s/'([0-9]+(\.[0-9]+){0,2})'::semver/fsm.to_semver('\1')/g" \
    -e "s/\b([0-9]+\.[0-9]+)::semver/fsm.to_semver('\1')/g"
  git grep -n '::semver' -- deploy revert verify   # should print nothing

The quoted form goes first so the second expression cannot re-match inside the replacement. Migrations generated by 0.0.0 already use to_semver(...), are not touched by this, and keep working through the compatibility alias.

Do not regenerate the old migrations with pg_statecharts_dev to fix this. Its verify scripts also check that every callback function the chart references exists with the right signature, which is only true if those functions come before the statechart in the plan. Historical charts rarely satisfy that. Keep only the newest .scxml file for each chart under generator control, and fix the older migrations with the rewrite above.

What the upgrade changes

The update script is the same for both upgrade paths. It converts fsm.statechart.version off the semver extension’s type onto the built-in fsm.semver domain, removes the now unnecessary dependency on semver, drops the two file handling functions that 0.0.0 had and that have moved to pg_statecharts_dev, and registers the fsm tables and sequences with pg_dump (see Backups). Your data is preserved and migrations generated by 0.0.0 keep deploying, reverting and verifying unchanged.

Three things to know:

• semver is dropped. Once fsm.statechart no longer uses it, nothing in pg_statecharts does, and it has to go because it owns the name to_semver(text). If anything else in your database still uses the semver type, the upgrade stops with an error naming exactly what depends on it and changes nothing.

• The version column reads differently. fsm.semver is a domain over integer[], so a column that used to show 1.10.0 now shows {1,10,0}. The versions themselves are unchanged. Anything that displays a version, or compares one against a string, needs fsm.semver_text():

  select name, fsm.semver_text(version) from fsm.statechart;

Ordering and equality both need no helper. order by version desc is a numeric, component-by-component comparison, and where version = fsm.to_semver('1.2.3') works as before:

  -- correct with no sort key: 1.10.0 outranks 1.9.0 because 10 > 9
  select * from fsm.statechart order by version desc;

Integers were chosen over text precisely so that this is right by default. A text version column sorts lexicographically and would rank 1.9.0 above 1.10.0 unless every caller remembered a helper.

There is one way to lose that. ORDER BY resolves a bare name against the output columns first, so rendering to text and reusing the name version shadows the integer column and quietly restores lexicographic ordering:

  -- WRONG: orders by the rendered text, giving 2.0.0, 1.9.0, 1.10.0
  select fsm.semver_text(version) as version from fsm.statechart order by version desc;

  -- right: the alias does not shadow the column
  select fsm.semver_text(version) as rendered from fsm.statechart order by version desc;

• Your existing backups do not contain the machines. Nothing registered the fsm tables with pg_dump before this, so every dump taken under 0.0.0 is missing them. Take a new backup right after upgrading.

Prerelease versions (1.0.0-rc1) are not supported. 0.0.0 accepted them, because the semver extension does, so the upgrade checks for them first and refuses with a list of the offending rows rather than failing part way through. Rename those versions to plain major.minor.patch and run the update again.

After upgrading, pg_statecharts.so is no longer referenced by anything and can be deleted from $(pg_config --pkglibdir).

Versions and the semver extension

Everything version related lives in the fsm schema: the fsm.semver domain, fsm.to_semver(text) and fsm.semver_text(fsm.semver). Use those names.

fsm.semver is a three element integer[] — 1.10.0 is array[1, 10, 0]. Storing integers rather than text is what makes order by version desc correct without a helper function, which is why the column no longer renders as 1.10.0 on its own. Nothing here needs the semver extension.

One unqualified to_semver(text) is also created, because migrations generated by 0.0.0 call it. It is only a thin alias for fsm.to_semver(text), and it is skipped when something already owns that name — most obviously the semver extension, which is where that name came from in the first place. So:

• A database with no semver extension gets the alias, and migrations generated by 0.0.0 keep working with no action from you.
• A database that uses semver for its own unrelated purposes can now install pg_statecharts at all, which it could not before. create extension reports the skip as a WARNING (PostgreSQL runs extension scripts with client_min_messages at warning, so a NOTICE would not be seen). Migrations generated by 0.0.0 will not deploy there until they are regenerated against 0.1.0.

Regenerating a chart with 0.1.0 rewrites bare to_semver(...) calls to fsm.to_semver(...), so expect that one-line diff per migration the first time you regenerate.

Installing semver afterwards

Only one of the two orders is handled. Installing pg_statecharts into a database that already has semver works, because the alias steps aside. The other way round it does not: pg_statecharts took the name first, and create extension semver fails with

ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION to_semver(text) first.

The hint is not enough on its own, because the alias belongs to the extension and drop function refuses for that reason. Release it first:

alter extension pg_statecharts drop function public.to_semver(text);
drop function public.to_semver(text);
create extension semver;

Nothing in pg_statecharts calls the alias, so dropping it costs you only the migrations generated by 0.0.0, which need regenerating against 0.1.0 anyway. Both orders are asserted in .github/scripts/check_semver_coexistence.sh.

Layout

sql/ holds one file per object. sql/parts.txt lists them in creation order, and both make and install.sh read that list to assemble the single pg_statecharts--<version>.sql script that PostgreSQL actually loads. Add a new object by adding its file and putting it in the right place in parts.txt.

sql/upgrade/ holds handwritten --<from>--<to>.sql scripts.

Tests

make install
make installcheck          # needs a server you can connect to