# pg_plan_guard

[![CI](https://github.com/Manuelreyesbravo/pg_plan_guard/actions/workflows/ci.yml/badge.svg)](https://github.com/Manuelreyesbravo/pg_plan_guard/actions/workflows/ci.yml)

**Detect when a query plan drifts away from the plan you approved.**

PostgreSQL 19 added [`pg_plan_advice`](https://www.postgresql.org/docs/19/pgplanadvice.html)
(generate advice for a plan, then force it) and
[`pg_stash_advice`](https://www.postgresql.org/docs/19/pgstashadvice.html) (store
advice per `query_id` and apply it automatically). Both are deliberate, manual
acts: you decide a plan is good, and you pin it.

Neither of them **watches**. There is no way to ask:

> Is the plan for this query still the plan I approved?

`pg_plan_guard` answers that question.

## Why it matters

A plan regression usually does not fail. The query still returns the same rows —
it just stops using the index and starts scanning. Nothing errors, nothing logs,
nothing alerts.

The case this was built for: a vector similarity search backed by a DiskANN
index over ~40,000 embeddings. If the planner stops choosing that index, the
results are **identical** and still correctly ordered. It is simply a sequential
scan now. Correct, silent, and slow.

That class of failure is the expensive one precisely because it does not
announce itself. You find out weeks later from a latency graph, if at all.

## Usage

```sql
CREATE EXTENSION pg_plan_guard;

-- Approve today's plan for a critical query.
SELECT plan_guard.capture(
    'semantic_search',
    'SELECT id FROM docs ORDER BY embedding <=> ''[...]'' LIMIT 10',
    'must use the diskann index');

-- Later, on a schedule: has anything drifted?
SELECT * FROM plan_guard.verify();

--       name       |  state  |        expected_advice        |    actual_advice
-- -----------------+---------+-------------------------------+---------------------
--  semantic_search | drifted | INDEX_SCAN(docs docs_emb_idx) | SEQ_SCAN(docs) ...
```

Run it from `pg_cron`, or from whatever already runs your checks:

```sql
SELECT cron.schedule('plan-guard', '37 */6 * * *',
    $$SELECT * FROM plan_guard.verify()$$);
```

And point your monitoring at one view:

```sql
SELECT * FROM plan_guard.status WHERE state <> 'ok';
```

## API

| Function | Purpose |
|---|---|
| `plan_guard.capture(name, query_sql [, description])` | Approve the current plan as the baseline |
| `plan_guard.verify([name])` | Re-plan every baseline and report drift |
| `plan_guard.sync_stash(stash_name)` | Push approved advice into a `pg_stash_advice` stash |
| `plan_guard.advice_for(query_sql)` | Plan advice for an arbitrary query |
| `plan_guard.query_id_for(query_sql)` | `query_id` of a query, for stash operations |

| Relation | Contents |
|---|---|
| `plan_guard.baselines` | The approved plan for each query |
| `plan_guard.drift_log` | Append-only history of every detected drift |
| `plan_guard.status` | Current state, worst first — the view a monitor polls |

## Design decisions

These are the choices that make it usable rather than annoying, and the reasons
behind them:

**Advice is compared, not `EXPLAIN` output.** `EXPLAIN` text changes with row
estimates and costs even when the plan shape is identical. Comparing it would
make baselines drift constantly and train everyone to ignore the alerts. Advice
describes the *shape* — which scan on which relation, which join order, which
method — so it changes only when the planner's decision changes.

**Capture is explicit, never automatic.** A baseline that captured itself would
happily bless whatever plan happened to be in effect, including the regression
you are hunting.

**Drift is logged once per transition, not once per check.** A baseline that has
been drifting for a week should not produce a row per cron run.

**A broken baseline does not abort the run.** If a query no longer plans (table
dropped, column renamed) it is reported as `error` and the remaining baselines
are still checked. A monitor that dies on the first problem stops working
exactly when something is wrong.

**A baseline is re-planned against the tables its author meant.** The query is
stored as text, so the names in it are resolved by a `search_path`. `capture()`
records the capturing session's, and `verify()` and `sync_stash()` re-plan under
it with `pg_temp` moved to the end. So a baseline captured with `SET search_path
= app` plans the same from `pg_cron`, and a temporary table in the verifying
session cannot answer for a watched one: unnamed, PostgreSQL searches `pg_temp`
first, and until 1.1.3 a temporary copy wrote false drifts into `drift_log` and
hid real ones (`test/pg_temp.sh`). Baselines captured before 1.1.4 have no
recorded path and are planned under the caller's, also with `pg_temp` last;
capture them again to pin it. The recorded path is applied only inside the
sealed `EXPLAIN` described below, so it never reaches the session that called
`verify()` -- until 1.1.5 it did, and that session's next `capture()` recorded
the baseline's path instead of its own (`test/audit.sh`).

**The table is the source of truth, not shared memory.** `pg_stash_advice`
persists across restarts, but if persistence ever fails or the cluster is
recreated, the pinning disappears silently — queries keep working, just slowly.
That is the same failure mode this extension exists to catch, so the stash is
treated as a cache that can always be rebuilt from `plan_guard.baselines` via
`sync_stash()`.

## Requirements

- PostgreSQL 19 or later, with `pg_plan_advice` available.
- `sync_stash()` additionally requires `pg_stash_advice`, which needs **two**
  things to actually apply advice — and if either is missing, nothing is applied
  and nothing warns you:
  1. `shared_preload_libraries` includes `pg_plan_advice` and `pg_stash_advice`
  2. `pg_stash_advice.stash_name` is set (the default is empty)

  Diagnose with:
  ```sql
  SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_stash%';
  ```

Baselines are captured with `EXPLAIN (PLAN_ADVICE)`, which **does not execute**
the query -- but planning is not nothing: the planner folds an `IMMUTABLE`
function called with constant arguments, so a stored query can run code as
whoever plans it. Since 1.1.5 every `EXPLAIN` of stored text runs sealed, the
way pg_living_assertions runs a check: in a subtransaction switched to read-only
and always rolled back, under a path pinned to `pg_catalog, pg_temp` outside it.
What planning does is refused if it writes and undone if it does not.

A seal is not enough on its own: `COPY ... TO PROGRAM`, `pg_switch_wal()` or a
session advisory lock are not writes to the database, and until 1.1.7 a folded
function ran them as whoever ran `verify()` -- usually a superuser (external
audit, round 4). **Since 1.1.7 a baseline is planned as the role that wrote it.**
`baselines.captured_by` records that role: a trigger sets it to whoever writes
or rewrites the query, and accepts another name only from a role that may
`SET ROLE` to it. **Since 1.1.9 the `EXPLAIN` runs inside a temporary `SECURITY
DEFINER` function owned by the author**, created and rolled back inside the seal.
Inside such a function PostgreSQL refuses to change `role` or
`session_authorization` at all, so a stored query can do no more than its author
could do directly and cannot become anyone else; anything beyond is that
baseline's `error`, not an aborted `verify()`. Session advisory locks taken inside
the seal are released. The role that runs `verify()` must be able to hand that
function to each author -- a superuser can; otherwise it needs to be able to `SET
ROLE` to the author, and the author needs `TEMP` on the database. A baseline
captured before 1.1.7 has no recorded author and is refused until it is captured
again.

In 1.1.7 and 1.1.8 the `EXPLAIN` ran after `SET ROLE` to the author instead, and
an external audit (round 5) measured why that is not a boundary: a folded
function ran `RESET ROLE`, `SET SESSION AUTHORIZATION DEFAULT` or
`set_config('role', ...)` and was the runner again, then ran a program. `make
check-audit` (S1) shows each way back refused, against a control that shows it
working under `SET ROLE`.

`capture()`, `verify()` and `advice_for()` work for a role that is not a
superuser once `pg_plan_advice` is in `shared_preload_libraries`: `LOAD` needs
superuser, and since 1.1.5 a refused `LOAD` of an already loaded library is not
an error. `sync_stash()` sets `compute_query_id`, which only a superuser can.

## Tested on

PostgreSQL 19 only, and that is not conservatism: the extension reads plan
advice through `pg_plan_advice`, which arrived in 19.  Verified on 2026-09-16
by running `make installcheck` against 10 through 18 as well, each in a
container of the official image: every one of them fails at the first capture
with

    ERROR:  pg_plan_guard requires pg_plan_advice (PostgreSQL 19+)

## Install

From [PGXN](https://pgxn.org/dist/pg_plan_guard/):

```sh
pgxn install pg_plan_guard
psql -c 'CREATE EXTENSION pg_plan_guard'
```

From source:

```sh
make install PG_CONFIG=/path/to/pg_config
psql -c 'CREATE EXTENSION pg_plan_guard'
```

Run the tests against a live server:

```sh
make installcheck PG_CONFIG=/path/to/pg_config
```

And the `search_path` suite, in a throwaway cluster built from the same binaries:

```sh
PG_CONFIG=/path/to/pg_config test/cluster.sh init
PG_CONFIG=/path/to/pg_config test/cluster.sh start
make check-pgtemp PG_CONFIG=/path/to/pg_config
```

## Limitations

- Queries are stored as text and re-planned as written. Parameterized queries
  must be captured in an executable form (literal values), since `EXPLAIN`
  needs a complete statement.
- Drift detection is only as good as the baseline: capturing a bad plan pins a
  bad plan. Review what `capture()` returns.
- `capture()` and `verify()` run `EXPLAIN` on stored SQL, so execute rights are
  not granted to `PUBLIC`.
- `verify()` plans with the settings of the session that runs it, not those of
  the application's role: a planner setting on that role (`enable_indexscan`,
  say) is not seen.
- A query_id ignores constants, so two baselines of one statement with
  different literals share a stash slot, and the last `sync_stash()` writes
  wins.
- A temporary table still answers for a name found nowhere on the recorded
  path: `pg_temp` goes last, not away.

## License

Apache License 2.0 -- see [LICENSE](LICENSE). Copyright 2026 Manuel Reyes Bravo.

The name is not licensed with the code: see [TRADEMARK.md](TRADEMARK.md).