pg_pure roadmap

This is the only plan file. The design and its reasons are in DESIGN.md, the working rules in AGENTS.md. Each phase ends with a gate. A phase is done when its gate has run on the EC2 box and the output shows that every test in it actually ran.

Phase 0: libpure prerequisites (in github.com/gburd/pure-lang) – DONE

Both landed in the fork on libpure-embeddable (tip ab12de13):

  • [x] 0.1 libpure no longer exports its LLVM symbols, so it cannot clash with a host’s llvmjit of a different LLVM major (commit “Hide libpure’s LLVM symbols …”). pg_pure links it unchanged; the “PostgreSQL without -jit” workaround is retired. Verified: 0 llvm:: exports, libpure make check 95/95, both pg_pure suites pass with jit=on, jit_above_cost=0 on PG 17/18/19, 0 crashes.
  • [x] 0.3 A safe mode (commit “Add a safe mode …”): pure_is_safe, pure_safe_allow_using, pure_safe_arm. This is what the trusted pg_pure uses (phase 3).
  • [ ] 0.2 Keep a crash-matrix reproducer in pg_pure’s TAP suite (ROADMAP 1.7) so a future libpure that re-exports LLVM is caught. (Not in the fork; it belongs here.)

Phase 1: the extension (pg_pureu now, pg_pure on top)

  • [x] 1.1 Repository skeleton: PGXS Makefile (with dist and check-ascii), pg_pure.control and pg_pureu.control, their --0.1.sql scripts, sql/ with expected/, README, LICENSE, META.json (two provides), CHANGELOG, ci/ (announce + noop), and the CI and release workflows. Still to add: a t/ TAP suite.
  • [x] 1.2 Port the prototype as pg_pure.c and fix the six defects in DESIGN.md section 8. Each fix has a regression case in sql/pg_pureu.sql; all pass on PG 17, 18 and 19.
  • [x] 1.3 Type mapping: scalars, exact numeric (bigint and rational, with a verified round-trip), range checks, text-only text results, NULL, numeric via math, and float8[] <-> Pure double matrix (0.3: 1-D vectors and 2-D matrices both directions; NULL-element arrays and >2-D rejected; using gsl::matrix etc. work because the add-on Pure modules are now built and installed alongside libpure). Still to do: composites as records, other types through text I/O, a UTF-8 conversion for other encodings.
  • [x] 1.4 The sql namespace: query, exec, notice, warning, null, error carrying SQLSTATE. (Parameters to query/exec are designed but not yet implemented; the body still builds its own SQL string.)
  • [x] The two trusted/untrusted handler pairs (pg_pure/pg_pureu), the DO inline handler, the validator, and using lines pulled to the top of a body.
  • [x] 1.9 Cross-platform: Linux (Debian 13) PG 17/18/19 and FreeBSD 14.5 PG 17 both build against the fork’s libpure and pass the suite with 0 crashes. Windows ruled out with reasons (DESIGN.md section 10).
  • [x] 1.4a Parameterized SPI -> done in phase 2.C (see below).
  • [x] 1.5 Set-returning functions -> done in phase 2.D (SETOF scalar/array; RETURNS TABLE / SETOF composite remain in phase 2b). Triggers and procedures remain in phase 2b.
  • [ ] 1.6 Preloading: measured end to end (t/004_preload.pl). The _PG_init warm-up runs in the postmaster (a preloaded start takes seconds, warming both interpreters), but a forked backend still pays the per-function LLVM codegen on its first call: the warmed JIT state is not effectively inherited across fork, so preload does not lower first-call latency today. A connection pooler, which keeps backends warm, is the current answer. Making warm state survive fork is open (likely libpure).
  • [x] 1.7 TAP tests: crash safety (JIT plus Pure in both orders, checked in the server log – t/001_crash_matrix.pl, 0-crash on a JIT-enabled PG 17/18/19); cancel and pg_terminate_backend; 8 concurrent backends; RSS over millions of calls plateaus – t/002_robustness.pl. Run by ci/gate.sh and in CI. (A LATIN1-database case remains for phase 2b.)
  • [ ] 1.8 A cassert build of PostgreSQL from source on the box (and, once Greg’s LLVM-21 work lands, a server whose llvmjit matches libpure’s LLVM major – the matched-major no-clash case, DESIGN.md 5a).

