pg_improv SQL reference

Every function, setting and status column of pg_improv 0.13.0, read from the extension's catalog. Every function is in schema improv and runs SECURITY INVOKER; a function on a model acts as the model's owner (see Privileges). Improv in Postgres explains how they fit together, and Compatibility which of them are stable.

Functions

`bind_category`

improv.bind_category(model text, category text, rel regclass, col text) → void

Creates the category if missing and records that rel.col supplies its items.

`bind_measure`

improv.bind_measure(model text, measure text, rel regclass, value_col text, agg text DEFAULT NULL::text) → void

Creates an input measure read from rel.value_col. Its dimensions are the categories already bound on rel, in category-id order. Two rows on one coordinate are an error unless agg (sum, avg, min, max, count) folds them; an aggregated measure is read-only in the GUI.

`categories`

improv.categories(model text) → TABLE(id bigint, name text)

The model's categories, for a role its policies let open it (ViewModel), and for the owner.

`cells`

improv.cells(model text, measure text) → TABLE(coord jsonb, value jsonb)

The cells of one measure the caller may read (ReadCell): an input's from its table, a derived measure's from its result table, read as the owner. A hidden measure answers as one that does not exist.

`create_model`

improv.create_model(name text) → bigint

Creates the model and its result schema improv_<lower(name)>.

`define`

improv.define(model text, definition text, replace boolean DEFAULT false) → bigint

Adds a derived measure from Target = expr and creates its result table, or a named LAMBDA from Name = LAMBDA(p, …, body) (see the README). SQL(...) and CALL(...) are rejected. An existing name is an ERROR unless replace => true, which redefines the derived measure (see the README). Returns the measure's (or lambda's) id.

`drop_measure`

improv.drop_measure(model text, measure text) → void

Drops a measure: a derived one's result table, an input's binding and stored cells, and its catalog row; or a named LAMBDA. An ERROR names the measures (and lambdas) that still reference it.

`drop_model`

improv.drop_model(name text) → void

Drops the catalog rows, the result schema and the triggers, and takes relations no other logical-mode model binds out of improv_db.

`enable_logical`

improv.enable_logical(model text) → void

Adds the model's bound relations to the database's publication improv_db and gives PK-less ones REPLICA IDENTITY FULL. Called for you by bind_measure and set_sync_mode(…, 'logical'). The worker creates the slot.

`explain_access`

improv.explain_access(model text, action text, resource jsonb DEFAULT NULL::jsonb, role text DEFAULT NULL::text) → TABLE(step integer, action text, resource text, allowed boolean, policies text[], errors text[])

Each question the policy rules ask for role (the caller when NULL) doing action on resource (NULL: the model; {"measure": M}, {"measure": M, "at": {"Category": "Item"}}, {"category": C}, {"category": C, "item": I}, {"view": V}), with the deciding policies, to the first denial. Only the owner's side and Admins ask about others or see the policies; a role asking about itself gets one row, allowed or not.

`items`

improv.items(model text) → TABLE(category text, id bigint, name text)

The items the caller may read (ReadItem).

`measures`

improv.measures(model text) → TABLE(id bigint, name text, kind text, value_type text, categories text[], formula text)

The measures the caller may read (ReadMeasure); formula is NULL unless every measure it reads is readable too.

`policies`

improv.policies(model text) → TABLE(version bigint, policies text, groups text, set_by text, set_at timestamp with time zone)

The stored policies: no row if never set, a NULL policies after a clear. For the owner's side and Admins.

`recalc` (1 arguments)

improv.recalc(model text) → bigint

Recomputes every derived measure in the caller's transaction and returns the number of result rows inserted, updated or deleted.

`recalc` (2 arguments)

improv.recalc(model text, measures text[]) → bigint

Recomputes only the named measures (input or derived, case-insensitive) and every derived measure that depends on them. NULL means all.

`set_cells`

improv.set_cells(model text, edits jsonb) → bigint

Sets (null: clears) input cells, as what_if's edits name them, writing the bound table (or improv.cell) as the owner and recomputing. Each cell must pass EditCell; one that does not refuses the call. Returns the cells written. Audited.

`set_policies`

improv.set_policies(model text, policies text, groups text DEFAULT NULL::text) → bigint

Stores the model's access policies (Cedar text, groups JSON), validated as improv policy set validates them; NULL policies clears them. Returns the new version. For the owner, a role that may act as it, or one the policies allow Admin. Audited. See Access policies.

`set_sync_mode`

improv.set_sync_mode(model text, mode text) → void

