 |
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
|
Create a new gate in the provenance circuit. More...
Topics | |
| Internal constants | |
| Trigger function for DELETE statement provenance tracking. | |
Functions | |
| TRIGGER | delete_statement_trigger () |
| Trigger function for DELETE statement provenance tracking. | |
| VOID | set_table_info (OID relid, TEXT kind, INT2[] block_key=ARRAY[]::INT2[]) |
| Record per-relation provenance metadata used by the safe-query optimisation. | |
| VOID | remove_table_info (OID relid) |
| Remove per-relation provenance metadata. | |
| RECORD | get_table_info (OID relid, OUT TEXTkind, INT2[] &block_key) |
| Read per-relation provenance metadata. | |
| VOID | set_ancestors (OID relid, OID[] ancestors=ARRAY[]::OID[]) |
| Record the base-relation ancestor set of a tracked relation. | |
| VOID | remove_ancestors (OID relid) |
| Clear the ancestor half of a per-relation RECORD (keeps kind/block_key). | |
| OID[] | get_ancestors (OID relid) |
| Read the base-relation ancestor set of a tracked relation. | |
| TRIGGER | provenance_guard () |
BEFORE INSERT OR UPDATE OF provsql row trigger installed by add_provenance. | |
| VOID | add_provenance (REGCLASS _tbl) |
| Enable provenance tracking on an existing table. | |
| VOID | remove_provenance (REGCLASS _tbl) |
| Remove provenance tracking from a table. | |
| VOID | repair_key (REGCLASS _tbl, TEXT key_att) |
| Set up provenance for a table with duplicate key values. | |
| event_trigger | cleanup_table_info () |
| Event trigger that purges per-table provenance metadata when a tracked relation is dropped outside of remove_provenance(). | |
| VOID | create_provenance_mapping (TEXT newtbl, REGCLASS oldtbl, TEXT att, BOOL preserve_case='f') |
| Create a provenance mapping table from an attribute. | |
| VOID | create_provenance_mapping_view (TEXT newview, REGCLASS oldtbl, TEXT att, BOOL preserve_case=false) |
| Create a view mapping provenance tokens to attribute values. | |
Variables | |
| DROP EVENT TRIGGER IF EXISTS | provsql_cleanup_table_info |
Create a new gate in the provenance circuit.
Functions for enabling, disabling, and configuring provenance tracking on user tables.
| token | UUID identifying the new gate |
| type | gate type (see PROVENANCE_GATE) |
| children | optional array of child gate UUIDs |
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.
Return the children of a provenance gate
Set the probability of an input gate
| token | UUID of the input gate |
| p | probability value in [0,1] |
Get the probability associated with an input gate
Set additional INTEGER values on provenance circuit gate
This function sets two INTEGER values associated to a circuit gate, used in different ways by different gate types:
| token | UUID of the circuit gate |
| info1 | first INTEGER value |
| info2 | second INTEGER value |
Get the INTEGER info values associated with a circuit gate
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.
Wrap token in a Boolean-assumption marker (compatibility name; see provenance_assume).
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.
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.
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).
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.
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.
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).
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.
Placeholder for the prefix "| (predicate)" whole-tuple form.
Lets the whole-tuple conditioning event be a natural Boolean predicate (e.g. "SELECT a, | (sensor > 3) FROM readings") instead of a hand-built gate. Never executes: the planner converts the Boolean operand into a condition gate and emits given, which the rewriter then strips and folds into each output row's provenance.
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).
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.
Set extra TEXT information on provenance circuit gate
This function sets TEXT-encoded data associated to a circuit gate, used in different ways by different gate types:
| token | UUID of the circuit gate |
| data | TEXT-encoded information |
Get the TEXT-encoded extra data associated with a circuit gate
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.
Functions for enabling, disabling, and configuring provenance tracking on user tables.
| VOID update_provenance::add_provenance | ( | REGCLASS | _tbl | ) |
Enable provenance tracking on an existing table.
Adds a provsql UUID column to the table, an index for fast UUID-keyed lookups, and a BEFORE INSERT/UPDATE row trigger (provenance_guard) that mints a fresh uuid_generate_v4 leaf when the user omits the column on INSERT, or flips the table's metadata to OPAQUE when the user supplies their own value. Input gates for existing rows are created lazily when first referenced by a query.
| _tbl | the table to add provenance tracking to |
| CREATE EVENT TRIGGER provsql_cleanup_table_info ON sql_drop EXECUTE PROCEDURE provsql cleanup_table_info | ( | ) |
Event trigger that purges per-table provenance metadata when a tracked relation is dropped outside of remove_provenance().
EXECUTE PROCEDURE (rather than the PG 11+ EXECUTE
Plain DROP TABLE bypasses remove_provenance() and would otherwise leave a stale entry in the table-info store keyed by a now-recycled OID, with confusing consequences for the safe-query rewriter the next time the OID is reused. This trigger forwards every dropped relation OID to provsql.remove_table_info(), which is a no-op for relations that were not tracked.
FUNCTION alias) so the extension installs on PG 10 too.
| VOID update_provenance::create_provenance_mapping | ( | TEXT | newtbl, |
| REGCLASS | oldtbl, | ||
| TEXT | att, | ||
| BOOL | preserve_case = 'f' ) |
Create a provenance mapping table from an attribute.
Creates a new table mapping provenance tokens to values of a given attribute, for use with semiring evaluation functions. Idempotent: if the mapping table already exists, raises a NOTICE and changes nothing (drop it first to rebuild).
| newtbl | name of the mapping table to create |
| oldtbl | source table with provenance tracking |
| att | attribute whose values populate the mapping |
| preserve_case | if true, quote the table name to preserve case |
| VOID update_provenance::create_provenance_mapping_view | ( | TEXT | newview, |
| REGCLASS | oldtbl, | ||
| TEXT | att, | ||
| BOOL | preserve_case = false ) |
Create a view mapping provenance tokens to attribute values.
Like create_provenance_mapping but creates a view instead of a table, so it always reflects the current state of the source table.
| newview | name of the view to create |
| oldtbl | source table with provenance tracking |
| att | attribute whose values populate the mapping |
| preserve_case | if true, quote the view name to preserve case |
| TRIGGER update_provenance::delete_statement_trigger | ( | ) |
Trigger function for DELETE statement provenance tracking.
Records the deletion and applies monus to provenance tokens of deleted rows. This is the version for PostgreSQL < 14.
| OID[] update_provenance::get_ancestors | ( | OID | relid | ) |
Read the base-relation ancestor set of a tracked relation.
Returns NULL when no ancestor RECORD exists for relid (or the RECORD is empty – both cases make the safe-query rewriter take its conservative refuse path, so they collapse here).
| RECORD update_provenance::get_table_info | ( | OID | relid, |
| OUT TEXT | kind, | ||
| INT2[] & | block_key ) |
Read per-relation provenance metadata.
Returns NULL if no RECORD exists. kind is one of 'tid' / 'bid' / 'opaque'; block_key is the (possibly empty) array of block-key column numbers, only meaningful when kind = 'bid'. Used by the planner-time hierarchy detector to gate the safe-query rewrite.
| TRIGGER update_provenance::provenance_guard | ( | ) |
BEFORE INSERT OR UPDATE OF provsql row trigger installed by add_provenance.
Two jobs:
NEW.provsql with a fresh uuid_generate_v4 leaf when the user did not supply one (a column DEFAULT would not do here: it fires before the trigger sees the row, so we could not tell "user omitted the column" from "user supplied a value").provsql on INSERT, or changes it on UPDATE, flip the table's per-table metadata to OPAQUE. The user is free to write whatever UUIDs they want (cross-table reuse, compound tokens minted via create_gate, ...); the cost is that the safe-query rewriter then refuses to fire on this table, because TID independence can no longer be assumed. | VOID update_provenance::remove_ancestors | ( | OID | relid | ) |
Clear the ancestor half of a per-relation RECORD (keeps kind/block_key).
No-op when missing.
| VOID update_provenance::remove_provenance | ( | REGCLASS | _tbl | ) |
Remove provenance tracking from a table.
Drops the provsql column and associated triggers.
| _tbl | the table to remove provenance tracking from |
| VOID update_provenance::remove_table_info | ( | OID | relid | ) |
| VOID update_provenance::repair_key | ( | REGCLASS | _tbl, |
| TEXT | key_att ) |
Set up provenance for a table with duplicate key values.
When a table has duplicate rows for a given key, this function replaces simple input gates with multivalued input (mulinput) gates that model a uniform distribution over duplicates.
| _tbl | the table to repair |
| key_att | the key attribute(s) as a comma-separated string, or empty string if the whole table is one group |
| VOID update_provenance::set_ancestors | ( | OID | relid, |
| OID[] | ancestors = ARRAY[]::OID[] ) |
Record the base-relation ancestor set of a tracked relation.
Base tables created with add_provenance / repair_key carry {self}; CTAS-derived tables inherit the union of their sources' ancestor sets. The safe-query rewriter consults the registry to enforce that joined FROM entries have disjoint base ancestors before firing the read-once factoring.
The worker preserves the relation's existing kind / block_key half on update; it silently no-ops when no kind RECORD exists for relid (callers should run add_provenance / repair_key first). The ancestor list is capped at 64 entries (clear error if exceeded).
| relid | pg_class OID of the relation. |
| ancestors | Sorted, deduplicated base-relation OIDs. |
| VOID update_provenance::set_table_info | ( | OID | relid, |
| TEXT | kind, | ||
| INT2[] | block_key = ARRAY[]::INT2[] ) |
Record per-relation provenance metadata used by the safe-query optimisation.
Stores a (relid, kind, block_key) RECORD in the persistent mmap-backed table-info store. kind is one of:
'tid' – independent input leaves (post-add_provenance default)'bid' – block-correlated leaves; rows sharing the same value of block_key are mutually exclusive. An empty block_key means the whole table is one block.'opaque' – arbitrary correlations from a derived source (CREATE TABLE AS SELECT, INSERT INTO SELECT, UPDATE under provsql.update_provenance); the safe-query rewriter must bail on these.| relid | pg_class OID of the relation. |
| kind | One of 'tid' / 'bid' / 'opaque'. |
| block_key | Block-key column numbers (only meaningful for 'bid'; ignored otherwise but conventionally passed empty). |
| DROP EVENT TRIGGER IF EXISTS provsql_cleanup_table_info |