pg_living_assertions

A registry of things you claim are true about your database, each with the SQL that proves it and the date it was last proven.

The hole this fills

PostgreSQL says it itself:

=# create assertion a check ((select count(*) from t) > 0);
ERROR:  CREATE ASSERTION is not yet implemented

=# select feature_id, feature_name, is_supported
     from information_schema.sql_features where feature_id = 'F521';
 feature_id | feature_name | is_supported
------------+--------------+--------------
 F521       | Assertions   | NO

This is not an implementation of that feature. SQL-92 assertions are constraints on data, evaluated on every write, and expensive for exactly that reason. These are assertions about the state of the system, evaluated when you ask. Saying otherwise would be selling this as something it is not.

The thesis

A guarantee with no date of last check is a belief.

Your database is full of things that “are true”: this index is UNIQUE, this constraint applies to every row, this audit trigger is enabled, this RLS policy protects the table, this replica is caught up, this backup restores.

The catalog stores the state – indisvalid, convalidated, tgenabled – and never the question that matters: is it still true, and since when have we not looked?

And the failure mode is always the same, and never loud:

The mechanism works and the thing that records it lies.

The index stays marked UNIQUE in the catalog while duplicates go in. The plan keeps returning rows. The vector index keeps returning k neighbours. Nothing ever errors.

Use

CREATE EXTENSION pg_living_assertions;

SELECT living_assertions.declare(
    'no_orphaned_invoices',
    'every invoice points at a customer that exists',
    $$select count(*) = 0 as holds,
             count(*) || ' orphaned invoices' as detail
        from invoices i
        left join customers c on c.id = i.customer_id
       where c.id is null$$);

SELECT name, state, age FROM living_assertions.status;

The check must return one row with a boolean column holds, and optionally a text column detail. Re-run everything with living_assertions.run_all(), or one with living_assertions.run(name).

Six answers, and the extra ones are the point

state means
holds still true
broken no longer true
unknown the check ran and could not decide. Not false.
erroring the check itself is failing. Not false, and not unknown – it is a defect, and until it is fixed this assertion is watching nothing.
unchecked nobody has ever run it. Not a clean bill of health.
retired somebody turned it off on purpose, with a reason and a date
unregistered nothing by that name is registered, so nothing is watching it

Collapsing unknown into broken is how a monitor starts reporting something it cannot know. Collapsing unchecked into holds is how a guarantee nobody ever looked at gets trusted. And erroring exists because a check with a typo in it otherwise sits forever looking exactly like one patiently waiting for data.

state() never returns NULL and never returns an empty result. Both read as “fine”.

The age travels with the verdict

living_assertions.status always reports checked_at and age next to the state, and stale(interval) lists what has gone too long – labelling “never checked” apart from “checked long ago”, because only one of those is fixed by waiting.

This is the whole thesis in one view: a stale holds looks exactly like a fresh one and means something completely different.

declare_unchanged – approve what something says today

“Approve what this expression evaluates to now, and tell me when it changes” is the shape a guard keeps writing:

SELECT living_assertions.declare_unchanged(
    'the_columns_my_api_returns',
    'the shape of this view is what the client was built against',
    $$select string_agg(attname, ',' ORDER BY attnum)
        from pg_attribute where attrelid = 'public.api_v1'::regclass and attnum > 0$$);

It takes the expression, not the value. A stored value would be compared against itself forever – a check that can never fail and therefore never protects anything. A wrong expression raises here, at approval time, rather than being stored and reported as broken forever after.

It compares the TEXT of the value, and making that text canonical is your job. jsonb already normalises key order; an array does not; a float renders however it renders. Two worlds you consider equivalent have to render the same, and only you know what “the same” means – which is why the registry refuses to decide it for you.

This arrived in 0.2.0 because a measurement said the first port had not saved enough: pg_grammar_guard had moved its baseline and its drift here and then rebuilt those three steps by hand, and pg_plan_guard writes the same three for plan advice. The duplication had moved up a level rather than gone. The metric was not wrong; the port was not finished.

An assertion is not renegotiated in place

Recording a declaration date buys nothing if UPDATE is allowed: softening an assertion would leave no trace and the date would be decoration. So a trigger refuses it. The only permitted change is retiring one, which costs a reason.

To change an assertion you supersede it, which costs writing down why (a CHECK, not a convention) and retires the predecessor in the same statement:

