# 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 ` 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).