Gate: ci/gate.sh passes installcheck and TAP on PG 17 and PG 18, plus the cassert leg; the server log has 0 “terminated by signal” lines and 0 TRAP lines; make check-ascii passes.

Phase 2: adoption blockers from the pg.ddx.io evaluation

Ordered by feasibility (easiest first). The pg.ddx.io team’s 2026-10-29 evaluation listed these as “what would change the answer”; arrays (1.3) and the gsl modules are done, and the gsl::linalg::cholesky_solve binding (fork PR #1) is MERGED and exercised by pg_pure 0.4.0’s suite – a compiled SPD solve ~40x faster than PL/pgSQL. Four asks remain. As of 0.5.0, three of them – the Nix package (2.A), the TAP suite (2.B) and parameterized SPI (2.C) – are done; set-returning functions (2.D) are the remaining one.

  • [x] 2.A Nix package for libpure + add-on modules. nix/flake.nix builds libpure from the fork (LLVM 21, GMP/MPFR) and pure-gsl, exposes libpure, pure-gsl, pure-with-modules (the default) and a devShell; nix flake check passes. A host no longer maintains a bespoke LLVM build. See nix/README.md.
  • [x] 2.B TAP suite (t/001_crash_matrix.pl, t/002_robustness.pl): the JIT+Pure crash matrix in both orders reading the log; cancel via statement_timeout and pg_terminate_backend; 8 concurrent backends; and a backend’s RSS plateauing over millions of calls (the 4th 1M-call batch adds ~0 MB). Run by ci/gate.sh and in CI. ROADMAP 1.7 / 0.2.
  • [x] 2.C Parameterized SPI sql::query (q, [p1, ...]) / sql::exec (q, […]): parameters go to the planner as Datums via SPI_execute_with_args, never spliced; type inferred from the Pure value (int->int8, double->float8, string->text, bigint->numeric, sql::null->NULL). Works in the armed trusted interpreter too.
  • [x] 2.D Set-returning functions. A RETURNS SETOF <scalar/array> function whose body yields a Pure list produces one row per element, collected into a tuplestore (materialize mode); each element is converted with the scalar range checks. A lazy/infinite stream is walked one cons cell at a time, so a body that bounds the stream (take n ...) returns without realizing the rest, and a CHECK_FOR_INTERRUPTS per row plus a hard row cap keep an unbounded body cancellable rather than hanging or exhausting memory. Works in the trusted interpreter. RETURNS TABLE(...) / SETOF composite is the remaining piece (needs multi-column tuples); tracked in phase 2b.

Phase 2b: depth

  • [x] 2.1 Prepared plans (0.9): a transparent per-backend plan cache for sql::query / sql::exec. A parameterized query repeated with the same text and parameter types is planned once and reused; the key includes the inferred parameter type Oids (so int and double uses of one text get separate correct plans), and the saved plan relies on PostgreSQL’s own revalidation for search_path / role / DDL changes. No sql::prepare handle: a raw plan handle returned to Pure would dangle once the cache evicts it, and the transparent cache needs no API.
  • [ ] 2.2 sql::stream q: a lazy stream backed by a cursor. Forcing it after its call has returned must throw, never crash.
  • [x] 2.3 bytea and domains (0.7). bytea maps to a Pure int matrix of byte values (arbitrary binary, embedded NUL, out-of-range element is an error); a domain is accepted as its base type with its CHECK enforced on the argument and the result. An explicit user-visible subtransaction block (sql::atomic or similar) is still open: sql::query/sql::exec already run in an internal subtransaction, so this is a smaller ergonomics item, deferred.
  • [x] 2.4 Vet modules and promote them to the tested list, each with an in-database regression test: gsl/gsl::linalg (0.3/0.4), and mpfr, dict (stldict) and rational (0.8). All are built and installed by the dev, CI and release workflows. csv, xml and stllib build but are not yet promoted (csv/xml are file/document oriented, awkward in a single-expression body); glpk needs an ODBC lib and a fork-side fix to its error hook, tracked separately.
  • [x] 2.5 Property round-trip tests for the conversions (t/003_conversions.pl): for generated values of each supported type, identity(v) = v, and a value out of range is an ERROR, never a wrong answer. These found and guard a numeric precision bug fixed in 0.6.1. (Written as a TAP test, not a hegel C harness: the conversion only runs through a live backend, so the oracle is a SQL value round-tripped through a pg_pure function.)
  • [ ] 2.6 Benchmarks against PL/pgSQL, PL/Python and PL/Perl: interleaved runs and medians, method and box recorded under bench/ with a date.

Gate: phase 1 gate, plus the module tests and the property suite.

Phase 3: the trusted language and the remaining PL features

  • [x] 3.1/3.2 libpure safe mode designed and landed in the fork (pure_is_safe/pure_safe_allow_using/pure_safe_arm); see phase 0.
  • [x] 3.3a pg_pure (trusted) runs each function in a --safe, armed interpreter: a body attempting extern, inline code, eval/evalcmd, a native or off-allowlist using, or a dangerous prelude function is refused as an SQL ERROR and the backend survives. sql/pg_pure.sql covers each route; passes on PG 17/18/19.
  • [ ] 3.3b One interpreter per SQL user (DESIGN.md 9.4): deferred as defense in depth, not a fix for a present hole. The state-leak channel it was meant to close (a body defining a top-level rule that another function in the backend inherits) is closed at compile time as of 0.6.2 – a body must be a single expression. If a future need appears (a multi-tenant trusted workload, or a module with mutable global state), the minimal form keys an interpreter by effective GetUserId() for SECURITY DEFINER functions only, created lazily with a small cap, behind a GUC (SECURITY INVOKER functions and the GUC-off path keep today’s single shared interpreter).
  • [ ] 3.3c A curated --safe prelude (DESIGN.md 9.5) instead of the denylist, designed with the Pure maintainer; independent security review before relying on the trusted language for untrusted multi-tenant use.
  • [ ] 3.4 Event triggers; transaction control in procedures (sql::commit, sql::rollback).

Gate: phase 2 gate plus the escape suite (have it in sql/pg_pure.sql), and an independent security review of the safe mode before relying on pg_pure for untrusted multi-tenant workloads.

Phase 4: release

  • [ ] 4.1 Docs (SGML and HTML), the PGXN package, Debian packaging, and RELEASING.md in the style of pg_fts’s.

Decided

  • 2026-10-27: development and every build and test run on an EC2 Debian 13 box in the kiwi account, never on floki (Greg).
  • 2026-10-27: the languages are pg_pure (trusted, the ship target) and pg_pureu (untrusted, superuser only), following the PL/Perl plperl/plperlu model with pg_ names. We keep both; we ultimately ship pg_pure (Greg).
  • 2026-10-28: reuse pg_fts’s PGXN/CI/release/announce machinery (META.json two-provides, ci/announce.sh, GitHub Actions). Repo home is GitHub (github.com/gburd/pg_pure), not Codeberg.
  • 2026-10-28: support PG 17, 18 and 19 (19 via the PGDG snapshot repo until release). FreeBSD is a supported platform; Windows is not (DESIGN.md 10).
  • 2026-11-19: PostgreSQL 19.0 reached GA. CI and the release build now install it from the released PGDG repo, not the snapshot; the snapshot repo line is removed.
  • 2026-10-27: a PostgreSQL built without LLVM (no postgresql-NN-jit) is a supported deployment and needs no libpure symbol fix; it gives up the server’s own JIT. DESIGN.md section 5a. (Greg is also moving PostgreSQL to LLVM 21 in a separate effort, which removes the clash a different way.)
  • 2026-10-29: libpure landed both fixes in the fork (ab12de13): LLVM symbols hidden (no coexistence workaround needed) and a safe mode. pg_pure 0.2.0 wires the trusted pg_pure to the safe mode; both languages now run and pass on PG 17/18/19 with jit=on.
  • 2026-10-30: pg_pure 0.3.0 adds float8[] <-> Pure double-matrix support (both languages) and the dev build installs the add-on Pure modules, so using gsl::matrix works. From the pg.ddx.io evaluation: arrays unblock writing their commit_model_solve, but a pure-Pure Cholesky over Pure lists is ~832 ms at K=18 vs ~23 ms PL/pgSQL – not competitive. The unlock is a compiled binding: gsl::linalg::cholesky_solve (PR to the fork, gburd/pure-lang#1) runs K=18 in 0.02 ms. Remaining pg.ddx.io asks are phase 2.A-2.D, ordered by feasibility. Dev work now on the chef burner account (kiwi expired).