SELECT living_assertions.declare(
    'no_orphaned_invoices', 'the corrected claim', $$...$$,
    'no_orphaned_invoices',                 -- supersedes
    'the old one ignored soft-deleted customers and was counting them as orphans');

SELECT * FROM living_assertions.renegotiated;

renegotiated cannot tell you an arbitrary SQL check got looser – that is undecidable in general, and pretending otherwise is how a dashboard starts lying. What it reports is the timing, which is the part that accuses: replacing an assertion after it has been evaluated is normal; replacing one whose last word was broken is the case worth seeing, and it is labelled REPLACED WHILE BROKEN.

The check log is append-only for the same reason. If it could be edited, the declaration date would protect nothing.

The stored SQL runs sealed

This is the only place the extension runs text somebody stored earlier, so the check runs read-only, inside a subtransaction that is always rolled back once it has answered. The answer is kept; everything the check did is thrown away.

  • A write – direct, or through any function the check calls – and a nextval() on an ordinary sequence are refused by the engine, and the assertion comes back erroring: the check tried to write, and a check may only read.
  • What read-only lets through – a session setting changed with set_config(), a row in a temporary table – is undone by the rollback, so the caller’s session and data are exactly as they were.
  • The caller is not sealed: it keeps writing afterwards, and the check still sees rows the caller wrote earlier in the same transaction (which is what a gate evaluating assertions inside a commit needs).

Up to 0.4.x this section said the stored SQL “cannot write”, and that was false. The evaluator was STABLE, and PostgreSQL enforces that only for the statements written directly in the check. A check that called a volatile function wrote its row and reported holds; so did one that advanced a sequence or changed the caller’s work_mem. The regression test only ever tried a direct INSERT – the one case STABLE does catch. test/sql/read_only.sql now tries all of them, and it failed on 0.4.1 before 0.5.0 was written. Measured cost of the seal: none distinguishable, about 24 µs per run() either way.

What it still does not stop is listed under What it does not do, and each item is pinned by the same test, so the list is a tested fact rather than a hope.

A check that returns more than one row is also erroring, not answered with the first one. EXECUTE ... INTO keeps the first row without complaining, which would be a wrong answer that looks exactly like a right one – in the one place whose entire job is to decide.

Why this is a piece and not a utility

Four PostgreSQL extensions were each built with their own copy of this:

extension its baseline its severity vocabulary stores a last-check date
pg_plan_guard baselines + drift_log ok / drifted / error yes
pg_recall_guard baselines approved vs measured recall no
pg_grammar_guard approved_grammars drift / never_approved no
pg_promise_guard (reads the catalog live) breach / gap no

Three of four invented a baseline table, an approve(), a check_*() and a notion of drift; all four invented a different word for the same distinction; only one recorded when it last checked. That is not a coincidence – it is the symptom of a missing abstraction underneath.

pg_grammar_guard 0.3.0 is the first consumer rewritten on top of this, and porting it exposed a defect its old design could hide: check_grammar(name, fields) made the caller bring the world. Pass a stale spec and it compares the baseline against something that is not your catalog and reports no drift. A registry that stores the check has no such option – what gets stored is the query that rebuilds the claim from the live database, so a cron job, a deploy gate or somebody who was not there when it was approved all get a real answer.

And it works outside the LLM tooling it came from, which is the test of whether it is a piece: a DBA has dozens of living assertions today, kept in their head, in a runbook, or in a monitor that only knows OK and CRITICAL.

Who may store SQL that someone else will run

This registry stores SQL and later runs it as whoever calls run(). Nothing here is SECURITY DEFINER, so a check runs with the caller’s privileges – and the caller is usually a cron job owned by someone with more rights than whoever wrote the check.

Whoever can INSERT into assertions can run arbitrary SQL as every future caller of run_all(). Grant it the way you grant cron.schedule.

That is not a bug, it is the shape of the feature. It is stated here because a registry of stored SQL that does not say it out loud is a footgun with good manners.

It is closed by default, and that is verified rather than assumed. A role with USAGE on the schema still gets permission denied for table assertions, because the INSERT runs as them and an extension’s tables belong to its owner. Since 0.3.0 the write functions are also revoked from PUBLIC – a second gate that changes nothing today and matters the day somebody grants table privileges without thinking about what that implies.

