 |
ProvSQL SQL API
Adding support for provenance and uncertainty management to PostgreSQL databases
|
|
ProvSQL SQL API
Adding support for provenance and uncertainty management to PostgreSQL databases
|
Low-level functions for creating and querying provenance circuit gates. More...
Functions | |
| VOID | provsql.create_gate (UUID token, PROVENANCE_GATE type, UUID[] children=NULL) |
| Create a new gate in the provenance circuit. | |
| VOID | provsql.create_gate (UUID token, PROVENANCE_GATE type, UUID[] children, INT info1, INT info2, TEXT extra) |
| Create a gate together with what it records (internal). | |
| PROVENANCE_GATE | provsql.get_gate_type (UUID token) |
| Return the gate type of a provenance token. | |
| UUID[] | provsql.get_children (UUID token) |
| Return the children of a provenance gate. | |
| VOID | provsql.set_prob (UUID token, DOUBLE PRECISION p) |
| Write the probability of an input gate, once. | |
| BOOLEAN | provsql.probability_is_set (UUID token) |
| Report whether a probability has been written on a gate. | |
| VOID | provsql.note_fresh_leaf (UUID token) |
| Declare a just-minted leaf gate a replacement for a tracked row. | |
| BOOLEAN | provsql.is_fresh_leaf (UUID token) |
Whether token was minted as a replacement leaf by this transaction (see note_fresh_leaf). | |
| UUID | provsql.replace_input (UUID old, DOUBLE PRECISION p) |
| Mint a replacement input gate carrying a different probability. | |
| UUID | provsql.replace_input (REGCLASS _tbl, UUID old, DOUBLE PRECISION p) |
| Replace a tracked row's input gate, rewriting the row. | |
| VOID | provsql.replace_block (REGCLASS _tbl, UUID old_key, DOUBLE_PRECISION[] probs=NULL) |
Give a repair_key block a new set of probabilities. | |
| DOUBLE PRECISION | provsql.get_prob (UUID token) |
| Get the probability associated with an input gate. | |
| RECORD | provsql.get_infos (UUID token, OUT INT info1, OUT INT info2) |
| Get the INTEGER info values associated with a circuit gate. | |
| UUID | provsql.provenance_assume (UUID token, TEXT assumption) |
Wrap token in a fresh gate_assumed carrying assumption as its label, and return the wrapper's UUID. | |
| UUID | provsql.assume_boolean (UUID token) |
Wrap token in a Boolean-assumption marker (compatibility name; see provenance_assume). | |
| UUID | provsql.annotate (UUID token, TEXT extra) |
Wrap token in a fresh transparent gate_annotation carrying extra, and return the wrapper's UUID. | |
| UUID | provsql.strip_annotations (UUID token) |
Peel every transparent gate_annotation wrapper off token, returning the first non-annotation gate underneath. | |
| UUID | provsql.cond (UUID target, UUID evidence) |
| Condition a provenance token (a Boolean event) on another. | |
| BOOLEAN | provsql.UUID_op_UUID (UUID left, UUID right) |
Binary "target | evidence". | |
| UUID | provsql.cond_predicate (UUID target, BOOLEAN predicate) |
Placeholder for "X | (predicate)" on a UUID event. | |
| BOOLEAN | provsql.UUID_op_boolean (UUID left, BOOLEAN right) |
| UUID | provsql.predicate_cond_predicate (BOOLEAN target, BOOLEAN evidence) |
Placeholder for "(predicate) | (predicate)" on two events. | |
| BOOLEAN | provsql.boolean_op_boolean (BOOLEAN left, BOOLEAN right) |
| UUID | provsql.regular_indicator (BOOLEAN cond) |
| Deterministic indicator gate for an ordinary (regular) comparison. | |
| UUID | provsql.given (UUID evidence) |
Whole-tuple output conditioning directive: "given(evidence)". | |
| UUID | provsql.given (BOOLEAN predicate) |
Prefix unary given, "| evidence". | |
| UUID | provsql.provenance_not (UUID event) |
Event negation: "! event" / "provenance_not(event)". | |
| TEXT | provsql.inversion_free_key (TEXT root, TEXT sec, INT factor) |
Prefix unary ! | |
| TEXT | provsql.get_extra (UUID token) |
| Get the TEXT-encoded extra data associated with a circuit gate. | |
| BIGINT | provsql.get_nb_gates () |
| Return the total number of materialized gates in the provenance circuit. | |
| RECORD | provsql.check_store (OUT BOOLEAN unclean_shutdown, OUT BIGINT nb_gates, OUT BIGINT nb_tokens, OUT BIGINT next_index, OUT BIGINT dangling_indices, OUT BIGINT unreferenced, OUT BIGINT bad_wires, OUT BIGINT bad_extra) |
| Report what does not add up in this database's circuit store. | |
| RECORD | provsql.circuit_cleanup (BOOLEAN dry_run=false, OUT BIGINT gates_before, OUT BIGINT gates_after, OUT BIGINT wires_before, OUT BIGINT wires_after, OUT BIGINT extra_bytes_before, OUT BIGINT extra_bytes_after) |
| Rebuild this database's circuit store, keeping only what the tokens stored in the database reach. | |
Low-level functions for creating and querying provenance circuit gates.
| UUID provsql.annotate | ( | UUID | token, |
| TEXT | extra ) |
Wrap token in a fresh transparent gate_annotation carrying extra, and return the wrapper's UUID.
Unlike every other gate, the annotation wrapper's UUID folds in extra (not just the child): uuid_generate_v5 over concat('annotation', token, extra). This is deliberate – two annotations over the same child with different extra must be distinct gates (e.g. the same input tuple carrying different per-occurrence order keys, or two queries attaching different certificates to a shared root). The wrapper is transparent (identity) for EVERY evaluator; extra is inert metadata read only by the code that placed it. No-op (returns NULL) on a NULL input.
Implemented in C (gate_builders.c), with the declared cost of the function it replaces, so that plans stay the same.
| UUID provsql.assume_boolean | ( | UUID | token | ) |
Wrap token in a Boolean-assumption marker (compatibility name; see provenance_assume).
This is the entry point the safe-query (read-once) rewriter calls on every per-row root it produces, so the wrapper additionally carries PROVSQL_ROUTE_SQ_REWRITE in info1: the assumption kind alone does not identify the route (provenance_assume(t, 'BOOLEAN') is public), and the probability dispatcher reads the tag back to report sq-rewrite rather than the generic independent. Build an untagged Boolean-assumption wrapper with provenance_assume directly.
Implemented in C (gate_builders.c): the gate is created together with what it records, in one unanswered message.
| BOOLEAN provsql.boolean_op_boolean | ( | BOOLEAN | left, |
| BOOLEAN | right ) |
| RECORD provsql.check_store | ( | OUT BOOLEAN | unclean_shutdown, |
| OUT BIGINT | nb_gates, | ||
| OUT BIGINT | nb_tokens, | ||
| OUT BIGINT | next_index, | ||
| OUT BIGINT | dangling_indices, | ||
| OUT BIGINT | unreferenced, | ||
| OUT BIGINT | bad_wires, | ||
| OUT BIGINT | bad_extra ) |
Report what does not add up in this database's circuit store.
The store lives in four files under the database's directory, outside PostgreSQL's WAL and buffer manager, so PostgreSQL's own crash recovery says nothing about it. Writes are ordered so that an interrupted one leaves a RECORD nothing points at rather than a pointer to a RECORD that is not there, and the rehash of the token table is done by renaming a complete new file over the old one; this function checks that those invariants hold.
Every count is 0 for a healthy store. unclean_shutdown is true when a file was still marked open-for-writing when it was opened, which an immediate shutdown or a crash of the server leaves behind and is not by itself a problem. A non-zero dangling_indices, bad_wires or bad_extra means the files do not agree with each other – typically a file-level backup taken while the server was running, or a base backup; provsql.circuit_cleanup() rebuilds the store from what is still reachable. unreferenced counts gate records the token table does not point at: a handful is normal (an interrupted write), a large number means the token table is missing entries.
| RECORD provsql.circuit_cleanup | ( | BOOLEAN | dry_run = false, |
| OUT BIGINT | gates_before, | ||
| OUT BIGINT | gates_after, | ||
| OUT BIGINT | wires_before, | ||
| OUT BIGINT | wires_after, | ||
| OUT BIGINT | extra_bytes_before, | ||
| OUT BIGINT | extra_bytes_after ) |
Rebuild this database's circuit store, keeping only what the tokens stored in the database reach.
The store only grows: a gate is never removed, because a rolled-back transaction leaves an orphan rather than an inconsistency, and because the same expression recomputed lands on the same content-addressed gate. This is the complement of that – the one operation allowed to remove gates, run explicitly, the way VACUUM FULL is the complement of MVCC. It is also the repair tool for a store an interrupted write left inconsistent (see provsql.check_store).
It takes the database exclusively: it holds the lock DROP DATABASE holds, so sessions connecting from then on wait, and it refuses to run while another session is already connected. That is unavoidable – a query running alongside can adopt an orphan gate a moment before the sweep removes it, since gates are re-created idempotently and a backend's own cache answers "it exists" without asking the store at all.
A root is every value of a UUID, AGG_TOKEN or random_variable column, and of arrays of those, in every table and materialised view of the database – not only columns named provsql – plus the constants gate_zero, gate_one and gate_null. A token that lives only outside the database is not a root: one kept in a notebook cell, a deep link, a file, or a TEXT / jsonb column. Content-addressed gates come back by re-running the query that built them; freshly minted ones do not.
| dry_run | report what would be kept without writing anything; the wire and byte totals are then NULL, since the size of the rewrite is not known without doing it | |
| [out] | gates_before | gate records in the store beforehand |
| [out] | gates_after | gate records the roots reach, and so kept |
| [out] | wires_before | child wires beforehand |
| [out] | wires_after | child wires kept (NULL on a dry run) |
| [out] | extra_bytes_before | bytes of gate annotations beforehand |
| [out] | extra_bytes_after | bytes of gate annotations kept (NULL on a dry run) |
| UUID provsql.cond | ( | UUID | target, |
| UUID | evidence ) |
Condition a provenance token (a Boolean event) on another.
Builds the terminal gate_conditioned that the measure evaluators read as "P(target ∧ evidence) / P(evidence)". This is the backing function of the binary | operator ("target | evidence", value-level conditioning of the UUID carrier).
The gate stores three children [target, evidence, joint] with joint = times(target, evidence); evaluation is then the plain ratio P(joint)/P(evidence), and content-addressing makes a base tuple shared by target and evidence the same input gate in both circuits, so the conditional is exact and correlation-aware.
Conventions:
evidence NULL or gate_one() returns target unchanged ("P(X|true)=P(X)").target with no provenance defaults to the certain event 1, so "1 | c" is the well-defined certain-row posterior."(X | A) | B = X | (A ∧ B)" – the gate never nests, it stays one level deep with the evidence accumulated by times.The result is TERMINAL: a conditioned token may not become a child of a plus / times / monus / agg gate (those constructors refuse it); the only operation it admits is more conditioning.
| UUID provsql.cond_predicate | ( | UUID | target, |
| BOOLEAN | predicate ) |
Placeholder for "X | (predicate)" on a UUID event.
Lets the conditioning event be written as a natural Boolean combination of random_variable / aggregate comparisons (e.g. "event | (sensor > 3)") instead of a hand-built gate. Never executes: the ProvSQL planner hook converts the Boolean operand into a condition gate and emits cond.
| VOID provsql.create_gate | ( | UUID | token, |
| PROVENANCE_GATE | type, | ||
| UUID[] | children, | ||
| INT | info1, | ||
| INT | info2, | ||
| TEXT | extra ) |
Create a gate together with what it records (internal).
One unanswered message to the worker, where create_gate() followed by set_infos() or set_extra() would wait for the worker's verdict on each. For the callers that know what the gate records when they create it: a gate whose address hashes its infos and TEXT, or a fresh token nobody else can have written to. Infos and TEXT are still written once: should something else be recorded at that address, the first value stays and the worker logs it.
| token | UUID identifying the new gate |
| type | gate type (see PROVENANCE_GATE) |
| children | optional array of child gate UUIDs The infos are used in different ways by different gate types: for mulinput, info1 is the value of the multivalued variable; for eq, info1 and info2 are the attribute positions equated; for agg, info1 is the OID of the aggregate function and info2 that of its result type; for cmp, info1 is the OID of the comparison operator; for arith, info1 is the operator. The TEXT is used in different ways too: for project, a TEXT-encoded array of two-element arrays mapping input attributes to output attributes; for value and agg, the TEXT-encoded scalar value; for rv, the distribution family and its parameters; for assumed, the assumption kind; for annotation, inert metadata read only by the code that placed it. |
| info1 | first info, or NULL |
| info2 | second info, or NULL |
| extra | TEXT of the gate, or NULL |
| VOID provsql.create_gate | ( | UUID | token, |
| PROVENANCE_GATE | type, | ||
| UUID[] | children = NULL ) |
Create a new gate in the provenance circuit.
| token | UUID identifying the new gate |
| type | gate type (see PROVENANCE_GATE) |
| children | optional array of child gate UUIDs |
| UUID[] provsql.get_children | ( | UUID | token | ) |
Return the children of a provenance gate.
| TEXT provsql.get_extra | ( | UUID | token | ) |
Get the TEXT-encoded extra data associated with a circuit gate.
| PROVENANCE_GATE provsql.get_gate_type | ( | UUID | token | ) |
Return the gate type of a provenance token.
Returns 'input' for any token not yet materialized in the circuit, since input is the default semantics of an unmaterialized provenance token.
| RECORD provsql.get_infos | ( | UUID | token, |
| OUT INT | info1, | ||
| OUT INT | info2 ) |
Get the INTEGER info values associated with a circuit gate.
| BIGINT provsql.get_nb_gates | ( | ) |
Return the total number of materialized gates in the provenance circuit.
Input gates for provenance-tracked table rows are created lazily on first reference; rows that have never appeared in a query result are not counted.
| DOUBLE PRECISION provsql.get_prob | ( | UUID | token | ) |
Get the probability associated with an input gate.
| UUID provsql.given | ( | BOOLEAN | predicate | ) |
Prefix unary | : alias for given, "| evidence".
Disambiguated from the binary | by the absence of a left operand ("a, | c" parses "| c" as the prefix form). PostgreSQL keeps prefix operators on every supported version (postfix operators were removed in PG14), so "| c" is safe across the CI matrix.
Conditioning-evidence from a predicate: "given(predicate)" (also the prefix "| (predicate)").
Two uses of the same marker:
given ("SELECT a, given(sensor > 3)");and_agg – "and_agg(given(normal(mu,1) = x))" turns each row's observation into likelihood-weighting evidence.Never executes: the planner converts the Boolean operand into a condition gate and emits given(gate); a point-equality "Y = d" on a bare random-variable leaf then becomes an observation (see given(UUID) / evidence_as_observation).
| UUID provsql.given | ( | UUID | evidence | ) |
Whole-tuple output conditioning directive: "given(evidence)".
Written as a term in the select list, given(c) conditions the OUTPUT provenance of the current query's rows on c:
The query rewriter recognises the marker, STRIPS it from the visible projection, and wraps each output row's provenance expression in cond(row_provenance, c) – deriving a new conditioned relation, never mutating any stored provenance. c is evaluated per output row and may correlate with the row's columns, so each tuple is conditioned on its own evidence. When the rewriter is inactive the call is a harmless identity (it returns evidence as an ordinary column).
When executed rather than stripped – i.e. nested inside an expression, the idiom "and_agg(given(Y = d))" that folds one observation per row into a latent-variable evidence circuit -- a point-equality "Y = d" on a bare random-variable leaf is turned into likelihood-weighting evidence (evidence_as_observation); any other evidence passes through unchanged.
| TEXT provsql.inversion_free_key | ( | TEXT | root, |
| TEXT | sec, | ||
| INT | factor ) |
Prefix unary !
: alias for provenance_not, "! event".
Prefix operators are kept on every supported PostgreSQL version (postfix operators were removed in PG14), and core PG defines no prefix ! on UUID, so "! event" is safe across the CI matrix.
Build a per-input order-key string for the inversion-free path.
Emitted by the planner per certified atom: K-prefixed, length-prefixed "K<factor> <octet_length(root)>:<root><octet_length(sec)>:<sec>", parsed back at evaluation by safe_cert_key_parse. root / sec are the tuple's root- and secondary-class column values (TEXT-cast by the caller); the byte-length prefixes keep the values unambiguous for any column type, including TEXT containing spaces, colons or digits. factor is the atom's factor id (or -1 for the shared self-join guard). IMMUTABLE so the planner can fold it and the marker dedups by content-addressing.
Implemented in C (gate_builders.c); it was an inlined SQL function, hence the default cost.
| BOOLEAN provsql.is_fresh_leaf | ( | UUID | token | ) |
Whether token was minted as a replacement leaf by this transaction (see note_fresh_leaf).
Internal.
| VOID provsql.note_fresh_leaf | ( | UUID | token | ) |
Declare a just-minted leaf gate a replacement for a tracked row.
Internal. provsql.replace_input and provsql.replace_block call this so the UPDATE that stores the new token does not look, to provenance_guard, like a user pasting in an arbitrary UUID – which would flip the table to OPAQUE. The declaration lasts for the transaction.
| UUID provsql.predicate_cond_predicate | ( | BOOLEAN | target, |
| BOOLEAN | evidence ) |
Placeholder for "(predicate) | (predicate)" on two events.
Conditions one comparison event on another when both operands are written as comparisons rather than pre-built tokens (e.g. "probability((x >= 2000) | (x >= 1000))"): an random_variable / AGG_TOKEN comparison is statically BOOLEAN-typed, so neither the "UUID | UUID" (cond) nor the "UUID | BOOLEAN" (cond_predicate) operator resolves. Never executes: the ProvSQL planner hook lowers each Boolean operand to its event gate and emits cond(target, evidence), so the result carries the correlation-aware Pr(A ∧ B) / Pr(B). Returns UUID, so "A | B" is a first-class event token in every position (a probability(UUID) argument, a projected column, a further "|").
| BOOLEAN provsql.probability_is_set | ( | UUID | token | ) |
Report whether a probability has been written on a gate.
get_prob returns the value an evaluation would use, so it answers 1 both for a gate written as certain and for one nobody has given a probability. This distinguishes them, which is what a client needs in order to offer "set" on the one and "replace" on the other.
| UUID provsql.provenance_assume | ( | UUID | token, |
| TEXT | assumption ) |
Wrap token in a fresh gate_assumed carrying assumption as its label, and return the wrapper's UUID.
Public primitive callable from any rewrite or driver that needs to flag a sub-circuit as sound only under an evaluation assumption:
'BOOLEAN' – the sub-circuit only preserves the Boolean function of the lineage (e.g. the safe-query rewrite collapses derivation multiplicities); transparent for semirings admitting a homomorphism from Boolean functions.'absorptive' – the sub-circuit was truncated at the absorptive value fixpoint (cyclic recursive query); transparent for absorptive semirings (probability, BOOLEAN, min-plus over nonnegative costs...), fatal for the rest (counting, why-provenance).Incompatible evaluators raise a CircuitException. Always kept as an explicit node in PROV-XML export.
The wrapper UUID is content-derived via uuid_generate_v5 on the assumption and the child, so identical children always wrap to the same outer UUID per assumption. No-op (returns NULL) on a NULL input.
Implemented in C (gate_builders.c): the gate is created together with what it records, in one unanswered message.
| UUID provsql.provenance_not | ( | UUID | event | ) |
Event negation: "! event" / "provenance_not(event)".
The complement of a Boolean provenance event: "!x" holds in exactly the worlds where x does not. It is sugar for "monus(one, x)" -- an ordinary m-semiring expression (Boolean NOT, probability "1 - P(x)"), NOT a measure-only marker – so it composes like any monus, and a conditioned / terminal token is refused as its child (so "!(x | c)" errors, as conditioning cannot be buried under further algebra).
The motivating use is conditioning on the NON-occurrence of an arbitrary violation query W (a denial constraint), where W itself is built with ordinary idioms and needs no hand-rolled gates:
Named provenance_not, after the "provenance_times / _plus / _monus" family; the prefix ! operator is the ergonomic form (SQL's reserved NOT keyword cannot serve as a function name).
| UUID provsql.regular_indicator | ( | BOOLEAN | cond | ) |
Deterministic indicator gate for an ordinary (regular) comparison.
The predicate-provenance of an ordinary comparison (both sides of regular type, e.g. "region = 'north'") is the deterministic indicator "χ(cond)": gate_one() when the comparison holds on the row, gate_zero() otherwise (Definition in the HAVING-provenance semantics). The planner emits this for a regular comparison appearing inside a MIXED conditioning predicate (one that also has a random_variable / aggregate comparison); cond is evaluated per row, so the indicator is the row's own truth value, combined by ⊗ / ⊕ with the probabilistic gates.
| VOID provsql.replace_block | ( | REGCLASS | _tbl, |
| UUID | old_key, | ||
| DOUBLE_PRECISION[] | probs = NULL ) |
Give a repair_key block a new set of probabilities.
The block-level counterpart of replace_input. A block's values share one key gate and their masses are meaningful together, so they are replaced together: this mints a new key gate and one new mulinput per row of the block, writes probs to them in the block's own order (the within_group index repair_key recorded), and rewrites the block's rows in _tbl to carry the new tokens.
| _tbl | the tracked table holding the block's rows |
| old_key | the block's key gate – the shared child of its rows' mulinput tokens, as reported by "get_children(provsql)[1]" |
| probs | one probability per row of the block, in block order; NULL leaves them unwritten, so the block evaluates at the uniform weight again |
| UUID provsql.replace_input | ( | REGCLASS | _tbl, |
| UUID | old, | ||
| DOUBLE PRECISION | p ) |
Replace a tracked row's input gate, rewriting the row.
The procedural form of replace_input: it mints the new gate and issues the UPDATE itself, which is what a client with only the token in hand (the Studio inspector, say) needs.
| _tbl | the tracked table holding the row |
| old | the token the row carries |
| p | the new probability, in [0,1] |
| UUID provsql.replace_input | ( | UUID | old, |
| DOUBLE PRECISION | p ) |
Mint a replacement input gate carrying a different probability.
Probabilities are written once, so a tuple's probability is changed the way its data is: by rewriting the row. This mints a fresh input gate with probability p and returns it, for the row's provsql column to carry:
The update is an ordinary heap write, so it has MVCC isolation, WAL, replication and pg_dump behind it, and the new gate and its probability roll back with it. The old gate and everything derived from it stay as they were: re-running a query over the base table builds new derived gates over the new token and so sees the new probability, while a table materialised earlier keeps the old tokens and the old probability – a derived table reflects the base tables as they were when it was built, the same rule a DELETE under provsql.update_provenance already follows.
| old | the token being replaced; must name an input gate |
| p | the new probability, in [0,1] |
| VOID provsql.set_prob | ( | UUID | token, |
| DOUBLE PRECISION | p ) |
Write the probability of an input gate, once.
A probability is a fact appended to the circuit, like the gate it belongs to: it can be written on a gate that has none, written again with the identical value (so setup scripts and notebook cells stay re-runnable), and is otherwise refused. A write made by a transaction that rolls back is cleared, leaving the circuit as the transaction found it.
To give a tuple a different probability, mint a fresh input gate with provsql.replace_input and store it in the row's provsql column. The base table's token column is the place of truth, the same rule the data-modification triggers already follow, and the circuit keeps the old gate and everything derived from it.
| token | UUID of the input gate |
| p | probability value in [0,1] |
| UUID provsql.strip_annotations | ( | UUID | token | ) |
Peel every transparent gate_annotation wrapper off token, returning the first non-annotation gate underneath.
The dual of annotate, for consumers keyed to gate identity rather than gate value: a provenance mapping matches input-gate UUIDs, and the reachability edge classifier matches token shapes, so both must see the wrapped gate, not the wrapper (e.g. the inversion-free certificate / order marker the planner attaches to a certified query's row roots). Identity on a token with no annotation wrapper; NULL on NULL.
| BOOLEAN provsql.UUID_op_boolean | ( | UUID | left, |
| BOOLEAN | right ) |
| BOOLEAN provsql.UUID_op_UUID | ( | UUID | left, |
| UUID | right ) |
Binary | : value-level conditioning, "target | evidence".
Carrier-parametric in its left operand; the UUID form builds the terminal gate_conditioned via cond. Does not collide with core PostgreSQL's INTEGER bitwise | (different argument types).