provsql

provsql : Semiring provenance and uncertainty management for PostgreSQL

Overview

Packages

Source

pig build pkg provsql;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install provsql;		# install via package name, for the active PG version

pig install provsql -v 18;   # install for PG 18
pig install provsql -v 17;   # install for PG 17
pig install provsql -v 16;   # install for PG 16
pig install provsql -v 15;   # install for PG 15
pig install provsql -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'provsql';

Create this extension with:

CREATE EXTENSION provsql CASCADE; -- requires uuid-ossp

Usage

Sources: README, v1.4.0 release, latest release, v1.4.0 control, getting started, configuration, semirings, v1.4.0 upgrade

provsql adds semiring provenance and uncertainty management to PostgreSQL. Upstream documents provenance tracking, semiring evaluation, probabilities, Shapley and Banzhaf values, where-provenance, update provenance, and temporal features.

Load and Track Provenance

shared_preload_libraries = 'provsql'

CREATE EXTENSION provsql CASCADE;

The CASCADE form installs uuid-ossp automatically if needed. The getting-started guide says the preload step is mandatory because ProvSQL installs a planner hook.

SELECT provsql.add_provenance('mytable');

SELECT name, provenance()
FROM mytable;

SELECT provsql.remove_provenance('mytable');

The user docs also describe provenance mappings:

SELECT create_provenance_mapping('my_mapping', 'mytable', 'column_name');
SELECT create_provenance_mapping_view('my_mapping_view', 'mytable', 'column_name');

Probability and Influence

Assign probabilities to tuple tokens:

SELECT set_prob(provenance(), 0.8)
FROM mytable
WHERE id = 1;

SELECT name, probability_evaluate(provenance()) AS prob
FROM mytable;

Compute influence scores:

SELECT shapley(provenance(), m.token)
FROM mytable, my_mapping AS m;

SELECT banzhaf(provenance(), m.token)
FROM mytable, my_mapping AS m;

The docs also describe shapley_all_vars and banzhaf_all_vars for computing scores for all input variables at once.

Built-in Semirings

Built-in semiring functions use a provenance token and a provenance mapping table:

SELECT name, sr_boolean(provenance(), 'my_mapping')
FROM mytable;

SELECT name, sr_formula(provenance(), 'my_mapping')
FROM mytable;

SELECT name, sr_how(provenance(), 'my_mapping')
FROM mytable;

provsql 1.4.0 adds compiled wrappers for sr_how, sr_which, sr_tropical, sr_viterbi, sr_lukasiewicz, sr_minmax, and sr_maxmin. For PostgreSQL 14 and later it also adds sr_temporal, sr_interval_num, and sr_interval_int over multirange values.

SELECT city,
       sr_minmax(provenance(), 'personnel_level',
                 'unclassified'::classification_level) AS clearance
FROM (SELECT DISTINCT city FROM personnel) AS t;

SELECT entity_id, sr_temporal(provenance(), 'validity_mapping')
FROM mytable;

Advanced users can still define custom semirings and evaluate them with provenance_evaluate or aggregation_evaluate; upstream recommends the compiled semirings when one matches the needed algebra.

Extra Modes and Helpers

Session GUCs documented upstream include:

SET provsql.active = on;
SET provsql.where_provenance = on;
SET provsql.update_provenance = on;
SET provsql.tool_search_path = '/opt/d4:/home/postgres/bin';
SET provsql.aggtoken_text_as_uuid = on;

provsql.tool_search_path is used for external probability and visualization tools such as d4, c2d, dsharp, minic2d, weightmc, and graph-easy. provsql.aggtoken_text_as_uuid makes aggregate-token cells render as their provenance UUIDs; agg_token_value_text(token) can recover the display text for those aggregate tokens.

The user guide separately documents where-provenance helpers, update provenance, and temporal helpers such as get_valid_time, timetravel, timeslice, history, and undo. Version 1.4.0 also adds circuit-inspection helpers circuit_subgraph(root, max_depth) and resolve_input(uuid), used by ProvSQL Studio and useful for browsing circuit fragments.

Notes

• The package row in db/extension.csv lists version 1.4.0, package provsql, dependency uuid-ossp, and PostgreSQL support for 14 through 18.
• Upstream docs say ProvSQL has been tested on PostgreSQL 10 through 18. The Pigsty row tracks 1.4.0 even though upstream GitHub now has v1.6.0, so package metadata should be treated as the installed version for Pigsty builds.
• provsql.update_provenance and the multirange semirings require PostgreSQL 14 or later.