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 hidden 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.

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 value that represents the provenance circuit gate for that tuple.

Removing Provenance

To stop tracking provenance for a table (and drop the provsql column), use remove_provenance:

SELECT provsql.remove_provenance('mytable');

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: token (uuid) and value (text or numeric, depending on the source column type).

Inspecting the Circuit

get_gate_type and get_children let you inspect the structure of the provenance circuit:

SELECT provsql.get_gate_type(token);  -- returns the gate type
SELECT provsql.get_children(token);   -- returns the child tokens

Gate types include input, plus, times, monus, project, eq, agg, semimod, zero, one, and others.