Configuration Reference
ProvSQL is controlled by GUC (Grand Unified Configuration) variables,
all settable per session with SET or permanently in
postgresql.conf
or with ALTER DATABASE
/ ALTER ROLE.
provsql.active(default:on)Master switch. When
off, ProvSQL drops all provenance annotations silently, as if the extension were not loaded. Useful to temporarily disable provenance tracking without unloading the extension.
provsql.provenance(default:'semiring')The provenance class of the session: the most specific class of provenance semantics circuits must remain valid for. A circuit built under a narrower class records it, and evaluating it under a semiring outside that class raises an error. From the most general to the most specialised:
'where'Universal semiring provenance plus where-provenance tracking (see Where-Provenance): each output value records its source cell. Not the default due to overhead.
'semiring'Universal semiring provenance (the default): circuits are valid for every commutative (m-)semiring, recursive queries over cyclic data included (see Querying with Provenance).
'absorptive'Circuits may additionally be valid only for absorptive commutative semirings (those where
: Boolean, min-plus over nonnegative costs, Viterbi…), and for probability computation; a semiring whose multiplication does not commute, such as
sr_best_path, refuses the circuits that rely on it, which do not keep the order of the query. Recursive reachability on bounded-treewidth data is then compiled so that it evaluates exactly under all of them, e.g., min-cost reachability (see Semiring Evaluation and Probabilities), and circuits are simplified accordingly.'boolean'Implies
'absorptive', and additionally enables every optimisation sound only when provenance is interpreted as a Boolean function, chiefly the safe-query rewriting, which computes in linear time the probability of self-join-free hierarchical queries over TID / BID tables. Semirings that are not Boolean-faithful refuse to evaluate the resulting circuits (see Probabilities and the compatibility note in Semiring Evaluation), and the multiplicity of result rows may change.
provsql.update_provenance(default:off)Enable provenance tracking for
INSERT,UPDATE, andDELETEstatements (see Data Modification Tracking). Requires PostgreSQL ≥ 14.
provsql.implicit_freeze(default:'warn')What happens when part of a query cannot be tracked (see Parts Whose Provenance Is Not Tracked) while the rest of the statement tracks the same relations:
'warn'runs it with aWARNING;'error'refuses the query (SQLSTATE0A000). A part marked withplain, or an aggregate read through an explicit cast (count(*)::numeric), is never refused.
provsql.classify_top_level(default:off)Emit a
NOTICEfor every top-levelSELECTreporting the certified kind of the result relation under theprovsql_table_kindtaxonomy (TID/BID/OPAQUE) and the provenance-tracked base relations it touches:NOTICE: ProvSQL: query result is TID (sources: public.personnel) NOTICE: ProvSQL: query result is OPAQUE NOTICE: ProvSQL: query result is TID (no provenance-tracked sources)
ProvSQL Studio enables it automatically (see ProvSQL Studio).
provsql.verbose_level(default:0)Controls the verbosity of ProvSQL diagnostic messages.
0is silent;1–9enable informational messages,10–100debug information. Thresholds include:≥ 1: report the safe-query / inversion-free certificate attached to a rewritten query.
≥ 5: approximation guarantees of sampling-based probability methods, comparison-resolution summaries, reasons for declining the safe-query rewrite.
≥ 10: notices from the SQL-level evaluators (e.g., when the reachability route falls back to the generic path).
≥ 20: print the SQL query before and after provenance rewriting (requires PostgreSQL ≥ 15); report the knowledge compilation method chosen and the size of its result.
≥ 25: report the gate count of a d-DNNF obtained by tree decomposition.
≥ 30: debug traces of the probability evaluators and the safe-query detector; keep all intermediate temporary files (Tseytin, d-DNNF, DOT) instead of deleting them, and report where they are.
≥ 40: also print the time spent on rewriting.
≥ 50: also print the full parse tree of the query before and after rewriting, and the cost-calibration notices of the probability method chooser.
provsql.verify_rewrite(default:off)Developer debugging aid: checks the query tree after each step of the rewriting, and raises an error naming the step that broke it.
provsql.trace_rewrite(default:off)Developer debugging aid: a
NOTICEfor each step of the rewriting that changed the query.provsql.aggtoken_text_as_uuid(default:off)When
on, anagg_tokencell renders as its provenance UUID instead of"value (*)"(ProvSQL Studio sets this per session);agg_token_value_textgives the display string of such a UUID. Only the text output changes.
provsql.monte_carlo_seed(default:-1)Seed for all Monte Carlo sampling (probabilities and continuous distributions). The default
-1seeds randomly; any other integer value (including0) is used as a fixed seed, makingprobability_evaluate(..., 'monte-carlo', 'n')and random-variable sampling reproducible across runs.
provsql.rv_mc_samples(default:10000)Sample count when
expected,variance,moment,rv_sampleorrv_histogramcannot compute a result exactly and falls back to sampling. Set to0to get an error instead, when only exact answers are acceptable. Does not affectprobability_evaluate(..., 'monte-carlo', 'n'), whose sample count is an argument.
provsql.ess_warn_fraction(default:0.1)Posterior inference over latent variables warns that its estimate is unreliable when the effective sample size falls below this fraction of the accepted draws; raise
provsql.rv_mc_samplesthen.0silences the warning.
provsql.simplify_on_load(default:on)Simplify provenance circuits when they are loaded, without changing the stored circuit. For every consumer,
view_circuitand Studio included, only rewrites that depend on no tuple’s provenance apply: a comparison decidable from the supports of its operands becomes a constant, a mixture whose choice is then constant keeps its surviving branch, and constant arithmetic is computed. The probability and distribution readouts (expected,variance,quantile,probability_evaluate…) further treat a mixture over tuples that are certainly present or absent (probability 1 or 0, including the default 1 of a tuple with no probability) as its surviving branch; the displayed circuit keeps those tuples’ tokens. Set tooffto inspect the raw circuit. See Continuous Distributions.
provsql.gate_cache_size(default:64MB)Per-session cache of circuit gates. Raise it for queries that create many gates before reading them back (a hash aggregate over a large table); count roughly 100 bytes per gate.
provsql.max_memory(default:0, no limit)Memory the evaluations of a statement (semiring, probability, Shapley values…), external tools included, may add to the backend; beyond it, the evaluation stops with a
program_limit_exceedederror (SQLSTATE54000,provsql-reason: memory-limit). Loading the circuit counts too, as does building it in the background worker that reads the provenance store, a process of its own, which is refused what the statement has not used. The query that builds the circuit (the aggregation over many rows, say) is not an evaluation, and does not count. The check is periodic, so the limit is approximate. Linux, macOS and FreeBSD only. Example:SET provsql.max_memory = '1GB'.Whatever this setting, a text result over 1 GB, the most PostgreSQL holds in a value, stops the evaluation with the same kind of error (
provsql-reason: result-too-large): a formula or a Boolean expression writes out a sub-circuit shared by several gates at each of them, and may grow exponentially with the circuit.
provsql.max_worlds(default:1048576, that is 220)Maximum number of possible worlds of a group enumerated to evaluate a condition on its aggregate that has no closed form (a comparison on a
SUM, on two aggregates…; up to 2n worlds for n rows). Beyond it, the evaluation stops with aprogram_limit_exceedederror (SQLSTATE54000,provsql-reason: world-limit).0for no limit.
provsql.store_synchronous_commit(default:off)Flush the provenance circuit to stable storage before a transaction that wrote to it commits, so that a machine crash cannot lose the gates of recently committed transactions (see Persistence, backups and replication). Costs one flush per such transaction, and provenance queries write to the circuit, reads included.
provsql.store_wal_logging(default:off, PostgreSQL 15+)Write every modification of the circuit to the WAL, so that a physical standby carries the provenance the primary computed. Requires
provsql.store_synchronous_commit; provenance queries then do not run on the standby. Superuser only. See Persistence, backups and replication.
provsql.tool_search_path(default: empty)Colon-separated list of directories prepended to
PATHwhen ProvSQL runs external command-line tools: every tool of theprovsql.toolsregistry (the d-DNNF compilersd4,d4v2,c2d,minic2d,dsharp, the model counters, the GraphViz ASCII renderergraph-easy, admin-registered tools…; see External-Tool Registry). Tools ofkind = 'kcmcp'are reached over a socket and do not involvePATH. The server’sPATHis searched as a fallback, so an entry here only needs to be set when a tool lives outside the server’s defaultPATH(e.g., in$HOME/local/bin, a Conda environment,/opt/...). Example:SET provsql.tool_search_path = '/opt/d4:/home/postgres/bin';
Superuser only (or, on PostgreSQL 15+, a role granted
SETon the parameter), since it decides which executables the server runs; an administrator can pin it for other roles withALTER ROLE ... SET.
provsql.fallback_compiler(default:d4)Name of the external compiler
probability_evaluate(with the empty or'default'method) invokes as the final fallback, when no in-process method succeeds. Accepts any compiler nameprobability_evaluateaccepts under the'compilation'method:d4(default),d4v2,c2d,minic2d,dsharp,panini-obdd,panini-obdd-and,panini-decdnnf. Useful on hosts whered4is not installed but another compiler is. Example:SET provsql.fallback_compiler = 'c2d';
provsql.prob_cache(default:'auto')Reuse compiled d-DNNFs across circuits of the same shape, within a session. With
'auto', the cache serves the automatic method selection only, and a method called by name runs as named.'on'extends it to'tree-decomposition'and'compilation'called by name, each answered only from a d-DNNF it compiled itself.'off'disables it. A cached answer appears ascache:<method>in provsql.last_eval_method.
provsql.prob_cache_size(default:64MB)Size of the cache of
provsql.prob_cache, per session. Past it, the d-DNNFs least recently used are dropped; one larger than a quarter of it is not kept. When provsql.max_memory is set, the cache takes at most a quarter of that limit.
provsql.idp_fold(default:'auto')Split a circuit into independent parts, evaluate each separately, and combine the results.
'auto','on', and'off'have the same scope as forprovsql.prob_cache. A split evaluation appears asfoldin provsql.last_eval_method.
provsql.last_eval_method(default: empty)Set by
probability_evaluate: the evaluation methods used in the session, comma-separated, each listed once, to see what the automatic selection chose; see 'sq-rewrite', 'bounded-jw', 'reachability'. Each call adds its method to the list, so empty it first (SET provsql.last_eval_method = '') to read the methods of one query only.
provsql.joint_max_treewidth(default:10)Maximum joint treewidth the joint-width UCQ probability compiler attempts. Above this bound, evaluation falls back to the other probability methods.
provsql.joint_max_states(default:65536)Maximum number of dynamic-programming states per bag of the joint-width UCQ probability compiler; above it, evaluation falls back to the other probability methods.
provsql.mobius_max_gates(default:4000000)Cap, in number of gates built, on the data cost of the safe-UCQ Möbius-inversion probability route; above it, evaluation falls back to the joint-width compiler or the other probability methods.
provsql.mobius_max_cnf(default:8)Cap on the query cost of the safe-UCQ Möbius-inversion probability route (the number of conjuncts in the CNF of each sentence, a cost exponential in it). Only a very large union, or a self-joining query, reaches it.
0disables the cap.
provsql.kcmcp_server(default: empty)Launch command for a managed KCMCP knowledge-compiler server (see the KCMCP server protocol). When non-empty, ProvSQL keeps a server started with this command running, and a registry tool of
kind = 'kcmcp'whoseendpointis'managed'compiles over it.{endpoint}is replaced by a Unix-socket path (unix:scheme included). Example:ALTER SYSTEM SET provsql.kcmcp_server = 'tdkc --kcmcp {endpoint}'; SELECT pg_reload_conf();
Set in the configuration file or with
ALTER SYSTEM, and applied on reload.
All variables above except provsql.tool_search_path,
provsql.store_wal_logging and provsql.kcmcp_server can be changed by any
user for their own session.
Schema and search_path
ProvSQL installs all its types, functions, and operators into a schema
named provsql. Functions and operators are resolved through
PostgreSQL’s search_path,
so unless provsql is on the path you must qualify every name
(provsql.expected(...), OPERATOR(provsql.+)…). The convenient
setup keeps provsql on the path so unqualified names just work:
-- per database (persistent; affects new sessions):
ALTER DATABASE mydb SET search_path = "$user", public, provsql;
-- or for the current session only:
SET search_path TO "$user", public, provsql;
What goes wrong without it
When provsql is not on the path:
Random-variable comparisons and arithmetic (
v < w,v + w,sum(v)over arandom_variablecolumnv) raiseoperator does not exist: provsql.random_variable ….Aggregate-token comparisons on a materialised
agg_tokencolumn (WHERE s > 15) likewise raise an error, instead of silently comparing the bare value and losing the provenance.Plain ProvSQL function calls (
expected(...),provenance(), thesr_*semiring evaluators,probability(...)…) raisefunction … does not exist.
The fix is always the same: put provsql on the search_path (or
qualify the name).
The setup_search_path() helper
CREATE EXTENSION provsql prints a NOTICE when the database’s
default search_path does not include provsql. The bundled
helper does the edit for you:
SELECT provsql.setup_search_path();
It appends provsql to the database’s search_path setting if it
is not already present (keeping the existing entries in order), and
applies the result with ALTER DATABASE. It is idempotent and reports
what it did with a NOTICE. Only new sessions pick up the change
– reconnect (or SET search_path in the current session) to use
unqualified names right away. The caller must be the database owner or a
superuser, and role-level search_path settings (if any) take
precedence over the database-level one and are left untouched.
ProvSQL never edits your search_path on its own: CREATE EXTENSION
only advises, and setup_search_path() runs only when you call it.
The Build in Use
provsql.build_id() returns the commit the loaded library was built
from, as git describe gives it (e.g., v1.12.0-431-g577cc730), or
unknown for a build outside a git checkout. Quote it in a bug report
on a development version.