make check-privs proves both directions: a stranger cannot declare or read, the role you deliberately granted can, and the escalation is demonstrated – the trusted role stores a check, the owner’s cron runs it, and it reads what the owner can read. Shown rather than described, so nobody grants it believing they are granting less.

One thing that is not a boundary: the evaluator runs with a fixed search_path, so an unqualified name will not resolve. That limits accidental damage. Qualifying a name costs eight characters, so do not mistake it for protection.

What it does not do

  • It does not make anything correct. It tells you whether something you claimed is still true. It cannot discover claims nobody wrote down.
  • It does not replace constraints. A constraint stops bad data getting in; a living assertion tells you a guarantee stopped applying. Confusing the two would be selling this as what it is not.
  • Somebody has to write the check. Same as a CHECK constraint.
  • Assertions outlive the extension that declared them. Drop a consumer and its assertions turn erroring rather than vanishing. That is the right direction – loud beats silent – but it means orphans need retiring by hand.
  • unknown is not a diagnosis. It says the check could not decide, not why.
  • The seal has three known gaps, all things a rollback cannot undo and read-only does not refuse: a nextval() on a temporary sequence, a session-level advisory lock, and anything that leaves the transaction – dblink, a foreign data wrapper, a function in an untrusted language that writes a file. The first two are pinned by test/sql/read_only.sql. This is why declaring an assertion is a privilege (below): the seal limits what a trusted author can break by mistake, it does not make an untrusted one safe.

Tested on

Measured on 2026-09-16, not assumed: make installcheck was run against each of these releases, every one in a container of the official image for that version (19beta2 is a local build).

That table measured 0.4.1. 0.5.0 has been run on 19beta2 only, so far. The seal uses nothing newer than subtransactions in PL/pgSQL and set_config() on transaction_read_only, both far older than 11 – which is an argument, and this section exists to hold measurements, not arguments. The table is re-run before 0.5.0 is called tested anywhere else.

10 11 12 13 14 15 16 17 18 19
✗ ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓

PostgreSQL 10 is not supported, and will not be: two triggers are created with EXECUTE FUNCTION, which PostgreSQL 10 does not accept. The older EXECUTE PROCEDURE spelling would work everywhere, but it has been deprecated since 11, and 10 has been out of support since 2022.

Install

make install
make cluster        # a throwaway cluster, on port 5498, for the suites below
make installcheck   # needs a running server
make check-dump     # does the registry survive pg_dump + restore?
make check-privs    # who may store SQL that somebody else will execute
make cluster-stop

Until 0.5.0 the throwaway cluster tested the installed copy, not this repo. PostgreSQL looks for control files in the extension/ subdirectory of each extension_control_path element; the cluster pointed at the repo root, which has none, so every suite silently ran against whatever version was last make installed. It was found when the 0.5.0 test kept reporting 0.4.1’s behaviour. test/cluster.sh start now links the repo’s files into a directory of the right shape, and refuses to start the suites if the server sees a different version than this repo declares.

check-dump and check-privs create and drop roles and databases, so they run against the throwaway cluster of test/cluster.sh and not against whatever server the environment points at. Until this was written, their headers documented running them with PGPORT aimed at a real server, their names were generic (vigilante, la_dumpeada), and they dropped those names on the way in: anyone with a monitoring role called vigilante would have lost it, grants included, by following this page. The author ran them against a production cluster and left a database behind, which is how it was found.

Three things guard that now, and the third is the one that holds if you point PGHOST somewhere by hand: the suites default to the throwaway cluster, their names carry the extension’s prefix, and they refuse to drop anything they did not create – if one of those names already exists the suite stops instead of removing it.

check-dump is separate because pg_regress cannot shell out to pg_dump, and the claim that the registry survives a restore is too central to leave unverified. It checks that the assertions, their last verdicts, the retire reasons and the supersede chain all come out the other side.

pg_dump warns about a circular foreign key on assertions. It is the self reference in supersedes, and it is real: a --data-only dump may need --disable-triggers. A normal full dump restores cleanly, chain included, and that is what check-dump exercises.

Pure SQL: no shared library, no dependencies. The database that most needs its guarantees audited is usually the one where getting a C extension approved is hardest.

Distribution 0.5.0 provides extension 0.5.0. An existing 0.4.1 installation moves with ALTER EXTENSION pg_living_assertions UPDATE TO '0.5.0', which only replaces the evaluator: no table changes, and every recorded verdict stays.

License

PostgreSQL License.