pg_living_assertions

pg_living_assertions : Executable SQL checks with verdict dates and assertion history

Overview

Packages

Source

pig build pkg pg_living_assertions;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_living_assertions;		# install via package name, for the active PG version

pig install pg_living_assertions -v 18;   # install for PG 18
pig install pg_living_assertions -v 17;   # install for PG 17
pig install pg_living_assertions -v 16;   # install for PG 16
pig install pg_living_assertions -v 15;   # install for PG 15
pig install pg_living_assertions -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_living_assertions;

Usage

Sources:

• README.md
• pg_living_assertions.control
• pg_living_assertions–0.4.1–0.5.0.sql
• pg_living_assertions–0.5.0–0.5.1.sql
• test/sql/read_only.sql

pg_living_assertions 0.5.1 stores SQL checks, verdicts, verification times and replacement history. These are checks run on demand, not SQL ASSERTION constraints evaluated on every write. It is a pure SQL extension with no preload requirement.

Register and Verify

CREATE EXTENSION pg_living_assertions;
SELECT living_assertions.declare(
  'simple_check', 'one equals one',
  $$SELECT 1 = 1 AS holds, 'arithmetic check'::text AS detail$$);
SELECT living_assertions.run('simple_check');
SELECT name, state, age FROM living_assertions.status;

Results and History

A check must return exactly one row with boolean holds and optional text detail. living_assertions.run_all() evaluates registered checks. living_assertions.state() distinguishes holds, broken, unknown, erroring, unchecked, retired and unregistered; living_assertions.stale() separates never-checked assertions from old results. living_assertions.declare_unchanged() records an expression for later text comparison, so the author must canonicalize its output. Definitions are superseded with a reason; results and registry data are included in database dumps.

Execution and Privileges

Since 0.5.0 the evaluator runs read-only inside a subtransaction that is always rolled back, preserving its verdict. This repairs the older STABLE-only evaluator, which did not stop side effects through volatile functions. It is not a sandbox for untrusted SQL: temporary-sequence changes, session advisory locks and external effects can survive. Only trusted administrators should register checks; they execute with the privileges of the later caller. Registry tables belong to the extension owner and write functions are revoked from PUBLIC by default.

Upgrade

After installing the matching files, use ALTER EXTENSION pg_living_assertions UPDATE TO '0.5.1'. The 0.4.1→0.5.0→0.5.1 chain replaces evaluator functions without changing registry tables. The final patch qualifies row types in run() so type-cache invalidation does not resolve them under an assertion’s unrelated search path.

Fresh installation also uses an earlier base SQL script followed by the packaged upgrade chain; keep the complete set of matching scripts installed.