trigger: installs the sync triggers on every bound relation and recomputes once (re-run it after binding a new relation). commit: recomputes once, then once per writing transaction before it commits; needs the preload. logical/manual: removes the triggers.

`status`

improv.status(model text) → TABLE(sync_mode text, slot text, lag_bytes bigint, confirmed_lsn text, last_applied_at timestamp with time zone, rows_applied bigint, last_error text, incremental_applies bigint, rebuilds bigint, fold_bytes bigint, engine_bytes bigint)

How the model is kept current, how far the logical worker is behind, and how it applied its batches: incremental_applies to the live engine, rebuilds full loads (the first catch-up, structural changes, and what it could not apply incrementally).

`sync_publication`

improv.sync_publication() → void

Sets improv_db to the union of the relations every logical-mode model binds (creating it when missing). Called for you by every function that changes that set; run it by hand to repair a publication edited by hand.

`what_if`

improv.what_if(model text, edits jsonb) → TABLE(measure text, coord jsonb, value jsonb)

Evaluates the model with edits applied in memory and returns the derived cells that differ from the stored results (value NULL: the cell would vanish). Writes nothing. When every edit has the same categories and the measures they feed are element-wise, it loads and compares only the edited coordinates, as trigger mode does. Only a role that may act as the model's owner may call it, and it reads as the owner (see Privileges, which shows how to give it to other roles).

Internal functions

The extension calls these itself (as triggers, as the LANGUAGE improv handler, or from its row-level security policies). Do not call them.

