provsql

Overview

Version

Build

You can build the RPM / DEB packages for provsql using pig build:

pig build pkg provsql         # build RPM / DEB packages

Install

You can install provsql directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install provsql;          # Install for current active PG version

pig ext install -y provsql -v 18  # PG 18
pig ext install -y provsql -v 17  # PG 17
pig ext install -y provsql -v 16  # PG 16
pig ext install -y provsql -v 15  # PG 15
pig ext install -y provsql -v 14  # PG 14

dnf install -y provsql_18       # PG 18
dnf install -y provsql_17       # PG 17
dnf install -y provsql_16       # PG 16
dnf install -y provsql_15       # PG 15
dnf install -y provsql_14       # PG 14

apt install -y postgresql-18-provsql   # PG 18
apt install -y postgresql-17-provsql   # PG 17
apt install -y postgresql-16-provsql   # PG 16
apt install -y postgresql-15-provsql   # PG 15
apt install -y postgresql-14-provsql   # PG 14

Preload:

shared_preload_libraries = 'provsql';

Create Extension:

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.