 |
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
|
Functions for enabling, disabling, and configuring provenance tracking on user tables. More...
Functions | |
| TRIGGER | provsql.delete_statement_trigger () |
| Trigger function for DELETE statement provenance tracking. | |
| CREATE TABLE IF NOT EXISTS | provsql.table_info (relid REGCLASS PRIMARY KEY, kind TEXT NOT NULL, block_key int2[] NOT NULL DEFAULT ARRAY[]::int2[], ancestors oid[] NOT NULL DEFAULT ARRAY[]::oid[]) |
| Per-relation provenance metadata used by the safe-query optimisation. | |
| trigger | provsql.table_info_invalidate () |
| Row trigger keeping every backend's metadata cache honest. | |
| VOID | provsql.set_table_info (OID relid, TEXT kind, INT2[] block_key=ARRAY[]::INT2[]) |
| Record per-relation provenance metadata used by the safe-query optimisation. | |
| VOID | provsql.remove_table_info (OID relid) |
Remove a relation's row from provsql.table_info. | |
| RECORD | provsql.get_table_info (OID relid, OUT TEXTkind, INT2[] &block_key) |
| Read per-relation provenance metadata. | |
| VOID | provsql.set_ancestors (OID relid, OID[] ancestors=ARRAY[]::OID[]) |
| Record the base-relation ancestor set of a tracked relation. | |
| VOID | provsql.remove_ancestors (OID relid) |
| Clear the ancestor half of a per-relation RECORD (keeps kind/block_key). | |
| BIGINT | provsql.migrate_table_info () |
Copy per-relation metadata out of the legacy provsql_table_info.mmap file into provsql.table_info. | |
| OID[] | provsql.get_ancestors (OID relid) |
| Read the base-relation ancestor set of a tracked relation. | |
| TRIGGER | provsql.provenance_guard () |
BEFORE INSERT OR UPDATE OF provsql row trigger installed by add_provenance. | |
| VOID | provsql.add_provenance (REGCLASS _tbl) |
| Enable provenance tracking on an existing table. | |
| VOID | provsql.remove_provenance (REGCLASS _tbl) |
| Remove provenance tracking from a table. | |
| VOID | provsql.repair_key (REGCLASS _tbl, TEXT key_att) |
| Set up provenance for a table with duplicate key values. | |
| event_trigger | provsql.cleanup_table_info () |
| Event trigger that purges per-table provenance metadata when a tracked relation is dropped outside of remove_provenance(). | |
| CREATE TABLE IF NOT EXISTS provsql | provsql.provenance_mapping_registry (mapping oid PRIMARY KEY, source oid NOT NULL, attribute name NOT NULL, maintained BOOLEAN NOT NULL DEFAULT false) |
| Registry of provenance mappings. | |
| CREATE INDEX IF NOT EXISTS provenance_mapping_registry_source_idx ON provsql | provsql.provenance_mapping_registry (source) |
| VOID | provsql.create_provenance_mapping (TEXT newtbl, REGCLASS oldtbl, TEXT att, BOOL preserve_case='f', BOOL maintained=false) |
| Create a provenance mapping table from an attribute. | |
Variables | |
| DROP TRIGGER IF EXISTS table_info_invalidate ON | provsql.table_info |
| DROP EVENT TRIGGER IF EXISTS | provsql.provsql_cleanup_table_info |
| ALTER TABLE provsql provenance_mapping_registry ADD COLUMN IF NOT EXISTS maintained BOOLEAN NOT NULL DEFAULT | provsql.false |
Functions for enabling, disabling, and configuring provenance tracking on user tables.
| VOID provsql.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 FUNCTION provsql 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 row in provsql.table_info 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. Both the deletion and the registry cleanup below roll back with the DROP that triggered them.
FUNCTION alias) so the extension installs on PG 10 too.
| VOID provsql.create_provenance_mapping | ( | TEXT | newtbl, |
| REGCLASS | oldtbl, | ||
| TEXT | att, | ||
| BOOL | preserve_case = 'f', | ||
| BOOL | maintained = false ) |
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 |
| maintained | if true, later inserts into oldtbl keep the mapping current, and it stays correct after data modification (deletes/updates rewrite a row's provsql, but the validity stays keyed to the original input token). att must then be a plain column name. When false (the default) the table is a one-off snapshot; either way the mapping is recorded in provenance_mapping_registry, so a row whose input gate is replaced by provsql.replace_input keeps its value in it. |
| TRIGGER provsql.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[] provsql.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 provsql.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.
| BIGINT provsql.migrate_table_info | ( | ) |
Copy per-relation metadata out of the legacy provsql_table_info.mmap file into provsql.table_info.
ProvSQL 1.13.0 moved this metadata from a fifth mmap file to a heap table, so that it follows the transaction that writes it and is carried by pg_dump. This function reads the legacy file, if the database still has one, and inserts every RECORD it holds that the heap table does not already have; it returns the number of rows inserted, and 0 when there is no file to read. Idempotent, and a no-op on a database that never had one.
| TRIGGER provsql.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. The exception is a leaf provsql.replace_input / replace_block minted in this transaction: that one is an independent fresh leaf, so the kind survives and the maintained mappings follow the token to its replacement. | CREATE TABLE IF NOT EXISTS provsql provsql.provenance_mapping_registry | ( | mapping oid PRIMARY | KEY, |
| source oid NOT | NULL, | ||
| attribute name NOT | NULL, | ||
| maintained BOOLEAN NOT NULL DEFAULT | false ) |
Registry of provenance mappings.
Each row records that mapping table mapping was built from the attribute column of the provenance-tracked source table. When maintained, every genuine insert into source also appends (value, provenance) to it, so the mapping stays current; otherwise the mapping is a snapshot and the row is here only so that a token replacement carries the mapping over (see provenance_guard: a leaf minted by replace_input names the same tuple, so its value is copied to the new token in every mapping of the table, maintained or not). Keyed on the mapping table, indexed on the source so the guard can look up a table's mappings cheaply. Entries are removed when either table is dropped (see cleanup_table_info).
| CREATE INDEX IF NOT EXISTS provenance_mapping_registry_source_idx ON provsql provsql.provenance_mapping_registry | ( | source | ) |
| VOID provsql.remove_ancestors | ( | OID | relid | ) |
Clear the ancestor half of a per-relation RECORD (keeps kind/block_key).
No-op when missing.
| VOID provsql.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 provsql.remove_table_info | ( | OID | relid | ) |
Remove a relation's row from provsql.table_info.
No-op when missing.
| VOID provsql.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. The uniform weight is the default a row evaluates at, not a probability written on it, so the usual next step – "SELECT set_prob(provenance(),
p) FROM t" – gives each row its real probability as a first write.
| _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 provsql.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.
Preserves the relation's existing kind / block_key half on update, and silently no-ops when no row 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 provsql.set_table_info | ( | OID | relid, |
| TEXT | kind, | ||
| INT2[] | block_key = ARRAY[]::INT2[] ) |
Record per-relation provenance metadata used by the safe-query optimisation.
Upserts the (relid, kind, block_key) half of the relation's row in provsql.table_info, preserving its ancestors. 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). |
| CREATE TABLE IF NOT EXISTS provsql.table_info | ( | relid REGCLASS PRIMARY | KEY, |
| kind TEXT NOT | NULL, | ||
| block_key int2[] NOT NULL DEFAULT ARRAY[]::int2[] | , | ||
| ancestors oid[] NOT NULL DEFAULT ARRAY[]::oid[] | ) |
Per-relation provenance metadata used by the safe-query optimisation.
One row per relation ProvSQL tracks. relid is stored as REGCLASS so a dump carries the relation's name: OIDs are not stable across databases, and pg_dump / pg_restore resolve a REGCLASS value back to whatever OID the relation has in the target database. kind is one of 'tid' / 'bid' / 'opaque' (see set_table_info); block_key lists the block-key column numbers of a BID relation; ancestors lists the base relations this one's atoms ultimately come from.
Being a heap table, every change follows the transaction that made it: a rolled-back add_provenance leaves no RECORD, a rolled-back DROP TABLE keeps one, and a concurrent session sees a change only once it commits. Marked as a configuration table so pg_dump carries it.
| CREATE TRIGGER table_info_invalidate AFTER INSERT OR UPDATE OR DELETE ON table_info FOR EACH ROW EXECUTE FUNCTION provsql provsql.table_info_invalidate | ( | ) |
Row trigger keeping every backend's metadata cache honest.
Each backend caches the metadata of the relations its queries touch and drops an entry when PostgreSQL invalidates that relation's relcache entry. This trigger issues that invalidation for the relation named by every inserted, updated, or deleted row, so a hand-written UPDATE on the table and the COPY a pg_restore performs are as visible to the caches as the setter functions are.
| ALTER TABLE provsql provenance_mapping_registry ADD COLUMN IF NOT EXISTS maintained BOOLEAN NOT NULL DEFAULT provsql.false |
| DROP EVENT TRIGGER IF EXISTS provsql.provsql_cleanup_table_info |
| DROP TRIGGER IF EXISTS table_info_invalidate ON provsql.table_info |