Function	Returns	What it is
`cell_name(model bigint, measure bigint, dim integer, slot integer)`	text	Row-level security on a result table, under `cell_rule` 2: the `slot`th name of dimension `dim` the caller may not read (NULL past the last).
`cell_rule(model bigint, measure bigint)`	integer	Row-level security on a result table of a model with access policies, once per statement (an InitPlan): 0 shows every row, 1 none (the stored policies cannot be used, or nothing of the measure is readable), 2 compares each row's names with `cell_name`'s (only while `improv.stale` vouches that every result row names the model's items), 3 checks each row with `row_visible`.
`cell_visible(model bigint, measure bigint, dims text[])`	boolean	Whether the caller may read the cell of `measure` at `dims` (item names in the measure's category order), as the result tables' row-level security decides it: true without policies and for the owner's side; false for a hidden cell, a name the model lacks, and every cell when the stored policies cannot be used. The check of a result table without dimensions.
`check_owner()`	trigger	Trigger on `improv.model`: only a role that may act as both the current and the new owner may set or change a model's owner.
`commit_capture()`	trigger	Commit mode's row-capture statement trigger (`improv.commit_capture = 'rows'`): notes the coordinates a statement touched for the pre-commit recompute.
`commit_catalog_changed()`	trigger	Statement trigger on `improv.model` and `improv.binding`: invalidates every backend's cache of the commit-mode relations.
`item_changed()`	trigger	Statement trigger on `improv.item`: an item renamed or deleted marks its model's result rows stale (`improv.stale`), so the result tables' qual checks them row by row until a recalc.
`may_act_as(r regrole)`	boolean	Whether the caller may act as role `r`: a superuser, or a member with the right to `SET ROLE` to it (any member on PG 15). The row-level security policies on the catalog call it.
`mint_items(p_model bigint, p_cats bigint[], p_names text[])`	TABLE(category_id bigint, name text, id bigint)	Assigns ids to item names it has not seen, in lexical order, never reusing one. Shared with the `postgres://` model store.
`on_change()`	trigger	Trigger mode's sync trigger: recomputes the changed inputs' dependents in the writer's transaction, as the model's owner.
`plimprov_call_handler()`	language_handler	The `LANGUAGE improv` call handler.
`plimprov_validator(oid)`	void	The `LANGUAGE improv` validator: checks the signature, and the body when `check_function_bodies` is on.
`result_written()`	trigger	Statement trigger on a filtered result table: a write marks the model's result rows stale (`improv.stale`) until a recalc checks them.
`row_visible(model bigint, measure bigint, VARIADIC dims "any")`	boolean	Row-level security on a result table, per row under `cell_rule` 3: `cell_visible`'s answer for the row's dimension values (text, in the measure's category order), from a decision table kept for the query.
`unrestricted(model bigint)`	boolean	Row-level security on the catalog: whether the caller may read model `model`'s measures, bindings, views and lambdas. True without access policies (0.12's rule) and for the owner's side.

Settings (GUCs)

Set in is the GUC's context: where a change takes effect.

GUC	Type	Default	Range	Set in	Description
`improv.commit_capture`	enum	`relations`	`relations`, `rows`	any session (`SET`)	What entering commit sync mode installs on the bound tables. relations (the default): nothing, and each COMMIT recomputes the written tables' dependents in full; rows: statement triggers that capture the touched coordinates, so COMMIT recomputes only those. Read by improv.set_sync_mode.
`improv.database`	string	`postgres`		`postgresql.conf`, restart	Database the pg_improv sync worker connects to. The logical-decoding worker serves the Improv models of this one database.
`improv.max_cells`	integer	`5000000`	0–2147483647	any session (`SET`)	Maximum bound input cells per Improv model. Every load of a model (recalc, define, a trigger-mode or commit-mode recompute, the sync worker's rebuild and apply) refuses a model with more bound input cells.
`improv.max_changes`	integer	`10000`	1–2147483647	`postgresql.conf`, reload	Most decoded changes one poll of the sync worker reads. Bounds the work (and memory) of one poll.
`improv.max_model_memory`	integer	`0` MB	0–2147483647 MB	`postgresql.conf`, reload	Most memory (MB) one model's live state may hold in the sync worker. A model whose fold state plus engine (as improv.status estimates them) would exceed it is refused: the worker logs why, sets its last_error, and retries it with backoff instead of growing until the backend is killed; 0 is no cap.
`improv.max_slot_lag`	integer	`4096` MB	0–2147483647 MB	`postgresql.conf`, reload	WAL (MB) the sync worker's slot may lag before it is dropped and remade. Past it the worker drops the slot and rebuilds each model from its tables, rather than retain more WAL; 0 never drops it. A cap that also holds while the worker is down is max_slot_wal_keep_size.
`improv.poll_ms`	integer	`200`	10–60000	`postgresql.conf`, reload	How often the pg_improv sync worker polls its slot, in ms. Lower is fresher results; higher is fewer idle wakeups.
`improv.raise_errors`	bool	`off`		any session (`SET`)	Raise a LANGUAGE improv error value instead of returning NULL. When on, a LANGUAGE improv function whose result is an error value (#DIV/0!, #NUM!, #N/A, ...) raises an ERROR naming it; when off it returns NULL.

improv.database exists only when pg_improv is in shared_preload_libraries.

`improv.status(model)` columns

One row per call; Monitoring says what a healthy value looks like and which to alert on.

Column	Type	Meaning
`sync_mode`	text	`logical`, `trigger`, `commit` or `manual`.
`slot`	text	`improv_db` in logical mode (the slot every logical model of the database shares); NULL otherwise.
`lag_bytes`	bigint	`pg_current_wal_lsn() − confirmed_flush_lsn` of the slot: WAL the worker has not confirmed yet. It is the slot's lag, so every logical model of the database shows the same number. NULL when the slot does not exist (not yet created, or dropped).
`confirmed_lsn`	text	The slot's `confirmed_flush_lsn`.
`last_applied_at`	timestamptz	When the worker last applied a batch to this model, or rebuilt it. It does not move while the model's tables are idle. NULL before the first catch-up, and after a new slot resets progress.
`rows_applied`	bigint	Result rows the worker has written for this model (inserted, updated or deleted), in total.
`last_error`	text	Why the worker's last attempt at this model failed; NULL after a success. While set, the model is retried with backoff (0.5 s, doubling, up to 60 s), and every retry is a full rebuild.
`incremental_applies`	bigint	Batches the worker fed to the live engine (the cheap path).
`rebuilds`	bigint	Full reloads and recomputes. The first catch-up counts one; so does each worker restart, structural change (`define`, a new binding), and batch that needs one (Concepts lists the triggers).
`fold_bytes`	bigint	Estimated heap of the model's fold state in the worker: what its aggregated bindings (`agg => …`) keep per coordinate. A `count_distinct`, `min` or `max` binding keeps its values too, so it grows with their number. 0 without aggregated bindings. Measured after each rebuild, and at most once a second while the model applies batches; NULL while the worker holds no live state for the model (before its first change, or after it failed).
`engine_bytes`	bigint	Estimated heap of the model's live engine and input cells in the worker, as of the same moment. `fold_bytes + engine_bytes` is what `improv.max_model_memory` caps.

`improv.models` columns

A view of every model, for anyone who may read the catalog.

Column	Type	Meaning
`name`	text	The model's name.
`owner`	regrole	The role the model reads its tables and writes its results as.
`sync_mode`	text	`logical`, `trigger`, `commit` or `manual`.
`may_change`	boolean	Whether the caller may act as the owner: change the model, read its items and cells, `what_if` it.
`result_schema`	text	The schema of its result tables, `improv_<lower(name)>`.

Keyboard shortcuts

Improv