 |
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. | |
| 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 per-relation provenance metadata. | |
| 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). | |
| 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) |
| Registry of maintained 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 EVENT TRIGGER IF EXISTS | provsql.provsql_cleanup_table_info |
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 PROCEDURE 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 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 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, register the mapping so later inserts into oldtbl keep it 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. |
| 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.
| 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. | CREATE TABLE IF NOT EXISTS provsql provsql.provenance_mapping_registry | ( | mapping oid PRIMARY | KEY, |
| source oid NOT | NULL, | ||
| attribute name NOT | NULL ) |
Registry of maintained provenance mappings.
Each row records that mapping table mapping is kept current for the attribute column of the provenance-tracked source table: every genuine insert into source appends (value, provenance) to it (see provenance_guard). 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 | ) |
| 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.
| _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.
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 provsql.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.provsql_cleanup_table_info |