Adding Provenance to a Table
Before ProvSQL can track provenance, the extension must be loaded and provenance must be enabled on each relevant table. ProvSQL represents provenance as a circuit of gates following the model of [Deutch et al., 2014].
Loading the Extension
In every database where you want provenance support:
CREATE EXTENSION provsql CASCADE;
The CASCADE option installs the required uuid-ossp dependency
automatically.
To call ProvSQL functions without the provsql. prefix, add provsql
to the search path at the start of each session:
SET search_path TO public, provsql;
To make this permanent for a specific database:
ALTER DATABASE mydb SET search_path TO public, provsql;
Most examples in this documentation omit the provsql. prefix, assuming
provsql is in the search path.
Disabling Provenance Temporarily
Setting provsql.active to off makes ProvSQL silently drop all
provenance annotations for the current session, as if the extension were
not loaded:
SET provsql.active = off;
This is useful for running queries without provenance overhead while keeping the extension installed. See Configuration Reference for all configuration variables.
Enabling Provenance on a Table
Use add_provenance to add provenance tracking to an existing table:
SELECT provsql.add_provenance('mytable');
This adds a provsql column of type uuid to the table. Each
row receives a freshly generated UUID that identifies a leaf (input) gate
in the provenance circuit.
After enabling provenance, every query that reads from mytable will
automatically carry provenance annotations in its result set.
The table is recorded as TID (tuple-independent) in ProvSQL’s
per-database metadata store. To set up a table as BID
(block-independent) instead, use repair_key (see
Probabilities) on a table that does not yet have
provenance: it adds the provsql column itself and registers the
table as BID with the chosen block-key columns. Do not call
add_provenance first; repair_key is an
alternative to it, not a follow-up. The 'boolean' provenance-class
optimisation (see Probabilities) relies on this classification.
Note
add_provenance must be called on the base table, not on a view.
Accessing the Provenance Token
The provsql column is intentionally opaque – it is silently removed
from WHERE or ORDER BY clauses. To refer to the current row’s
provenance token, use the provenance() function:
SELECT name, provenance() FROM mytable;
Within a query result, the provsql attribute carries a UUID that
identifies the tuple’s gate in the provenance circuit.
Removing Provenance
To stop tracking provenance for a table (and drop the provsql column),
use remove_provenance:
SELECT provsql.remove_provenance('mytable');
Both add_provenance and remove_provenance are
idempotent: enabling tracking on a table that already has it, or
removing it from a table that never had it, reports what it found with a
NOTICE and does nothing. Setup scripts and notebook cells can be
re-run freely.
Aggregates and INSERT ... SELECT
An aggregate over a provenance-tracked relation evaluates to an
agg_token (see Aggregation and Grouping), which pairs the aggregate’s
value with the provenance of the aggregation. Inserting such an
aggregate into an ordinary column with INSERT ... SELECT:
INSERT INTO headcount SELECT count(*) FROM personnel;
stores the aggregate’s value in the target column and warns that the
aggregate’s own provenance is dropped. If the target table is itself
provenance-tracked, the inserted row still gets its tuple provenance in
the provsql column; it is only the value-level aggregate provenance
that an ordinary bigint / numeric column cannot hold. To keep
it, materialise the aggregate with CREATE TABLE ... AS SELECT
instead, which lets the column take the agg_token type.
Provenance Mappings
A provenance mapping associates provenance tokens with values from a table
column. Mappings are the bridge between abstract circuit tokens and
domain-meaningful labels used by semiring evaluation functions.
Use create_provenance_mapping to create one:
SELECT create_provenance_mapping('my_mapping', 'mytable', 'column_name');
The mapping is stored as an ordinary PostgreSQL table called my_mapping
with two columns: provenance (uuid) and value (the source column’s
type). By default it is a one-off snapshot of the table as it stands.
Pass maintained => true to keep it current instead:
SELECT create_provenance_mapping('my_mapping', 'mytable', 'column_name',
maintained => true);
A maintained mapping is extended automatically as new rows are inserted, and
it stays correct under data modification: a delete or update changes a row’s
provsql token, but the value remains keyed to the original input token,
so evaluation still resolves it. This matters for temporal
validity, where a row deleted at time T keeps its original interval,
bounded at T. A maintained mapping requires column_name to be a plain
column.
ProvSQL Studio
ProvSQL Studio’s schema panel is an interactive surface for the operations above:
it lists every
SELECT-able relation, with a purple prov pill on tables whoseprovsqlcolumn is injected by the planner (provenance tracking is active) and a gold mapping pill on relations shaped(value <T>, provenance uuid);+ prov and − prov action chips on provenance-eligible plain tables prefill the corresponding
SELECT add_provenance(...)/SELECT remove_provenance(...)call into the query box;clicking a column on a tracked table prefills a
SELECT create_provenance_mapping('<table>_<col>_mapping', '<schema>.<table>', '<col>');call, so a fresh provenance mapping is two clicks away.
Inspecting the Circuit
ProvSQL represents provenance as a circuit: a directed acyclic graph
(DAG) of gates. Each tuple in a provenance-tracked table is
associated with an input gate, created lazily the first time that
tuple appears in a query result. Tuples may also carry more complex
provenance – for instance, rows created by INSERT ... SELECT or
CREATE TABLE AS inherit the provenance expression of the source
query.
As queries combine tuples, internal gates record the semiring operations that were applied:
plus(⊕): alternative derivations (UNION,DISTINCT)times(⊗): combined use (JOIN, cross product)monus(⊖): difference (EXCEPT)delta(δ): aggregation boundary (GROUP BY)agg,semimod: aggregate provenanceproject,eq: where-provenance (column tracking, equijoin)cmp:HAVINGcomparisons (and comparisons on random variables inWHERE, see Continuous Distributions)mulinput: multivalued input (one alternative of a block-independent input; see Probabilities)
Two constant gates represent the semiring identity elements:
gate_zero (additive identity, 𝟘) and
gate_one (multiplicative identity, 𝟙).
Additional gate types support scalar values and continuous random variables (see Continuous Distributions):
value: scalar constant (HAVINGprovenance and random variables)rv: random-variable leaf carrying one of the registered distribution families (Normal, Uniform, Exponential, Gamma, Beta…;rv_familieslists them all)arith:N-ary arithmetic over scalar children (+ - * /, unary-, min/max,pow/ln/exp, and thepercentile_contorder statistic)mixture: Bernoulli or categorical mixture of scalar random-variable rootscase: guarded selection over random variables (CASEexpressions, first-match semantics)observe: likelihood-weighting evidence on an observed random-variable leaf
Further gate types serve specific features:
update: data-modification tracking (see Data Modification Tracking)conditioned: conditioning marker with children[target, evidence](see Conditioning)mobius: signed Möbius combination over child islands, with one integer coefficient per child (safe-UCQ probability evaluation)fixvar,fixsystem,fixpoint: the equation system of a recursive query over cyclic data (see Recursive Queries)
Two transparent marker gates wrap a single child without changing its value (a circuit carrying them evaluates identically to one without):
assumed: structural assumption marker whoseextralabel names the assumption the wrapped sub-circuit was computed under:'boolean'(the default when the label is absent: only Boolean semantics are preserved) or'absorptive'(the bounded-treewidth reachability route). Evaluation under a semiring outside the recorded class refuses with an explicit error.annotation: carries the inversion-free certificate on a result root, or a per-input order key, for the'inversion-free'probability method (see Probabilities).
The following functions let you navigate and inspect the circuit:
get_gate_type– returns the type of a gate.get_children– returns the child tokens of a gate.identify_token– given a provenance token, returns the source table and row it originates from.get_infos– returns the integer metadata attached to a gate (e.g., aggregate function OID, comparison operator OID).get_extra– returns the text metadata attached to a gate (e.g., aggregate value, column positions for where-provenance).get_nb_gates– returns the total number of gates in the circuit, useful for diagnosing circuit size and performance.
SELECT provsql.get_gate_type(provenance()) FROM mytable;
SELECT provsql.get_children(provenance()) FROM mytable;