19SET search_path
TO provsql;
27CREATE TYPE PROVENANCE_GATE
AS
122CREATE OR REPLACE
FUNCTION create_gate(
124 type PROVENANCE_GATE,
125 children UUID[]
DEFAULT NULL)
127 'provsql',
'create_gate' LANGUAGE C PARALLEL SAFE;
159CREATE OR REPLACE
FUNCTION create_gate(
161 type PROVENANCE_GATE,
167 'provsql',
'create_gate' LANGUAGE C PARALLEL SAFE;
174CREATE OR REPLACE
FUNCTION get_gate_type(
176 RETURNS PROVENANCE_GATE
AS
177 'provsql',
'get_gate_type' LANGUAGE C IMMUTABLE PARALLEL SAFE;
179CREATE OR REPLACE
FUNCTION get_children(
182 'provsql',
'get_children' LANGUAGE C IMMUTABLE PARALLEL SAFE;
202CREATE OR REPLACE
FUNCTION set_prob(
203 token UUID, p
DOUBLE PRECISION)
205 'provsql',
'set_prob' LANGUAGE C PARALLEL RESTRICTED;
215CREATE OR REPLACE
FUNCTION probability_is_set(token UUID)
217 'provsql',
'probability_is_set' LANGUAGE C STABLE PARALLEL SAFE;
228CREATE OR REPLACE
FUNCTION note_fresh_leaf(token UUID)
230 'provsql',
'note_fresh_leaf' LANGUAGE C;
234CREATE OR REPLACE
FUNCTION is_fresh_leaf(token UUID)
236 'provsql',
'is_fresh_leaf' LANGUAGE C VOLATILE;
263CREATE OR REPLACE
FUNCTION replace_input(
old UUID, p
DOUBLE PRECISION)
268 tp provsql.PROVENANCE_GATE;
270 IF old IS NULL OR p
IS NULL THEN
271 RAISE EXCEPTION
'replace_input: neither argument may be NULL';
273 tp := provsql.get_gate_type(
old);
274 IF tp =
'mulinput' THEN
275 RAISE EXCEPTION
'replace_input: % belongs to a repair_key block',
old
276 USING HINT =
'A block''s values share one key gate and their masses '
277 'are meaningful together, so they are replaced together: '
278 'use provsql.replace_block().';
279 ELSIF tp =
'update' THEN
280 RAISE EXCEPTION
'replace_input: % is an update gate',
old
281 USING HINT =
'Use provsql.replace_update() to give a recorded data '
282 'modification a different probability.';
283 ELSIF tp <>
'input' THEN
284 RAISE EXCEPTION
'replace_input: % is a gate of type %, not an input',
old, tp
285 USING HINT =
'Only a leaf carries a probability of its own; a derived '
286 'gate''s is computed from its leaves.';
288 t := public.uuid_generate_v4();
289 PERFORM provsql.create_gate(t,
'input');
290 PERFORM provsql.set_prob(t, p);
291 PERFORM provsql.note_fresh_leaf(t);
308CREATE OR REPLACE
FUNCTION replace_input(
309 _tbl REGCLASS,
old UUID, p
DOUBLE PRECISION)
316 t := provsql.replace_input(
old, p);
317 EXECUTE format(
'UPDATE %s SET provsql = $1 WHERE provsql = $2', _tbl)
319 GET DIAGNOSTICS n = ROW_COUNT;
321 RAISE EXCEPTION
'replace_input: no row of % carries the token %', _tbl,
old;
345CREATE OR REPLACE
FUNCTION replace_block(
346 _tbl REGCLASS, old_key UUID, probs
DOUBLE PRECISION[]
DEFAULT NULL)
357 IF provsql.get_gate_type(old_key) <>
'input' THEN
358 RAISE EXCEPTION
'replace_block: % is not a block key gate', old_key;
365 was_active := coalesce(current_setting(
'provsql.active',
true),
'on');
366 PERFORM set_config(
'provsql.active',
'off',
true);
369 'CREATE TEMP TABLE provsql_replace_block_tmp ON COMMIT DROP AS
370 SELECT t.provsql AS old_token,
371 NULL::UUID AS new_token,
372 (provsql.get_infos(t.provsql)).info1 AS ord
374 WHERE provsql.get_gate_type(t.provsql) = ''mulinput''
375 AND (provsql.get_children(t.provsql))[1] = %L', _tbl, old_key);
377 SELECT count(*)
INTO n
FROM provsql_replace_block_tmp;
379 RAISE EXCEPTION
'replace_block: no row of % belongs to block %', _tbl, old_key;
381 IF probs
IS NOT NULL AND array_length(probs,
1) <> n
THEN
382 RAISE EXCEPTION
'replace_block: block % has % rows but % probabilities were given',
383 old_key, n, array_length(probs,
1);
386 new_key := public.uuid_generate_v4();
387 PERFORM provsql.create_gate(new_key,
'input');
389 FOR r
IN SELECT old_token, ord
FROM provsql_replace_block_tmp
ORDER BY ord LOOP
391 new_tok := public.uuid_generate_v4();
392 PERFORM provsql.create_gate(new_tok,
'mulinput',
ARRAY[new_key], r.ord, n,
NULL);
393 IF probs
IS NOT NULL THEN
394 PERFORM provsql.set_prob(new_tok, probs[i]);
396 PERFORM provsql.note_fresh_leaf(new_tok);
397 UPDATE provsql_replace_block_tmp
SET new_token = new_tok
398 WHERE old_token = r.old_token;
402 'UPDATE %s t SET provsql = b.new_token
403 FROM provsql_replace_block_tmp b WHERE t.provsql = b.old_token', _tbl);
405 DROP TABLE provsql_replace_block_tmp;
406 PERFORM set_config(
'provsql.active', was_active,
true);
410CREATE OR REPLACE
FUNCTION get_prob(
412 RETURNS DOUBLE PRECISION AS
413 'provsql',
'get_prob' LANGUAGE C STABLE PARALLEL SAFE;
417CREATE OR REPLACE
FUNCTION get_infos(
418 token UUID,
OUT info1
INT,
OUT info2
INT)
420 'provsql',
'get_infos' LANGUAGE C STABLE PARALLEL SAFE;
449CREATE OR REPLACE
FUNCTION provenance_assume(token UUID, assumption TEXT)
451 'provsql',
'provenance_assume' LANGUAGE C COST
100 PARALLEL SAFE;
468CREATE OR REPLACE
FUNCTION assume_boolean(token UUID)
RETURNS UUID
AS
469 'provsql',
'assume_boolean' LANGUAGE C COST
100 PARALLEL SAFE;
487CREATE OR REPLACE
FUNCTION annotate(token UUID, extra TEXT)
RETURNS UUID
AS
488 'provsql',
'annotate' LANGUAGE C COST
100 PARALLEL SAFE;
501CREATE OR REPLACE
FUNCTION strip_annotations(token UUID)
RETURNS UUID
AS
503WITH RECURSIVE peel(g)
AS (
506 SELECT (provsql.get_children(p.g))[
1]
FROM peel p
507 WHERE provsql.get_gate_type(p.g) =
'annotation'
509SELECT g
FROM peel
WHERE provsql.get_gate_type(g) <>
'annotation' LIMIT
1;
510$$
LANGUAGE sql STABLE PARALLEL SAFE;
539CREATE OR REPLACE
FUNCTION cond(target UUID, evidence UUID)
RETURNS UUID
AS
549 IF evidence
IS NULL OR evidence = gate_one()
THEN
554 tgt := coalesce(target, gate_one());
556 IF get_gate_type(tgt) =
'conditioned' THEN
560 ch := get_children(tgt);
562 ev := provenance_times(ch[
2], evidence);
563 jnt := provenance_times(ch[
3], evidence);
566 jnt := provenance_times(tgt, evidence);
569 result := public.uuid_generate_v5(uuid_ns_provsql(),
570 concat(
'conditioned', tgt, ev, jnt));
571 PERFORM create_gate(
result,
'conditioned',
ARRAY[tgt, ev, jnt]);
574$$
LANGUAGE plpgsql IMMUTABLE
SET search_path=provsql,pg_temp,public
575 SECURITY DEFINER PARALLEL SAFE;
584CREATE OPERATOR | (LEFTARG=UUID, RIGHTARG=UUID,
PROCEDURE=cond);
594CREATE OR REPLACE
FUNCTION cond_predicate(target UUID, predicate
BOOLEAN)
598 RAISE EXCEPTION
'UUID | (predicate) must be rewritten by the ProvSQL '
599 'planner hook: the right operand must be a Boolean combination of '
600 'random_variable / aggregate comparisons (is provsql.active off?)'
601 USING ERRCODE =
'feature_not_supported',
602 DETAIL =
'provsql-reason: operator-not-rewritten; scope: gap';
604$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
606CREATE OPERATOR | (LEFTARG=UUID, RIGHTARG=
BOOLEAN,
PROCEDURE=cond_predicate);
622CREATE OR REPLACE
FUNCTION predicate_cond_predicate(target
BOOLEAN, evidence
BOOLEAN)
626 RAISE EXCEPTION
'(predicate) | (predicate) must be rewritten by the ProvSQL '
627 'planner hook: both operands must be Boolean combinations of '
628 'random_variable / aggregate comparisons (is provsql.active off?)'
629 USING ERRCODE =
'feature_not_supported',
630 DETAIL =
'provsql-reason: operator-not-rewritten; scope: gap';
632$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
634CREATE OPERATOR | (LEFTARG=
BOOLEAN, RIGHTARG=
BOOLEAN,
PROCEDURE=predicate_cond_predicate);
648CREATE OR REPLACE
FUNCTION regular_indicator(cond
BOOLEAN)
RETURNS UUID
AS
650 SELECT CASE WHEN cond
THEN provsql.gate_one()
ELSE provsql.gate_zero()
END;
651$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
681CREATE OR REPLACE
FUNCTION given(evidence UUID)
RETURNS UUID
AS
684 RETURN provsql.evidence_as_observation(evidence);
686$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
687 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
697CREATE OPERATOR | (RIGHTARG=UUID,
PROCEDURE=given);
715CREATE OR REPLACE
FUNCTION given(predicate
BOOLEAN)
RETURNS UUID
AS
718 RAISE EXCEPTION
'given(predicate) / prefix | (predicate) must be rewritten '
719 'by the ProvSQL planner hook: the operand must be a Boolean combination '
720 'of random_variable / aggregate comparisons (is provsql.active off?)'
721 USING ERRCODE =
'feature_not_supported',
722 DETAIL =
'provsql-reason: operator-not-rewritten; scope: gap';
724$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
726CREATE OPERATOR | (RIGHTARG=
BOOLEAN,
PROCEDURE=given);
758CREATE OR REPLACE
FUNCTION provenance_not(event UUID)
RETURNS UUID
AS
760 SELECT provsql.provenance_monus(provsql.gate_one(), event);
761$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
762 SET search_path=provsql,pg_temp,public;
771CREATE OPERATOR ! (RIGHTARG=UUID,
PROCEDURE=provenance_not);
788CREATE OR REPLACE
FUNCTION inversion_free_key(root TEXT, sec TEXT, factor
INT)
790 'provsql',
'inversion_free_key' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
793CREATE OR REPLACE
FUNCTION get_extra(token UUID)
795 'provsql',
'get_extra' LANGUAGE C STABLE PARALLEL SAFE
RETURNS NULL ON NULL INPUT;
804CREATE OR REPLACE
FUNCTION get_nb_gates()
RETURNS BIGINT AS
805 'provsql',
'get_nb_gates' LANGUAGE C PARALLEL SAFE;
829CREATE OR REPLACE
FUNCTION check_store(
830 OUT unclean_shutdown
BOOLEAN,
832 OUT nb_tokens
BIGINT,
833 OUT next_index
BIGINT,
834 OUT dangling_indices
BIGINT,
835 OUT unreferenced
BIGINT,
836 OUT bad_wires
BIGINT,
837 OUT bad_extra
BIGINT)
839 'provsql',
'check_store' LANGUAGE C;
881CREATE OR REPLACE
FUNCTION circuit_cleanup(
882 dry_run
BOOLEAN DEFAULT false,
883 OUT gates_before
BIGINT,
884 OUT gates_after
BIGINT,
885 OUT wires_before
BIGINT,
886 OUT wires_after
BIGINT,
887 OUT extra_bytes_before
BIGINT,
888 OUT extra_bytes_after
BIGINT)
890 'provsql',
'circuit_cleanup' LANGUAGE C;
907CREATE OR REPLACE
FUNCTION delete_statement_trigger()
917 delete_token := public.uuid_generate_v4();
919 PERFORM create_gate(delete_token,
'input');
923 FROM pg_stat_activity
924 WHERE pid = pg_backend_pid();
926 INSERT INTO delete_provenance (delete_token, query, deleted_by, deleted_at)
927 VALUES (delete_token, query_text,
current_user,
CURRENT_TIMESTAMP);
929 EXECUTE format(
'INSERT INTO %I.%I SELECT * FROM OLD_TABLE;', TG_TABLE_SCHEMA, TG_TABLE_NAME);
931 FOR r
IN (
SELECT *
FROM OLD_TABLE) LOOP
932 old_token := r.provsql;
933 new_token := provenance_monus(old_token, delete_token);
935 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2;', TG_TABLE_SCHEMA, TG_TABLE_NAME)
936 USING new_token, old_token;
941$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp SECURITY DEFINER;
963CREATE TABLE IF NOT EXISTS table_info(
964 relid REGCLASS
PRIMARY KEY,
966 block_key int2[]
NOT NULL DEFAULT ARRAY[]::int2[],
967 ancestors oid[]
NOT NULL DEFAULT ARRAY[]::oid[]
969SELECT pg_catalog.pg_extension_config_dump(
'table_info',
'');
981CREATE OR REPLACE
FUNCTION table_info_invalidate()
983 'provsql',
'provsql_table_info_invalidate' LANGUAGE C;
985DROP TRIGGER IF EXISTS table_info_invalidate
ON table_info;
986CREATE TRIGGER table_info_invalidate
987 AFTER
INSERT OR UPDATE OR DELETE ON table_info
988 FOR EACH ROW EXECUTE FUNCTION provsql.table_info_invalidate();
1012CREATE OR REPLACE
FUNCTION set_table_info(
1013 relid OID, kind TEXT, block_key INT2[]
DEFAULT ARRAY[]::INT2[])
1015 'provsql',
'set_table_info' LANGUAGE C SECURITY DEFINER;
1019CREATE OR REPLACE
FUNCTION remove_table_info(relid OID)
1021 'provsql',
'remove_table_info' LANGUAGE C SECURITY DEFINER;
1032CREATE OR REPLACE
FUNCTION get_table_info(
1033 relid OID,
OUT kind TEXT,
OUT block_key INT2[])
1035 'provsql',
'get_table_info' LANGUAGE C STABLE PARALLEL SAFE;
1054CREATE OR REPLACE
FUNCTION set_ancestors(
1055 relid OID, ancestors OID[]
DEFAULT ARRAY[]::OID[])
1057 'provsql',
'set_ancestors' LANGUAGE C SECURITY DEFINER;
1061CREATE OR REPLACE
FUNCTION remove_ancestors(relid OID)
1063 'provsql',
'remove_ancestors' LANGUAGE C SECURITY DEFINER;
1077CREATE OR REPLACE
FUNCTION migrate_table_info()
1079 'provsql',
'migrate_table_info' LANGUAGE C SECURITY DEFINER;
1088CREATE OR REPLACE
FUNCTION get_ancestors(relid OID)
1090 'provsql',
'get_ancestors' LANGUAGE C STABLE PARALLEL SAFE;
1114CREATE OR REPLACE
FUNCTION provenance_guard()
1115 RETURNS TRIGGER AS $$
1119 IF TG_OP =
'INSERT' THEN
1120 IF NEW.provsql
IS NULL THEN
1128 NEW.provsql := public.uuid_generate_v4();
1129 FOR _m
IN SELECT mapping, attribute
1130 FROM provsql.provenance_mapping_registry
1131 WHERE source = TG_RELID
AND maintained
1134 'INSERT INTO %s(value, provenance) SELECT ($1).%I, $2',
1135 _m.mapping::REGCLASS, _m.attribute)
1136 USING NEW,
NEW.provsql;
1139 PERFORM provsql.set_table_info(TG_RELID,
'opaque');
1141 ELSIF TG_OP =
'UPDATE' THEN
1142 IF NEW.provsql
IS DISTINCT FROM OLD.provsql
THEN
1143 IF provsql.is_fresh_leaf(
NEW.provsql)
THEN
1149 FOR _m
IN SELECT mapping, attribute
1150 FROM provsql.provenance_mapping_registry
WHERE source = TG_RELID
1153 'INSERT INTO %1$s(value, provenance) '
1154 'SELECT value, $2 FROM %1$s WHERE provenance = $1',
1155 _m.mapping::REGCLASS)
1156 USING OLD.provsql,
NEW.provsql;
1159 PERFORM provsql.set_table_info(TG_RELID,
'opaque');
1165$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp,public
1181CREATE OR REPLACE
FUNCTION add_provenance(_tbl REGCLASS)
1189 SELECT 1 FROM pg_attribute
1190 WHERE attrelid = _tbl
AND attname =
'provsql' AND NOT attisdropped
1192 RAISE NOTICE
'table % already has provenance tracking', _tbl;
1201 EXECUTE format(
'ALTER TABLE %s ADD COLUMN provsql UUID', _tbl);
1203 'UPDATE %s SET provsql = public.uuid_generate_v4() WHERE provsql IS NULL',
1205 EXECUTE format(
'CREATE INDEX ON %s(provsql)', _tbl);
1207 'CREATE TRIGGER provenance_guard BEFORE INSERT OR UPDATE OF provsql '
1208 'ON %s FOR EACH ROW EXECUTE FUNCTION provsql.provenance_guard()',
1210 PERFORM provsql.set_table_info(_tbl::oid,
'tid');
1215 PERFORM provsql.set_ancestors(_tbl::oid,
ARRAY[_tbl::oid]);
1217$$
LANGUAGE plpgsql SECURITY DEFINER;
1226CREATE OR REPLACE
FUNCTION remove_provenance(_tbl REGCLASS)
1231 PERFORM provsql.remove_table_info(_tbl::oid);
1237 SELECT 1 FROM pg_attribute
1238 WHERE attrelid = _tbl
AND attname =
'provsql' AND NOT attisdropped
1240 RAISE NOTICE
'table % does not have provenance tracking', _tbl;
1247 EXECUTE format(
'DROP TRIGGER provenance_guard on %s', _tbl);
1248 EXCEPTION
WHEN undefined_object
THEN
1250 EXECUTE format(
'ALTER TABLE %s DROP COLUMN provsql', _tbl);
1252 EXECUTE format(
'DROP TRIGGER add_gate on %s', _tbl);
1253 EXCEPTION
WHEN undefined_object
THEN
1256 EXECUTE format(
'DROP TRIGGER insert_statement on %s', _tbl);
1257 EXECUTE format(
'DROP TRIGGER update_statement on %s', _tbl);
1258 EXECUTE format(
'DROP TRIGGER delete_statement on %s', _tbl);
1259 EXCEPTION
WHEN undefined_object
THEN
1278CREATE OR REPLACE
FUNCTION repair_key(_tbl REGCLASS, key_att TEXT)
1284 block_key_cols INT2[];
1290 IF key_att =
'' THEN
1291 block_key_cols :=
ARRAY[]::INT2[];
1293 SELECT array_agg(a.attnum
ORDER BY t.ord)::INT2[]
1295 FROM unnest(string_to[](key_att,
','))
WITH ORDINALITY
AS t(name, ord)
1297 ON a.attrelid = _tbl
1298 AND a.attname = trim(t.name)
1300 AND NOT a.attisdropped;
1301 IF block_key_cols
IS NULL OR array_length(block_key_cols,
1)
IS NULL THEN
1302 RAISE EXCEPTION
'repair_key: could not resolve key columns from "%"', key_att
1303 USING ERRCODE =
'feature_not_supported',
1304 DETAIL =
'provsql-reason: repair-key-columns; scope: gap';
1306 IF array_length(block_key_cols,
1) >
16 THEN
1307 RAISE EXCEPTION
'repair_key: block key wider than 16 columns is not supported'
1308 USING ERRCODE =
'feature_not_supported',
1309 DETAIL =
'provsql-reason: repair-key-too-wide; scope: gap';
1319 EXECUTE format(
'ALTER TABLE %s ADD COLUMN provsql_temp UUID DEFAULT public.uuid_generate_v4()', _tbl);
1330 IF key_att =
'' THEN
1332 'CREATE TEMP TABLE provsql_repair_key_tmp ON COMMIT DROP AS
1333 SELECT public.uuid_generate_v4() AS provsql_key_token,
1334 COUNT(*) AS provsql_group_size
1336 rows_query := format(
1337 'SELECT t.provsql_temp,
1338 k.provsql_key_token AS key_token,
1339 ROW_NUMBER() OVER (ORDER BY t.ctid) AS within_group,
1340 k.provsql_group_size AS group_size
1341 FROM %s t CROSS JOIN provsql_repair_key_tmp k', _tbl);
1344 'CREATE TEMP TABLE provsql_repair_key_tmp ON COMMIT DROP AS
1346 public.uuid_generate_v4() AS provsql_key_token,
1347 COUNT(*) AS provsql_group_size
1349 GROUP BY %1$s', key_att, _tbl);
1350 rows_query := format(
1351 'SELECT t.provsql_temp,
1352 k.provsql_key_token AS key_token,
1353 ROW_NUMBER() OVER (PARTITION BY k.provsql_key_token
1354 ORDER BY t.ctid) AS within_group,
1355 k.provsql_group_size AS group_size
1357 JOIN provsql_repair_key_tmp k USING (%1$s)', key_att, _tbl);
1361 FOR r
IN SELECT provsql_key_token
FROM provsql_repair_key_tmp LOOP
1362 PERFORM provsql.create_gate(r.provsql_key_token,
'input');
1372 FOR r
IN EXECUTE rows_query LOOP
1373 PERFORM provsql.create_gate(r.provsql_temp,
'mulinput',
ARRAY[r.key_token],
1374 r.within_group::
INT, r.group_size::
INT,
NULL);
1377 DROP TABLE provsql_repair_key_tmp;
1379 EXECUTE format(
'ALTER TABLE %s ALTER COLUMN provsql_temp DROP DEFAULT', _tbl);
1380 EXECUTE format(
'ALTER TABLE %s RENAME COLUMN provsql_temp TO provsql', _tbl);
1381 EXECUTE format(
'CREATE INDEX ON %s(provsql)', _tbl);
1383 'CREATE TRIGGER provenance_guard BEFORE INSERT OR UPDATE OF provsql '
1384 'ON %s FOR EACH ROW EXECUTE FUNCTION provsql.provenance_guard()',
1386 PERFORM provsql.set_table_info(_tbl::oid,
'bid', block_key_cols);
1389 PERFORM provsql.set_ancestors(_tbl::oid,
ARRAY[_tbl::oid]);
1406CREATE OR REPLACE
FUNCTION cleanup_table_info()
1407 RETURNS event_trigger
AS
1413 SELECT objid
FROM pg_event_trigger_dropped_objects()
1414 WHERE object_type
IN (
'table',
'foreign table',
'materialized view')
1416 PERFORM provsql.remove_table_info(r.objid);
1418 DELETE FROM provsql.provenance_mapping_registry
1419 WHERE source = r.objid
OR mapping = r.objid;
1424DROP EVENT
TRIGGER IF EXISTS provsql_cleanup_table_info;
1427CREATE EVENT
TRIGGER provsql_cleanup_table_info
ON sql_drop
1428 EXECUTE FUNCTION provsql.cleanup_table_info();
1445CREATE TABLE IF NOT EXISTS provsql.provenance_mapping_registry(
1446 mapping oid
PRIMARY KEY,
1447 source oid
NOT NULL,
1448 attribute name
NOT NULL,
1449 maintained
BOOLEAN NOT NULL DEFAULT false
1451ALTER TABLE provsql.provenance_mapping_registry
1452 ADD COLUMN IF NOT EXISTS maintained
BOOLEAN NOT NULL DEFAULT false;
1453CREATE INDEX
IF NOT EXISTS provenance_mapping_registry_source_idx
1454 ON provsql.provenance_mapping_registry(source);
1477CREATE OR REPLACE
FUNCTION create_provenance_mapping(
1481 preserve_case BOOL
DEFAULT 'f',
1482 maintained BOOL
DEFAULT false
1490 IF (
CASE WHEN preserve_case
THEN to_regclass(format(
'%I', newtbl))
1491 ELSE to_regclass(newtbl)
END)
IS NOT NULL THEN
1492 RAISE NOTICE
'mapping table % already exists', newtbl;
1499 IF to_regclass(
'pg_temp.tmp_provsql')
IS NOT NULL THEN
1500 DROP TABLE tmp_provsql;
1502 EXECUTE format(
'CREATE TEMP TABLE tmp_provsql ON COMMIT DROP AS TABLE %s', oldtbl);
1503 ALTER TABLE tmp_provsql RENAME provsql
TO provenance;
1507 UPDATE tmp_provsql
SET provenance = provsql.strip_annotations(provenance)
1508 WHERE provsql.get_gate_type(provenance) =
'annotation';
1509 IF preserve_case
THEN
1510 EXECUTE format(
'CREATE TABLE %I AS SELECT %s AS value, provenance FROM tmp_provsql', newtbl, att);
1511 EXECUTE format(
'CREATE INDEX ON %I(provenance)', newtbl);
1513 EXECUTE format(
'CREATE TABLE %s AS SELECT %s AS value, provenance FROM tmp_provsql', newtbl, att);
1514 EXECUTE format(
'CREATE INDEX ON %s(provenance)', newtbl);
1522 INSERT INTO provsql.provenance_mapping_registry(mapping, source, attribute, maintained)
1524 (
CASE WHEN preserve_case
THEN to_regclass(format(
'%I', newtbl))
1525 ELSE to_regclass(newtbl)
END)::oid,
1526 oldtbl::oid, att, maintained)
1527 ON CONFLICT (mapping)
1528 DO
UPDATE SET source = EXCLUDED.source, attribute = EXCLUDED.attribute,
1529 maintained = EXCLUDED.maintained;
1542CREATE OR REPLACE
FUNCTION uuid_ns_provsql()
RETURNS UUID
AS
1545 SELECT '920d4f02-8718-5319-9532-d4ab83a64489'::UUID
1546$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
1549CREATE OR REPLACE
FUNCTION gate_zero()
RETURNS UUID
AS
1551 SELECT public.uuid_generate_v5(provsql.uuid_ns_provsql(),
'zero');
1552$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
1555CREATE OR REPLACE
FUNCTION gate_one()
RETURNS UUID
AS
1557 SELECT public.uuid_generate_v5(provsql.uuid_ns_provsql(),
'one');
1558$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
1569CREATE OR REPLACE
FUNCTION gate_null()
RETURNS UUID
AS
1571 SELECT public.uuid_generate_v5(provsql.uuid_ns_provsql(),
'null');
1572$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
1611CREATE OR REPLACE
FUNCTION provenance_times(VARIADIC tokens UUID[])
1613 'provsql',
'provenance_times' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
1624CREATE OR REPLACE
FUNCTION provenance_monus(token1 UUID, token2 UUID)
1626 'provsql',
'provenance_monus' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
1639CREATE OR REPLACE
FUNCTION provenance_project(token UUID, VARIADIC positions
INT[])
1641 'provsql',
'provenance_project' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
1653CREATE OR REPLACE
FUNCTION provenance_eq(token UUID, pos1
INT, pos2
INT)
1655 'provsql',
'provenance_eq' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
1671CREATE OR REPLACE
FUNCTION provenance_plus(tokens UUID[])
1673 'provsql',
'provenance_plus' LANGUAGE C COST
100 STRICT PARALLEL SAFE IMMUTABLE;
1685CREATE OR REPLACE
FUNCTION planted_scope(work_name TEXT)
1687 'provsql',
'planted_scope' LANGUAGE C STRICT;
1711CREATE OR REPLACE
FUNCTION plant_canonical(
1712 work_name TEXT, kind TEXT, tokens UUID[], target UUID,
1713 info1
INT, info2
INT DEFAULT 0)
1715 'provsql',
'plant_canonical' LANGUAGE C STRICT;
1725CREATE OR REPLACE
FUNCTION fixpoint_token(sys UUID, i
INT)
RETURNS UUID
AS
1727 SELECT public.uuid_generate_v5(provsql.uuid_ns_provsql(),
1728 'fixpoint:' || sys::TEXT ||
':' || i::TEXT);
1729$$
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
1743CREATE OR REPLACE
FUNCTION resolve_fix_system(sys UUID)
RETURNS UUID[]
AS
1744 'provsql',
'resolve_fix_system' LANGUAGE C STRICT VOLATILE;
1794CREATE OR REPLACE
FUNCTION eval_recursive_system(
1802 seed UUID := public.uuid_generate_v4();
1803 active TEXT := current_setting(
'provsql.active');
1814 EXECUTE format(
'DROP TABLE IF EXISTS %I', work_name);
1815 DROP TABLE IF EXISTS provsql_rec_new;
1816 DROP TABLE IF EXISTS provsql_rec_eq;
1818 EXECUTE format(
'CREATE TEMP TABLE %I (%s, provsql UUID) ON COMMIT DROP',
1820 PERFORM provsql.planted_scope(work_name);
1821 EXECUTE format(
'CREATE TEMP TABLE provsql_rec_new (LIKE %I) ON COMMIT DROP',
1827 SELECT string_agg(
'n.' || trim(c),
', '), string_agg(
'w.' || trim(c),
', ')
1829 FROM unnest(string_to[](colnames,
','))
AS c;
1830 joincond := format(
'ROW(%s)::TEXT = ROW(%s)::TEXT', ncols, wcols);
1831 EXECUTE 'CREATE TEMP TABLE provsql_rec_eq (tid tid, i bigint, x UUID) '
1835 PERFORM set_config(
'provsql.active',
'off',
true);
1837 'INSERT INTO %1$I(%2$s) WITH RECURSIVE %1$I(%2$s) AS (%3$s) '
1838 'SELECT %2$s FROM %1$I',
1839 work_name, colnames, body_sql);
1843 PERFORM set_config(
'provsql.active',
'off',
true);
1845 'INSERT INTO provsql_rec_eq '
1846 'SELECT ctid, %2$s + i, public.uuid_generate_v5(%1$L, ''x'' || (%2$s + i)) '
1847 'FROM (SELECT ctid, row_number() OVER () AS i FROM %3$I '
1848 ' WHERE provsql IS NULL) t',
1849 seed, known, work_name);
1851 'UPDATE %I w SET provsql = e.x FROM provsql_rec_eq e '
1852 'WHERE w.ctid = e.tid AND w.provsql IS NULL',
1854 PERFORM provsql.create_gate(e.x,
'fixvar')
1855 FROM provsql_rec_eq e
WHERE e.i > known;
1856 SELECT coalesce(max(i),
0)
INTO known
FROM provsql_rec_eq;
1857 PERFORM set_config(
'provsql.active', active,
true);
1860 EXECUTE 'TRUNCATE provsql_rec_new';
1861 EXECUTE format(
'INSERT INTO provsql_rec_new(%s) %s', colnames, body_sql);
1870 PERFORM set_config(
'provsql.active',
'off',
true);
1872 'INSERT INTO %1$I(%2$s) SELECT %3$s FROM provsql_rec_new n '
1873 'WHERE NOT EXISTS (SELECT 1 FROM %1$I w WHERE %4$s)',
1874 work_name, colnames, ncols, joincond);
1875 GET DIAGNOSTICS added = ROW_COUNT;
1876 EXIT
WHEN added =
0;
1877 rounds := rounds +
1;
1878 IF rounds >
100000 THEN
1879 RAISE EXCEPTION
'ProvSQL: the rows this recursion derives in other '
1880 'worlds than the database as it is do not stop '
1887 'SELECT array_agg(e.x ORDER BY e.i), '
1888 'array_agg(coalesce(n.provsql, provsql.gate_zero()) ORDER BY e.i) '
1889 'FROM provsql_rec_eq e JOIN %I w ON w.provsql = e.x '
1890 'LEFT JOIN provsql_rec_new n ON %s',
1891 work_name, joincond)
INTO xs, fs;
1892 IF xs
IS NOT NULL THEN
1893 sys := public.uuid_generate_v5(seed,
'system');
1894 PERFORM provsql.create_gate(sys,
'fixsystem', xs || fs);
1901 'UPDATE %I w SET provsql = t.tok '
1902 'FROM provsql_rec_eq e '
1903 'JOIN unnest(provsql.resolve_fix_system(%L)) WITH ORDINALITY AS t(tok, i) '
1905 'WHERE w.provsql = e.x',
1908 PERFORM set_config(
'provsql.active', active,
true);
1910$$
LANGUAGE plpgsql
SET client_min_messages = warning;
1938CREATE OR REPLACE
FUNCTION eval_recursive_all(
1945 max_iter
INT DEFAULT 1000)
1952 EXECUTE format(
'DROP TABLE IF EXISTS %I', work_name);
1953 EXECUTE format(
'DROP TABLE IF EXISTS %I', all_name);
1954 DROP TABLE IF EXISTS provsql_rec_new;
1956 EXECUTE format(
'CREATE TEMP TABLE %I (%s, provsql UUID) ON COMMIT DROP',
1958 EXECUTE format(
'CREATE TEMP TABLE %I (LIKE %I) ON COMMIT DROP',
1959 all_name, work_name);
1960 EXECUTE format(
'CREATE TEMP TABLE provsql_rec_new (LIKE %I) ON COMMIT DROP',
1962 PERFORM provsql.planted_scope(all_name);
1968 EXECUTE format(
'INSERT INTO provsql_rec_new(%s) %s', colnames, q0_sql);
1969 GET DIAGNOSTICS new_count = ROW_COUNT;
1970 UPDATE provsql_rec_new
SET provsql = provsql.gate_one()
WHERE provsql
IS NULL;
1973 EXIT
WHEN new_count =
0;
1977 EXECUTE format(
'INSERT INTO %1$I(%2$s) SELECT %2$s FROM provsql_rec_new',
1978 all_name, colnames);
1981 EXECUTE format(
'TRUNCATE %I', work_name);
1982 EXECUTE format(
'INSERT INTO %1$I(%2$s) SELECT %2$s FROM provsql_rec_new',
1983 work_name, colnames);
1986 IF iters > max_iter
THEN
1991 RAISE EXCEPTION
'ProvSQL: the rounds of this UNION ALL recursion do not '
1992 'end (after % of them): its answer is the rows of every '
1993 'round, which SQL itself does not reach either on such '
1995 USING ERRCODE =
'feature_not_supported',
1996 DETAIL =
'provsql-reason: recursion-does-not-end; scope: deliberate';
1999 EXECUTE format(
'TRUNCATE provsql_rec_new');
2000 EXECUTE format(
'INSERT INTO provsql_rec_new(%s) %s', colnames, q1_sql);
2001 GET DIAGNOSTICS new_count = ROW_COUNT;
2002 UPDATE provsql_rec_new
SET provsql = provsql.gate_one()
WHERE provsql
IS NULL;
2005$$
LANGUAGE plpgsql
SET client_min_messages = warning;
2017CREATE OR REPLACE
FUNCTION provenance_cmp(
2023 'provsql',
'provenance_cmp' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
2035CREATE FUNCTION cmp_surviving_factors(tokens UUID[], cmp UUID)
2037 'provsql',
'cmp_surviving_factors' LANGUAGE C PARALLEL SAFE STABLE;
2047CREATE OR REPLACE
FUNCTION provenance_cmp_times(cmp UUID, tokens UUID[])
2053 kept := provsql.cmp_surviving_factors(tokens, cmp);
2054 IF kept
IS NULL OR array_length(kept,
1)
IS NULL THEN
2057 RETURN provsql.provenance_times(VARIADIC kept || cmp);
2059$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
2080CREATE OR REPLACE
FUNCTION provenance_arith(
2085 'provsql',
'provenance_arith' LANGUAGE C COST
100 STRICT PARALLEL SAFE IMMUTABLE;
2103CREATE OR REPLACE
FUNCTION provenance_case(
2111 IF array_length(children,
1)
IS NULL OR array_length(children,
1) %
2 =
0 THEN
2112 RAISE EXCEPTION
'provenance_case expects an odd number of children '
2113 '(guard/value pairs followed by a default), got %',
2114 coalesce(array_length(children,
1),
0);
2116 case_token := public.uuid_generate_v5(
2118 concat(
'case', children::TEXT)
2120 PERFORM create_gate(case_token,
'case', children);
2124 SET search_path=provsql,pg_temp,public
2149CREATE OR REPLACE
FUNCTION provenance_evaluate_compiled(
2151 token2value REGCLASS,
2153 element_one ANYELEMENT)
2154RETURNS ANYELEMENT
AS
2155 'provsql',
'provenance_evaluate_compiled' LANGUAGE C PARALLEL SAFE STABLE;
2174CREATE OR REPLACE
FUNCTION provenance_evaluate(
2176 token2value REGCLASS,
2177 element_one ANYELEMENT,
2179 plus_function REGPROC,
2180 times_function REGPROC,
2181 monus_function REGPROC,
2182 delta_function REGPROC)
2183 RETURNS ANYELEMENT
AS
2186 gate_type PROVENANCE_GATE;
2187 result ALIAS
FOR $
0;
2193 SELECT get_gate_type(token)
INTO gate_type;
2195 IF gate_type
IS NULL THEN
2198 ELSIF gate_type =
'input' THEN
2199 EXECUTE format(
'SELECT value FROM %s WHERE provenance=%L', token2value, token)
2201 IF result IS NULL THEN
2202 result := element_one;
2204 ELSIF gate_type =
'mulinput' THEN
2205 SELECT concat(
'{',(get_children(token))[
1]::TEXT,
'=',(get_infos(token)).info1,
'}')
2207 ELSIF gate_type=
'update' THEN
2208 EXECUTE format(
'SELECT value FROM %s WHERE provenance=%L',token2value,token)
INTO result;
2209 IF result IS NULL THEN
2210 result:=element_one;
2212 ELSIF gate_type =
'plus' THEN
2213 EXECUTE format(
'SELECT %s(provsql.provenance_evaluate(t,%L,%L::%s,%L,%L,%L,%L,%L)) FROM unnest(get_children(%L)) AS t',
2214 plus_function, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function, token)
2217 ELSIF gate_type =
'times' THEN
2218 EXECUTE format(
'SELECT %s(provsql.provenance_evaluate(t,%L,%L::%s,%L,%L,%L,%L,%L)) FROM unnest(get_children(%L)) AS t',
2219 times_function, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function, token)
2222 ELSIF gate_type =
'monus' THEN
2223 IF monus_function
IS NULL THEN
2224 RAISE EXCEPTION
USING MESSAGE=
'Provenance with negation evaluated over a semiring without monus function',
2225 DETAIL =
'provsql-reason: semiring-no-monus; scope: deliberate';
2227 EXECUTE format(
'SELECT %s(a1,a2) FROM (SELECT provsql.provenance_evaluate(c[1],%L,%L::%s,%L,%L,%L,%L,%L) AS a1, ' ||
2228 'provsql.provenance_evaluate(c[2],%L,%L::%s,%L,%L,%L,%L,%L) AS a2 FROM get_children(%L) c) tmp',
2229 monus_function, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function,
2230 token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function, token)
2234 ELSIF gate_type =
'eq' THEN
2235 EXECUTE format(
'SELECT provsql.provenance_evaluate((get_children(%L))[1],%L,%L::%s,%L,%L,%L,%L,%L)',
2236 token, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function)
2257 ELSIF gate_type =
'delta' THEN
2258 IF delta_function
IS NULL THEN
2259 RAISE EXCEPTION
USING MESSAGE=
'Provenance with aggregation evaluated over a semiring without delta function',
2260 DETAIL =
'provsql-reason: semiring-no-delta; scope: deliberate';
2262 EXECUTE format(
'SELECT %I(a) FROM (SELECT provsql.provenance_evaluate((get_children(%L))[1],%L,%L::%s,%L,%L,%L,%L,%L) AS a) tmp',
2263 delta_function, token, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function)
2267 ELSIF gate_type =
'zero' THEN
2268 EXECUTE format(
'SELECT %I(a) FROM (SELECT %L::%I AS a WHERE FALSE) temp', plus_function, element_one, value_type)
2271 ELSIF gate_type =
'one' THEN
2272 EXECUTE format(
'SELECT %L::%I', element_one, value_type)
2275 ELSIF gate_type =
'project' THEN
2276 EXECUTE format(
'SELECT provsql.provenance_evaluate((get_children(%L))[1],%L,%L::%s,%L,%L,%L,%L,%L)',
2277 token, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function)
2280 ELSIF gate_type =
'annotation' THEN
2284 EXECUTE format(
'SELECT provsql.provenance_evaluate((get_children(%L))[1],%L,%L::%s,%L,%L,%L,%L,%L)',
2285 token, token2value, element_one, value_type, value_type, plus_function, times_function, monus_function, delta_function)
2289 RAISE EXCEPTION
USING MESSAGE=
'provenance_evaluate cannot be called on formulas using ' || gate_type ||
' gates; use compiled semirings instead',
2290 DETAIL =
'provsql-reason: evaluate-gate-kind; scope: gap';
2295$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
2312CREATE OR REPLACE
FUNCTION provenance_evaluate(
2314 token2value REGCLASS,
2315 element_one ANYELEMENT,
2316 plus_function REGPROC,
2317 times_function REGPROC,
2318 monus_function REGPROC =
NULL,
2319 delta_function REGPROC =
NULL)
2320 RETURNS ANYELEMENT
AS
2321 'provsql',
'provenance_evaluate' LANGUAGE C STABLE;
2332CREATE TYPE GATE_WITH_DESC
AS (f UUID, t UUID, gate_type PROVENANCE_GATE, desc_str
CHARACTER VARYING, infos
INTEGER[], extra TEXT);
2344CREATE OR REPLACE
FUNCTION sub_circuit_with_desc(
2346 token2desc REGCLASS)
RETURNS SETOF GATE_WITH_DESC
AS
2349 RETURN QUERY
EXECUTE
2350 'WITH RECURSIVE transitive_closure(f,t,gate_type) AS (
2351 SELECT $1,t,provsql.get_gate_type($1) FROM unnest(provsql.get_children($1)) AS t
2353 SELECT p1.t,u,provsql.get_gate_type(p1.t) FROM transitive_closure p1, unnest(provsql.get_children(p1.t)) AS u)
2354 SELECT *, ARRAY[(get_infos(f)).info1, (get_infos(f)).info2], get_extra(f) FROM (
2355 SELECT f::UUID,t::UUID,gate_type,NULL FROM transitive_closure
2357 SELECT p2.provenance::UUID as f, NULL::UUID, ''input'', CAST (p2.value AS varchar) FROM transitive_closure p1 JOIN ' || token2desc ||
' AS p2
2360 SELECT provenance::UUID as f, NULL::UUID, ''input'', CAST (value AS varchar) FROM ' || token2desc ||
' WHERE provenance=$1
2365$$
LANGUAGE plpgsql STABLE PARALLEL RESTRICTED;
2377CREATE OR REPLACE
FUNCTION identify_token(
2378 token UUID,
OUT table_name REGCLASS,
OUT nb_columns
INTEGER)
AS
2387 SELECT pg_namespace.nspname, relname, attrelid,
2388 (
SELECT count(*)
FROM pg_attribute a2
WHERE a2.attrelid=a1.attrelid
AND attnum>
0 AND atttypid<>
0)-1 c
2389 FROM pg_attribute a1
JOIN pg_type
ON atttypid=pg_type.oid
2390 JOIN pg_class
ON attrelid=pg_class.oid
2391 JOIN pg_namespace
ON relnamespace=pg_namespace.oid
2392 WHERE typname=
'UUID' AND relkind=
'r'
2393 AND nspname<>
'provsql'
2394 AND attname=
'provsql'
2403 EXECUTE format(
'LOCK TABLE %I.%I IN ACCESS SHARE MODE NOWAIT',
2404 t.nspname, t.relname);
2405 EXECUTE format(
'SELECT provsql FROM %I.%I WHERE provsql=%L LIMIT 1',
2406 t.nspname, t.relname, token)
INTO found;
2407 EXCEPTION
WHEN undefined_table
OR undefined_column
OR lock_not_available
THEN
2410 IF found
IS NOT NULL THEN
2411 table_name:=t.attrelid::REGCLASS;
2417$$
LANGUAGE plpgsql STRICT;
2425CREATE OR REPLACE
FUNCTION sub_circuit_for_where(token UUID)
2426 RETURNS TABLE(f UUID, t UUID, gate_type PROVENANCE_GATE, table_name REGCLASS, nb_columns
INTEGER, infos
INTEGER[], extra TEXT)
AS
2431 WITH RECURSIVE transitive_closure(f,t,idx,gate_type)
AS (
2432 SELECT $
1,t,id,provsql.get_gate_type($
1)
FROM unnest(provsql.get_children($
1))
WITH ORDINALITY
AS a(t,id)
2433 WHERE provsql.get_gate_type($
1) <>
'cmp'
2435 SELECT p1.t,u,id,provsql.get_gate_type(p1.t)
FROM transitive_closure p1,
unnest(provsql.get_children(p1.t))
WITH ORDINALITY
AS a(u, id)
2436 WHERE provsql.get_gate_type(p1.t) <>
'cmp'
2437 )
SELECT f, t, gate_type, table_name, nb_columns,
ARRAY[(get_infos(f)).info1, (get_infos(f)).info2], get_extra(f)
FROM (
2447 SELECT DISTINCT f, t::UUID, idx, gate_type,
NULL::REGCLASS
AS table_name,
NULL::
INTEGER AS nb_columns
FROM transitive_closure
2452 SELECT DISTINCT leaf,
NULL::UUID,
NULL::
INT, leaf_type, i.table_name, i.nb_columns
FROM (
SELECT DISTINCT t
AS leaf, provsql.get_gate_type(t)
AS leaf_type
FROM transitive_closure
WHERE t
NOT IN (
SELECT f
FROM transitive_closure)) leaves
LEFT JOIN LATERAL provsql.identify_token(leaf) i
ON leaf_type =
'input'
2454 SELECT DISTINCT $
1,
NULL::UUID,
NULL::
INT, leaf_type, i.table_name, i.nb_columns
FROM (
SELECT provsql.get_gate_type($
1)
AS leaf_type
WHERE $
1 NOT IN (
SELECT f
FROM transitive_closure)) root
LEFT JOIN LATERAL provsql.identify_token($
1) i
ON leaf_type =
'input'
2489CREATE OR REPLACE
FUNCTION circuit_subgraph(root UUID, max_depth
INT DEFAULT 8)
2490 RETURNS TABLE(node UUID, parent UUID, child_pos
INT, gate_type TEXT, info1 TEXT, info2 TEXT, depth
INT)
AS
2492 WITH RECURSIVE bfs(node, parent, child_pos, depth)
AS (
2493 SELECT root,
NULL::UUID,
NULL::
INT,
0
2495 SELECT c.t, b.node, c.idx::
INT, b.depth +
1
2497 CROSS JOIN LATERAL unnest(provsql.get_children(b.node))
2498 WITH ORDINALITY
AS c(t, idx)
2499 WHERE b.depth < max_depth
2507 SELECT node, MAX(depth)
AS depth
FROM bfs
GROUP BY node
2513 SELECT DISTINCT parent, node
AS child, child_pos
2514 FROM bfs
WHERE parent
IS NOT NULL
2520 provsql.get_gate_type(d.node)::TEXT,
2525 LEFT JOIN edges e
ON e.child = d.node
2526 LEFT JOIN LATERAL provsql.get_infos(d.node) i
ON TRUE
2527 ORDER BY d.depth, d.node, e.parent;
2528$$
LANGUAGE sql STABLE PARALLEL SAFE;
2549CREATE OR REPLACE
FUNCTION simplified_circuit_subgraph(
2550 root UUID, max_depth
INT DEFAULT 8)
RETURNS jsonb
2551 AS 'provsql',
'simplified_circuit_subgraph'
2552 LANGUAGE C STABLE PARALLEL SAFE;
2576CREATE OR REPLACE
FUNCTION rv_histogram(
2577 token UUID, bins
INT DEFAULT 30, prov UUID
DEFAULT gate_one())
2579 AS 'provsql',
'rv_histogram'
2580 LANGUAGE C VOLATILE PARALLEL SAFE;
2607CREATE OR REPLACE
FUNCTION rv_analytical_curves(
2608 token UUID, samples
INT DEFAULT 100, prov UUID
DEFAULT gate_one())
2610 AS 'provsql',
'rv_analytical_curves'
2611 LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
2632CREATE OR REPLACE
FUNCTION rv_sample(
2633 token UUID, n
INTEGER, prov UUID
DEFAULT gate_one())
2634 RETURNS SETOF float8
2635 AS 'provsql',
'rv_sample'
2636 LANGUAGE C VOLATILE PARALLEL SAFE;
2654CREATE OR REPLACE
FUNCTION resolve_input(UUID UUID)
2655 RETURNS TABLE(relation REGCLASS, row_data JSONB)
AS
2666 SELECT c.oid::REGCLASS
AS regc, ns.nspname, c.relname
2668 JOIN pg_class c
ON a.attrelid = c.oid
2669 JOIN pg_namespace ns
ON c.relnamespace = ns.oid
2670 JOIN pg_type ty
ON a.atttypid = ty.oid
2671 WHERE a.attname =
'provsql'
2672 AND ty.typname =
'UUID'
2674 AND ns.nspname <>
'provsql'
2680 EXECUTE format(
'LOCK TABLE %I.%I IN ACCESS SHARE MODE NOWAIT',
2681 t.nspname, t.relname);
2684 'SELECT to_jsonb(t) - ''provsql'', t.provsql FROM %I.%I AS t WHERE provsql = $1',
2685 t.nspname, t.relname)
2692 EXCEPTION
WHEN undefined_table
OR undefined_column
OR lock_not_available
THEN
2697$$
LANGUAGE plpgsql VOLATILE;
2720CREATE TYPE AGG_TOKEN;
2723CREATE OR REPLACE
FUNCTION agg_token_in(CSTRING)
2725 AS 'provsql',
'agg_token_in' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
2743CREATE OR REPLACE
FUNCTION agg_token_out(AGG_TOKEN)
2745 AS 'provsql',
'agg_token_out' LANGUAGE C STABLE STRICT PARALLEL SAFE;
2748CREATE OR REPLACE
FUNCTION agg_token_cast(AGG_TOKEN)
2750 AS 'provsql',
'agg_token_cast' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
2752CREATE TYPE AGG_TOKEN (
2753 internallength =
117,
2754 input = agg_token_in,
2755 output = agg_token_out,
2760CREATE OR REPLACE
FUNCTION agg_token_uuid(aggtok AGG_TOKEN)
2764 RETURN agg_token_cast(aggtok)::UUID;
2766$$
LANGUAGE plpgsql STRICT
SET search_path=provsql,pg_temp,public SECURITY DEFINER IMMUTABLE PARALLEL SAFE;
2769CREATE CAST (AGG_TOKEN
AS UUID)
WITH FUNCTION agg_token_uuid(AGG_TOKEN)
AS IMPLICIT;
2780CREATE OR REPLACE
FUNCTION agg_gate_value_missing(token UUID)
2784 gt provsql.PROVENANCE_GATE := provsql.get_gate_type(token);
2787 IF gt
IN (
'agg',
'arith',
'value')
THEN
2788 RETURN provsql.get_extra(token)
IS NULL;
2789 ELSIF gt =
'semimod' THEN
2790 ch := provsql.get_children(token);
2791 RETURN array_length(ch,
1) =
2
2792 AND provsql.get_extra(ch[
2])
IS NULL;
2793 ELSIF gt =
'conditioned' THEN
2794 ch := provsql.get_children(token);
2795 RETURN array_length(ch,
1) >=
1
2796 AND provsql.agg_gate_value_missing(ch[
1]);
2801$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
2802 SET search_path=provsql,pg_temp,public;
2815CREATE OR REPLACE
FUNCTION agg_gate_value_text(token UUID)
2819 gt provsql.PROVENANCE_GATE := provsql.get_gate_type(token);
2821 IF gt
IN (
'agg',
'arith',
'value')
THEN
2822 RETURN provsql.get_extra(token);
2823 ELSIF gt =
'semimod' THEN
2824 RETURN provsql.agg_gate_value_text((provsql.get_children(token))[
2]);
2825 ELSIF gt =
'conditioned' THEN
2826 RETURN provsql.agg_gate_value_text((provsql.get_children(token))[
1]);
2830$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
2831 SET search_path=provsql,pg_temp,public;
2845CREATE OR REPLACE
FUNCTION agg_guard_holds(token UUID)
2849 gt PROVENANCE_GATE := get_gate_type(token);
2862 ELSIF gt =
'zero' THEN
2864 ELSIF gt
IN (
'times',
'plus')
THEN
2865 SELECT bool_and(h), bool_or(h), bool_or(h
IS NULL)
2866 INTO all_true, any_true, any_null
2867 FROM (
SELECT provsql.agg_guard_holds(c)
AS h
2868 FROM unnest(get_children(token))
AS c)
AS s;
2869 IF gt =
'times' THEN
2872 RETURN CASE WHEN NOT all_true
THEN false
2873 WHEN any_null
THEN NULL
2877 RETURN CASE WHEN any_true
THEN true
2878 WHEN any_null
THEN NULL
2881 ELSIF gt =
'cmp' THEN
2882 ch := get_children(token);
2883 l := agg_gate_value(ch[
1]);
2884 r := agg_gate_value(ch[
2]);
2885 IF l
IS NULL OR r
IS NULL THEN
2892 IF agg_gate_value_missing(ch[
1])
OR agg_gate_value_missing(ch[
2])
THEN
2900 lt := lower(agg_gate_value_text(ch[
1]));
2901 rt := lower(agg_gate_value_text(ch[
2]));
2902 IF lt
IN (
'true',
'false',
't',
'f')
AND
2903 rt
IN (
'true',
'false',
't',
'f')
THEN
2904 SELECT oprname
INTO opname
2905 FROM pg_catalog.pg_operator
WHERE oid = (get_infos(token)).info1;
2906 IF opname =
'=' THEN
2907 RETURN lt::
BOOLEAN = rt::
BOOLEAN;
2908 ELSIF opname =
'<>' THEN
2909 RETURN lt::
BOOLEAN <> rt::
BOOLEAN;
2914 SELECT oprname
INTO opname
2915 FROM pg_catalog.pg_operator
WHERE oid = (get_infos(token)).info1;
2918 WHEN '<=' THEN l <= r
2920 WHEN '<>' THEN l <> r
2921 WHEN '>=' THEN l >= r
2924 ELSIF gt
IN (
'input',
'delta',
'monus',
'project',
'eq',
'mulinput',
2925 'assumed',
'annotation')
THEN
2931 RETURN provsql.plain_truth(token);
2935$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
2936 SET search_path=provsql,pg_temp,public;
2952CREATE OR REPLACE
FUNCTION agg_gate_value(token UUID)
2956 gt PROVENANCE_GATE := get_gate_type(token);
2962 IF gt
IN (
'agg',
'arith',
'value')
THEN
2970 extra := get_extra(token);
2971 IF extra ~
'^\s*([-+]?([0-9]+\.?[0-9]*|\.[0-9]+)([eE][-+]?[0-9]+)?|[Nn][Aa][Nn])\s*$' THEN
2972 RETURN extra::
NUMERIC;
2975 ELSIF gt =
'semimod' THEN
2976 RETURN agg_gate_value((get_children(token))[
2]);
2977 ELSIF gt =
'conditioned' THEN
2978 RETURN agg_gate_value((get_children(token))[
1]);
2979 ELSIF gt =
'case' THEN
2980 ch := get_children(token);
2981 n := array_length(ch,
1);
2982 FOR i
IN 1 .. (n -
1) /
2 LOOP
2983 holds := agg_guard_holds(ch[
2 * i -
1]);
2984 IF holds
IS NULL THEN
2987 RETURN agg_gate_value(ch[
2 * i]);
2990 RETURN agg_gate_value(ch[n]);
2994$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
2995 SET search_path=provsql,pg_temp,public;
3016CREATE OR REPLACE
FUNCTION agg_token_value_text(token UUID)
3022 WHEN provsql.get_gate_type(token) =
'agg'
3023 THEN provsql.get_extra(token) ||
' (*)'
3027 WHEN provsql.get_gate_type(token) =
'arith'
3028 THEN CASE provsql.agg_token_value_type(token)
3029 WHEN 'float8'::REGTYPE::oid
3030 THEN (provsql.get_extra(token)::
NUMERIC::float8)::TEXT ||
' (*)'
3031 WHEN 'float4'::REGTYPE::oid
3032 THEN (provsql.get_extra(token)::
NUMERIC::float4)::TEXT ||
' (*)'
3033 ELSE provsql.get_extra(token) ||
' (*)'
3037 WHEN provsql.get_gate_type(token)
IN (
'case',
'conditioned',
'semimod',
'value')
3038 THEN provsql.agg_gate_value(token)::TEXT ||
' (*)'
3041$$
LANGUAGE sql STABLE STRICT PARALLEL SAFE;
3044CREATE OR REPLACE
FUNCTION agg_token_to_numeric(AGG_TOKEN)
3046 AS 'provsql',
'agg_token_to_numeric' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3049CREATE OR REPLACE
FUNCTION agg_token_to_float8(AGG_TOKEN)
3050 RETURNS double precision
3051 AS 'provsql',
'agg_token_to_float8' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3054CREATE OR REPLACE
FUNCTION agg_token_to_int4(AGG_TOKEN)
3056 AS 'provsql',
'agg_token_to_int4' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3059CREATE OR REPLACE
FUNCTION agg_token_to_int8(AGG_TOKEN)
3061 AS 'provsql',
'agg_token_to_int8' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3064CREATE OR REPLACE
FUNCTION agg_token_to_bool(AGG_TOKEN)
3066 AS 'provsql',
'agg_token_to_bool' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3069CREATE OR REPLACE
FUNCTION agg_token_to_text(AGG_TOKEN)
3071 AS 'provsql',
'agg_token_to_text' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3080CREATE CAST (AGG_TOKEN
AS NUMERIC)
WITH FUNCTION agg_token_to_numeric(AGG_TOKEN)
AS ASSIGNMENT;
3097CREATE OR REPLACE
FUNCTION agg_token_value(AGG_TOKEN)
3099 AS 'provsql',
'agg_token_value' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3104CREATE FUNCTION agg_token_plain_text(AGG_TOKEN)
3106 AS 'provsql',
'agg_token_plain_text' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3113CREATE FUNCTION agg_token_explode_children(token UUID)
3117 IF provsql.get_gate_type(token) <>
'agg' THEN
3118 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3119 MESSAGE =
'ProvSQL: only the result of an aggregate can be exploded '
3121 DETAIL =
'provsql-reason: explode-not-an-aggregate; scope: deliberate';
3123 IF (provsql.get_infos(token)).info1 <>
3124 'provsql.choose(ANYELEMENT)'::regprocedure::oid
THEN
3125 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3126 MESSAGE = format(
'ProvSQL: the result of %s() cannot be exploded into '
3127 'rows, one per value it aggregates: only that of '
3128 'choose() is one of them; compare it in a HAVING '
3129 'clause, or mark it plain() to read its plain value',
3130 (
SELECT proname
FROM pg_catalog.pg_proc
3131 WHERE oid = (provsql.get_infos(token)).info1)),
3132 DETAIL =
'provsql-reason: explode-rows-aggregate-kind; scope: gap';
3134 RETURN provsql.get_children(token);
3136$$
LANGUAGE plpgsql STABLE PARALLEL SAFE;
3153CREATE FUNCTION agg_possible_values(
input ANYELEMENT,
3154 with_null
BOOLEAN DEFAULT false)
3158 max_values CONSTANT
INT :=
1000;
3175 IF pg_typeof(
input) <>
'provsql.AGG_TOKEN'::REGTYPE
THEN
3176 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3177 MESSAGE =
'ProvSQL: only the result of an aggregate can be exploded '
3178 'into one row per value it takes over the possible worlds',
3179 DETAIL =
'provsql-reason: explode-not-an-aggregate; scope: deliberate';
3181 token :=
input::UUID;
3182 IF provsql.get_gate_type(token) =
'case' THEN
3187 arms := provsql.get_children(token);
3188 vals :=
ARRAY[]::TEXT[];
3189 FOR i
IN 1 .. coalesce(array_length(arms,
1),
0) LOOP
3190 CONTINUE WHEN i %
2 =
1 AND i < array_length(arms,
1);
3191 CASE provsql.get_gate_type(arms[i])
3197 IF coalesce(provsql.get_extra(arms[i]),
'')
NOT IN (
'',
'NULL')
THEN
3198 vals := vals || provsql.get_extra(arms[i]);
3200 WHEN 'agg',
'case' THEN
3201 vals := vals || provsql.agg_possible_values(
3202 provsql.agg_token_make(arms[i],
0));
3204 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3205 MESSAGE =
'ProvSQL: an arithmetic expression over aggregate '
3206 'results cannot be exploded into one row per value it '
3207 'takes over the possible worlds',
3208 DETAIL =
'provsql-reason: explode-arithmetic; scope: gap';
3211 vals :=
ARRAY(
SELECT DISTINCT v
FROM unnest(vals)
AS v);
3212 IF array_length(vals,
1) > max_values
THEN
3213 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3214 MESSAGE = format(
'ProvSQL: this expression over aggregate results '
3215 'takes more than %s values over the possible worlds, '
3216 'too many to explode it into one row per value',
3218 HINT =
'mark it plain() to read its plain value',
3219 DETAIL =
'provsql-reason: explode-too-many-values; scope: gap';
3221 RETURN ARRAY(
SELECT v
FROM unnest(vals)
AS v
3222 ORDER BY v::
NUMERIC NULLS LAST);
3224 IF provsql.get_gate_type(token) <>
'agg' THEN
3225 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3226 MESSAGE =
'ProvSQL: an arithmetic expression over aggregate results '
3227 'cannot be exploded into one row per value it takes over '
3228 'the possible worlds',
3229 DETAIL =
'provsql-reason: explode-arithmetic; scope: gap';
3231 SELECT p.proname, s.nspname
INTO fn, ns
3232 FROM pg_catalog.pg_proc p
3233 JOIN pg_catalog.pg_namespace s
ON s.oid = p.pronamespace
3234 WHERE p.oid = (provsql.get_infos(token)).info1;
3236 vals :=
ARRAY(
SELECT provsql.get_extra((provsql.get_children(sm))[
2])
3237 FROM unnest(provsql.get_children(token))
AS sm);
3238 n := coalesce(array_length(vals,
1),
0);
3245 scalar_agg := with_null;
3247 IF (ns, fn) = (
'pg_catalog',
'count')
THEN
3255 counted := (
SELECT count(*)
FROM unnest(vals)
AS v
3256 WHERE v
IS NOT NULL AND v <>
'0');
3257 first :=
CASE WHEN (provsql.get_infos(token)).info2 <
0 OR counted < n
3259 IF counted +
1 - first > max_values
THEN
3260 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3261 MESSAGE = format(
'ProvSQL: the result of %s() aggregates %s rows, '
3262 'so it takes too many values over the possible '
3263 'worlds to explode it into one row per value',
3265 HINT =
'mark it plain() to read its plain value',
3266 DETAIL =
'provsql-reason: explode-too-many-values; scope: gap';
3268 RETURN ARRAY(
SELECT i::TEXT
3269 FROM generate_series(least(first, counted), counted)
AS i);
3272 IF (ns, fn)
NOT IN ((
'pg_catalog',
'min'), (
'pg_catalog',
'max'),
3273 (
'pg_catalog',
'sum'), (
'provsql',
'choose'))
THEN
3274 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3275 MESSAGE = format(
'ProvSQL: the result of %s() cannot be exploded into '
3276 'one row per value it takes over the possible worlds: '
3277 'only count(), min(), max(), sum() and choose() have '
3278 'values that can be enumerated', fn),
3279 HINT =
'compare it in a HAVING clause, or mark it plain() to read its '
3281 DETAIL =
'provsql-reason: explode-aggregate-kind; scope: gap';
3292 IF EXISTS (
SELECT 1 FROM unnest(provsql.get_children(token))
AS sm
3293 WHERE provsql.get_gate_type(sm) =
'semimod'
3294 AND provsql.get_gate_type((provsql.get_children(sm))[
2])
3296 sums :=
ARRAY[]::
NUMERIC[];
3297 vals :=
ARRAY[]::TEXT[];
3298 FOR cv
IN SELECT (provsql.get_children(sm))[
2]
3299 FROM unnest(provsql.get_children(token))
AS sm
3300 WHERE provsql.get_gate_type(sm) =
'semimod' LOOP
3301 cvals :=
CASE WHEN provsql.get_gate_type(cv) =
'value'
3302 THEN ARRAY[provsql.get_extra(cv)]
3303 ELSE provsql.agg_possible_values(
3304 provsql.agg_token_make(cv,
0))
END;
3305 cvals :=
ARRAY(
SELECT v
FROM unnest(cvals)
AS v
3306 WHERE coalesce(v,
'')
NOT IN (
'',
'NULL'));
3307 IF (ns, fn) = (
'pg_catalog',
'sum')
THEN
3308 sums :=
ARRAY(
SELECT DISTINCT s
FROM
3309 (
SELECT unnest(sums)
AS s
3310 UNION ALL SELECT v::
NUMERIC FROM unnest(cvals)
AS v
3311 UNION ALL SELECT a + b::
NUMERIC
3312 FROM unnest(sums)
AS a,
unnest(cvals)
AS b)
3315 vals :=
ARRAY(
SELECT DISTINCT v
FROM
3316 (
SELECT unnest(vals)
AS v
3317 UNION ALL SELECT unnest(cvals))
AS u);
3319 IF coalesce(array_length(sums,
1),
0) > max_values
3320 OR coalesce(array_length(vals,
1),
0) > max_values
THEN
3321 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3322 MESSAGE = format(
'ProvSQL: the result of %s() takes more than %s '
3323 'values over the possible worlds, too many to '
3324 'explode it into one row per value', fn,
3326 HINT =
'mark it plain() to read its plain value',
3327 DETAIL =
'provsql-reason: explode-too-many-values; scope: gap';
3330 IF (ns, fn) = (
'pg_catalog',
'sum')
THEN
3331 RETURN ARRAY(
SELECT s::TEXT
FROM unnest(sums)
AS s
ORDER BY s)
3332 ||
CASE WHEN scalar_agg
THEN ARRAY[
NULL::TEXT]
3333 ELSE ARRAY[]::TEXT[]
END;
3335 RETURN ARRAY(
SELECT v
FROM unnest(vals)
AS v
ORDER BY v)
3336 ||
CASE WHEN scalar_agg
THEN ARRAY[
NULL::TEXT]
3337 ELSE ARRAY[]::TEXT[]
END;
3340 IF EXISTS (
SELECT 1 FROM unnest(vals)
AS v
WHERE v
IS NULL)
THEN
3341 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3342 MESSAGE = format(
'ProvSQL: the result of %s() aggregates a NULL value, '
3343 'so it cannot be exploded into one row per value it '
3344 'takes over the possible worlds: a comparison with '
3345 'NULL does not say that the result is NULL', fn),
3346 DETAIL =
'provsql-reason: explode-null-value; scope: gap';
3349 IF (ns, fn) = (
'pg_catalog',
'sum')
THEN
3357 sums :=
ARRAY[]::
NUMERIC[];
3358 FOREACH one
IN ARRAY ARRAY(
SELECT v::
NUMERIC FROM unnest(vals)
AS v) LOOP
3359 sums :=
ARRAY(
SELECT DISTINCT s
FROM
3360 (
SELECT unnest(sums)
AS s
3361 UNION ALL SELECT one
3362 UNION ALL SELECT unnest(sums) + one)
AS u);
3363 IF array_length(sums,
1) > max_values
THEN
3364 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3365 MESSAGE = format(
'ProvSQL: the result of %s() takes more than %s '
3366 'values over the possible worlds, too many to '
3367 'explode it into one row per value', fn,
3369 HINT =
'mark it plain() to read its plain value',
3370 DETAIL =
'provsql-reason: explode-too-many-values; scope: gap';
3373 RETURN ARRAY(
SELECT s::TEXT
FROM unnest(sums)
AS s
ORDER BY s)
3374 ||
CASE WHEN scalar_agg
THEN ARRAY[
NULL::TEXT]
3375 ELSE ARRAY[]::TEXT[]
END;
3378 IF n > max_values
THEN
3379 RAISE EXCEPTION
USING ERRCODE =
'feature_not_supported',
3380 MESSAGE = format(
'ProvSQL: the result of %s() aggregates %s rows, so '
3381 'it takes too many values over the possible worlds to '
3382 'explode it into one row per value', fn, n),
3383 HINT =
'mark it plain() to read its plain value',
3384 DETAIL =
'provsql-reason: explode-too-many-values; scope: gap';
3389 RETURN ARRAY(
SELECT DISTINCT v
FROM unnest(vals)
AS v
ORDER BY v)
3390 ||
CASE WHEN scalar_agg
THEN ARRAY[
NULL::TEXT]
3391 ELSE ARRAY[]::TEXT[]
END;
3393$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE;
3400CREATE FUNCTION agg_token_frozen_value(AGG_TOKEN)
3402 AS 'provsql',
'agg_token_plain_text' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
3407CREATE OR REPLACE
FUNCTION agg_token_make(tok UUID, val
NUMERIC)
3408 RETURNS AGG_TOKEN
AS
3410 SELECT format(
'( %s , %s )', tok::TEXT, val::TEXT)::provsql.AGG_TOKEN;
3411$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
3412 SET search_path=provsql,pg_temp,public;
3417CREATE OR REPLACE
FUNCTION agg_value_gate(v
NUMERIC)
3421 token UUID := public.uuid_generate_v5(
3422 provsql.uuid_ns_provsql(), concat(
'value', v::TEXT));
3424 PERFORM provsql.create_gate(token,
'value',
NULL,
NULL,
NULL, v::TEXT);
3427$$
LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE
3428 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
3439CREATE OR REPLACE
FUNCTION agg_token_value_type(token UUID)
3442 SELECT CASE provsql.get_gate_type(token)
3443 WHEN 'agg' THEN (i.info2 &
2147483647)::oid
3444 WHEN 'arith' THEN CASE WHEN coalesce(i.info2,
0) =
0
3445 THEN 'NUMERIC'::REGTYPE::oid
ELSE i.info2::oid
END
3446 ELSE 'NUMERIC'::REGTYPE::oid
3448 FROM provsql.get_infos(token) i;
3449$$
LANGUAGE sql STABLE STRICT PARALLEL SAFE
3450 SET search_path=provsql,pg_temp,public;
3468CREATE OR REPLACE
FUNCTION agg_arith_result_type(op
INT, children UUID[])
3474 has_float8
BOOLEAN :=
false;
3475 has_float4
BOOLEAN :=
false;
3476 has_numeric
BOOLEAN :=
false;
3477 widest_int oid :=
NULL;
3479 IF children
IS NULL THEN
3480 RETURN 'NUMERIC'::REGTYPE::oid;
3482 FOREACH c
IN ARRAY children LOOP
3483 t := provsql.agg_token_value_type(c);
3484 IF t =
'float8'::REGTYPE::oid
THEN has_float8 :=
true;
3485 ELSIF t =
'float4'::REGTYPE::oid
THEN has_float4 :=
true;
3486 ELSIF t =
'NUMERIC'::REGTYPE::oid
THEN has_numeric :=
true;
3487 ELSIF t =
'int8'::REGTYPE::oid
THEN widest_int :=
'int8'::REGTYPE::oid;
3488 ELSIF t =
'int4'::REGTYPE::oid
THEN
3489 widest_int := coalesce(nullif(widest_int,
'int2'::REGTYPE::oid),
3490 'int4'::REGTYPE::oid);
3491 ELSIF t =
'int2'::REGTYPE::oid
THEN
3492 widest_int := coalesce(widest_int,
'int2'::REGTYPE::oid);
3496 RETURN 'float8'::REGTYPE::oid;
3499 RETURN 'float4'::REGTYPE::oid;
3502 RETURN coalesce(widest_int,
'int8'::REGTYPE::oid);
3504 IF op =
12 AND array_length(children,
1) =
2 THEN
3505 RETURN 'NUMERIC'::REGTYPE::oid;
3508 RETURN 'float8'::REGTYPE::oid;
3511 RETURN CASE WHEN has_numeric
THEN 'float8'::REGTYPE::oid
3512 ELSE 'float4'::REGTYPE::oid
END;
3515 RETURN 'NUMERIC'::REGTYPE::oid;
3517 RETURN coalesce(widest_int,
'NUMERIC'::REGTYPE::oid);
3519$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
3520 SET search_path=provsql,pg_temp,public;
3534CREATE OR REPLACE
FUNCTION agg_arith_make(op
INT, children UUID[], val
NUMERIC)
3535 RETURNS AGG_TOKEN
AS
3538 token UUID := public.uuid_generate_v5(
3539 provsql.uuid_ns_provsql(), concat(
'arith', op::TEXT, children::TEXT));
3543 IF op
IS NULL OR children
IS NULL THEN
3549 restype := provsql.agg_arith_result_type(op, children);
3550 PERFORM provsql.create_gate(token,
'arith', children, op, restype::
INT,
3555 WHEN val
IS NULL THEN 'NULL'
3556 WHEN restype =
'float8'::REGTYPE::oid
THEN (val::float8)::TEXT
3557 WHEN restype =
'float4'::REGTYPE::oid
THEN (val::float4)::TEXT
3560 RETURN format(
'( %s , %s )', token::TEXT, shown)::provsql.AGG_TOKEN;
3562$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
3563 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
3567CREATE OR REPLACE
FUNCTION agg_token_plus(a AGG_TOKEN, b AGG_TOKEN)
3568 RETURNS AGG_TOKEN
AS
3569$$
SELECT provsql.agg_arith_make(
0,
ARRAY[(a)::UUID, (b)::UUID],
3570 provsql.agg_token_value(a) + provsql.agg_token_value(b)); $$
3571 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3574CREATE OR REPLACE
FUNCTION agg_token_minus(a AGG_TOKEN, b AGG_TOKEN)
3575 RETURNS AGG_TOKEN
AS
3576$$
SELECT provsql.agg_arith_make(
2,
ARRAY[(a)::UUID, (b)::UUID],
3577 provsql.agg_token_value(a) - provsql.agg_token_value(b)); $$
3578 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3581CREATE OR REPLACE
FUNCTION agg_token_times(a AGG_TOKEN, b AGG_TOKEN)
3582 RETURNS AGG_TOKEN
AS
3583$$
SELECT provsql.agg_arith_make(
1,
ARRAY[(a)::UUID, (b)::UUID],
3584 provsql.agg_token_value(a) * provsql.agg_token_value(b)); $$
3585 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3588CREATE OR REPLACE
FUNCTION agg_token_div(a AGG_TOKEN, b AGG_TOKEN)
3589 RETURNS AGG_TOKEN
AS
3590$$
SELECT provsql.agg_arith_make(
3,
ARRAY[(a)::UUID, (b)::UUID],
3591 provsql.agg_token_value(a) / NULLIF(provsql.agg_token_value(b),
0)); $$
3592 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3595CREATE OR REPLACE
FUNCTION agg_token_neg(a AGG_TOKEN)
3596 RETURNS AGG_TOKEN
AS
3597$$
SELECT provsql.agg_arith_make(
4,
ARRAY[(a)::UUID],
3598 - provsql.agg_token_value(a)); $$
3599 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3609CREATE OR REPLACE
FUNCTION provsql_round(a AGG_TOKEN)
3610 RETURNS AGG_TOKEN
AS
3611$$
SELECT provsql.agg_arith_make(
12,
ARRAY[(a)::UUID],
3612 pg_catalog.round(provsql.agg_token_value(a))); $$
3613 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3616CREATE OR REPLACE
FUNCTION provsql_round(a AGG_TOKEN, d
INTEGER)
3617 RETURNS AGG_TOKEN
AS
3618$$
SELECT provsql.agg_arith_make(
12,
3619 ARRAY[(a)::UUID, provsql.agg_value_gate(d::
NUMERIC)],
3620 pg_catalog.round(provsql.agg_token_value(a), d)); $$
3621 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3637CREATE OR REPLACE
FUNCTION provsql_numeric(a AGG_TOKEN)
3638 RETURNS AGG_TOKEN
AS $$
SELECT a $$
3639 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3651CREATE OR REPLACE
FUNCTION provsql_float8(a AGG_TOKEN)
3652 RETURNS AGG_TOKEN
AS
3654 WHEN provsql.agg_token_value_type((a)::UUID) =
'float8'::REGTYPE::oid
3656 ELSE provsql.agg_arith_make(
16,
ARRAY[(a)::UUID],
3657 (provsql.agg_token_value(a)::float8)::
NUMERIC)
3659 LANGUAGE sql STABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3662CREATE OR REPLACE
FUNCTION provsql_float4(a AGG_TOKEN)
3663 RETURNS AGG_TOKEN
AS
3665 WHEN provsql.agg_token_value_type((a)::UUID) =
'float4'::REGTYPE::oid
3667 ELSE provsql.agg_arith_make(
17,
ARRAY[(a)::UUID],
3668 (provsql.agg_token_value(a)::float4)::
NUMERIC)
3670 LANGUAGE sql STABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3673CREATE OR REPLACE
FUNCTION provsql_int8(a AGG_TOKEN)
3674 RETURNS AGG_TOKEN
AS $$
SELECT provsql.provsql_round(a) $$
3675 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3678CREATE OR REPLACE
FUNCTION provsql_int4(a AGG_TOKEN)
3679 RETURNS AGG_TOKEN
AS $$
SELECT provsql.provsql_round(a) $$
3680 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3683CREATE OR REPLACE
FUNCTION provsql_int2(a AGG_TOKEN)
3684 RETURNS AGG_TOKEN
AS $$
SELECT provsql.provsql_round(a) $$
3685 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3689CREATE OR REPLACE
FUNCTION provsql_floor(a AGG_TOKEN)
3690 RETURNS AGG_TOKEN
AS
3691$$
SELECT provsql.agg_arith_make(
13,
ARRAY[(a)::UUID],
3692 pg_catalog.floor(provsql.agg_token_value(a))); $$
3693 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3696CREATE OR REPLACE
FUNCTION provsql_ceil(a AGG_TOKEN)
3697 RETURNS AGG_TOKEN
AS
3698$$
SELECT provsql.agg_arith_make(
14,
ARRAY[(a)::UUID],
3699 pg_catalog.ceil(provsql.agg_token_value(a))); $$
3700 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3703CREATE OR REPLACE
FUNCTION provsql_ceiling(a AGG_TOKEN)
3704 RETURNS AGG_TOKEN
AS
3705$$
SELECT provsql.agg_arith_make(
14,
ARRAY[(a)::UUID],
3706 pg_catalog.ceil(provsql.agg_token_value(a))); $$
3707 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3710CREATE OR REPLACE
FUNCTION provsql_abs(a AGG_TOKEN)
3711 RETURNS AGG_TOKEN
AS
3712$$
SELECT provsql.agg_arith_make(
15,
ARRAY[(a)::UUID],
3713 pg_catalog.abs(provsql.agg_token_value(a))); $$
3714 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3718CREATE OR REPLACE
FUNCTION agg_token_plus_numeric(a AGG_TOKEN, b
NUMERIC)
3719 RETURNS AGG_TOKEN
AS
3720$$
SELECT provsql.agg_arith_make(
0,
ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3721 provsql.agg_token_value(a) + b); $$
3722 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3725CREATE OR REPLACE
FUNCTION agg_token_minus_numeric(a AGG_TOKEN, b
NUMERIC)
3726 RETURNS AGG_TOKEN
AS
3727$$
SELECT provsql.agg_arith_make(
2,
ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3728 provsql.agg_token_value(a) - b); $$
3729 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3732CREATE OR REPLACE
FUNCTION agg_token_times_numeric(a AGG_TOKEN, b
NUMERIC)
3733 RETURNS AGG_TOKEN
AS
3734$$
SELECT provsql.agg_arith_make(
1,
ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3735 provsql.agg_token_value(a) * b); $$
3736 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3739CREATE OR REPLACE
FUNCTION agg_token_div_numeric(a AGG_TOKEN, b
NUMERIC)
3740 RETURNS AGG_TOKEN
AS
3741$$
SELECT provsql.agg_arith_make(
3,
ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3742 provsql.agg_token_value(a) / NULLIF(b,
0)); $$
3743 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3747CREATE OR REPLACE
FUNCTION numeric_plus_agg_token(a
NUMERIC, b AGG_TOKEN)
3748 RETURNS AGG_TOKEN
AS
3749$$
SELECT provsql.agg_arith_make(
0,
ARRAY[provsql.agg_value_gate(a), (b)::UUID],
3750 a + provsql.agg_token_value(b)); $$
3751 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3754CREATE OR REPLACE
FUNCTION numeric_minus_agg_token(a
NUMERIC, b AGG_TOKEN)
3755 RETURNS AGG_TOKEN
AS
3756$$
SELECT provsql.agg_arith_make(
2,
ARRAY[provsql.agg_value_gate(a), (b)::UUID],
3757 a - provsql.agg_token_value(b)); $$
3758 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3761CREATE OR REPLACE
FUNCTION numeric_times_agg_token(a
NUMERIC, b AGG_TOKEN)
3762 RETURNS AGG_TOKEN
AS
3763$$
SELECT provsql.agg_arith_make(
1,
ARRAY[provsql.agg_value_gate(a), (b)::UUID],
3764 a * provsql.agg_token_value(b)); $$
3765 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3768CREATE OR REPLACE
FUNCTION numeric_div_agg_token(a
NUMERIC, b AGG_TOKEN)
3769 RETURNS AGG_TOKEN
AS
3770$$
SELECT provsql.agg_arith_make(
3,
ARRAY[provsql.agg_value_gate(a), (b)::UUID],
3771 a / NULLIF(provsql.agg_token_value(b),
0)); $$
3772 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3783CREATE OR REPLACE
FUNCTION agg_token_intdiv(a AGG_TOKEN, b AGG_TOKEN)
3784 RETURNS AGG_TOKEN
AS
3785$$
SELECT provsql.agg_arith_make(
11,
ARRAY[(a)::UUID, (b)::UUID],
3786 trunc(provsql.agg_token_value(a) / NULLIF(provsql.agg_token_value(b),
0))); $$
3787 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3790CREATE OR REPLACE
FUNCTION agg_token_intdiv_numeric(a AGG_TOKEN, b
NUMERIC)
3791 RETURNS AGG_TOKEN
AS
3792$$
SELECT provsql.agg_arith_make(
11,
ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3793 trunc(provsql.agg_token_value(a) / NULLIF(b,
0))); $$
3794 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3797CREATE OR REPLACE
FUNCTION numeric_intdiv_agg_token(a
NUMERIC, b AGG_TOKEN)
3798 RETURNS AGG_TOKEN
AS
3799$$
SELECT provsql.agg_arith_make(
11,
ARRAY[provsql.agg_value_gate(a), (b)::UUID],
3800 trunc(a / NULLIF(provsql.agg_token_value(b),
0))); $$
3801 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3804CREATE OPERATOR + (LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_plus, COMMUTATOR = +);
3805CREATE OPERATOR - (LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_minus);
3806CREATE OPERATOR * (LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_times, COMMUTATOR = *);
3807CREATE OPERATOR / (LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_div);
3808CREATE OPERATOR - (RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_neg);
3813CREATE OPERATOR @ (RIGHTARG=AGG_TOKEN,
PROCEDURE=provsql_abs);
3827CREATE OR REPLACE
FUNCTION agg_transcendental(fn TEXT, a AGG_TOKEN)
3831 WHEN provsql.agg_token_value_type((a)::UUID)
3832 IN (
'float8'::REGTYPE::oid,
'float4'::REGTYPE::oid)
3834 WHEN 'sqrt' THEN sqrt(provsql.agg_token_value(a)::float8)
3835 WHEN 'ln' THEN ln(provsql.agg_token_value(a)::float8)
3836 WHEN 'exp' THEN exp(provsql.agg_token_value(a)::float8)
3841 WHEN 'sqrt' THEN sqrt(provsql.agg_token_value(a))
3842 WHEN 'ln' THEN ln(provsql.agg_token_value(a))
3843 WHEN 'exp' THEN exp(provsql.agg_token_value(a))
3846$$
LANGUAGE sql STABLE STRICT PARALLEL SAFE
3847 SET search_path=provsql,pg_temp,public;
3852CREATE OR REPLACE
FUNCTION provsql_ln(a AGG_TOKEN)
3853 RETURNS AGG_TOKEN
AS
3854$$
SELECT provsql.agg_arith_make(
8,
ARRAY[(a)::UUID],
3855 provsql.agg_transcendental(
'ln', a)); $$
3856 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3859CREATE OR REPLACE
FUNCTION provsql_exp(a AGG_TOKEN)
3860 RETURNS AGG_TOKEN
AS
3861$$
SELECT provsql.agg_arith_make(
9,
ARRAY[(a)::UUID],
3862 provsql.agg_transcendental(
'exp', a)); $$
3863 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3867CREATE OR REPLACE
FUNCTION provsql_sqrt(a AGG_TOKEN)
3868 RETURNS AGG_TOKEN
AS
3869$$
SELECT provsql.agg_arith_make(
7,
3870 ARRAY[(a)::UUID, provsql.agg_value_gate(
0.
5)],
3871 provsql.agg_transcendental(
'sqrt', a)); $$
3872 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3876CREATE OR REPLACE
FUNCTION agg_token_pow_numeric(a AGG_TOKEN, b
NUMERIC)
3877 RETURNS AGG_TOKEN
AS
3878$$
SELECT provsql.agg_arith_make(
7,
3879 ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3880 provsql.agg_token_value(a) ^ b); $$
3881 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3883CREATE OPERATOR ^ (LEFTARG=AGG_TOKEN, RIGHTARG=
NUMERIC,
3884 PROCEDURE=agg_token_pow_numeric);
3889CREATE OR REPLACE
FUNCTION provsql_power(a AGG_TOKEN, b
NUMERIC)
3890 RETURNS AGG_TOKEN
AS
3891$$
SELECT provsql.agg_arith_make(
7,
3892 ARRAY[(a)::UUID, provsql.agg_value_gate(b)],
3893 power(provsql.agg_token_value(a), b)); $$
3894 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3899CREATE OR REPLACE
FUNCTION provsql_power(a AGG_TOKEN, b
double precision)
3900 RETURNS AGG_TOKEN
AS
3901$$
SELECT provsql.agg_arith_make(
7,
3902 ARRAY[(a)::UUID, provsql.agg_value_gate(b::text::
NUMERIC)],
3903 power(provsql.agg_token_value(a)::float8, b)::text::
NUMERIC); $$
3904 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3907CREATE OR REPLACE
FUNCTION provsql_pow(a AGG_TOKEN, b
NUMERIC)
3908 RETURNS AGG_TOKEN
AS
3909$$
SELECT provsql.provsql_power(a, b); $$
3910 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3913CREATE OR REPLACE
FUNCTION provsql_pow(a AGG_TOKEN, b
double precision)
3914 RETURNS AGG_TOKEN
AS
3915$$
SELECT provsql.provsql_power(a, b); $$
3916 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
3918CREATE OPERATOR + (LEFTARG=AGG_TOKEN, RIGHTARG=
NUMERIC,
PROCEDURE=agg_token_plus_numeric, COMMUTATOR = +);
3919CREATE OPERATOR - (LEFTARG=AGG_TOKEN, RIGHTARG=
NUMERIC,
PROCEDURE=agg_token_minus_numeric);
3920CREATE OPERATOR * (LEFTARG=AGG_TOKEN, RIGHTARG=
NUMERIC,
PROCEDURE=agg_token_times_numeric, COMMUTATOR = *);
3921CREATE OPERATOR / (LEFTARG=AGG_TOKEN, RIGHTARG=
NUMERIC,
PROCEDURE=agg_token_div_numeric);
3923CREATE OPERATOR + (LEFTARG=
NUMERIC, RIGHTARG=AGG_TOKEN,
PROCEDURE=numeric_plus_agg_token, COMMUTATOR = +);
3924CREATE OPERATOR - (LEFTARG=
NUMERIC, RIGHTARG=AGG_TOKEN,
PROCEDURE=numeric_minus_agg_token);
3925CREATE OPERATOR * (LEFTARG=
NUMERIC, RIGHTARG=AGG_TOKEN,
PROCEDURE=numeric_times_agg_token, COMMUTATOR = *);
3926CREATE OPERATOR / (LEFTARG=
NUMERIC, RIGHTARG=AGG_TOKEN,
PROCEDURE=numeric_div_agg_token);
3929CREATE CAST (AGG_TOKEN
AS double precision)
WITH FUNCTION agg_token_to_float8(AGG_TOKEN)
AS ASSIGNMENT;
3931CREATE CAST (AGG_TOKEN
AS INTEGER)
WITH FUNCTION agg_token_to_int4(AGG_TOKEN)
AS ASSIGNMENT;
3933CREATE CAST (AGG_TOKEN
AS bigint)
WITH FUNCTION agg_token_to_int8(AGG_TOKEN)
AS ASSIGNMENT;
3935CREATE CAST (AGG_TOKEN
AS TEXT)
WITH FUNCTION agg_token_to_text(AGG_TOKEN)
AS ASSIGNMENT;
3937CREATE CAST (AGG_TOKEN
AS BOOLEAN)
WITH FUNCTION agg_token_to_bool(AGG_TOKEN)
AS ASSIGNMENT;
3952CREATE OR REPLACE
FUNCTION agg_token_cond(a AGG_TOKEN, cond UUID)
3953 RETURNS AGG_TOKEN
AS
3961 IF cond
IS NULL OR cond = gate_one()
THEN
3966 IF get_gate_type(tok) =
'conditioned'
3967 AND array_length(get_children(tok),
1) =
2 THEN
3968 ch := get_children(tok);
3970 ev := provenance_times(ch[
2], cond);
3975 result := public.uuid_generate_v5(uuid_ns_provsql(),
3976 concat(
'conditioned', tok, ev));
3977 PERFORM create_gate(
result,
'conditioned',
ARRAY[tok, ev]);
3978 RETURN agg_token_make(
result, agg_token_value(a));
3980$$
LANGUAGE plpgsql IMMUTABLE
SET search_path=provsql,pg_temp,public
3981 SECURITY DEFINER PARALLEL SAFE;
3984 LEFTARG = AGG_TOKEN,
3986 PROCEDURE = agg_token_cond
3997CREATE OR REPLACE
FUNCTION agg_token_cond_predicate(
3998 a AGG_TOKEN, predicate
BOOLEAN)
RETURNS AGG_TOKEN
AS
4001 RAISE EXCEPTION
'AGG_TOKEN | (predicate) must be rewritten by the ProvSQL '
4002 'planner hook: the right operand must be a Boolean combination of '
4003 'aggregate / random_variable comparisons (is provsql.active off?)'
4004 USING ERRCODE =
'feature_not_supported',
4005 DETAIL =
'provsql-reason: operator-not-rewritten; scope: gap';
4007$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
4010 LEFTARG = AGG_TOKEN,
4012 PROCEDURE = agg_token_cond_predicate
4024CREATE OR REPLACE
FUNCTION agg_conditioned_target(a AGG_TOKEN)
4025 RETURNS AGG_TOKEN
AS
4028 WHEN provsql.get_gate_type((a)::UUID) =
'conditioned'
4029 AND array_length(provsql.get_children((a)::UUID),
1) =
2
4030 THEN provsql.agg_token_make(
4031 (provsql.get_children((a)::UUID))[
1], provsql.agg_token_value(a))
4034$$
LANGUAGE sql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
4043CREATE OR REPLACE
FUNCTION agg_token_comp_numeric(a AGG_TOKEN, b
NUMERIC)
4046IMMUTABLE STRICT PARALLEL SAFE
4049 RAISE EXCEPTION
'Comparison AGG_TOKEN-NUMERIC not implemented, should be replaced by ProvSQL behavior'
4050 USING ERRCODE =
'feature_not_supported',
4051 DETAIL =
'provsql-reason: agg-comparison-not-rewritten; scope: gap';
4061CREATE OR REPLACE
FUNCTION numeric_comp_agg_token(a
NUMERIC, b AGG_TOKEN)
4064IMMUTABLE STRICT PARALLEL SAFE
4067 RAISE EXCEPTION
'Comparison NUMERIC-AGG_TOKEN not implemented, should be replaced by ProvSQL behavior'
4068 USING ERRCODE =
'feature_not_supported',
4069 DETAIL =
'provsql-reason: agg-comparison-not-rewritten; scope: gap';
4075 LEFTARG = AGG_TOKEN,
4077 PROCEDURE = agg_token_comp_numeric,
4084 RIGHTARG = AGG_TOKEN,
4085 PROCEDURE = numeric_comp_agg_token,
4092 LEFTARG = AGG_TOKEN,
4094 PROCEDURE = agg_token_comp_numeric,
4101 RIGHTARG = AGG_TOKEN,
4102 PROCEDURE = numeric_comp_agg_token,
4109 LEFTARG = AGG_TOKEN,
4111 PROCEDURE = agg_token_comp_numeric,
4118 RIGHTARG = AGG_TOKEN,
4119 PROCEDURE = numeric_comp_agg_token,
4126 LEFTARG = AGG_TOKEN,
4128 PROCEDURE = agg_token_comp_numeric,
4135 RIGHTARG = AGG_TOKEN,
4136 PROCEDURE = numeric_comp_agg_token,
4143 LEFTARG = AGG_TOKEN,
4145 PROCEDURE = agg_token_comp_numeric,
4152 RIGHTARG = AGG_TOKEN,
4153 PROCEDURE = numeric_comp_agg_token,
4160 LEFTARG = AGG_TOKEN,
4162 PROCEDURE = agg_token_comp_numeric,
4169 RIGHTARG = AGG_TOKEN,
4170 PROCEDURE = numeric_comp_agg_token,
4184CREATE OR REPLACE
FUNCTION agg_token_comp_agg_token(a AGG_TOKEN, b AGG_TOKEN)
4187IMMUTABLE STRICT PARALLEL SAFE
4190 RAISE EXCEPTION
'Comparison AGG_TOKEN-AGG_TOKEN not implemented, should be replaced by ProvSQL behavior'
4191 USING ERRCODE =
'feature_not_supported',
4192 DETAIL =
'provsql-reason: agg-comparison-not-rewritten; scope: gap';
4198 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4199 COMMUTATOR = >, NEGATOR = >=
4203 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4204 COMMUTATOR = >=, NEGATOR = >
4208 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4209 COMMUTATOR = <, NEGATOR = <=
4213 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4214 COMMUTATOR = <=, NEGATOR = <
4218 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4219 COMMUTATOR = =, NEGATOR = <>
4223 LEFTARG=AGG_TOKEN, RIGHTARG=AGG_TOKEN,
PROCEDURE=agg_token_comp_agg_token,
4224 COMMUTATOR = <>, NEGATOR = =
4234CREATE OR REPLACE
FUNCTION agg_token_comp_text(a AGG_TOKEN, b TEXT)
4237IMMUTABLE STRICT PARALLEL SAFE
4240 RAISE EXCEPTION
'Comparison AGG_TOKEN-TEXT not implemented, should be replaced by ProvSQL behavior'
4241 USING ERRCODE =
'feature_not_supported',
4242 DETAIL =
'provsql-reason: agg-comparison-not-rewritten; scope: gap';
4252CREATE OR REPLACE
FUNCTION text_comp_agg_token(a TEXT, b AGG_TOKEN)
4255IMMUTABLE STRICT PARALLEL SAFE
4258 RAISE EXCEPTION
'Comparison TEXT-AGG_TOKEN not implemented, should be replaced by ProvSQL behavior'
4259 USING ERRCODE =
'feature_not_supported',
4260 DETAIL =
'provsql-reason: agg-comparison-not-rewritten; scope: gap';
4266 LEFTARG = AGG_TOKEN,
4268 PROCEDURE = agg_token_comp_text,
4275 RIGHTARG = AGG_TOKEN,
4276 PROCEDURE = text_comp_agg_token,
4283 LEFTARG = AGG_TOKEN,
4285 PROCEDURE = agg_token_comp_text,
4292 RIGHTARG = AGG_TOKEN,
4293 PROCEDURE = text_comp_agg_token,
4331CREATE TYPE random_variable;
4334CREATE OR REPLACE
FUNCTION random_variable_in(CSTRING)
4335 RETURNS random_variable
4336 AS 'provsql',
'random_variable_in' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
4339CREATE OR REPLACE
FUNCTION random_variable_out(random_variable)
4341 AS 'provsql',
'random_variable_out' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
4343CREATE TYPE random_variable (
4344 internallength =
16,
4345 input = random_variable_in,
4346 output = random_variable_out,
4351CREATE OR REPLACE
FUNCTION random_variable_make(tok UUID)
4352 RETURNS random_variable
4353 AS 'provsql',
'random_variable_make' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
4366CREATE CAST (random_variable
AS UUID)
WITHOUT FUNCTION AS ASSIGNMENT;
4367CREATE CAST (UUID
AS random_variable)
WITHOUT FUNCTION;
4382CREATE OR REPLACE
FUNCTION agg_token_to_random_variable(a AGG_TOKEN)
4383 RETURNS random_variable
AS
4384$$
SELECT provsql.random_variable_make(provsql.agg_token_uuid($
1)); $$
4385 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
4386CREATE CAST (AGG_TOKEN
AS random_variable)
4387 WITH FUNCTION agg_token_to_random_variable(AGG_TOKEN)
AS IMPLICIT;
4397CREATE OR REPLACE
FUNCTION is_finite_float8(x
double precision)
4400 SELECT $
1 <>
'NaN'::float8
AND $
1 <>
'Infinity'::float8
AND $
1 <>
'-Infinity'::float8;
4401$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
4429CREATE OR REPLACE
FUNCTION rv_parametric2(
4431 p1_tok UUID, p1_lit
double precision,
4432 p2_tok UUID, p2_lit
double precision)
4433 RETURNS random_variable
AS
4437 wires UUID[] :=
ARRAY[]::UUID[];
4441 IF p1_tok
IS NOT NULL THEN
4442 wires := wires || p1_tok;
4443 s1 :=
'$' || (array_length(wires,
1) -
1);
4445 IF NOT provsql.is_finite_float8(p1_lit)
THEN
4446 RAISE EXCEPTION
'provsql.%: literal parameter must be finite (got %)',
4451 IF p2_tok
IS NOT NULL THEN
4452 wires := wires || p2_tok;
4453 s2 :=
'$' || (array_length(wires,
1) -
1);
4455 IF NOT provsql.is_finite_float8(p2_lit)
THEN
4456 RAISE EXCEPTION
'provsql.%: literal parameter must be finite (got %)',
4461 token := public.uuid_generate_v4();
4462 PERFORM provsql.create_gate(token,
'rv', wires,
NULL,
NULL,
4463 family ||
':' || s1 ||
',' || s2);
4464 RETURN provsql.random_variable_make(token);
4466$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE;
4471CREATE OR REPLACE
FUNCTION rv_parametric1(family TEXT, p_tok UUID)
4472 RETURNS random_variable
AS
4477 token := public.uuid_generate_v4();
4478 PERFORM provsql.create_gate(token,
'rv',
ARRAY[p_tok],
NULL,
NULL, family ||
':$0');
4479 RETURN provsql.random_variable_make(token);
4481$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4494CREATE OR REPLACE
FUNCTION normal(mu random_variable, sigma
double precision)
4495 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'normal', ($
1)::UUID,
NULL,
NULL, $
2); $$
4496 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4497CREATE OR REPLACE
FUNCTION normal(mu
double precision, sigma random_variable)
4498 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'normal',
NULL, $
1, ($
2)::UUID,
NULL); $$
4499 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4500CREATE OR REPLACE
FUNCTION normal(mu random_variable, sigma random_variable)
4501 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'normal', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4502 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4505CREATE OR REPLACE
FUNCTION logistic(mu random_variable, s
double precision)
4506 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'logistic', ($
1)::UUID,
NULL,
NULL, $
2); $$
4507 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4508CREATE OR REPLACE
FUNCTION logistic(mu
double precision, s random_variable)
4509 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'logistic',
NULL, $
1, ($
2)::UUID,
NULL); $$
4510 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4511CREATE OR REPLACE
FUNCTION logistic(mu random_variable, s random_variable)
4512 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'logistic', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4513 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4516CREATE OR REPLACE
FUNCTION uniform(a random_variable, b
double precision)
4517 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'uniform', ($
1)::UUID,
NULL,
NULL, $
2); $$
4518 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4519CREATE OR REPLACE
FUNCTION uniform(a
double precision, b random_variable)
4520 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'uniform',
NULL, $
1, ($
2)::UUID,
NULL); $$
4521 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4522CREATE OR REPLACE
FUNCTION uniform(a random_variable, b random_variable)
4523 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'uniform', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4524 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4527CREATE OR REPLACE
FUNCTION exponential(lambda random_variable)
4528 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric1(
'exponential', ($
1)::UUID); $$
4529 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4532CREATE OR REPLACE
FUNCTION gamma(k random_variable, lambda
double precision)
4533 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'gamma', ($
1)::UUID,
NULL,
NULL, $
2); $$
4534 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4535CREATE OR REPLACE
FUNCTION gamma(k
double precision, lambda random_variable)
4536 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'gamma',
NULL, $
1, ($
2)::UUID,
NULL); $$
4537 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4538CREATE OR REPLACE
FUNCTION gamma(k random_variable, lambda random_variable)
4539 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'gamma', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4540 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4543CREATE OR REPLACE
FUNCTION lognormal(mu random_variable, sigma
double precision)
4544 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'lognormal', ($
1)::UUID,
NULL,
NULL, $
2); $$
4545 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4546CREATE OR REPLACE
FUNCTION lognormal(mu
double precision, sigma random_variable)
4547 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'lognormal',
NULL, $
1, ($
2)::UUID,
NULL); $$
4548 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4549CREATE OR REPLACE
FUNCTION lognormal(mu random_variable, sigma random_variable)
4550 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'lognormal', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4551 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4554CREATE OR REPLACE
FUNCTION weibull(k random_variable, lambda
double precision)
4555 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'weibull', ($
1)::UUID,
NULL,
NULL, $
2); $$
4556 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4557CREATE OR REPLACE
FUNCTION weibull(k
double precision, lambda random_variable)
4558 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'weibull',
NULL, $
1, ($
2)::UUID,
NULL); $$
4559 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4560CREATE OR REPLACE
FUNCTION weibull(k random_variable, lambda random_variable)
4561 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'weibull', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4562 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4565CREATE OR REPLACE
FUNCTION pareto(xm random_variable, alpha
double precision)
4566 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'pareto', ($
1)::UUID,
NULL,
NULL, $
2); $$
4567 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4568CREATE OR REPLACE
FUNCTION pareto(xm
double precision, alpha random_variable)
4569 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'pareto',
NULL, $
1, ($
2)::UUID,
NULL); $$
4570 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4571CREATE OR REPLACE
FUNCTION pareto(xm random_variable, alpha random_variable)
4572 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'pareto', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4573 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4576CREATE OR REPLACE
FUNCTION beta(alpha random_variable, beta
double precision)
4577 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'beta', ($
1)::UUID,
NULL,
NULL, $
2); $$
4578 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4579CREATE OR REPLACE
FUNCTION beta(alpha
double precision, beta random_variable)
4580 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'beta',
NULL, $
1, ($
2)::UUID,
NULL); $$
4581 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4582CREATE OR REPLACE
FUNCTION beta(alpha random_variable, beta random_variable)
4583 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'beta', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4584 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4587CREATE OR REPLACE
FUNCTION inverse_gamma(alpha random_variable, beta
double precision)
4588 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gamma', ($
1)::UUID,
NULL,
NULL, $
2); $$
4589 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4590CREATE OR REPLACE
FUNCTION inverse_gamma(alpha
double precision, beta random_variable)
4591 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gamma',
NULL, $
1, ($
2)::UUID,
NULL); $$
4592 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4593CREATE OR REPLACE
FUNCTION inverse_gamma(alpha random_variable, beta random_variable)
4594 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gamma', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4595 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4598CREATE OR REPLACE
FUNCTION inverse_gaussian(mu random_variable, lambda
double precision)
4599 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gaussian', ($
1)::UUID,
NULL,
NULL, $
2); $$
4600 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4601CREATE OR REPLACE
FUNCTION inverse_gaussian(mu
double precision, lambda random_variable)
4602 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gaussian',
NULL, $
1, ($
2)::UUID,
NULL); $$
4603 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4604CREATE OR REPLACE
FUNCTION inverse_gaussian(mu random_variable, lambda random_variable)
4605 RETURNS random_variable
AS $$
SELECT provsql.rv_parametric2(
'inverse_gaussian', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
4606 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
4636CREATE OR REPLACE
FUNCTION normal(mu
double precision, sigma
double precision)
4637 RETURNS random_variable
AS
4642 IF NOT provsql.is_finite_float8(mu)
OR NOT provsql.is_finite_float8(sigma)
THEN
4643 RAISE EXCEPTION
'provsql.normal: parameters must be finite (got mu=%, sigma=%)', mu, sigma;
4646 RAISE EXCEPTION
'provsql.normal: sigma must be non-negative (got %)', sigma;
4649 RETURN provsql.as_random(mu);
4651 token := public.uuid_generate_v4();
4652 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'normal:' || mu ||
',' || sigma);
4653 RETURN provsql.random_variable_make(token);
4655$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4676CREATE OR REPLACE
FUNCTION logistic(mu
double precision, s
double precision)
4677 RETURNS random_variable
AS
4682 IF NOT provsql.is_finite_float8(mu)
OR NOT provsql.is_finite_float8(s)
THEN
4683 RAISE EXCEPTION
'provsql.logistic: parameters must be finite (got mu=%, s=%)', mu, s;
4686 RAISE EXCEPTION
'provsql.logistic: scale s must be non-negative (got %)', s;
4689 RETURN provsql.as_random(mu);
4691 token := public.uuid_generate_v4();
4692 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'logistic:' || mu ||
',' || s);
4693 RETURN provsql.random_variable_make(token);
4695$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4712CREATE OR REPLACE
FUNCTION uniform(a
double precision, b
double precision)
4713 RETURNS random_variable
AS
4718 IF NOT provsql.is_finite_float8(a)
OR NOT provsql.is_finite_float8(b)
THEN
4719 RAISE EXCEPTION
'provsql.uniform: bounds must be finite (got a=%, b=%)', a, b;
4722 RAISE EXCEPTION
'provsql.uniform: a must be <= b (got a=%, b=%)', a, b;
4725 RETURN provsql.as_random(a);
4727 token := public.uuid_generate_v4();
4728 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'uniform:' || a ||
',' || b);
4729 RETURN provsql.random_variable_make(token);
4731$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4746CREATE OR REPLACE
FUNCTION exponential(lambda
double precision)
4747 RETURNS random_variable
AS
4752 IF NOT provsql.is_finite_float8(lambda)
THEN
4753 RAISE EXCEPTION
'provsql.exponential: lambda must be finite (got %)', lambda;
4756 RAISE EXCEPTION
'provsql.exponential: lambda must be strictly positive (got %)', lambda;
4758 token := public.uuid_generate_v4();
4759 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'exponential:' || lambda);
4760 RETURN provsql.random_variable_make(token);
4762$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4787CREATE OR REPLACE
FUNCTION erlang(k
INTEGER, lambda
double precision)
4788 RETURNS random_variable
AS
4794 RAISE EXCEPTION
'provsql.erlang: k must be >= 1 (got %)', k;
4796 IF NOT provsql.is_finite_float8(lambda)
THEN
4797 RAISE EXCEPTION
'provsql.erlang: lambda must be finite (got %)', lambda;
4800 RAISE EXCEPTION
'provsql.erlang: lambda must be strictly positive (got %)', lambda;
4803 RETURN provsql.exponential(lambda);
4805 token := public.uuid_generate_v4();
4806 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'erlang:' || k ||
',' || lambda);
4807 RETURN provsql.random_variable_make(token);
4809$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4832CREATE OR REPLACE
FUNCTION gamma(k
double precision, lambda
double precision)
4833 RETURNS random_variable
AS
4838 IF NOT provsql.is_finite_float8(k)
THEN
4839 RAISE EXCEPTION
'provsql.gamma: k must be finite (got %)', k;
4842 RAISE EXCEPTION
'provsql.gamma: k must be strictly positive (got %)', k;
4844 IF NOT provsql.is_finite_float8(lambda)
THEN
4845 RAISE EXCEPTION
'provsql.gamma: lambda must be finite (got %)', lambda;
4848 RAISE EXCEPTION
'provsql.gamma: lambda must be strictly positive (got %)', lambda;
4850 IF k = floor(k)
AND k <=
2147483647 THEN
4851 RETURN provsql.erlang(k::
INTEGER, lambda);
4853 token := public.uuid_generate_v4();
4854 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'gamma:' || k ||
',' || lambda);
4855 RETURN provsql.random_variable_make(token);
4857$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4872CREATE OR REPLACE
FUNCTION chi_squared(k
double precision)
4873 RETURNS random_variable
AS
4876 IF NOT provsql.is_finite_float8(k)
THEN
4877 RAISE EXCEPTION
'provsql.chi_squared: k must be finite (got %)', k;
4880 RAISE EXCEPTION
'provsql.chi_squared: k must be strictly positive (got %)', k;
4882 RETURN provsql.gamma(k /
2,
0.
5);
4884$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4906CREATE OR REPLACE
FUNCTION lognormal(mu
double precision, sigma
double precision)
4907 RETURNS random_variable
AS
4912 IF NOT provsql.is_finite_float8(mu)
OR NOT provsql.is_finite_float8(sigma)
THEN
4913 RAISE EXCEPTION
'provsql.lognormal: parameters must be finite (got mu=%, sigma=%)', mu, sigma;
4916 RAISE EXCEPTION
'provsql.lognormal: sigma must be non-negative (got %)', sigma;
4919 RETURN provsql.as_random(exp(mu));
4921 token := public.uuid_generate_v4();
4922 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'lognormal:' || mu ||
',' || sigma);
4923 RETURN provsql.random_variable_make(token);
4925$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4946CREATE OR REPLACE
FUNCTION weibull(k
double precision, lambda
double precision)
4947 RETURNS random_variable
AS
4952 IF NOT provsql.is_finite_float8(k)
OR NOT provsql.is_finite_float8(lambda)
THEN
4953 RAISE EXCEPTION
'provsql.weibull: parameters must be finite (got k=%, lambda=%)', k, lambda;
4955 IF k <=
0 OR lambda <=
0 THEN
4956 RAISE EXCEPTION
'provsql.weibull: parameters must be strictly positive (got k=%, lambda=%)', k, lambda;
4959 RETURN provsql.exponential(
1 / lambda);
4961 token := public.uuid_generate_v4();
4962 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'weibull:' || k ||
',' || lambda);
4963 RETURN provsql.random_variable_make(token);
4965$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
4985CREATE OR REPLACE
FUNCTION pareto(xm
double precision, alpha
double precision)
4986 RETURNS random_variable
AS
4991 IF NOT provsql.is_finite_float8(xm)
OR NOT provsql.is_finite_float8(alpha)
THEN
4992 RAISE EXCEPTION
'provsql.pareto: parameters must be finite (got xm=%, alpha=%)', xm, alpha;
4994 IF xm <=
0 OR alpha <=
0 THEN
4995 RAISE EXCEPTION
'provsql.pareto: parameters must be strictly positive (got xm=%, alpha=%)', xm, alpha;
4997 token := public.uuid_generate_v4();
4998 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'pareto:' || xm ||
',' || alpha);
4999 RETURN provsql.random_variable_make(token);
5001$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5022CREATE OR REPLACE
FUNCTION inverse_gamma(alpha
double precision, beta
double precision)
5023 RETURNS random_variable
AS
5028 IF NOT provsql.is_finite_float8(alpha)
OR NOT provsql.is_finite_float8(beta)
THEN
5029 RAISE EXCEPTION
'provsql.inverse_gamma: parameters must be finite (got alpha=%, beta=%)', alpha, beta;
5031 IF alpha <=
0 OR beta <=
0 THEN
5032 RAISE EXCEPTION
'provsql.inverse_gamma: parameters must be strictly positive (got alpha=%, beta=%)', alpha, beta;
5034 token := public.uuid_generate_v4();
5035 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'inverse_gamma:' || alpha ||
',' || beta);
5036 RETURN provsql.random_variable_make(token);
5038$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5059CREATE OR REPLACE
FUNCTION inverse_gaussian(mu
double precision, lambda
double precision)
5060 RETURNS random_variable
AS
5065 IF NOT provsql.is_finite_float8(mu)
OR NOT provsql.is_finite_float8(lambda)
THEN
5066 RAISE EXCEPTION
'provsql.inverse_gaussian: parameters must be finite (got mu=%, lambda=%)', mu, lambda;
5068 IF mu <=
0 OR lambda <=
0 THEN
5069 RAISE EXCEPTION
'provsql.inverse_gaussian: parameters must be strictly positive (got mu=%, lambda=%)', mu, lambda;
5071 token := public.uuid_generate_v4();
5072 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'inverse_gaussian:' || mu ||
',' || lambda);
5073 RETURN provsql.random_variable_make(token);
5075$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5082CREATE OR REPLACE
FUNCTION wald(mu
double precision, lambda
double precision)
5083 RETURNS random_variable
AS
5085 SELECT provsql.inverse_gaussian(mu, lambda);
5086$$
LANGUAGE sql VOLATILE PARALLEL UNSAFE;
5106CREATE OR REPLACE
FUNCTION categorical_from_log_pmf(
5107 outcomes
double precision[], log_pmf
double precision[])
5108 RETURNS random_variable
AS
5111 n
INT := array_length(outcomes,
1);
5112 max_lp
double precision :=
'-Infinity';
5113 kept_o
double precision[] :=
'{}';
5114 kept_p
double precision[] :=
'{}';
5115 total
double precision :=
0;
5119 IF n
IS NULL OR n =
0 OR n <> coalesce(array_length(log_pmf,
1),
0)
THEN
5120 RAISE EXCEPTION
'provsql.categorical_from_log_pmf: outcomes and log_pmf must be non-empty arrays of the same length';
5123 IF log_pmf[i] > max_lp
THEN max_lp := log_pmf[i];
END IF;
5125 IF max_lp =
'-Infinity' THEN
5126 RAISE EXCEPTION
'provsql.categorical_from_log_pmf: all masses are zero';
5129 v := exp(log_pmf[i] - max_lp);
5131 kept_o := array_append(kept_o, outcomes[i]);
5132 kept_p := array_append(kept_p, v);
5136 FOR i
IN 1..array_length(kept_p,
1) LOOP
5137 kept_p[i] := kept_p[i] / total;
5139 RETURN provsql.categorical(kept_p, kept_o);
5141$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5158CREATE OR REPLACE
FUNCTION poisson(lambda
double precision)
5159 RETURNS random_variable
AS
5164 outcomes
double precision[] :=
'{}';
5165 lps
double precision[] :=
'{}';
5166 lp
double precision :=
0;
5169 IF NOT provsql.is_finite_float8(lambda)
OR lambda <
0 THEN
5170 RAISE EXCEPTION
'provsql.poisson: lambda must be finite and non-negative (got %)', lambda;
5173 RETURN provsql.as_random(
0);
5175 lo := greatest(
0, floor(lambda -
12 * sqrt(lambda)))::
INT;
5176 hi := ceil(lambda +
12 * sqrt(lambda))::
INT +
30;
5177 IF hi - lo +
1 >
10000 THEN
5178 RAISE EXCEPTION
'provsql.poisson: support window of % outcomes exceeds 10000; approximate with normal(%, sqrt(%))', hi - lo +
1, lambda, lambda;
5183 lp := lp + ln(lambda) - ln(k::
double precision);
5185 outcomes := array_append(outcomes, k::
double precision);
5186 lps := array_append(lps, lp);
5190 outcomes := array_prepend(
0::
double precision, outcomes);
5191 lps := array_prepend(-lambda, lps);
5193 RETURN provsql.categorical_from_log_pmf(outcomes, lps);
5195$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5208CREATE OR REPLACE
FUNCTION poisson(lambda random_variable)
5209 RETURNS random_variable
AS
5210$$
SELECT provsql.rv_parametric1(
'poisson', ($
1)::UUID); $$
5211 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5230CREATE OR REPLACE
FUNCTION beta(alpha
double precision, beta
double precision)
5231 RETURNS random_variable
AS
5236 IF NOT provsql.is_finite_float8(alpha)
OR NOT provsql.is_finite_float8(beta)
THEN
5237 RAISE EXCEPTION
'provsql.beta: parameters must be finite (got alpha=%, beta=%)', alpha, beta;
5239 IF alpha <=
0 OR beta <=
0 THEN
5240 RAISE EXCEPTION
'provsql.beta: parameters must be strictly positive (got alpha=%, beta=%)', alpha, beta;
5242 IF alpha =
1 AND beta =
1 THEN
5243 RETURN provsql.uniform(
0,
1);
5245 token := public.uuid_generate_v4();
5246 PERFORM provsql.create_gate(token,
'rv',
NULL,
NULL,
NULL,
'beta:' || alpha ||
',' || beta);
5247 RETURN provsql.random_variable_make(token);
5249$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5262CREATE OR REPLACE
FUNCTION binomial(n
INTEGER, p
double precision)
5263 RETURNS random_variable
AS
5266 outcomes
double precision[] :=
'{}';
5267 lps
double precision[] :=
'{}';
5268 lp
double precision;
5271 IF n
IS NULL OR n <
0 THEN
5272 RAISE EXCEPTION
'provsql.binomial: n must be non-negative (got %)', n;
5274 IF NOT provsql.is_finite_float8(p)
OR p <
0 OR p >
1 THEN
5275 RAISE EXCEPTION
'provsql.binomial: p must be in [0, 1] (got %)', p;
5278 RAISE EXCEPTION
'provsql.binomial: n = % exceeds 10000; approximate with normal(n*p, sqrt(n*p*(1-p)))', n;
5280 IF n =
0 OR p =
0 THEN
5281 RETURN provsql.as_random(
0);
5284 RETURN provsql.as_random(n);
5286 lp := n * ln(
1 - p);
5287 outcomes := array_append(outcomes,
0::
double precision);
5288 lps := array_append(lps, lp);
5289 FOR k
IN 0..(n -
1) LOOP
5290 lp := lp + ln((n - k)::
double precision / (k +
1)) + ln(p / (
1 - p));
5291 outcomes := array_append(outcomes, (k +
1)::
double precision);
5292 lps := array_append(lps, lp);
5294 RETURN provsql.categorical_from_log_pmf(outcomes, lps);
5296$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5309CREATE OR REPLACE
FUNCTION binomial(n
INTEGER, p random_variable)
5310 RETURNS random_variable
AS
5311$$
SELECT provsql.rv_parametric2(
'binomial',
NULL, $
1::
double precision,
5312 ($
2)::UUID,
NULL); $$
5313 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5325CREATE OR REPLACE
FUNCTION geometric(p
double precision)
5326 RETURNS random_variable
AS
5330 outcomes
double precision[] :=
'{}';
5331 lps
double precision[] :=
'{}';
5334 IF NOT provsql.is_finite_float8(p)
OR p <=
0 OR p >
1 THEN
5335 RAISE EXCEPTION
'provsql.geometric: p must be in (0, 1] (got %)', p;
5338 RETURN provsql.as_random(
1);
5340 k_max :=
1 + ceil(ln(1e-15) / ln(
1 - p))::
INT;
5341 IF k_max >
10000 THEN
5342 RAISE EXCEPTION
'provsql.geometric: support window of % outcomes exceeds 10000 (p = % is too small); approximate with exponential(%)', k_max, p, p;
5344 FOR k
IN 1..k_max LOOP
5345 outcomes := array_append(outcomes, k::
double precision);
5346 lps := array_append(lps, (k -
1) * ln(
1 - p) + ln(p));
5348 RETURN provsql.categorical_from_log_pmf(outcomes, lps);
5350$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5360CREATE OR REPLACE
FUNCTION geometric(p random_variable)
5361 RETURNS random_variable
AS
5362$$
SELECT provsql.rv_parametric1(
'geometric', ($
1)::UUID); $$
5363 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5379CREATE OR REPLACE
FUNCTION hypergeometric(pop_n
INTEGER, k_marked
INTEGER, n
INTEGER)
5380 RETURNS random_variable
AS
5385 outcomes
double precision[] :=
'{}';
5386 lps
double precision[] :=
'{}';
5387 lp
double precision :=
0;
5390 IF pop_n
IS NULL OR k_marked
IS NULL OR n
IS NULL
5391 OR pop_n <
0 OR k_marked <
0 OR n <
0
5392 OR k_marked > pop_n
OR n > pop_n
THEN
5393 RAISE EXCEPTION
'provsql.hypergeometric: need 0 <= k_marked, n <= pop_n (got pop_n=%, k_marked=%, n=%)', pop_n, k_marked, n;
5395 lo := greatest(
0, n - (pop_n - k_marked));
5396 hi := least(n, k_marked);
5397 IF hi - lo +
1 >
10000 THEN
5398 RAISE EXCEPTION
'provsql.hypergeometric: support window of % outcomes exceeds 10000', hi - lo +
1;
5400 outcomes := array_append(outcomes, lo::
double precision);
5401 lps := array_append(lps, lp);
5402 FOR k
IN lo..(hi -
1) LOOP
5404 lp := lp + ln((k_marked - k)::
double precision * (n - k))
5405 - ln((k +
1)::
double precision * (pop_n - k_marked - n + k +
1));
5406 outcomes := array_append(outcomes, (k +
1)::
double precision);
5407 lps := array_append(lps, lp);
5409 RETURN provsql.categorical_from_log_pmf(outcomes, lps);
5411$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5426CREATE OR REPLACE
FUNCTION negative_binomial(r
double precision, p
double precision)
5427 RETURNS random_variable
AS
5430 outcomes
double precision[] :=
'{}';
5431 lps
double precision[] :=
'{}';
5432 lp
double precision;
5433 max_lp
double precision;
5434 mean
double precision;
5437 IF NOT provsql.is_finite_float8(r)
OR r <=
0 THEN
5438 RAISE EXCEPTION
'provsql.negative_binomial: r must be finite and strictly positive (got %)', r;
5440 IF NOT provsql.is_finite_float8(p)
OR p <=
0 OR p >
1 THEN
5441 RAISE EXCEPTION
'provsql.negative_binomial: p must be in (0, 1] (got %)', p;
5444 RETURN provsql.as_random(
0);
5446 mean := r * (
1 - p) / p;
5449 outcomes := array_append(outcomes,
0::
double precision);
5450 lps := array_append(lps, lp);
5452 lp := lp + ln((k + r) / (k +
1)) + ln(
1 - p);
5454 IF lp > max_lp
THEN max_lp := lp;
END IF;
5455 outcomes := array_append(outcomes, k::
double precision);
5456 lps := array_append(lps, lp);
5457 EXIT
WHEN k > mean
AND lp < max_lp + ln(1e-15);
5459 RAISE EXCEPTION
'provsql.negative_binomial: support window exceeds 10000 outcomes (r=%, p=%)', r, p;
5462 RETURN provsql.categorical_from_log_pmf(outcomes, lps);
5464$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5473CREATE OR REPLACE
FUNCTION negative_binomial(r
double precision, p random_variable)
5474 RETURNS random_variable
AS
5475$$
SELECT provsql.rv_parametric2(
'negative_binomial',
NULL, $
1, ($
2)::UUID,
NULL); $$
5476 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5477CREATE OR REPLACE
FUNCTION negative_binomial(r random_variable, p
double precision)
5478 RETURNS random_variable
AS
5479$$
SELECT provsql.rv_parametric2(
'negative_binomial', ($
1)::UUID,
NULL,
NULL, $
2); $$
5480 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5481CREATE OR REPLACE
FUNCTION negative_binomial(r random_variable, p random_variable)
5482 RETURNS random_variable
AS
5483$$
SELECT provsql.rv_parametric2(
'negative_binomial', ($
1)::UUID,
NULL, ($
2)::UUID,
NULL); $$
5484 LANGUAGE sql STRICT VOLATILE PARALLEL UNSAFE;
5498CREATE OR REPLACE
FUNCTION rv_families()
5499 RETURNS TABLE(name TEXT, nparams
INT, param_names TEXT[], label TEXT)
AS
5500 'provsql',
'rv_families' LANGUAGE C STABLE PARALLEL SAFE;
5542CREATE OR REPLACE
FUNCTION mixture(
5543 p UUID, x random_variable, y random_variable)
5544 RETURNS random_variable
AS
5548 p_kind provsql.PROVENANCE_GATE;
5551 x_kind provsql.PROVENANCE_GATE;
5552 y_kind provsql.PROVENANCE_GATE;
5554 p_kind := provsql.get_gate_type(p);
5555 IF p_kind
NOT IN (
'input',
'mulinput',
'update',
5556 'plus',
'times',
'monus',
5557 'project',
'eq',
'cmp',
5559 RAISE EXCEPTION
'provsql.mixture: p must be a Boolean gate '
5560 '(input/mulinput/update/plus/times/monus/project/eq/cmp/zero/one), got %', p_kind
5561 USING ERRCODE =
'feature_not_supported',
5562 DETAIL =
'provsql-reason: mixture-argument-kind; scope: out-of-scope';
5565 x_uuid := (x)::UUID;
5566 y_uuid := (y)::UUID;
5567 x_kind := provsql.get_gate_type(x_uuid);
5568 y_kind := provsql.get_gate_type(y_uuid);
5569 IF x_kind
NOT IN (
'rv',
'value',
'arith',
'mixture')
THEN
5570 RAISE EXCEPTION
'provsql.mixture: x must be a scalar RV root (rv / value / arith / mixture), got %', x_kind
5571 USING ERRCODE =
'feature_not_supported',
5572 DETAIL =
'provsql-reason: mixture-argument-kind; scope: out-of-scope';
5574 IF y_kind
NOT IN (
'rv',
'value',
'arith',
'mixture')
THEN
5575 RAISE EXCEPTION
'provsql.mixture: y must be a scalar RV root (rv / value / arith / mixture), got %', y_kind
5576 USING ERRCODE =
'feature_not_supported',
5577 DETAIL =
'provsql-reason: mixture-argument-kind; scope: out-of-scope';
5580 token := public.uuid_generate_v5(
5581 provsql.uuid_ns_provsql(),
5582 concat(
'mixture', p, x_uuid, y_uuid));
5583 PERFORM provsql.create_gate(token,
'mixture',
ARRAY[p, x_uuid, y_uuid]);
5584 RETURN provsql.random_variable_make(token);
5586$$
LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE;
5616CREATE OR REPLACE
FUNCTION mixture(
5617 p_value
double precision,
5620 RETURNS random_variable
AS
5625 IF p_value
IS NULL OR p_value <> p_value
OR p_value <
0 OR p_value >
1 THEN
5626 RAISE EXCEPTION
'provsql.mixture: probability must be in [0,1] (got %)', p_value;
5630 ELSIF p_value =
0 THEN
5633 p_token := public.uuid_generate_v4();
5634 PERFORM provsql.create_gate(p_token,
'input');
5635 PERFORM provsql.set_prob(p_token, p_value);
5636 RETURN provsql.mixture(p_token, x, y);
5638$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5672CREATE OR REPLACE
FUNCTION categorical(
5673 probs
double precision[],
5674 outcomes
double precision[])
5675 RETURNS random_variable
AS
5679 p_sum
double precision :=
0.
0;
5684 mul_tokens UUID[] :=
ARRAY[]::UUID[];
5686 pi_i
double precision;
5687 vi_i
double precision;
5689 IF probs
IS NULL OR outcomes
IS NULL THEN
5690 RAISE EXCEPTION
'provsql.categorical: probs and outcomes must be non-null';
5692 n := array_length(probs,
1);
5693 IF n
IS NULL OR n <
1 THEN
5694 RAISE EXCEPTION
'provsql.categorical: probs must be non-empty';
5696 IF array_length(outcomes,
1) <> n
THEN
5697 RAISE EXCEPTION
'provsql.categorical: probs and outcomes must have the same length (got % and %)',
5698 n, array_length(outcomes,
1);
5703 vi_i := outcomes[i];
5708 IF pi_i
IS NULL OR pi_i =
'NaN'::float8
OR pi_i <
0 OR pi_i >
1 THEN
5709 RAISE EXCEPTION
'provsql.categorical: probs[%] must be in [0,1] (got %)', i, pi_i;
5711 IF vi_i
IS NULL OR vi_i =
'NaN'::float8
5712 OR vi_i =
'Infinity'::float8
OR vi_i =
'-Infinity'::float8
THEN
5713 RAISE EXCEPTION
'provsql.categorical: outcomes[%] must be finite (got %)', i, vi_i;
5715 p_sum := p_sum + pi_i;
5717 IF abs(p_sum -
1.
0) > 1e-9
THEN
5718 RAISE EXCEPTION
'provsql.categorical: probs must sum to 1 within 1e-9 (got %)', p_sum;
5730 nb_positive
INTEGER :=
0;
5731 only_idx
INTEGER :=
0;
5734 IF probs[i] >
0.
0 THEN
5735 nb_positive := nb_positive +
1;
5739 IF nb_positive =
1 THEN
5740 RETURN provsql.as_random(outcomes[only_idx]);
5747 key_token := public.uuid_generate_v4();
5748 PERFORM provsql.create_gate(key_token,
'input');
5749 PERFORM provsql.set_prob(key_token,
1.
0);
5756 IF pi_i <=
0.
0 THEN CONTINUE;
END IF;
5757 mul_token := public.uuid_generate_v4();
5758 PERFORM provsql.create_gate(mul_token,
'mulinput',
ARRAY[key_token],
5759 i -
1,
NULL, outcomes[i]::TEXT);
5760 PERFORM provsql.set_prob(mul_token, pi_i);
5761 mul_tokens := mul_tokens || mul_token;
5764 mix_wires :=
ARRAY[key_token] || mul_tokens;
5765 mix_token := public.uuid_generate_v4();
5766 PERFORM provsql.create_gate(mix_token,
'mixture', mix_wires);
5767 RETURN provsql.random_variable_make(mix_token);
5769$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5802CREATE OR REPLACE
FUNCTION gmm(
5803 weights
double precision[],
5804 means
double precision[],
5805 stddevs
double precision[])
5806 RETURNS random_variable
AS
5810 w_sum
double precision :=
0.
0;
5812 acc random_variable :=
NULL;
5813 remaining
double precision :=
0.
0;
5815 IF weights
IS NULL OR means
IS NULL OR stddevs
IS NULL THEN
5816 RAISE EXCEPTION
'provsql.gmm: weights, means, and stddevs must be non-null';
5818 n := array_length(weights,
1);
5819 IF n
IS NULL OR n <
1 THEN
5820 RAISE EXCEPTION
'provsql.gmm: weights must be non-empty';
5822 IF array_length(means,
1) <> n
OR array_length(stddevs,
1) <> n
THEN
5823 RAISE EXCEPTION
'provsql.gmm: weights, means, and stddevs must have the same length (got %, %, %)',
5824 n, array_length(means,
1), array_length(stddevs,
1);
5827 IF weights[i]
IS NULL OR weights[i] =
'NaN'::float8
5828 OR weights[i] <
0 OR weights[i] >
1 THEN
5829 RAISE EXCEPTION
'provsql.gmm: weights[%] must be in [0,1] (got %)',
5832 w_sum := w_sum + weights[i];
5834 IF abs(w_sum -
1.
0) > 1e-9
THEN
5835 RAISE EXCEPTION
'provsql.gmm: weights must sum to 1 within 1e-9 (got %)', w_sum;
5841 FOR i
IN REVERSE n..
1 LOOP
5842 IF weights[i] <=
0.
0 THEN
5846 acc := provsql.normal(means[i], stddevs[i]);
5847 remaining := weights[i];
5849 remaining := remaining + weights[i];
5850 acc := provsql.mixture(least(
1.
0, weights[i] / remaining),
5851 provsql.normal(means[i], stddevs[i]), acc);
5856$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5883CREATE OR REPLACE
FUNCTION empirical_samples(samples
double precision[])
5884 RETURNS random_variable
AS
5888 sorted
double precision[];
5889 outcomes
double precision[] :=
'{}';
5890 probs
double precision[] :=
'{}';
5892 prev
double precision;
5894 started
BOOLEAN :=
false;
5896 n := array_length(samples,
1);
5897 IF n
IS NULL OR n <
1 THEN
5898 RAISE EXCEPTION
'provsql.empirical_samples: samples must be non-empty';
5900 sorted :=
ARRAY(
SELECT s
FROM unnest(samples)
AS s
ORDER BY 1);
5901 FOREACH v
IN ARRAY sorted LOOP
5902 IF v
IS NULL OR v =
'NaN'::float8
5903 OR v =
'Infinity'::float8
OR v =
'-Infinity'::float8
THEN
5905 'provsql.empirical_samples: samples must be finite (got %)', v;
5907 IF started
AND v = prev
THEN
5911 outcomes := outcomes || prev;
5912 probs := probs || (run::
double precision / n);
5919 outcomes := outcomes || prev;
5920 probs := probs || (run::
double precision / n);
5921 IF array_length(outcomes,
1) >
10000 THEN
5923 'provsql.empirical_samples: at most 10000 distinct values are '
5924 'supported (got %); thin the chain or bin the samples (e.g. with '
5925 'width_bucket)', array_length(outcomes,
1);
5927 RETURN provsql.categorical(probs, outcomes);
5929$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
5959CREATE OR REPLACE
FUNCTION empirical_cdf(grid
double precision[],
5960 cdf
double precision[])
5961 RETURNS random_variable
AS
5966 acc random_variable :=
NULL;
5967 remaining
double precision :=
0.
0;
5969 comp random_variable;
5971 n := array_length(grid,
1);
5972 IF n
IS NULL OR n <
2 THEN
5973 RAISE EXCEPTION
'provsql.empirical_cdf: grid must have at least two entries';
5975 IF array_length(cdf,
1) <> n
THEN
5976 RAISE EXCEPTION
'provsql.empirical_cdf: grid and cdf must have the same length (got % and %)',
5977 n, array_length(cdf,
1);
5980 RAISE EXCEPTION
'provsql.empirical_cdf: at most 10000 grid points are supported (got %)', n;
5983 IF grid[i]
IS NULL OR grid[i] =
'NaN'::float8
5984 OR grid[i] =
'Infinity'::float8
OR grid[i] =
'-Infinity'::float8
THEN
5985 RAISE EXCEPTION
'provsql.empirical_cdf: grid[%] must be finite (got %)', i, grid[i];
5987 IF i >
1 AND NOT grid[i] > grid[i-1]
THEN
5988 RAISE EXCEPTION
'provsql.empirical_cdf: grid must be strictly increasing (grid[%] = %, grid[%] = %)',
5989 i-1, grid[i-1], i, grid[i];
5991 IF cdf[i]
IS NULL OR cdf[i] =
'NaN'::float8
OR cdf[i] <
0 OR cdf[i] >
1 THEN
5992 RAISE EXCEPTION
'provsql.empirical_cdf: cdf[%] must be in [0,1] (got %)', i, cdf[i];
5994 IF i >
1 AND cdf[i] < cdf[i-1]
THEN
5995 RAISE EXCEPTION
'provsql.empirical_cdf: cdf must be non-decreasing (cdf[%] = %, cdf[%] = %)',
5996 i-1, cdf[i-1], i, cdf[i];
5999 IF abs(cdf[n] -
1.
0) > 1e-9
THEN
6000 RAISE EXCEPTION
'provsql.empirical_cdf: cdf must end at 1 within 1e-9 (got %)', cdf[n];
6006 FOR i
IN REVERSE n..
1 LOOP
6007 w :=
CASE WHEN i =
1 THEN cdf[
1]
ELSE cdf[i] - cdf[i-1]
END;
6011 comp :=
CASE WHEN i =
1 THEN provsql.as_random(grid[
1])
6012 ELSE provsql.uniform(grid[i-1], grid[i])
END;
6017 remaining := remaining + w;
6018 acc := provsql.mixture(least(
1.
0, w / remaining), comp, acc);
6023$$
LANGUAGE plpgsql STRICT VOLATILE PARALLEL UNSAFE;
6044CREATE OR REPLACE
FUNCTION as_random(c
double precision)
6045 RETURNS random_variable
AS
6053 c_canon
double precision := c +
0.
0;
6054 c_text
varchar :=
CAST(c_canon
AS VARCHAR);
6055 token UUID := public.uuid_generate_v5(
6056 provsql.uuid_ns_provsql(), concat(
'value', c_text));
6058 PERFORM provsql.create_gate(token,
'value',
NULL,
NULL,
NULL, c_text);
6059 RETURN provsql.random_variable_make(token);
6061$$
LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE;
6076CREATE CAST (
double precision AS random_variable)
6077 WITH FUNCTION as_random(
double precision)
AS IMPLICIT;
6080CREATE OR REPLACE
FUNCTION as_random(c
INTEGER)
6081 RETURNS random_variable
AS
6082$$
SELECT provsql.as_random(c::
double precision); $$
6083LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE;
6086CREATE OR REPLACE
FUNCTION as_random(c
NUMERIC)
6087 RETURNS random_variable
AS
6088$$
SELECT provsql.as_random(c::
double precision); $$
6089LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE;
6092CREATE CAST (
INTEGER AS random_variable)
6093 WITH FUNCTION as_random(
INTEGER)
AS IMPLICIT;
6096CREATE CAST (
NUMERIC AS random_variable)
6097 WITH FUNCTION as_random(
NUMERIC)
AS IMPLICIT;
6129CREATE OR REPLACE
FUNCTION random_variable_plus(
6130 a random_variable, b random_variable)
6131 RETURNS random_variable
AS
6133 SELECT provsql.random_variable_make(
6134 provsql.provenance_arith(
6138$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6141CREATE OR REPLACE
FUNCTION random_variable_minus(
6142 a random_variable, b random_variable)
6143 RETURNS random_variable
AS
6145 SELECT provsql.random_variable_make(
6146 provsql.provenance_arith(
6150$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6153CREATE OR REPLACE
FUNCTION random_variable_times(
6154 a random_variable, b random_variable)
6155 RETURNS random_variable
AS
6157 SELECT provsql.random_variable_make(
6158 provsql.provenance_arith(
6162$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6165CREATE OR REPLACE
FUNCTION random_variable_div(
6166 a random_variable, b random_variable)
6167 RETURNS random_variable
AS
6169 SELECT provsql.random_variable_make(
6170 provsql.provenance_arith(
6174$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6177CREATE OR REPLACE
FUNCTION random_variable_neg(a random_variable)
6178 RETURNS random_variable
AS
6180 SELECT provsql.random_variable_make(
6181 provsql.provenance_arith(
6184$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6193CREATE OR REPLACE
FUNCTION random_variable_pow(
6194 a random_variable, b random_variable)
6195 RETURNS random_variable
AS
6197 SELECT provsql.random_variable_make(
6198 provsql.provenance_arith(
6202$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6211CREATE OR REPLACE
FUNCTION ln(a random_variable)
6212 RETURNS random_variable
AS
6214 SELECT provsql.random_variable_make(
6215 provsql.provenance_arith(
6218$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6221CREATE OR REPLACE
FUNCTION exp(a random_variable)
6222 RETURNS random_variable
AS
6224 SELECT provsql.random_variable_make(
6225 provsql.provenance_arith(
6228$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6236CREATE OR REPLACE
FUNCTION pow(a random_variable, b random_variable)
6237 RETURNS random_variable
AS
6239 SELECT provsql.random_variable_pow(a, b);
6240$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6242CREATE OR REPLACE
FUNCTION power(a random_variable, b random_variable)
6243 RETURNS random_variable
AS
6245 SELECT provsql.random_variable_pow(a, b);
6246$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6253CREATE OR REPLACE
FUNCTION sqrt(a random_variable)
6254 RETURNS random_variable
AS
6256 SELECT provsql.random_variable_pow(a, provsql.as_random(
0.
5));
6257$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6267CREATE OR REPLACE
FUNCTION random_variable_cmp_oid(sym TEXT)
6270 SELECT (sym ||
'(double precision,double precision)')::regoperator::oid;
6271$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6287CREATE OR REPLACE
FUNCTION random_variable_cmp_placeholder(
6288 a random_variable, b random_variable)
6292 RAISE EXCEPTION
'random_variable comparison must be rewritten by the '
6293 'ProvSQL planner hook (is provsql.active off?)'
6294 USING ERRCODE =
'feature_not_supported',
6295 DETAIL =
'provsql-reason: rv-operator-not-rewritten; scope: out-of-scope';
6297$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
6299CREATE OR REPLACE
FUNCTION random_variable_lt(
6300 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6301$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6302LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6304CREATE OR REPLACE
FUNCTION random_variable_le(
6305 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6306$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6307LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6309CREATE OR REPLACE
FUNCTION random_variable_eq(
6310 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6311$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6312LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6314CREATE OR REPLACE
FUNCTION random_variable_ne(
6315 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6316$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6317LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6319CREATE OR REPLACE
FUNCTION random_variable_ge(
6320 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6321$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6322LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6324CREATE OR REPLACE
FUNCTION random_variable_gt(
6325 a random_variable, b random_variable)
RETURNS BOOLEAN AS
6326$$
SELECT provsql.random_variable_cmp_placeholder(a, b); $$
6327LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6335CREATE OR REPLACE
FUNCTION rv_cmp_lt(
6336 a random_variable, b random_variable)
RETURNS UUID
AS
6338 SELECT provsql.provenance_cmp(
6340 provsql.random_variable_cmp_oid(
'<'),
6342$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6345CREATE OR REPLACE
FUNCTION rv_cmp_le(
6346 a random_variable, b random_variable)
RETURNS UUID
AS
6348 SELECT provsql.provenance_cmp(
6350 provsql.random_variable_cmp_oid(
'<='),
6352$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6355CREATE OR REPLACE
FUNCTION rv_cmp_eq(
6356 a random_variable, b random_variable)
RETURNS UUID
AS
6358 SELECT provsql.provenance_cmp(
6360 provsql.random_variable_cmp_oid(
'='),
6362$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6365CREATE OR REPLACE
FUNCTION rv_cmp_ne(
6366 a random_variable, b random_variable)
RETURNS UUID
AS
6368 SELECT provsql.provenance_cmp(
6370 provsql.random_variable_cmp_oid(
'<>'),
6372$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6375CREATE OR REPLACE
FUNCTION rv_cmp_ge(
6376 a random_variable, b random_variable)
RETURNS UUID
AS
6378 SELECT provsql.provenance_cmp(
6380 provsql.random_variable_cmp_oid(
'>='),
6382$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6385CREATE OR REPLACE
FUNCTION rv_cmp_gt(
6386 a random_variable, b random_variable)
RETURNS UUID
AS
6388 SELECT provsql.provenance_cmp(
6390 provsql.random_variable_cmp_oid(
'>'),
6392$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
6395 LEFTARG = random_variable,
6396 RIGHTARG = random_variable,
6397 PROCEDURE = random_variable_plus,
6402 LEFTARG = random_variable,
6403 RIGHTARG = random_variable,
6404 PROCEDURE = random_variable_minus
6408 LEFTARG = random_variable,
6409 RIGHTARG = random_variable,
6410 PROCEDURE = random_variable_times,
6415 LEFTARG = random_variable,
6416 RIGHTARG = random_variable,
6417 PROCEDURE = random_variable_div
6422 RIGHTARG = random_variable,
6423 PROCEDURE = random_variable_neg
6427 LEFTARG = random_variable,
6428 RIGHTARG = random_variable,
6429 PROCEDURE = random_variable_pow
6433 LEFTARG = random_variable,
6434 RIGHTARG = random_variable,
6435 PROCEDURE = random_variable_lt,
6441 LEFTARG = random_variable,
6442 RIGHTARG = random_variable,
6443 PROCEDURE = random_variable_le,
6449 LEFTARG = random_variable,
6450 RIGHTARG = random_variable,
6451 PROCEDURE = random_variable_eq,
6457 LEFTARG = random_variable,
6458 RIGHTARG = random_variable,
6459 PROCEDURE = random_variable_ne,
6465 LEFTARG = random_variable,
6466 RIGHTARG = random_variable,
6467 PROCEDURE = random_variable_ge,
6481CREATE OR REPLACE
FUNCTION agg_token_btree_cmp(a AGG_TOKEN, b AGG_TOKEN)
6482 RETURNS INTEGER AS 'MODULE_PATHNAME',
'agg_token_btree_cmp'
6483 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
6485CREATE OPERATOR CLASS agg_token_ops
6486 DEFAULT FOR TYPE AGG_TOKEN
USING btree
AS
6492 FUNCTION 1 agg_token_btree_cmp(AGG_TOKEN, AGG_TOKEN);
6496 LEFTARG = random_variable,
6497 RIGHTARG = random_variable,
6498 PROCEDURE = random_variable_gt,
6517CREATE OR REPLACE
FUNCTION random_variable_btree_cmp(
6518 a random_variable, b random_variable)
RETURNS INTEGER AS
6521 RAISE EXCEPTION
'comparison or ordering of random_variable values is '
6522 'meaningless: a random_variable is a distribution, not a scalar'
6524 'Compare them as a probabilistic event -- in a WHERE / JOIN clause or '
6525 'with probability(x > y); take order statistics with provsql.greatest / '
6526 'provsql.least (or the min / max aggregates); summarise numerically with '
6527 'expected / variance / support.',
6528 DETAIL =
'provsql-reason: rv-comparison; scope: out-of-scope';
6530$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
6537CREATE OPERATOR CLASS random_variable_ops
6538 DEFAULT FOR TYPE random_variable
USING btree
AS
6544 FUNCTION 1 random_variable_btree_cmp(random_variable, random_variable);
6562CREATE OR REPLACE
FUNCTION random_variable_cond(rv random_variable, cond UUID)
6563 RETURNS random_variable
AS
6571 IF cond
IS NULL OR cond = gate_one()
THEN
6581 cond := provsql.evidence_as_observation(cond);
6584 IF get_gate_type(tgt) =
'conditioned'
6585 AND array_length(get_children(tgt),
1) =
2 THEN
6588 ch := get_children(tgt);
6590 ev := provenance_times(ch[
2], cond);
6595 result := public.uuid_generate_v5(uuid_ns_provsql(),
6596 concat(
'conditioned', tgt, ev));
6597 PERFORM create_gate(
result,
'conditioned',
ARRAY[tgt, ev]);
6598 RETURN (
result)::random_variable;
6600$$
LANGUAGE plpgsql VOLATILE
SET search_path=provsql,pg_temp,public
6601 SECURITY DEFINER PARALLEL UNSAFE;
6604 LEFTARG = random_variable,
6606 PROCEDURE = random_variable_cond
6620CREATE OR REPLACE
FUNCTION random_variable_cond_predicate(
6621 rv random_variable, predicate
BOOLEAN)
RETURNS random_variable
AS
6624 RAISE EXCEPTION
'random_variable | (predicate) must be rewritten by the '
6625 'ProvSQL planner hook: the right operand must be a Boolean combination '
6626 'of random_variable comparisons (is provsql.active off?)'
6627 USING ERRCODE =
'feature_not_supported',
6628 DETAIL =
'provsql-reason: rv-operator-not-rewritten; scope: out-of-scope';
6630$$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
6633 LEFTARG = random_variable,
6635 PROCEDURE = random_variable_cond_predicate
6646CREATE OR REPLACE
FUNCTION rv_conditioned_target(token UUID)
RETURNS UUID
AS
6649 WHEN provsql.get_gate_type(token) =
'conditioned'
6650 AND array_length(provsql.get_children(token),
1) =
2
6651 THEN (provsql.get_children(token))[
1]
6654$$
LANGUAGE sql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
6664CREATE OR REPLACE
FUNCTION rv_conditioned_prov(token UUID, prov UUID)
6668 WHEN provsql.get_gate_type(token) =
'conditioned'
6669 AND array_length(provsql.get_children(token),
1) =
2
6670 THEN provsql.provenance_times((provsql.get_children(token))[
2], prov)
6673$$
LANGUAGE sql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
6705CREATE OR REPLACE
FUNCTION evidence_as_observation(ev UUID)
RETURNS UUID
AS
6713 IF ev
IS NULL OR provsql.get_gate_type(ev) <>
'cmp' THEN
6716 ch := provsql.get_children(ev);
6717 IF array_length(ch,
1) <>
2 THEN
6723 SELECT info1
INTO i1
FROM provsql.get_infos(ev);
6724 IF (
SELECT oprname
FROM pg_catalog.pg_operator
WHERE oid = i1)
IS DISTINCT FROM '=' THEN
6727 IF provsql.get_gate_type(ch[
1]) =
'rv'
6728 AND provsql.get_gate_type(ch[
2]) =
'value' THEN
6729 leaf := ch[
1]; datum_gate := ch[
2];
6730 ELSIF provsql.get_gate_type(ch[
2]) =
'rv'
6731 AND provsql.get_gate_type(ch[
1]) =
'value' THEN
6732 leaf := ch[
2]; datum_gate := ch[
1];
6736 RETURN provsql.observe((leaf)::random_variable,
6737 provsql.get_extra(datum_gate)::
double precision);
6739$$
LANGUAGE plpgsql VOLATILE
6740 SET search_path=provsql,pg_temp,public SECURITY DEFINER PARALLEL UNSAFE;
6763CREATE OR REPLACE
FUNCTION observe(x random_variable, datum
double precision)
6767 leaf UUID := (x)::UUID;
6770 IF provsql.get_gate_type(leaf) <>
'rv' THEN
6771 RAISE EXCEPTION
'provsql.observe: the argument must be a bare '
6772 'random-variable leaf (a gate_rv), got a % gate', provsql.get_gate_type(leaf)
6773 USING HINT =
'observe binds a datum to a single distribution leaf; '
6774 'observing a derived quantity (a sum, product, or comparison) needs '
6775 'a change-of-variables density and is out of scope.',
6776 DETAIL =
'provsql-reason: observe-argument-kind; scope: out-of-scope';
6778 IF NOT provsql.is_finite_float8(datum)
THEN
6779 RAISE EXCEPTION
'provsql.observe: datum must be finite (got %)', datum;
6781 result := public.uuid_generate_v4();
6782 PERFORM provsql.create_gate(
result,
'observe',
ARRAY[leaf],
NULL,
NULL, datum::TEXT);
6785$$
LANGUAGE plpgsql VOLATILE
6786 SET search_path=provsql,pg_temp,public SECURITY DEFINER PARALLEL UNSAFE;
6795CREATE OR REPLACE
FUNCTION and_agg_sfunc(state UUID, ev UUID)
6798 SELECT provsql.provenance_times(state, ev);
6799$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
6809CREATE AGGREGATE and_agg(UUID) (
6810 SFUNC = and_agg_sfunc,
6822CREATE OR REPLACE
FUNCTION evidence(evidence UUID)
6823 RETURNS double precision
6824 AS 'provsql',
'rv_evidence' LANGUAGE C STRICT PARALLEL SAFE;
6835CREATE OR REPLACE
FUNCTION observe_atoms(evidence UUID)
6838 WITH RECURSIVE walk(tok)
AS (
6842 FROM walk,
LATERAL unnest(provsql.get_children(walk.tok))
AS c
6843 WHERE provsql.get_gate_type(walk.tok) =
'times'
6845 SELECT array_agg(tok
ORDER BY tok)
6847 WHERE provsql.get_gate_type(tok) =
'observe';
6848$$
LANGUAGE sql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
6872CREATE OR REPLACE
FUNCTION shapley_observe(
6873 target UUID, evidence UUID, payoff TEXT
DEFAULT 'expected')
6874 RETURNS TABLE(observation UUID,
value double precision)
AS
6880 pv
double precision[];
6882 fact
double precision[];
6889 sh
double precision;
6893 IF payoff
NOT IN (
'expected',
'variance')
THEN
6894 RAISE EXCEPTION
'provsql.shapley_observe: payoff must be ''expected'' or '
6895 '''variance'' (got %)', payoff;
6897 atoms := provsql.observe_atoms(evidence);
6898 n := coalesce(array_length(atoms,
1),
0);
6900 RAISE EXCEPTION
'provsql.shapley_observe: evidence contains no observe() '
6901 'atoms (got a % gate)', provsql.get_gate_type(evidence)
6902 USING ERRCODE =
'feature_not_supported',
6903 DETAIL =
'provsql-reason: shapley-observe-no-evidence; scope: out-of-scope';
6906 RAISE EXCEPTION
'provsql.shapley_observe: exact attribution over % '
6907 'observations is exponential; capped at 12 (sampling-based '
6908 'attribution is future work)', n
6909 USING ERRCODE =
'feature_not_supported',
6910 DETAIL =
'provsql-reason: shapley-observe-too-many; scope: out-of-scope';
6914 fact :=
ARRAY[
1::
double precision];
6915 FOR i
IN 1..n LOOP fact := fact || (fact[i] * i);
END LOOP;
6918 pv := array_fill(
NULL::
double precision,
ARRAY[nmasks]);
6919 popc := array_fill(
0,
ARRAY[nmasks]);
6922 FOR mask
IN 0 .. nmasks -
1 LOOP
6923 subset :=
ARRAY[]::UUID[];
6925 FOR i
IN 0 .. n -
1 LOOP
6926 IF (mask >> i) &
1 =
1 THEN
6927 subset := subset || atoms[i +
1];
6931 popc[mask +
1] := cnt;
6933 ev_s := provsql.gate_one();
6935 ev_s := provsql.provenance_times(VARIADIC subset);
6937 IF payoff =
'expected' THEN
6938 pv[mask +
1] := provsql.rv_moment(target,
1,
false, ev_s);
6940 pv[mask +
1] := provsql.rv_moment(target,
2,
true, ev_s);
6945 FOR i
IN 0 .. n -
1 LOOP
6948 FOR mask
IN 0 .. nmasks -
1 LOOP
6949 IF (mask >> i) &
1 =
0 THEN
6950 s_size := popc[mask +
1];
6952 sh := sh + (fact[s_size +
1] * fact[n - s_size] / fact[n +
1])
6953 * (pv[(mask | bit) +
1] - pv[mask +
1]);
6956 observation := atoms[i +
1];
6961$$
LANGUAGE plpgsql VOLATILE
6962 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
6992CREATE OR REPLACE
FUNCTION "greatest"(VARIADIC args random_variable[])
6993 RETURNS random_variable
AS
6998 IF args
IS NULL THEN
7001 SELECT array_agg(
DISTINCT (a)::UUID)
INTO children
7002 FROM unnest(args) a
WHERE a
IS NOT NULL;
7003 IF children
IS NULL OR array_length(children,
1)
IS NULL THEN
7006 IF array_length(children,
1) =
1 THEN
7007 RETURN provsql.random_variable_make(children[
1]);
7009 RETURN provsql.random_variable_make(
7010 provsql.provenance_arith(
5, children));
7012$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7014CREATE OR REPLACE
FUNCTION "least"(VARIADIC args random_variable[])
7015 RETURNS random_variable
AS
7020 IF args
IS NULL THEN
7023 SELECT array_agg(
DISTINCT (a)::UUID)
INTO children
7024 FROM unnest(args) a
WHERE a
IS NOT NULL;
7025 IF children
IS NULL OR array_length(children,
1)
IS NULL THEN
7028 IF array_length(children,
1) =
1 THEN
7029 RETURN provsql.random_variable_make(children[
1]);
7031 RETURN provsql.random_variable_make(
7032 provsql.provenance_arith(
6, children));
7034$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7045CREATE OR REPLACE
FUNCTION rv_case(
7048RETURNS random_variable
AS
7050 SELECT provsql.random_variable_make(provsql.provenance_case(children));
7051$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7067CREATE OR REPLACE
FUNCTION agg_case(
7072 SELECT format(
'( %s , %s )', t::TEXT,
7073 coalesce(provsql.agg_gate_value(t)::TEXT,
''))::provsql.AGG_TOKEN
7074 FROM (
SELECT provsql.provenance_case(children)
AS t)
AS s;
7075$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7120CREATE OR REPLACE
FUNCTION rv_aggregate_semimod(
7121 prov UUID, rv random_variable)
7122 RETURNS random_variable
AS
7124 SELECT provsql.mixture(prov, rv, provsql.as_random(
0::
double precision));
7125$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7139CREATE OR REPLACE
FUNCTION rv_aggregate_semimod(
7140 prov UUID, rv random_variable,
identity double precision)
7141 RETURNS random_variable
AS
7143 SELECT provsql.mixture(prov, rv, provsql.as_random(
identity));
7144$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7158CREATE OR REPLACE
FUNCTION rv_aggregate_indicator(prov UUID)
7159 RETURNS random_variable
AS
7161 SELECT provsql.rv_aggregate_semimod(prov, provsql.as_random(
1::
double precision));
7162$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7175CREATE OR REPLACE
FUNCTION rv_aggregate_indicator(prov UUID, rv random_variable)
7176 RETURNS random_variable
AS
7178 SELECT CASE WHEN rv
IS NULL THEN NULL
7179 ELSE provsql.rv_aggregate_indicator(prov)
END;
7180$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7191CREATE OR REPLACE
FUNCTION sum_rv_sfunc(
7192 state UUID[], rv random_variable)
7196 WHEN rv
IS NULL THEN state
7197 ELSE array_append(state, (rv)::UUID)
7199$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7214CREATE OR REPLACE
FUNCTION sum_rv_ffunc(state UUID[])
7215 RETURNS random_variable
AS
7220 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7223 IF array_length(state,
1) =
1 THEN
7224 RETURN provsql.random_variable_make(state[
1]);
7226 arith_token := provsql.provenance_arith(
0, state);
7227 RETURN provsql.random_variable_make(arith_token);
7229$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7231CREATE AGGREGATE sum(random_variable) (
7232 SFUNC = sum_rv_sfunc,
7235 FINALFUNC = sum_rv_ffunc
7251CREATE OR REPLACE
FUNCTION rv_sum_or_null_ffunc(state UUID[])
7252 RETURNS random_variable
AS
7255 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7258 IF array_length(state,
1) =
1 THEN
7259 RETURN provsql.random_variable_make(state[
1]);
7261 RETURN provsql.random_variable_make(
7262 provsql.provenance_arith(
0, state));
7264$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7266CREATE AGGREGATE rv_sum_or_null(random_variable) (
7267 SFUNC = sum_rv_sfunc,
7270 FINALFUNC = rv_sum_or_null_ffunc
7297CREATE OR REPLACE
FUNCTION avg_rv_ffunc(state UUID[])
7298 RETURNS random_variable
AS
7305 denom_state UUID[] :=
'{}';
7308 IF state
IS NULL THEN
7311 n := array_length(state,
1);
7316 one_uuid := (provsql.as_random(
1::
double precision))::UUID;
7318 denom_state := array_append(denom_state, one_uuid);
7322 num_token := state[
1];
7323 denom_token := denom_state[
1];
7325 num_token := provsql.provenance_arith(
0, state);
7326 denom_token := provsql.provenance_arith(
0, denom_state);
7329 RETURN provsql.random_variable_make(
7330 provsql.provenance_arith(
7332 ARRAY[num_token, denom_token]));
7334$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7336CREATE AGGREGATE avg(random_variable) (
7337 SFUNC = sum_rv_sfunc,
7340 FINALFUNC = avg_rv_ffunc
7368CREATE OR REPLACE
FUNCTION product_rv_ffunc(state UUID[])
7369 RETURNS random_variable
AS
7372 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7375 IF array_length(state,
1) =
1 THEN
7376 RETURN provsql.random_variable_make(state[
1]);
7378 RETURN provsql.random_variable_make(
7379 provsql.provenance_arith(
1, state));
7381$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7383CREATE AGGREGATE product(random_variable) (
7384 SFUNC = sum_rv_sfunc,
7387 FINALFUNC = product_rv_ffunc
7415CREATE OR REPLACE
FUNCTION extremum_rv_ffunc(
7416 state UUID[], op
INTEGER,
identity double precision)
7417 RETURNS random_variable
AS
7420 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7423 IF array_length(state,
1) =
1 THEN
7424 RETURN provsql.random_variable_make(state[
1]);
7426 RETURN provsql.random_variable_make(
7427 provsql.provenance_arith(op, state));
7429$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
7431CREATE OR REPLACE
FUNCTION max_rv_ffunc(state UUID[])
7432 RETURNS random_variable
AS
7435 SELECT provsql.extremum_rv_ffunc(state,
5,
'-Infinity'::
double precision);
7436$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7438CREATE OR REPLACE
FUNCTION min_rv_ffunc(state UUID[])
7439 RETURNS random_variable
AS
7442 SELECT provsql.extremum_rv_ffunc(state,
6,
'Infinity'::
double precision);
7443$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7445CREATE AGGREGATE max(random_variable) (
7446 SFUNC = sum_rv_sfunc,
7449 FINALFUNC = max_rv_ffunc
7452CREATE AGGREGATE min(random_variable) (
7453 SFUNC = sum_rv_sfunc,
7456 FINALFUNC = min_rv_ffunc
7484CREATE OR REPLACE
FUNCTION rv_stat1_sfunc(state UUID[], x random_variable)
7488 WHEN x
IS NULL THEN state
7489 ELSE state ||
ARRAY[(provsql.as_random(
1::
double precision))::UUID,
7492$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7498CREATE OR REPLACE
FUNCTION rv_stat2_sfunc(
7499 state UUID[], x random_variable, y random_variable)
7503 WHEN x
IS NULL OR y
IS NULL THEN state
7504 ELSE state ||
ARRAY[(provsql.as_random(
1::
double precision))::UUID,
7505 (x)::UUID, (y)::UUID]
7507$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7512CREATE OR REPLACE
FUNCTION rv_stat1_impl_sfunc(
7513 state UUID[], ind random_variable, x random_variable)
7517 WHEN x
IS NULL THEN state
7518 ELSE state ||
ARRAY[coalesce((ind)::UUID,
7519 (provsql.as_random(
1::
double precision))::UUID),
7522$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7526CREATE OR REPLACE
FUNCTION rv_stat2_impl_sfunc(
7527 state UUID[], ind random_variable, x random_variable, y random_variable)
7531 WHEN x
IS NULL OR y
IS NULL THEN state
7532 ELSE state ||
ARRAY[coalesce((ind)::UUID,
7533 (provsql.as_random(
1::
double precision))::UUID),
7534 (x)::UUID, (y)::UUID]
7536$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7553CREATE OR REPLACE
FUNCTION rv_stat_sum_tokens(
7554 state UUID[], stride
INTEGER,
7555 OUT n_tok UUID,
OUT sx_tok UUID,
OUT sxx_tok UUID,
7556 OUT sy_tok UUID,
OUT sxy_tok UUID,
OUT syy_tok UUID)
7560 nrows
INTEGER := coalesce(array_length(state,
1),
0) / stride;
7561 inds UUID[] :=
'{}';
7571 FOR i
IN 1..nrows LOOP
7572 ind := state[(i-1) * stride +
1];
7573 x := state[(i-1) * stride +
2];
7574 inds := array_append(inds, ind);
7575 xs := array_append(xs, provenance_arith(
1,
ARRAY[ind, x]));
7576 xxs := array_append(xxs, provenance_arith(
1,
ARRAY[ind, x, x]));
7578 y := state[(i-1) * stride +
3];
7579 ys := array_append(ys, provenance_arith(
1,
ARRAY[ind, y]));
7580 xys := array_append(xys, provenance_arith(
1,
ARRAY[ind, x, y]));
7581 yys := array_append(yys, provenance_arith(
1,
ARRAY[ind, y, y]));
7584 n_tok := provenance_arith(
0, inds);
7585 sx_tok := provenance_arith(
0, xs);
7586 sxx_tok := provenance_arith(
0, xxs);
7588 sy_tok := provenance_arith(
0, ys);
7589 sxy_tok := provenance_arith(
0, xys);
7590 syy_tok := provenance_arith(
0, yys);
7593$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7594 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7598CREATE OR REPLACE
FUNCTION rv_stat_var_pop_token(
7599 n_tok UUID, s_tok UUID, ss_tok UUID)
7602 SELECT provsql.provenance_arith(
2,
ARRAY[
7603 provsql.provenance_arith(
3,
ARRAY[ss_tok, n_tok]),
7604 provsql.provenance_arith(
1,
ARRAY[
7605 provsql.provenance_arith(
3,
ARRAY[s_tok, n_tok]),
7606 provsql.provenance_arith(
3,
ARRAY[s_tok, n_tok])])]);
7607$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7612CREATE OR REPLACE
FUNCTION rv_stat_var_samp_token(
7613 n_tok UUID, s_tok UUID, ss_tok UUID)
7616 SELECT provsql.provenance_arith(
3,
ARRAY[
7617 provsql.provenance_arith(
2,
ARRAY[
7619 provsql.provenance_arith(
3,
ARRAY[
7620 provsql.provenance_arith(
1,
ARRAY[s_tok, s_tok]), n_tok])]),
7621 provsql.provenance_arith(
2,
ARRAY[
7622 n_tok, (provsql.as_random(
1::
double precision))::UUID])]);
7623$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7628CREATE OR REPLACE
FUNCTION rv_stat_sqrt_token(v_tok UUID)
7631 SELECT provsql.provenance_arith(
7,
ARRAY[
7632 provsql.provenance_arith(
5,
ARRAY[
7633 v_tok, (provsql.as_random(
0::
double precision))::UUID]),
7634 (provsql.as_random(
0.
5::
double precision))::UUID]);
7635$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7639CREATE OR REPLACE
FUNCTION rv_stat_covar_pop_token(
7640 n_tok UUID, sx_tok UUID, sy_tok UUID, sxy_tok UUID)
7643 SELECT provsql.provenance_arith(
2,
ARRAY[
7644 provsql.provenance_arith(
3,
ARRAY[sxy_tok, n_tok]),
7645 provsql.provenance_arith(
1,
ARRAY[
7646 provsql.provenance_arith(
3,
ARRAY[sx_tok, n_tok]),
7647 provsql.provenance_arith(
3,
ARRAY[sy_tok, n_tok])])]);
7648$$
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;
7651CREATE OR REPLACE
FUNCTION covar_pop_rv_ffunc(state UUID[])
7652 RETURNS random_variable
AS
7657 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7660 SELECT *
INTO t
FROM rv_stat_sum_tokens(state,
3);
7661 RETURN random_variable_make(
7662 rv_stat_covar_pop_token(t.n_tok, t.sx_tok, t.sy_tok, t.sxy_tok));
7664$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7665 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7669CREATE OR REPLACE
FUNCTION covar_samp_rv_ffunc(state UUID[])
7670 RETURNS random_variable
AS
7675 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7678 SELECT *
INTO t
FROM rv_stat_sum_tokens(state,
3);
7679 RETURN random_variable_make(
7680 provenance_arith(
3,
ARRAY[
7681 provenance_arith(
2,
ARRAY[
7683 provenance_arith(
3,
ARRAY[
7684 provenance_arith(
1,
ARRAY[t.sx_tok, t.sy_tok]), t.n_tok])]),
7685 provenance_arith(
2,
ARRAY[
7686 t.n_tok, (as_random(
1::
double precision))::UUID])]));
7688$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7689 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7695CREATE OR REPLACE
FUNCTION corr_rv_ffunc(state UUID[])
7696 RETURNS random_variable
AS
7703 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7706 SELECT *
INTO t
FROM rv_stat_sum_tokens(state,
3);
7707 vx := rv_stat_var_pop_token(t.n_tok, t.sx_tok, t.sxx_tok);
7708 vy := rv_stat_var_pop_token(t.n_tok, t.sy_tok, t.syy_tok);
7709 RETURN random_variable_make(
7710 provenance_arith(
3,
ARRAY[
7711 rv_stat_covar_pop_token(t.n_tok, t.sx_tok, t.sy_tok, t.sxy_tok),
7712 rv_stat_sqrt_token(provenance_arith(
1,
ARRAY[vx, vy]))]));
7714$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7715 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7718CREATE OR REPLACE
FUNCTION stddev_pop_rv_ffunc(state UUID[])
7719 RETURNS random_variable
AS
7724 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7727 SELECT *
INTO t
FROM rv_stat_sum_tokens(state,
2);
7728 RETURN random_variable_make(
7730 rv_stat_var_pop_token(t.n_tok, t.sx_tok, t.sxx_tok)));
7732$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7733 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7736CREATE OR REPLACE
FUNCTION stddev_samp_rv_ffunc(state UUID[])
7737 RETURNS random_variable
AS
7742 IF state
IS NULL OR array_length(state,
1)
IS NULL THEN
7745 SELECT *
INTO t
FROM rv_stat_sum_tokens(state,
2);
7746 RETURN random_variable_make(
7748 rv_stat_var_samp_token(t.n_tok, t.sx_tok, t.sxx_tok)));
7750$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7751 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7753CREATE AGGREGATE covar_pop(random_variable, random_variable) (
7754 SFUNC = rv_stat2_sfunc,
7757 FINALFUNC = covar_pop_rv_ffunc
7760CREATE AGGREGATE covar_samp(random_variable, random_variable) (
7761 SFUNC = rv_stat2_sfunc,
7764 FINALFUNC = covar_samp_rv_ffunc
7767CREATE AGGREGATE corr(random_variable, random_variable) (
7768 SFUNC = rv_stat2_sfunc,
7771 FINALFUNC = corr_rv_ffunc
7774CREATE AGGREGATE stddev_pop(random_variable) (
7775 SFUNC = rv_stat1_sfunc,
7778 FINALFUNC = stddev_pop_rv_ffunc
7781CREATE AGGREGATE stddev_samp(random_variable) (
7782 SFUNC = rv_stat1_sfunc,
7785 FINALFUNC = stddev_samp_rv_ffunc
7791CREATE AGGREGATE rv_covar_pop_impl(
7792 random_variable, random_variable, random_variable) (
7793 SFUNC = rv_stat2_impl_sfunc,
7796 FINALFUNC = covar_pop_rv_ffunc
7799CREATE AGGREGATE rv_covar_samp_impl(
7800 random_variable, random_variable, random_variable) (
7801 SFUNC = rv_stat2_impl_sfunc,
7804 FINALFUNC = covar_samp_rv_ffunc
7807CREATE AGGREGATE rv_corr_impl(
7808 random_variable, random_variable, random_variable) (
7809 SFUNC = rv_stat2_impl_sfunc,
7812 FINALFUNC = corr_rv_ffunc
7815CREATE AGGREGATE rv_stddev_pop_impl(random_variable, random_variable) (
7816 SFUNC = rv_stat1_impl_sfunc,
7819 FINALFUNC = stddev_pop_rv_ffunc
7822CREATE AGGREGATE rv_stddev_samp_impl(random_variable, random_variable) (
7823 SFUNC = rv_stat1_impl_sfunc,
7826 FINALFUNC = stddev_samp_rv_ffunc
7841CREATE OR REPLACE
FUNCTION rv_percentile_make(fraction
double precision,
7843 RETURNS random_variable
AS
7848 IF fraction
IS NULL THEN
7851 IF fraction <
0 OR fraction >
1 THEN
7853 'percentile_cont: fraction must be between 0 and 1 (got %)', fraction;
7855 token := public.uuid_generate_v5(
7857 concat(
'arith',
'10', pairs::TEXT, fraction::TEXT));
7859 PERFORM create_gate(token,
'arith', pairs,
10,
NULL, fraction::TEXT);
7860 RETURN random_variable_make(token);
7862$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
7863 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
7872CREATE OR REPLACE
FUNCTION percentile_cont_rv_sfunc(
7873 state UUID[], x random_variable)
7876 SELECT provsql.rv_stat1_sfunc(state, x);
7877$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7881CREATE OR REPLACE
FUNCTION percentile_cont_rv_ffunc(
7882 state UUID[], fraction
double precision)
7883 RETURNS random_variable
AS
7886 WHEN state
IS NULL OR array_length(state,
1)
IS NULL THEN NULL
7887 ELSE provsql.rv_percentile_make(fraction, state)
7889$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7891CREATE AGGREGATE percentile_cont(
double precision ORDER BY random_variable) (
7892 SFUNC = percentile_cont_rv_sfunc,
7895 FINALFUNC = percentile_cont_rv_ffunc
7900CREATE TYPE rv_percentile_state
AS (
7901 fraction
double precision,
7908CREATE OR REPLACE
FUNCTION rv_percentile_impl_sfunc(
7909 state rv_percentile_state, fraction
double precision,
7910 ind random_variable, x random_variable)
7911 RETURNS rv_percentile_state
AS
7914 coalesce((state).fraction, fraction),
7916 WHEN x
IS NULL THEN (state).tokens
7917 ELSE (state).tokens ||
7918 ARRAY[coalesce((ind)::UUID,
7919 (provsql.as_random(
1::
double precision))::UUID),
7921 END)::provsql.rv_percentile_state;
7922$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7925CREATE OR REPLACE
FUNCTION rv_percentile_impl_ffunc(state rv_percentile_state)
7926 RETURNS random_variable
AS
7929 WHEN state
IS NULL OR array_length((state).tokens,
1)
IS NULL THEN NULL
7930 ELSE provsql.rv_percentile_make((state).fraction, (state).tokens)
7932$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
7934CREATE AGGREGATE rv_percentile_impl(
7935 double precision, random_variable, random_variable) (
7936 SFUNC = rv_percentile_impl_sfunc,
7937 STYPE = rv_percentile_state,
7938 INITCOND =
'(,"{}")',
7939 FINALFUNC = rv_percentile_impl_ffunc
7963CREATE OR REPLACE
FUNCTION provenance_delta
7966 'provsql',
'provenance_delta' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
7990CREATE OR REPLACE
FUNCTION provenance_aggregate(
7995 is_scalar
BOOLEAN DEFAULT false)
7996 RETURNS AGG_TOKEN
AS
7997 'provsql',
'provenance_aggregate' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
8011CREATE OR REPLACE
FUNCTION provenance_semimod(val ANYELEMENT, token UUID)
8013 'provsql',
'provenance_semimod' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
8027CREATE FUNCTION provenance_semimod_nested(val AGG_TOKEN, token UUID)
8029 'provsql',
'provenance_semimod_nested' LANGUAGE C PARALLEL SAFE IMMUTABLE;
8043CREATE FUNCTION provenance_semimod_flat(val AGG_TOKEN, token UUID)
8045 'provsql',
'provenance_semimod_flat' LANGUAGE C PARALLEL SAFE IMMUTABLE;
8049CREATE AGGREGATE provenance_contributions_cat(UUID[]) (
8069CREATE OR REPLACE
FUNCTION provenance_semimod_nullable(val ANYELEMENT, token UUID)
8071 'provsql',
'provenance_semimod_nullable' LANGUAGE C COST
100 PARALLEL SAFE IMMUTABLE;
8088CREATE OR REPLACE
FUNCTION row_number_as_rank(rank AGG_TOKEN, row_number
bigint,
8089 tied
BOOLEAN DEFAULT false)
8091 AS 'provsql',
'row_number_as_rank' LANGUAGE C VOLATILE PARALLEL SAFE;
8094CREATE OR REPLACE
FUNCTION order_determined_transfn(internal,
INTEGER, VARIADIC
"any")
8096 AS 'provsql',
'order_determined_transfn' LANGUAGE C PARALLEL SAFE;
8099CREATE OR REPLACE
FUNCTION order_determined_finalfn(internal)
8101 AS 'provsql',
'order_determined_finalfn' LANGUAGE C PARALLEL SAFE;
8114CREATE AGGREGATE order_determined(nvals
INTEGER, VARIADIC
"any") (
8115 SFUNC = order_determined_transfn,
8117 FINALFUNC = order_determined_finalfn
8129CREATE OR REPLACE
FUNCTION order_checked(val ANYELEMENT, determined
BOOLEAN,
8130 is_window
BOOLEAN DEFAULT false)
8132 AS 'provsql',
'order_checked' LANGUAGE C VOLATILE PARALLEL SAFE;
8146CREATE OR REPLACE
FUNCTION ntile_as_rank(bucket AGG_TOKEN, sql_bucket
bigint)
8148 AS 'provsql',
'ntile_as_rank' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
8163CREATE OR REPLACE
FUNCTION agg_token_null_event(aggtok AGG_TOKEN, want_null
BOOLEAN)
8170 IF aggtok
IS NULL THEN
8171 RETURN CASE WHEN want_null
THEN provsql.gate_one()
ELSE provsql.gate_zero()
END;
8173 RETURN provsql.agg_gate_null_event(provsql.agg_token_uuid(aggtok), want_null);
8175$$
LANGUAGE plpgsql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
8188CREATE OR REPLACE
FUNCTION agg_count_can_be_zero(cnt AGG_TOKEN)
8191 SELECT cnt
IS NULL OR provsql.get_gate_type(provsql.agg_token_uuid(cnt)) <>
'agg'
8192 OR NOT EXISTS (
SELECT 1 FROM unnest(provsql.get_children(provsql.agg_token_uuid(cnt))) c
8193 WHERE provsql.get_gate_type(c) =
'semimod')
8195 FROM unnest(provsql.get_children(provsql.agg_token_uuid(cnt))) c
8196 WHERE provsql.get_gate_type(c) =
'semimod'
8197 AND provsql.get_extra((provsql.get_children(c))[
2]) =
'0')
8198$$
LANGUAGE sql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
8210CREATE OR REPLACE
FUNCTION agg_gate_null_event(g UUID, want_null
BOOLEAN)
8214 t provsql.PROVENANCE_GATE;
8222 t := provsql.get_gate_type(g);
8224 SELECT provsql.provenance_plus(coalesce(array_agg((provsql.get_children(c))[
1]),
8227 FROM unnest(provsql.get_children(g))
AS c
8228 WHERE provsql.get_gate_type(c) =
'semimod';
8229 RETURN CASE WHEN want_null
THEN provsql.provenance_monus(provsql.gate_one(), p)
8231 ELSIF t =
'arith' THEN
8232 SELECT array_agg(provsql.agg_gate_null_event(c, want_null))
8234 FROM unnest(provsql.get_children(g))
AS c;
8235 IF parts
IS NULL THEN
8236 RETURN CASE WHEN want_null
THEN provsql.gate_zero()
ELSE provsql.gate_one()
END;
8239 RETURN CASE WHEN want_null
THEN provsql.provenance_plus(parts)
8240 ELSE provsql.provenance_times(VARIADIC parts)
END;
8241 ELSIF t =
'value' THEN
8242 RETURN CASE WHEN (coalesce(provsql.get_extra(g),
'')
IN (
'',
'NULL')) = want_null
8243 THEN provsql.gate_one()
ELSE provsql.gate_zero()
END;
8244 ELSIF t =
'case' THEN
8250 parts :=
ARRAY[]::UUID[];
8251 prev :=
ARRAY[]::UUID[];
8252 ch := provsql.get_children(g);
8253 FOR i
IN 1 .. coalesce(array_length(ch,
1),
0)
BY 2 LOOP
8254 IF i = array_length(ch,
1)
THEN
8255 region :=
CASE WHEN cardinality(prev) =
0 THEN provsql.gate_one()
8256 ELSE provsql.provenance_monus(provsql.gate_one(),
8257 provsql.provenance_plus(prev))
8261 region :=
CASE WHEN cardinality(prev) =
0 THEN ch[i]
8262 ELSE provsql.provenance_times(
8264 provsql.provenance_monus(provsql.gate_one(),
8265 provsql.provenance_plus(prev)))
8268 prev := prev || ch[i];
8270 parts := parts || provsql.provenance_times(
8271 region, provsql.agg_gate_null_event(arm, want_null));
8273 RETURN provsql.provenance_plus(parts);
8275 RAISE EXCEPTION
USING
8276 MESSAGE =
'ProvSQL: IS [NOT] NULL on this expression over aggregates, read '
8277 'from a subquery, is not supported',
8278 DETAIL =
'provsql-reason: nulltest-not-aggregate; scope: gap',
8279 ERRCODE =
'feature_not_supported';
8281$$
LANGUAGE plpgsql STABLE PARALLEL SAFE
SET search_path=provsql,pg_temp,public;
8297CREATE OR REPLACE
FUNCTION window_distinct_tokens(vals anyarray, tokens UUID[])
8300 SELECT array_agg(s
ORDER BY s)
8301 FROM (
SELECT provsql.provenance_semimod(
1, provsql.provenance_plus(array_agg(tokens[i])))
AS s
8302 FROM generate_subscripts(vals,
1)
AS i
GROUP BY vals[i]) g
8303$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
8318CREATE OR REPLACE
FUNCTION plain(
value ANYELEMENT)
8319 RETURNS ANYELEMENT
AS
8320$$
SELECT value $$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
8346CREATE OR REPLACE
FUNCTION probability_evaluate(
8349 arguments TEXT =
NULL)
8350 RETURNS DOUBLE PRECISION AS
8351 'provsql',
'probability_evaluate' LANGUAGE C VOLATILE PARALLEL UNSAFE;
8366CREATE OR REPLACE
FUNCTION probability(
8369 arguments TEXT =
NULL)
8370 RETURNS DOUBLE PRECISION AS
8371 'provsql',
'probability_evaluate' LANGUAGE C VOLATILE PARALLEL UNSAFE;
8400CREATE OR REPLACE
FUNCTION probability(
8403 arguments TEXT =
NULL)
8404 RETURNS DOUBLE PRECISION AS
8406 SELECT predicate::
INTEGER::
double precision;
8407$$
LANGUAGE sql IMMUTABLE PARALLEL SAFE;
8416CREATE OR REPLACE
FUNCTION probability_bounds(
8418 OUT lower
DOUBLE PRECISION,
8419 OUT upper DOUBLE PRECISION)
AS
8420 'provsql',
'probability_bounds' LANGUAGE C STABLE;
8443CREATE OR REPLACE
FUNCTION expected(
8445 prov UUID = gate_one(),
8447 arguments TEXT =
NULL)
8448 RETURNS DOUBLE PRECISION AS $$
8449 SELECT moment(
input,
1, prov,
method, arguments);
8450$$
LANGUAGE sql PARALLEL UNSAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
8463CREATE OR REPLACE
FUNCTION rv_moment(
8464 token UUID, k
INTEGER, central
BOOLEAN,
8465 prov UUID
DEFAULT gate_one())
8466 RETURNS double precision
8467 AS 'provsql',
'rv_moment' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
8473CREATE OR REPLACE
FUNCTION agg_avg_moment_exact(token UUID, k
INTEGER)
8474 RETURNS double precision
8475 AS 'provsql',
'agg_avg_moment_exact' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
8483CREATE OR REPLACE
FUNCTION agg_collapsed_moment(token UUID, k
INTEGER)
8484 RETURNS double precision
8485 AS 'provsql',
'agg_collapsed_moment' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
8492CREATE OR REPLACE
FUNCTION agg_collapsed_moments(token UUID)
8493 RETURNS double precision[]
8494 AS 'provsql',
'agg_collapsed_moments' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
8502CREATE OR REPLACE
FUNCTION agg_row_components(token UUID)
8504 AS 'provsql',
'agg_row_components' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
8522CREATE OR REPLACE
FUNCTION agg_defined_event(token UUID)
8525 gt PROVENANCE_GATE := get_gate_type(token);
8532 running_neg UUID := gate_one();
8533 parts UUID[] :=
'{}';
8535 IF token = gate_null()
THEN
8539 SELECT proname
INTO fname
8540 FROM pg_proc
WHERE oid = (get_infos(token)).info1;
8544 IF fname =
'count' AND (get_infos(token)).info2 <
0 THEN
8547 SELECT array_agg((get_children(c))[
1])
INTO toks
8548 FROM unnest(get_children(token))
AS c;
8549 IF toks
IS NULL THEN
8552 RETURN provenance_plus(toks);
8553 ELSIF gt =
'case' THEN
8554 wires := get_children(token);
8555 nw := array_length(wires,
1);
8558 parts := parts || provenance_times(
8559 running_neg, wires[
2 * i -
1],
8560 agg_defined_event(wires[
2 * i]));
8561 running_neg := provenance_times(running_neg,
8562 provenance_not(wires[
2 * i -
1]));
8564 parts := parts || provenance_times(running_neg,
8565 agg_defined_event(wires[nw]));
8566 RETURN provenance_plus(parts);
8567 ELSIF gt =
'arith' THEN
8576 SELECT array_agg(agg_defined_event(c))
INTO parts
8577 FROM unnest(get_children(token))
AS c;
8578 IF parts
IS NULL OR array_length(parts,
1)
IS NULL THEN
8581 RETURN provenance_times(VARIADIC parts);
8587$$
LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
8588 SET search_path=provsql,pg_temp,public SECURITY DEFINER;
8620CREATE OR REPLACE
FUNCTION agg_raw_moment(
8623 prov UUID = gate_one(),
8625 arguments TEXT =
NULL)
8626 RETURNS DOUBLE PRECISION AS $$
8628 aggregation_function
VARCHAR;
8630 pair_children UUID[];
8637 total_probability float8;
8641 distinct_tok UUID[];
8648 IF token
IS NULL OR k
IS NULL THEN
8652 RAISE EXCEPTION
'agg_raw_moment(): k must be non-negative (got %)', k;
8674 IF get_gate_type(token) =
'case' THEN
8679 wires UUID[] := get_children(token);
8680 nw
INTEGER := array_length(get_children(token),
1);
8681 m
INTEGER := (array_length(get_children(token),
1) -
1) /
2;
8682 running_neg UUID := gate_one();
8687 def_mass float8 :=
0;
8692 prov_p := probability(prov);
8693 IF prov_p
IS NULL OR prov_p <=
0 THEN
8698 FOR ci
IN 1 .. m +
1 LOOP
8700 region_full := provenance_times(running_neg, wires[
2 * ci -
1], prov);
8701 vuid := wires[
2 * ci];
8703 provenance_times(running_neg, provenance_not(wires[
2 * ci -
1]));
8705 region_full := provenance_times(running_neg, prov);
8708 p := probability(provenance_times(region_full,
8709 agg_defined_event(vuid)));
8717 IF get_gate_type(vuid) =
'value' THEN
8718 bm := power(
CAST(get_extra(vuid)
AS float8), k);
8719 ELSIF get_gate_type(vuid)
IN (
'agg',
'case')
THEN
8720 bm := agg_raw_moment(agg_token_make(vuid,
0), k, region_full,
8723 bm := rv_moment(vuid, k,
false, region_full);
8725 total := total + p * bm;
8726 def_mass := def_mass + p;
8729 IF def_mass <=
0 THEN
8732 RETURN total / def_mass;
8736 IF get_gate_type(token) <>
'agg' THEN
8737 IF get_gate_type(token)
IN (
'arith',
'conditioned')
THEN
8741 RETURN rv_moment((token)::UUID, k,
false, prov);
8743 RAISE EXCEPTION
USING MESSAGE=
'Wrong gate type for agg_raw_moment computation',
8744 DETAIL =
'provsql-reason: moment-not-an-aggregate; scope: deliberate';
8751 SELECT pp.proname::
varchar FROM pg_proc pp
8752 WHERE oid=(get_infos(token)).info1
8753 INTO aggregation_function;
8755 child_pairs := get_children(token);
8756 n := COALESCE(array_length(child_pairs,
1),
0);
8763 IF EXISTS (
SELECT 1 FROM unnest(child_pairs)
AS c
8764 WHERE get_gate_type((get_children(c))[
2]) <>
'value')
THEN
8765 RETURN rv_moment((token)::UUID, k,
false, prov);
8768 IF aggregation_function =
'sum' OR aggregation_function =
'count' THEN
8779 is_scalar := (get_infos(token)).info2 <
0;
8782 vals :=
ARRAY[]::float8[];
8783 toks :=
ARRAY[]::UUID[];
8785 pair_children := get_children(child_pairs[i]);
8786 toks := toks || pair_children[
1];
8787 vals := vals ||
CAST(get_extra(pair_children[
2])
AS float8);
8790 WHEN aggregation_function =
'count' AND is_scalar
THEN gate_one()
8791 WHEN n =
0 THEN gate_zero()
8792 ELSE provenance_plus(toks)
END;
8797 RETURN CASE WHEN defined_tok = gate_one()
THEN 0 ELSE NULL END;
8809 IF prov = gate_one()
AND k <=
2 THEN
8810 total := agg_collapsed_moment((token)::UUID, k);
8811 IF total
IS NOT NULL THEN
8812 IF defined_tok = gate_one()
THEN
8815 prob := probability_evaluate(defined_tok,
method, arguments);
8816 IF prob
IS NULL OR prob <=
0 THEN
8819 RETURN total / prob;
8834 IF prov = gate_one()
AND k <=
2 THEN
8836 comp
INTEGER[] := agg_row_components((token)::UUID);
8837 p float8[] :=
ARRAY[]::float8[];
8844 p := p || probability_evaluate(toks[i],
method, arguments);
8845 s1 := s1 + vals[i] * p[i];
8850 ncomp := (
SELECT max(c)
FROM unnest(comp)
AS c);
8851 sc := array_fill(
0::float8,
ARRAY[ncomp]);
8854 total := total + vals[i] * vals[i] * p[i];
8855 sc[comp[i]] := sc[comp[i]] + vals[i] * p[i];
8857 total := total + s1 * s1;
8858 FOR i
IN 1..ncomp LOOP
8859 total := total - sc[i] * sc[i];
8863 CONTINUE WHEN i = j
OR comp[i] <> comp[j];
8864 pair_tok :=
ARRAY(
SELECT DISTINCT unnest(
ARRAY[toks[i], toks[j]]));
8865 total := total + vals[i] * vals[j] *
CASE
8866 WHEN array_length(pair_tok,
1) =
1 THEN p[i]
8867 ELSE probability_evaluate(provenance_times(VARIADIC pair_tok),
8868 method, arguments)
END;
8872 IF defined_tok = gate_one()
THEN
8875 prob := probability_evaluate(defined_tok,
method, arguments);
8876 IF prob
IS NULL OR prob <=
0 THEN
8879 RETURN total / prob;
8885 tup := array_fill(
1,
ARRAY[k]);
8889 prod_v := prod_v * vals[tup[j]];
8892 SELECT array_agg(
DISTINCT toks[idx])
INTO distinct_tok
8893 FROM unnest(tup)
AS idx;
8895 IF prov <> gate_one()
THEN
8896 distinct_tok := distinct_tok || prov;
8898 conj_token := provenance_times(VARIADIC distinct_tok);
8899 prob := probability_evaluate(conj_token,
method, arguments);
8901 total := total + prod_v * prob;
8904 WHILE d >=
1 AND tup[d] = n LOOP
8909 tup[d] := tup[d] +
1;
8915 IF defined_tok <> gate_one()
THEN
8916 IF prov <> gate_one()
THEN
8917 defined_tok := provenance_times(prov, defined_tok);
8919 prob := probability_evaluate(defined_tok,
method, arguments);
8920 IF prob
IS NULL OR prob <=
0 THEN
8923 RETURN total / prob;
8925 ELSIF aggregation_function =
'min' OR aggregation_function =
'max' THEN
8933 WHEN aggregation_function =
'max'
8934 THEN power(-1::float8, k)
8950 SELECT (get_children(c))[
1]
AS tok,
8951 (
CASE WHEN aggregation_function=
'max' THEN -1
ELSE 1 END)
8952 *
CAST(get_extra((get_children(c))[
2])
AS DOUBLE PRECISION)
AS v
8953 FROM UNNEST(child_pairs)
AS c
8954 )
SELECT sign_max * COALESCE(SUM(p * power(v, k)),
0)
FROM (
8956 probability_evaluate(
8957 CASE WHEN prov = gate_one()
8958 THEN provenance_monus(provenance_plus(ARRAY_AGG(t1.tok)),
8959 provenance_plus(ARRAY_AGG(t2.tok)))
8960 ELSE provenance_times(prov,
8961 provenance_monus(provenance_plus(ARRAY_AGG(t1.tok)),
8962 provenance_plus(ARRAY_AGG(t2.tok))))
END,
8963 method, arguments)
AS p
8964 FROM tok_value t1
LEFT OUTER JOIN tok_value t2
ON t1.v > t2.v
8969 SELECT probability_evaluate(
8970 CASE WHEN prov = gate_one()
8971 THEN provenance_plus(ARRAY_AGG(tok))
8972 ELSE provenance_times(prov, provenance_plus(ARRAY_AGG(tok)))
END,
8974 FROM (
SELECT (get_children(c))[
1]
AS tok
FROM UNNEST(child_pairs)
AS c) s
8975 INTO total_probability;
8977 IF total_probability <=
0 THEN
8980 RETURN total / total_probability;
8981 ELSIF aggregation_function =
'avg' THEN
9002 IF prov <> gate_one()
THEN
9004 inner_prov UUID := prov;
9008 IF get_gate_type(inner_prov) =
'delta' THEN
9009 inner_prov := (get_children(inner_prov))[
1];
9011 IF get_gate_type(inner_prov) =
'plus' THEN
9012 prov_toks := get_children(inner_prov);
9014 prov_toks :=
ARRAY[inner_prov];
9016 SELECT array_agg((get_children(c))[
1])
INTO agg_toks
9017 FROM unnest(child_pairs)
AS c;
9018 IF agg_toks <@ prov_toks
THEN
9023 IF prov = gate_one()
THEN
9024 total := agg_avg_moment_exact((token)::UUID, k);
9025 IF total
IS NOT NULL THEN
9029 RETURN rv_moment((token)::UUID, k,
false, prov);
9031 RAISE EXCEPTION
USING MESSAGE=
9032 'Cannot compute moment for aggregation function ' || aggregation_function,
9033 DETAIL =
'provsql-reason: moment-aggregate-kind; scope: gap';
9037 IF prov <> gate_one()
9039 AND total <>
'Infinity'::float8
9040 AND total <>
'-Infinity'::float8
THEN
9041 total := total / probability_evaluate(prov,
method, arguments);
9046$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
SET search_path=provsql SECURITY DEFINER;
9059CREATE OR REPLACE
FUNCTION variance(
9061 prov UUID = gate_one(),
9063 arguments TEXT =
NULL)
9064 RETURNS DOUBLE PRECISION AS $$
9069 IF pg_typeof(
input) =
'random_variable'::REGTYPE
THEN
9070 IF input IS NULL THEN
9080 RETURN provsql.rv_moment(
9081 rv_conditioned_target((
input::random_variable)::UUID),
2,
true,
9082 rv_conditioned_prov((
input::random_variable)::UUID, prov));
9085 IF pg_typeof(
input) =
'AGG_TOKEN'::REGTYPE
THEN
9086 IF input IS NULL THEN
9094 IF rv_conditioned_prov(
input::UUID, prov) = gate_one()
THEN
9095 DECLARE ms float8[];
9097 ms := agg_collapsed_moments(
9098 (agg_conditioned_target(
input::AGG_TOKEN))::UUID);
9099 IF ms
IS NOT NULL THEN
9100 RETURN ms[
2] - ms[
1] * ms[
1];
9104 m1 := agg_raw_moment(agg_conditioned_target(
input::AGG_TOKEN),
1,
9105 rv_conditioned_prov(
input::UUID, prov),
method, arguments);
9106 m2 := agg_raw_moment(agg_conditioned_target(
input::AGG_TOKEN),
2,
9107 rv_conditioned_prov(
input::UUID, prov),
method, arguments);
9108 IF m1
IS NULL OR m2
IS NULL THEN
9111 RETURN m2 - m1 * m1;
9115 IF pg_typeof(
input) =
'UUID'::REGTYPE
THEN
9116 IF input IS NULL THEN
9119 m1 := provsql.probability_evaluate(provsql.cond(
input::UUID, prov),
9121 RETURN m1 * (
1 - m1);
9124 RAISE EXCEPTION
'variance() is not yet supported for input type %', pg_typeof(
input)
9125 USING ERRCODE =
'feature_not_supported',
9126 DETAIL =
'provsql-reason: moment-input-type; scope: gap';
9128$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
SET search_path=provsql SECURITY DEFINER;
9141CREATE OR REPLACE
FUNCTION moment(
9144 prov UUID = gate_one(),
9146 arguments TEXT =
NULL)
9147 RETURNS DOUBLE PRECISION AS $$
9149 IF pg_typeof(
input) =
'random_variable'::REGTYPE
THEN
9150 IF input IS NULL OR k
IS NULL THEN
9155 RETURN provsql.rv_moment(
9156 rv_conditioned_target((
input::random_variable)::UUID), k,
false,
9157 rv_conditioned_prov((
input::random_variable)::UUID, prov));
9160 IF pg_typeof(
input) =
'AGG_TOKEN'::REGTYPE
THEN
9161 RETURN agg_raw_moment(agg_conditioned_target(
input::AGG_TOKEN), k,
9162 rv_conditioned_prov(
input::UUID, prov),
method, arguments);
9169 IF pg_typeof(
input) =
'UUID'::REGTYPE
THEN
9170 IF input IS NULL OR k
IS NULL THEN
9176 RETURN provsql.probability_evaluate(provsql.cond(
input::UUID, prov),
9180 RAISE EXCEPTION
'moment() is not yet supported for input type %', pg_typeof(
input)
9181 USING ERRCODE =
'feature_not_supported',
9182 DETAIL =
'provsql-reason: moment-input-type; scope: gap';
9184$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
SET search_path=provsql SECURITY DEFINER;
9199CREATE OR REPLACE
FUNCTION rv_quantile(
9200 token UUID, p
double precision,
9201 prov UUID
DEFAULT gate_one())
9202 RETURNS double precision
9203 AS 'provsql',
'rv_quantile' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9219CREATE OR REPLACE
FUNCTION quantile(
9222 prov UUID = gate_one(),
9224 arguments TEXT =
NULL)
9225 RETURNS DOUBLE PRECISION AS $$
9230 IF p <> p
OR p <
0 OR p >
1 THEN
9231 RAISE EXCEPTION
'quantile: p must be in [0, 1] (got %)', p;
9234 IF pg_typeof(
input) =
'random_variable'::REGTYPE
THEN
9235 IF input IS NULL THEN
9240 RETURN provsql.rv_quantile(
9241 rv_conditioned_target((
input::random_variable)::UUID), p,
9242 rv_conditioned_prov((
input::random_variable)::UUID, prov));
9245 IF pg_typeof(
input)
IN (
'smallint'::REGTYPE,
'INTEGER'::REGTYPE,
9246 'bigint'::REGTYPE,
'NUMERIC'::REGTYPE,
9247 'real'::REGTYPE,
'double precision'::REGTYPE)
THEN
9249 RETURN input::
double precision;
9252 RAISE EXCEPTION
'quantile() is not yet supported for input type %', pg_typeof(
input)
9253 USING ERRCODE =
'feature_not_supported',
9254 DETAIL =
'provsql-reason: quantile-input-type; scope: gap';
9256$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
SET search_path=provsql SECURITY DEFINER;
9267CREATE OR REPLACE
FUNCTION rv_support(
9268 token UUID, prov UUID
DEFAULT gate_one(),
9269 OUT lo float8,
OUT hi float8)
9270 AS 'provsql',
'rv_support' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
9318CREATE OR REPLACE
FUNCTION support(
9320 prov UUID = gate_one(),
9322 arguments TEXT =
NULL,
9327 aggregation_function
VARCHAR;
9329 values_arr float8[];
9330 total_probability float8;
9333 some_always
BOOLEAN;
9335 IF input IS NULL THEN
9336 lo :=
NULL; hi :=
NULL;
RETURN;
9342 IF pg_typeof(
input)
IN (
9343 'smallint'::REGTYPE,
'INTEGER'::REGTYPE,
'bigint'::REGTYPE,
9344 'NUMERIC'::REGTYPE,
'real'::REGTYPE,
'double precision'::REGTYPE)
THEN
9345 lo :=
input::
double precision;
9346 hi :=
input::
double precision;
9359 IF pg_typeof(
input)
IN (
'random_variable'::REGTYPE,
'UUID'::REGTYPE)
THEN
9364 SELECT r.lo, r.hi
INTO lo, hi
9365 FROM provsql.rv_support(
9366 rv_conditioned_target(
input::UUID),
9367 rv_conditioned_prov(
input::UUID, prov)) r;
9371 IF pg_typeof(
input) =
'AGG_TOKEN'::REGTYPE
THEN
9376 atok AGG_TOKEN := agg_conditioned_target(
input::AGG_TOKEN);
9378 IF get_gate_type(atok) <>
'agg' THEN
9379 RAISE EXCEPTION
USING MESSAGE=
'Wrong gate type for support computation',
9380 DETAIL =
'provsql-reason: support-not-an-aggregate; scope: deliberate';
9382 SELECT pp.proname::
varchar FROM pg_proc pp
9383 WHERE oid=(get_infos(atok)).info1
9384 INTO aggregation_function;
9385 child_pairs := get_children(atok);
9387 IF aggregation_function =
'sum' OR aggregation_function =
'count' THEN
9391 IF COALESCE(array_length(child_pairs,
1),
0) =
0 THEN
9392 lo :=
0; hi :=
0;
RETURN;
9394 SELECT sum(
CASE WHEN always
THEN v
ELSE LEAST(v,
0::float8)
END),
9395 sum(
CASE WHEN always
THEN v
ELSE GREATEST(v,
0::float8)
END),
9396 min(v), max(v), bool_or(always)
9397 INTO lo, hi, vmin, vmax, some_always
9398 FROM (
SELECT CAST(get_extra((get_children(c))[
2])
AS float8)
AS v,
9399 get_gate_type((get_children(c))[
1]) =
'one' AS always
9400 FROM unnest(child_pairs)
AS c) sub;
9408 AND NOT (aggregation_function =
'count' AND (get_infos(atok)).info2 <
0)
THEN
9409 IF vmin >
0 THEN lo := vmin;
END IF;
9410 IF vmax <
0 THEN hi := vmax;
END IF;
9412 ELSIF aggregation_function =
'min' OR aggregation_function =
'max' THEN
9417 IF COALESCE(array_length(child_pairs,
1),
0) =
0 THEN
9418 lo :=
NULL; hi :=
NULL;
RETURN;
9421 SELECT min(v), max(v)
9423 FROM (
SELECT CAST(get_extra((get_children(c))[
2])
AS float8)
AS v
9424 FROM UNNEST(child_pairs)
AS c) sub;
9426 RAISE EXCEPTION
USING MESSAGE=
9427 'Cannot compute support for aggregation function ' || aggregation_function,
9428 DETAIL =
'provsql-reason: support-aggregate-kind; scope: gap';
9434 RAISE EXCEPTION
'support() is not yet supported for input type %', pg_typeof(
input)
9435 USING ERRCODE =
'feature_not_supported',
9436 DETAIL =
'provsql-reason: support-input-type; scope: gap';
9438$$
LANGUAGE plpgsql STABLE PARALLEL SAFE
SET search_path=provsql SECURITY DEFINER;
9451CREATE OR REPLACE
FUNCTION central_moment(
9454 prov UUID = gate_one(),
9456 arguments TEXT =
NULL)
9457 RETURNS DOUBLE PRECISION AS $$
9467 IF pg_typeof(
input) =
'random_variable'::REGTYPE
THEN
9468 IF input IS NULL OR k
IS NULL THEN
9473 RETURN provsql.rv_moment(
9474 rv_conditioned_target((
input::random_variable)::UUID), k,
true,
9475 rv_conditioned_prov((
input::random_variable)::UUID, prov));
9478 IF pg_typeof(
input) =
'AGG_TOKEN'::REGTYPE
THEN
9479 IF input IS NULL OR k
IS NULL THEN
9483 RAISE EXCEPTION
'central_moment(): k must be non-negative (got %)', k;
9485 IF k =
0 THEN RETURN 1;
END IF;
9486 IF k =
1 THEN RETURN 0;
END IF;
9488 mu := agg_raw_moment(agg_conditioned_target(
input::AGG_TOKEN),
1,
9489 rv_conditioned_prov(
input::UUID, prov),
method, arguments);
9490 IF mu
IS NULL THEN RETURN NULL;
END IF;
9493 IF mu =
'Infinity'::float8
OR mu =
'-Infinity'::float8
THEN
9501 raw_i := agg_raw_moment(agg_conditioned_target(
input::AGG_TOKEN), i,
9502 rv_conditioned_prov(
input::UUID, prov),
method, arguments);
9503 IF raw_i
IS NULL THEN RETURN NULL;
END IF;
9504 total := total + binom * power(-mu, k - i) * raw_i;
9507 binom := binom * (k_double - i) / (i +
1);
9515 IF pg_typeof(
input) =
'UUID'::REGTYPE
THEN
9516 IF input IS NULL OR k
IS NULL THEN
9520 RAISE EXCEPTION
'central_moment(): k must be non-negative (got %)', k;
9522 IF k =
0 THEN RETURN 1;
END IF;
9523 IF k =
1 THEN RETURN 0;
END IF;
9524 mu := provsql.probability_evaluate(provsql.cond(
input::UUID, prov),
9526 RETURN (
1 - mu) * power(-mu, k) + mu * power(
1 - mu, k);
9529 RAISE EXCEPTION
'central_moment() is not yet supported for input type %', pg_typeof(
input)
9530 USING ERRCODE =
'feature_not_supported',
9531 DETAIL =
'provsql-reason: moment-input-type; scope: gap';
9533$$
LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
SET search_path=provsql SECURITY DEFINER;
9536CREATE OR REPLACE
FUNCTION rv_covariance(x UUID, y UUID, prov UUID)
9537 RETURNS double precision
9538 AS 'provsql',
'rv_covariance' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9563CREATE OR REPLACE
FUNCTION covariance(
9564 x random_variable, y random_variable, prov UUID
DEFAULT gate_one())
9565 RETURNS double precision AS $$
9566 SELECT provsql.rv_covariance((x)::UUID, (y)::UUID, prov);
9567$$
LANGUAGE sql PARALLEL SAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9582CREATE OR REPLACE
FUNCTION stddev(
9583 x random_variable, prov UUID
DEFAULT gate_one())
9584 RETURNS double precision AS $$
9585 SELECT sqrt(provsql.variance(x, prov));
9586$$
LANGUAGE sql PARALLEL UNSAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9589CREATE OR REPLACE
FUNCTION rv_correlation(x UUID, y UUID, prov UUID)
9590 RETURNS double precision
9591 AS 'provsql',
'rv_correlation' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9608CREATE OR REPLACE
FUNCTION correlation(
9609 x random_variable, y random_variable, prov UUID
DEFAULT gate_one())
9610 RETURNS double precision AS $$
9611 SELECT provsql.rv_correlation((x)::UUID, (y)::UUID, prov);
9612$$
LANGUAGE sql PARALLEL SAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9615CREATE OR REPLACE
FUNCTION rv_entropy(token UUID, prov UUID)
9616 RETURNS double precision
9617 AS 'provsql',
'rv_entropy' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9635CREATE OR REPLACE
FUNCTION entropy(
9636 x random_variable, prov UUID
DEFAULT gate_one())
9637 RETURNS double precision AS $$
9638 SELECT provsql.rv_entropy((x)::UUID, prov);
9639$$
LANGUAGE sql PARALLEL SAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9642CREATE OR REPLACE
FUNCTION rv_kl(p UUID, q UUID)
9643 RETURNS double precision
9644 AS 'provsql',
'rv_kl' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9659CREATE OR REPLACE
FUNCTION kl(p random_variable, q random_variable)
9660 RETURNS double precision AS $$
9661 SELECT provsql.rv_kl((p)::UUID, (q)::UUID);
9662$$
LANGUAGE sql PARALLEL SAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9666CREATE OR REPLACE
FUNCTION rv_mutual_information(x UUID, y UUID)
9667 RETURNS double precision
9668 AS 'provsql',
'rv_mutual_information' LANGUAGE C VOLATILE STRICT PARALLEL SAFE;
9682CREATE OR REPLACE
FUNCTION mutual_information(
9683 x random_variable, y random_variable)
9684 RETURNS double precision AS $$
9685 SELECT provsql.rv_mutual_information((x)::UUID, (y)::UUID);
9686$$
LANGUAGE sql PARALLEL SAFE VOLATILE
SET search_path=provsql SECURITY DEFINER;
9700CREATE OR REPLACE
FUNCTION shapley(
9704 arguments TEXT =
NULL,
9705 banzhaf
BOOLEAN =
'f')
9706 RETURNS DOUBLE PRECISION AS
9707 'provsql',
'shapley' LANGUAGE C STABLE;
9710CREATE OR REPLACE
FUNCTION shapley_all_vars(
9712 IN method TEXT =
NULL,
9713 IN arguments TEXT =
NULL,
9714 IN banzhaf
BOOLEAN =
'f',
9716 OUT value DOUBLE PRECISION)
9717 RETURNS SETOF RECORD
AS
9718 'provsql',
'shapley_all_vars'
9722CREATE OR REPLACE
FUNCTION banzhaf(
9726 arguments TEXT =
NULL)
9727 RETURNS DOUBLE PRECISION AS
9728 $$
SELECT provsql.shapley(token, variable,
method, arguments,
't') $$
9732CREATE OR REPLACE
FUNCTION banzhaf_all_vars(
9734 IN method TEXT =
NULL,
9735 IN arguments TEXT =
NULL,
9737 OUT value DOUBLE PRECISION)
9738 RETURNS SETOF RECORD
AS
9739 $$
SELECT *
FROM provsql.shapley_all_vars(token,
method, arguments,
't') $$
9774CREATE OR REPLACE
FUNCTION reachability_evaluate(
9778 probabilities
DOUBLE PRECISION[],
9782 RETURNS DOUBLE PRECISION AS
9783 'provsql',
'reachability_evaluate' LANGUAGE C IMMUTABLE PARALLEL SAFE;
9813CREATE OR REPLACE
FUNCTION reachability_compile_stats(
9815 IN destinations
INT[],
9817 IN probabilities
DOUBLE PRECISION[],
9820 IN directed
BOOLEAN,
9821 OUT probability
DOUBLE PRECISION,
9822 OUT data_treewidth
INT,
9824 OUT max_states
BIGINT,
9825 OUT nb_gates
BIGINT,
9826 OUT nb_variables
BIGINT)
9827 AS 'provsql',
'reachability_compile_stats'
9828 LANGUAGE C IMMUTABLE PARALLEL SAFE;
9864CREATE OR REPLACE
FUNCTION ucq_joint_compile_stats(
9865 IN disjunct_nvars
INT[],
9866 IN atom_disjunct
INT[],
9869 IN atom_arity
INT[],
9871 IN fact_elems
INT[],
9872 IN fact_arity
INT[],
9873 IN fact_tokens UUID[],
9874 IN fact_probs
DOUBLE PRECISION[],
9875 OUT probability
DOUBLE PRECISION,
9876 OUT joint_treewidth
INT,
9877 OUT data_treewidth_lb
INT,
9878 OUT circuit_treewidth_lb
INT,
9880 OUT max_states
BIGINT,
9882 OUT n_enumerating
INT)
9883 AS 'provsql',
'ucq_joint_compile_stats'
9884 LANGUAGE C IMMUTABLE PARALLEL SAFE;
9893CREATE OR REPLACE
FUNCTION ucq_joint_compile_stats(
9896 IN fact_elems
INT[],
9897 IN fact_arity
INT[],
9898 IN fact_tokens UUID[],
9899 IN fact_probs
DOUBLE PRECISION[],
9900 OUT probability
DOUBLE PRECISION,
9901 OUT joint_treewidth
INT,
9902 OUT data_treewidth_lb
INT,
9903 OUT circuit_treewidth_lb
INT,
9905 OUT max_states
BIGINT,
9907 OUT n_enumerating
INT)
9910 dnv
INT[] :=
'{}'; adisj
INT[] :=
'{}'; arel
INT[] :=
'{}';
9911 avars
INT[] :=
'{}'; aarity
INT[] :=
'{}';
9912 d JSONB; a JSONB; v TEXT; didx
INT :=
0;
9914 FOR d
IN SELECT *
FROM jsonb_array_elements(query->
'disjuncts') LOOP
9915 dnv := dnv || (d->>
'n_vars')::
INT;
9916 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
9917 adisj := adisj || didx;
9918 arel := arel || (a->>
'rel')::
INT;
9919 aarity := aarity || jsonb_array_length(a->
'vars');
9920 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
9921 avars := avars || v::
INT;
9926 SELECT s.probability, s.joint_treewidth, s.data_treewidth_lb,
9927 s.circuit_treewidth_lb, s.n_bags, s.max_states, s.dd_size,
9929 INTO probability, joint_treewidth, data_treewidth_lb,
9930 circuit_treewidth_lb, n_bags, max_states, dd_size, n_enumerating
9931 FROM ucq_joint_compile_stats(dnv, adisj, arel, avars, aarity,
9932 fact_rel, fact_elems, fact_arity, fact_tokens, fact_probs) s;
9934$$
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;
9955CREATE OR REPLACE
FUNCTION ucq_joint_compile_stats_tracked(
9956 IN disjunct_nvars
INT[],
9957 IN atom_disjunct
INT[],
9960 IN atom_arity
INT[],
9962 IN fact_elems
INT[],
9963 IN fact_arity
INT[],
9964 IN fact_tokens UUID[],
9965 OUT probability
DOUBLE PRECISION,
9966 OUT joint_treewidth
INT,
9967 OUT data_treewidth_lb
INT,
9968 OUT circuit_treewidth_lb
INT,
9970 OUT max_states
BIGINT,
9972 OUT n_enumerating
INT)
9973 AS 'provsql',
'ucq_joint_compile_stats_tracked'
9974 LANGUAGE C STABLE PARALLEL SAFE;
9980CREATE OR REPLACE
FUNCTION ucq_joint_compile_stats_tracked(
9983 IN fact_elems
INT[],
9984 IN fact_arity
INT[],
9985 IN fact_tokens UUID[],
9986 OUT probability
DOUBLE PRECISION,
9987 OUT joint_treewidth
INT,
9988 OUT data_treewidth_lb
INT,
9989 OUT circuit_treewidth_lb
INT,
9991 OUT max_states
BIGINT,
9993 OUT n_enumerating
INT)
9996 dnv
INT[] :=
'{}'; adisj
INT[] :=
'{}'; arel
INT[] :=
'{}';
9997 avars
INT[] :=
'{}'; aarity
INT[] :=
'{}';
9998 d JSONB; a JSONB; v TEXT; didx
INT :=
0;
10000 FOR d
IN SELECT *
FROM jsonb_array_elements(query->
'disjuncts') LOOP
10001 dnv := dnv || (d->>
'n_vars')::
INT;
10002 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10003 adisj := adisj || didx;
10004 arel := arel || (a->>
'rel')::
INT;
10005 aarity := aarity || jsonb_array_length(a->
'vars');
10006 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10007 avars := avars || v::
INT;
10012 SELECT s.probability, s.joint_treewidth, s.data_treewidth_lb,
10013 s.circuit_treewidth_lb, s.n_bags, s.max_states, s.dd_size,
10015 INTO probability, joint_treewidth, data_treewidth_lb,
10016 circuit_treewidth_lb, n_bags, max_states, dd_size, n_enumerating
10017 FROM ucq_joint_compile_stats_tracked(dnv, adisj, arel, avars, aarity,
10018 fact_rel, fact_elems, fact_arity, fact_tokens) s;
10020$$
LANGUAGE plpgsql STABLE PARALLEL SAFE;
10035CREATE OR REPLACE
FUNCTION ucq_joint_materialize_tracked(
10036 disjunct_nvars
INT[],
10037 atom_disjunct
INT[],
10044 fact_tokens UUID[])
10046 'provsql',
'ucq_joint_materialize_tracked' LANGUAGE C VOLATILE;
10056CREATE OR REPLACE
FUNCTION ucq_joint_materialize_tracked(
10061 fact_tokens UUID[])
10064 dnv
INT[] :=
'{}'; adisj
INT[] :=
'{}'; arel
INT[] :=
'{}';
10065 avars
INT[] :=
'{}'; aarity
INT[] :=
'{}';
10066 d JSONB; a JSONB; v TEXT; didx
INT :=
0;
10068 FOR d
IN SELECT *
FROM jsonb_array_elements(query->
'disjuncts') LOOP
10069 dnv := dnv || (d->>
'n_vars')::
INT;
10070 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10071 adisj := adisj || didx;
10072 arel := arel || (a->>
'rel')::
INT;
10073 aarity := aarity || jsonb_array_length(a->
'vars');
10074 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10075 avars := avars || v::
INT;
10080 RETURN ucq_joint_materialize_tracked(dnv, adisj, arel, avars, aarity,
10081 fact_rel, fact_elems, fact_arity, fact_tokens);
10083$$
LANGUAGE plpgsql VOLATILE;
10117CREATE OR REPLACE
FUNCTION ucq_joint_provenance(
10118 descriptor JSONB, fallback UUID
DEFAULT NULL)
10121 legs TEXT;
sql TEXT; saved TEXT;
10122 fact_rel
INT[]; fact_elems
INT[]; fact_arity
INT[]; fact_tokens UUID[];
10123 dnv
INT[]:=
'{}'; adisj
INT[]:=
'{}'; arel
INT[]:=
'{}';
10124 avars
INT[]:=
'{}'; aarity
INT[]:=
'{}';
10125 d jsonb; a jsonb; v TEXT; didx
INT:=
0;
10128 FOR d
IN SELECT *
FROM jsonb_array_elements(descriptor->
'disjuncts') LOOP
10129 dnv := dnv || (d->>
'n_vars')::
INT;
10130 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10131 adisj := adisj || didx; arel := arel || (a->>
'rel')::
INT;
10132 aarity := aarity || jsonb_array_length(a->
'vars');
10133 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10134 avars := avars || v::
INT;
10144 format(
'SELECT %s, ARRAY[%s]::TEXT[], provsql FROM %s%s',
10146 (
SELECT string_agg(format(
'(%I)::TEXT', c),
',')
10147 FROM jsonb_array_elements_text(descriptor->
'elem_cols'->(rn-1)::
INT) c),
10151 CASE WHEN coalesce(descriptor->
'rel_where'->>(rn-1)::
INT,
'') <>
''
10152 THEN ' WHERE '||(descriptor->
'rel_where'->>(rn-1)::
INT)
10156 FROM jsonb_array_elements_text(descriptor->
'relations')
WITH ORDINALITY t(rel, rn);
10159 WITH facts(rel,elems,tok)
AS (%s),
10160 ord
AS (
SELECT row_number()
OVER ()
AS ord, rel, elems, tok
FROM facts),
10161 dict
AS (
SELECT val, (dense_rank()
OVER (
ORDER BY val))-1
AS id
10162 FROM (
SELECT DISTINCT unnest(elems)
AS val
FROM facts) u)
10163 SELECT (
SELECT array_agg(rel
ORDER BY ord)
FROM ord),
10164 (
SELECT array_agg(cardinality(elems)
ORDER BY ord)
FROM ord),
10165 (
SELECT array_agg(tok
ORDER BY ord)
FROM ord),
10166 (
SELECT array_agg(dd.id
ORDER BY o.ord, e.k)
10167 FROM ord o,
LATERAL unnest(o.elems)
WITH ORDINALITY e(val,k)
10168 JOIN dict dd
ON dd.val = e.val)
10173 saved := current_setting(
'provsql.active',
true);
10174 PERFORM set_config(
'provsql.active',
'off',
true);
10175 EXECUTE sql INTO fact_rel, fact_arity, fact_tokens, fact_elems;
10176 PERFORM set_config(
'provsql.active', saved,
true);
10178 RETURN ucq_joint_materialize_tracked(dnv,adisj,arel,avars,aarity,
10179 fact_rel,fact_elems,fact_arity,fact_tokens);
10180EXCEPTION
WHEN OTHERS
THEN
10186$$
LANGUAGE plpgsql VOLATILE;
10204CREATE OR REPLACE
FUNCTION ucq_mobius_materialize_tracked(
10205 disjunct_nvars
INT[],
10206 atom_disjunct
INT[],
10213 fact_tokens UUID[],
10214 lineage UUID
DEFAULT NULL)
10216 'provsql',
'ucq_mobius_materialize_tracked' LANGUAGE C VOLATILE;
10225CREATE OR REPLACE
FUNCTION ucq_mobius_compile_stats(
10226 IN disjunct_nvars
INT[],
10227 IN atom_disjunct
INT[],
10229 IN atom_vars
INT[],
10230 IN atom_arity
INT[],
10232 IN fact_elems
INT[],
10233 IN fact_arity
INT[],
10234 IN fact_tokens UUID[],
10235 OUT probability
DOUBLE PRECISION,
10236 OUT n_components
INT,
10237 OUT n_cnf_conjuncts
INT,
10238 OUT lattice_size
INT,
10240 OUT n_cancelled
INT,
10241 OUT cancelled_hard
BOOLEAN,
10242 OUT dd_size
BIGINT,
10243 OUT memo_hits
BIGINT)
10244 AS 'provsql',
'ucq_mobius_compile_stats'
10245 LANGUAGE C VOLATILE;
10259CREATE OR REPLACE
FUNCTION mobius_or_null(tok UUID)
10261 SELECT CASE WHEN tok
IS NOT NULL AND provsql.get_gate_type(tok) =
'mobius'
10263$$
LANGUAGE sql STABLE;
10271CREATE OR REPLACE
FUNCTION ucq_mobius_provenance(
10272 descriptor JSONB, fallback UUID
DEFAULT NULL)
10275 legs TEXT;
sql TEXT; saved TEXT;
10276 fact_rel
INT[]; fact_elems
INT[]; fact_arity
INT[]; fact_tokens UUID[];
10277 dnv
INT[]:=
'{}'; adisj
INT[]:=
'{}'; arel
INT[]:=
'{}';
10278 avars
INT[]:=
'{}'; aarity
INT[]:=
'{}';
10279 d jsonb; a jsonb; v TEXT; didx
INT:=
0;
10281 FOR d
IN SELECT *
FROM jsonb_array_elements(descriptor->
'disjuncts') LOOP
10282 dnv := dnv || (d->>
'n_vars')::
INT;
10283 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10284 adisj := adisj || didx; arel := arel || (a->>
'rel')::
INT;
10285 aarity := aarity || jsonb_array_length(a->
'vars');
10286 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10287 avars := avars || v::
INT;
10294 format(
'SELECT %s, ARRAY[%s]::TEXT[], provsql FROM %s%s',
10296 (
SELECT string_agg(format(
'(%I)::TEXT', c),
',')
10297 FROM jsonb_array_elements_text(descriptor->
'elem_cols'->(rn-1)::
INT) c),
10299 CASE WHEN coalesce(descriptor->
'rel_where'->>(rn-1)::
INT,
'') <>
''
10300 THEN ' WHERE '||(descriptor->
'rel_where'->>(rn-1)::
INT)
10304 FROM jsonb_array_elements_text(descriptor->
'relations')
WITH ORDINALITY t(rel, rn);
10307 WITH facts(rel,elems,tok)
AS (%s),
10308 ord
AS (
SELECT row_number()
OVER ()
AS ord, rel, elems, tok
FROM facts),
10309 dict
AS (
SELECT val, (dense_rank()
OVER (
ORDER BY val))-1
AS id
10310 FROM (
SELECT DISTINCT unnest(elems)
AS val
FROM facts) u)
10311 SELECT (
SELECT array_agg(rel
ORDER BY ord)
FROM ord),
10312 (
SELECT array_agg(cardinality(elems)
ORDER BY ord)
FROM ord),
10313 (
SELECT array_agg(tok
ORDER BY ord)
FROM ord),
10314 (
SELECT array_agg(dd.id
ORDER BY o.ord, e.k)
10315 FROM ord o,
LATERAL unnest(o.elems)
WITH ORDINALITY e(val,k)
10316 JOIN dict dd
ON dd.val = e.val)
10319 saved := current_setting(
'provsql.active',
true);
10320 PERFORM set_config(
'provsql.active',
'off',
true);
10321 EXECUTE sql INTO fact_rel, fact_arity, fact_tokens, fact_elems;
10322 PERFORM set_config(
'provsql.active', saved,
true);
10327 RETURN ucq_mobius_materialize_tracked(dnv,adisj,arel,avars,aarity,
10328 fact_rel,fact_elems,fact_arity,fact_tokens, fallback);
10329EXCEPTION
WHEN OTHERS
THEN
10332$$
LANGUAGE plpgsql VOLATILE;
10340CREATE OR REPLACE
FUNCTION mobius_compile_stats(
10341 IN descriptor JSONB,
10342 OUT probability
DOUBLE PRECISION,
10343 OUT n_components
INT,
10344 OUT n_cnf_conjuncts
INT,
10345 OUT lattice_size
INT,
10347 OUT n_cancelled
INT,
10348 OUT cancelled_hard
BOOLEAN,
10349 OUT dd_size
BIGINT,
10350 OUT memo_hits
BIGINT)
10351RETURNS RECORD
AS $$
10353 legs TEXT;
sql TEXT; saved TEXT;
10354 fact_rel
INT[]; fact_elems
INT[]; fact_arity
INT[]; fact_tokens UUID[];
10355 dnv
INT[]:=
'{}'; adisj
INT[]:=
'{}'; arel
INT[]:=
'{}';
10356 avars
INT[]:=
'{}'; aarity
INT[]:=
'{}';
10357 d jsonb; a jsonb; v TEXT; didx
INT:=
0;
10359 FOR d
IN SELECT *
FROM jsonb_array_elements(descriptor->
'disjuncts') LOOP
10360 dnv := dnv || (d->>
'n_vars')::
INT;
10361 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10362 adisj := adisj || didx; arel := arel || (a->>
'rel')::
INT;
10363 aarity := aarity || jsonb_array_length(a->
'vars');
10364 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10365 avars := avars || v::
INT;
10372 format(
'SELECT %s, ARRAY[%s]::TEXT[], provsql FROM %s%s',
10374 (
SELECT string_agg(format(
'(%I)::TEXT', c),
',')
10375 FROM jsonb_array_elements_text(descriptor->
'elem_cols'->(rn-1)::
INT) c),
10377 CASE WHEN coalesce(descriptor->
'rel_where'->>(rn-1)::
INT,
'') <>
''
10378 THEN ' WHERE '||(descriptor->
'rel_where'->>(rn-1)::
INT)
10382 FROM jsonb_array_elements_text(descriptor->
'relations')
WITH ORDINALITY t(rel, rn);
10385 WITH facts(rel,elems,tok)
AS (%s),
10386 ord
AS (
SELECT row_number()
OVER ()
AS ord, rel, elems, tok
FROM facts),
10387 dict
AS (
SELECT val, (dense_rank()
OVER (
ORDER BY val))-1
AS id
10388 FROM (
SELECT DISTINCT unnest(elems)
AS val
FROM facts) u)
10389 SELECT (
SELECT array_agg(rel
ORDER BY ord)
FROM ord),
10390 (
SELECT array_agg(cardinality(elems)
ORDER BY ord)
FROM ord),
10391 (
SELECT array_agg(tok
ORDER BY ord)
FROM ord),
10392 (
SELECT array_agg(dd.id
ORDER BY o.ord, e.k)
10393 FROM ord o,
LATERAL unnest(o.elems)
WITH ORDINALITY e(val,k)
10394 JOIN dict dd
ON dd.val = e.val)
10397 saved := current_setting(
'provsql.active',
true);
10398 PERFORM set_config(
'provsql.active',
'off',
true);
10399 EXECUTE sql INTO fact_rel, fact_arity, fact_tokens, fact_elems;
10400 PERFORM set_config(
'provsql.active', saved,
true);
10402 SELECT s.probability, s.n_components, s.n_cnf_conjuncts, s.lattice_size,
10403 s.n_nonzero, s.n_cancelled, s.cancelled_hard, s.dd_size, s.memo_hits
10404 INTO probability, n_components, n_cnf_conjuncts, lattice_size,
10405 n_nonzero, n_cancelled, cancelled_hard, dd_size, memo_hits
10406 FROM ucq_mobius_compile_stats(dnv,adisj,arel,avars,aarity,
10407 fact_rel,fact_elems,fact_arity,fact_tokens) s;
10409$$
LANGUAGE plpgsql VOLATILE;
10422CREATE OR REPLACE
FUNCTION ucq_joint_gather(
10424 OUT disjunct_nvars
INT[],
OUT atom_disjunct
INT[],
OUT atom_rel
INT[],
10425 OUT atom_vars
INT[],
OUT atom_arity
INT[],
10426 OUT fact_rel
INT[],
OUT fact_elems
INT[],
OUT fact_arity
INT[],
10427 OUT fact_tokens UUID[],
OUT val_by_id TEXT[])
10430 legs TEXT;
sql TEXT; saved TEXT; d jsonb; a jsonb; v TEXT; didx
INT :=
0;
10432 disjunct_nvars:=
'{}'; atom_disjunct:=
'{}'; atom_rel:=
'{}';
10433 atom_vars:=
'{}'; atom_arity:=
'{}';
10434 FOR d
IN SELECT *
FROM jsonb_array_elements(descriptor->
'disjuncts') LOOP
10435 disjunct_nvars := disjunct_nvars || (d->>
'n_vars')::
INT;
10436 FOR a
IN SELECT *
FROM jsonb_array_elements(d->
'atoms') LOOP
10437 atom_disjunct := atom_disjunct || didx;
10438 atom_rel := atom_rel || (a->>
'rel')::
INT;
10439 atom_arity := atom_arity || jsonb_array_length(a->
'vars');
10440 FOR v
IN SELECT *
FROM jsonb_array_elements_text(a->
'vars') LOOP
10441 atom_vars := atom_vars || v::
INT;
10448 format(
'SELECT %s, ARRAY[%s]::TEXT[], provsql FROM %s%s', rn -
1,
10449 (
SELECT string_agg(format(
'(%I)::TEXT', c),
',')
10450 FROM jsonb_array_elements_text(descriptor->
'elem_cols'->(rn-1)::
INT) c),
10452 CASE WHEN coalesce(descriptor->
'rel_where'->>(rn-1)::
INT,
'') <>
''
10453 THEN ' WHERE '||(descriptor->
'rel_where'->>(rn-1)::
INT)
10457 FROM jsonb_array_elements_text(descriptor->
'relations')
WITH ORDINALITY t(rel, rn);
10460 WITH facts(rel,elems,tok)
AS (%s),
10461 ord
AS (
SELECT row_number()
OVER ()
AS ord, rel, elems, tok
FROM facts),
10462 dict
AS (
SELECT val, (dense_rank()
OVER (
ORDER BY val))-1
AS id
10463 FROM (
SELECT DISTINCT unnest(elems)
AS val
FROM facts) u)
10464 SELECT (
SELECT array_agg(rel
ORDER BY ord)
FROM ord),
10465 (
SELECT array_agg(cardinality(elems)
ORDER BY ord)
FROM ord),
10466 (
SELECT array_agg(tok
ORDER BY ord)
FROM ord),
10467 (
SELECT array_agg(dd.id
ORDER BY o.ord, e.k)
10468 FROM ord o,
LATERAL unnest(o.elems)
WITH ORDINALITY e(val,k)
10469 JOIN dict dd
ON dd.val = e.val),
10470 (
SELECT array_agg(val
ORDER BY id)
FROM dict)
10473 saved := current_setting(
'provsql.active',
true);
10474 PERFORM set_config(
'provsql.active',
'off',
true);
10475 EXECUTE sql INTO fact_rel, fact_arity, fact_tokens, fact_elems, val_by_id;
10476 PERFORM set_config(
'provsql.active', saved,
true);
10478$$
LANGUAGE plpgsql VOLATILE;
10495CREATE OR REPLACE
FUNCTION ucq_joint_provenance_answer(
10496 descriptor JSONB, head_vars
INT[], head_vals TEXT[], fallback UUID
DEFAULT NULL)
10497RETURNS UUID
AS 'provsql',
'ucq_joint_provenance_answer'
10508CREATE OR REPLACE
FUNCTION ucq_mobius_provenance_answer(
10509 descriptor JSONB, head_vars
INT[], head_vals TEXT[], fallback UUID
DEFAULT NULL)
10510RETURNS UUID
AS 'provsql',
'ucq_mobius_provenance_answer'
10551CREATE OR REPLACE
FUNCTION reachability_materialize(
10553 IN destinations
INT[],
10555 IN probabilities
DOUBLE PRECISION[],
10556 IN block_keys UUID[],
10557 IN block_indices
INT[],
10558 IN source_vertices
INT[],
10559 IN source_tokens UUID[],
10560 IN source_probabilities
DOUBLE PRECISION[],
10561 IN directed
BOOLEAN,
10564 RETURNS SETOF RECORD
AS
10565 'provsql',
'reachability_materialize' LANGUAGE C VOLATILE;
10598CREATE OR REPLACE
FUNCTION reachability_materialize_hops(
10600 IN destinations
INT[],
10602 IN probabilities
DOUBLE PRECISION[],
10603 IN block_keys UUID[],
10604 IN block_indices
INT[],
10605 IN source_vertices
INT[],
10606 IN source_tokens UUID[],
10607 IN source_probabilities
DOUBLE PRECISION[],
10608 IN directed
BOOLEAN,
10614 RETURNS SETOF RECORD
AS
10615 'provsql',
'reachability_materialize_hops' LANGUAGE C VOLATILE;
10647CREATE OR REPLACE
FUNCTION reachability_materialize_any(
10649 IN destinations
INT[],
10651 IN probabilities
DOUBLE PRECISION[],
10652 IN block_keys UUID[],
10653 IN block_indices
INT[],
10654 IN source_vertices
INT[],
10655 IN source_tokens UUID[],
10656 IN source_probabilities
DOUBLE PRECISION[],
10657 IN directed
BOOLEAN,
10658 IN group_ids
INT[],
10659 IN member_vertices
INT[],
10662 RETURNS SETOF RECORD
AS
10663 'provsql',
'reachability_materialize_any' LANGUAGE C VOLATILE;
10693CREATE OR REPLACE
FUNCTION reachability_materialize_cover(
10695 destinations
INT[],
10697 probabilities
DOUBLE PRECISION[],
10699 block_indices
INT[],
10700 source_vertices
INT[],
10701 source_tokens UUID[],
10702 source_probabilities
DOUBLE PRECISION[],
10704 member_vertices
INT[])
10706 'provsql',
'reachability_materialize_cover' LANGUAGE C VOLATILE;
10743CREATE OR REPLACE
FUNCTION plant_reach_any_groups(
10745 node_attribute TEXT,
10746 member_rel REGCLASS,
10747 member_attribute TEXT,
10748 group_attribute TEXT,
10750 source_attribute TEXT,
10751 destination_attribute TEXT,
10754 edge_quals TEXT
DEFAULT NULL,
10755 source_rel REGCLASS
DEFAULT NULL,
10756 source_rel_attribute TEXT
DEFAULT NULL,
10757 edge_sql TEXT
DEFAULT NULL,
10758 member_quals TEXT
DEFAULT NULL)
10767 sp
double precision[];
10768 gids
INT[] :=
ARRAY[]::
INT[];
10769 mids
INT[] :=
ARRAY[]::
INT[];
10771 verbosity
INT := coalesce(current_setting(
'provsql.verbose_level',
true)::
INT,
0);
10776 IF EXISTS (
SELECT 1 FROM pg_attribute
10777 WHERE attrelid = member_rel
AND attname =
'provsql'
10778 AND atttypid =
'UUID'::REGTYPE
AND NOT attisdropped)
THEN
10782 IF source_rel
IS NOT NULL THEN
10783 SELECT g.source_values, g.source_tokens, g.source_probabilities
10785 FROM provsql.gather_reachability_sources(source_rel,
10786 source_rel_attribute) g;
10788 sv :=
ARRAY[]::TEXT[];
10789 st :=
ARRAY[]::UUID[];
10790 sp :=
ARRAY[]::float8[];
10793 sv :=
ARRAY[source_value];
10794 st :=
ARRAY[
'00000000-0000-0000-0000-000000000000'::UUID];
10795 sp :=
ARRAY[
1.
0::float8];
10798 e := provsql.gather_reachability_edges(edge_rel, source_attribute,
10799 destination_attribute,
10800 sv, edge_quals, edge_sql);
10812 DROP TABLE IF EXISTS provsql_reach_any_flat_tmp;
10814 'CREATE TEMP TABLE provsql_reach_any_flat_tmp AS '
10815 ||
'SELECT w.%1$I::TEXT AS node_val, provsql.provenance() AS tok, '
10816 ||
' t.%5$I AS grp_key '
10817 ||
'FROM %2$I w JOIN %3$s t ON w.%1$I = t.%4$I'
10821 || coalesce(
' WHERE ' || member_quals,
''),
10822 node_attribute, work_name, member_rel::TEXT, member_attribute,
10824 PERFORM provsql.remove_provenance(
'provsql_reach_any_flat_tmp');
10825 DROP TABLE IF EXISTS provsql_reach_any_groups_tmp;
10826 CREATE TEMP
TABLE provsql_reach_any_groups_tmp
AS
10827 SELECT (row_number()
OVER ())::
INT AS gid, members, toks
FROM (
10828 SELECT array_agg(node_val)
AS members, array_agg(tok)
AS toks
10829 FROM provsql_reach_any_flat_tmp
10830 GROUP BY grp_key
HAVING count(*) >=
2) g;
10831 DROP TABLE provsql_reach_any_flat_tmp;
10833 FOR grp
IN SELECT gid, members
FROM provsql_reach_any_groups_tmp LOOP
10834 FOR m
IN SELECT DISTINCT unnest(grp.members)
AS val LOOP
10835 vid := array_position(e.vertices, m.val);
10836 IF vid
IS NOT NULL THEN
10837 gids := gids || grp.gid;
10838 mids := mids || vid;
10842 IF cardinality(gids) =
0 THEN
10843 DROP TABLE provsql_reach_any_groups_tmp;
10848 SELECT a.group_id, a.token
AS any_token, t.toks
10849 FROM provsql.reachability_materialize_any(
10850 e.sources, e.destinations, e.tokens, e.probabilities,
10851 e.block_keys, e.block_indices, e.extra_ids, st, sp,
10852 directed, gids, mids) a
10853 JOIN provsql_reach_any_groups_tmp t
ON t.gid = a.group_id
10855 PERFORM provsql.plant_canonical(work_name,
'plus', grp.toks,
10858 DROP TABLE provsql_reach_any_groups_tmp;
10859 IF verbosity >=
20 THEN
10862 PERFORM set_config(
'client_min_messages',
'notice',
true);
10863 RAISE NOTICE
'ProvSQL: certified any-member gates planted for the aggregation of "%" by %.%',
10864 work_name, member_rel, group_attribute;
10865 PERFORM set_config(
'client_min_messages',
'warning',
true);
10867 EXCEPTION
WHEN OTHERS
THEN
10868 IF verbosity >=
10 THEN
10869 PERFORM set_config(
'client_min_messages',
'notice',
true);
10870 RAISE NOTICE
'ProvSQL: any-member planting for "%" skipped (%)',
10871 work_name, SQLERRM;
10872 PERFORM set_config(
'client_min_messages',
'warning',
true);
10878$$
LANGUAGE plpgsql
SET client_min_messages = warning;
10914CREATE OR REPLACE
FUNCTION plant_reach_cover(
10916 node_attribute TEXT,
10918 source_attribute TEXT,
10919 destination_attribute TEXT,
10922 node_values TEXT[],
10923 edge_quals TEXT
DEFAULT NULL,
10924 source_rel REGCLASS
DEFAULT NULL,
10925 source_rel_attribute TEXT
DEFAULT NULL,
10926 edge_sql TEXT
DEFAULT NULL)
10933 sp
double precision[];
10936 vids
INT[] :=
ARRAY[]::
INT[];
10938 toks UUID[] :=
ARRAY[]::UUID[];
10940 verbosity
INT := coalesce(current_setting(
'provsql.verbose_level',
true)::
INT,
0);
10943 IF source_rel
IS NOT NULL THEN
10944 SELECT g.source_values, g.source_tokens, g.source_probabilities
10946 FROM provsql.gather_reachability_sources(source_rel,
10947 source_rel_attribute) g;
10949 sv :=
ARRAY[]::TEXT[];
10950 st :=
ARRAY[]::UUID[];
10951 sp :=
ARRAY[]::float8[];
10954 sv :=
ARRAY[source_value];
10955 st :=
ARRAY[
'00000000-0000-0000-0000-000000000000'::UUID];
10956 sp :=
ARRAY[
1.
0::float8];
10959 e := provsql.gather_reachability_edges(edge_rel, source_attribute,
10960 destination_attribute,
10961 sv, edge_quals, edge_sql);
10967 FOREACH val
IN ARRAY node_values LOOP
10968 vid := array_position(e.vertices, val);
10969 IF vid
IS NULL THEN
10972 vids := vids || vid;
10973 EXECUTE format(
'SELECT provsql FROM %I WHERE %I::TEXT = $1',
10974 work_name, node_attribute)
10975 INTO tok
USING val;
10976 IF tok
IS NULL THEN
10979 toks := toks || tok;
10982 cover_token := provsql.reachability_materialize_cover(
10983 e.sources, e.destinations, e.tokens, e.probabilities,
10984 e.block_keys, e.block_indices, e.extra_ids, st, sp,
10987 PERFORM provsql.plant_canonical(work_name,
'times', toks, cover_token,
1);
10988 IF verbosity >=
20 THEN
10991 PERFORM set_config(
'client_min_messages',
'notice',
true);
10992 RAISE NOTICE
'ProvSQL: certified all-members gate planted for the self-join of "%"',
10994 PERFORM set_config(
'client_min_messages',
'warning',
true);
10996 EXCEPTION
WHEN OTHERS
THEN
10997 IF verbosity >=
10 THEN
10998 PERFORM set_config(
'client_min_messages',
'notice',
true);
10999 RAISE NOTICE
'ProvSQL: all-members planting for "%" skipped (%)',
11000 work_name, SQLERRM;
11001 PERFORM set_config(
'client_min_messages',
'warning',
true);
11007$$
LANGUAGE plpgsql
SET client_min_messages = warning;
11022CREATE OR REPLACE
FUNCTION token_conjunctive_leaves(token UUID)
11025WITH RECURSIVE walk(g)
AS (
11028 SELECT c
FROM walk w,
unnest(provsql.get_children(w.g))
AS c
11029 WHERE provsql.get_gate_type(w.g)
IN (
'times',
'project',
'eq',
'annotation')
11031SELECT CASE WHEN bool_and(provsql.get_gate_type(g)
11032 IN (
'times',
'project',
'eq',
'annotation',
'input'))
11033 THEN array_agg(
DISTINCT g)
11034 FILTER (
WHERE provsql.get_gate_type(g) =
'input')
11037$$
LANGUAGE sql STABLE;
11078CREATE OR REPLACE
FUNCTION gather_reachability_edges(
11080 IN source_attribute TEXT,
11081 IN destination_attribute TEXT,
11082 IN extra_vertices TEXT[],
11083 IN edge_quals TEXT
DEFAULT NULL,
11084 IN rel_sql TEXT
DEFAULT NULL,
11086 OUT destinations
INT[],
11088 OUT probabilities
DOUBLE PRECISION[],
11089 OUT block_keys UUID[],
11090 OUT block_indices
INT[],
11091 OUT extra_ids
INT[],
11092 OUT vertices TEXT[])
11101 verbosity
INT := coalesce(current_setting(
'provsql.verbose_level',
true)::
INT,
0);
11109 IF rel
IS NOT NULL AND rel_sql
IS NULL THEN
11110 tkind := (provsql.get_table_info(rel::oid)).kind;
11112 IF tkind
NOT IN (
'tid',
'bid')
THEN
11115 IF tkind =
'bid' THEN
11116 SELECT string_agg(quote_ident(a.attname) ||
'::TEXT',
' || '','' || '
11119 FROM unnest((provsql.get_table_info(rel::oid)).block_key)
11120 WITH ORDINALITY
AS k(attnum, ord)
11121 JOIN pg_attribute a
ON a.attrelid = rel
AND a.attnum = k.attnum;
11123 bkey_expr := coalesce(bkey_expr, quote_literal(
''));
11125 IF tkind
IS NOT NULL AND verbosity >=
20 THEN
11130 PERFORM set_config(
'client_min_messages',
'notice',
true);
11131 RAISE NOTICE
'ProvSQL: catalog characterises % as %', rel,
upper(tkind);
11132 PERFORM set_config(
'client_min_messages',
'warning',
true);
11141 DROP TABLE IF EXISTS provsql_reachability_edges_tmp;
11143 'CREATE TEMP TABLE provsql_reachability_edges_tmp AS '
11144 ||
'SELECT %1$I::TEXT AS u, %2$I::TEXT AS v, '
11145 ||
'provsql.strip_annotations(provsql.provenance()) AS token%5$s '
11146 ||
'FROM %3$s WHERE %1$I IS NOT NULL AND %2$I IS NOT NULL%4$s',
11147 source_attribute, destination_attribute,
11148 CASE WHEN rel_sql
IS NULL THEN rel::TEXT
11149 ELSE '(' || rel_sql ||
') AS provsql_edge_subquery' END,
11150 CASE WHEN edge_quals
IS NULL THEN ''
11151 ELSE ' AND (' || edge_quals ||
')' END,
11152 CASE WHEN tkind =
'bid'
11153 THEN ', public.uuid_generate_v5(provsql.uuid_ns_provsql(), '
11154 || quote_literal(
'bidblock' || rel::TEXT ||
':')
11155 ||
' || ' || bkey_expr ||
') AS bkey'
11156 ELSE ', NULL::UUID AS bkey' END);
11157 PERFORM provsql.remove_provenance(
'provsql_reachability_edges_tmp');
11159 DROP TABLE IF EXISTS provsql_reachability_support_tmp;
11160 IF tkind
IS NULL THEN
11163 IF EXISTS (
SELECT 1 FROM provsql_reachability_edges_tmp
11164 WHERE provsql.get_gate_type(token)
NOT IN (
'input',
'mulinput',
'times',
11165 'project',
'eq'))
THEN
11166 DROP TABLE provsql_reachability_edges_tmp;
11167 RAISE EXCEPTION
'reachability: the provenance of % must consist of base input, repair_key, or conjunctive join tokens', coalesce(rel::TEXT,
'the edge query')
11168 USING ERRCODE =
'feature_not_supported',
11169 DETAIL =
'provsql-reason: reachability-provenance-shape; scope: gap';
11171 CREATE TEMP
TABLE provsql_reachability_support_tmp
AS
11172 SELECT t.token, l.leaf
11173 FROM (
SELECT DISTINCT token
FROM provsql_reachability_edges_tmp
11174 WHERE provsql.get_gate_type(token)
IN (
'times',
'project',
'eq')) t,
11175 LATERAL unnest(provsql.token_conjunctive_leaves(t.token))
AS l(leaf);
11176 IF EXISTS (
SELECT 1
11177 FROM (
SELECT DISTINCT token
FROM provsql_reachability_edges_tmp) t
11178 WHERE provsql.get_gate_type(t.token)
IN (
'times',
'project',
'eq')
11179 AND provsql.token_conjunctive_leaves(t.token)
IS NULL)
THEN
11180 DROP TABLE provsql_reachability_support_tmp;
11181 DROP TABLE provsql_reachability_edges_tmp;
11182 RAISE EXCEPTION
'reachability: a join-defined edge token is not a pure conjunction of base tuples'
11183 USING ERRCODE =
'feature_not_supported',
11184 DETAIL =
'provsql-reason: reachability-edge-not-conjunction; scope: gap';
11186 IF EXISTS (
SELECT 1 FROM (
11187 SELECT leaf
FROM provsql_reachability_support_tmp
11189 SELECT DISTINCT token
FROM provsql_reachability_edges_tmp
11190 WHERE provsql.get_gate_type(token) =
'input'
11192 GROUP BY leaf
HAVING count(*) >
1)
THEN
11193 DROP TABLE provsql_reachability_support_tmp;
11194 DROP TABLE provsql_reachability_edges_tmp;
11195 RAISE EXCEPTION
'reachability: join-defined edges share base tuples (their supports overlap), so they are not independent'
11196 USING ERRCODE =
'feature_not_supported',
11197 DETAIL =
'provsql-reason: reachability-edges-share-tuples; scope: gap';
11206 IF tkind =
'tid' THEN
11207 sel_probs :=
'coalesce(provsql.get_prob(e.token), 1.0)';
11208 sel_bkeys := $
sql$
'00000000-0000-0000-0000-000000000000'::UUID$
sql$;
11210 ELSIF tkind =
'bid' THEN
11211 sel_probs :=
'coalesce(provsql.get_prob(e.token), 1.0)';
11212 sel_bkeys := $
sql$
CASE WHEN provsql.get_gate_type(e.token) =
'mulinput'
11214 ELSE '00000000-0000-0000-0000-000000000000'::UUID
END$
sql$;
11215 sel_bidx :=
'e.bidx';
11217 sel_probs := $
sql$
CASE WHEN provsql.get_gate_type(e.token)
IN (
'times',
'project',
'eq')
11218 THEN (
SELECT CASE WHEN bool_or(coalesce(provsql.get_prob(s.leaf),
1.
0) =
0)
11220 ELSE exp(sum(ln(coalesce(provsql.get_prob(s.leaf),
1.
0))))
END
11221 FROM provsql_reachability_support_tmp s
11222 WHERE s.token = e.token)
11223 ELSE coalesce(provsql.get_prob(e.token),
1.
0)
END$
sql$;
11224 sel_bkeys := $
sql$
CASE WHEN provsql.get_gate_type(e.token) =
'mulinput'
11225 THEN (provsql.get_children(e.token))[
1]
11226 ELSE '00000000-0000-0000-0000-000000000000'::UUID
END$
sql$;
11227 sel_bidx := $
sql$
CASE WHEN provsql.get_gate_type(e.token) =
'mulinput'
11228 THEN (provsql.get_infos(e.token)).info1
ELSE 0 END$
sql$;
11234 SELECT u
AS x
FROM provsql_reachability_edges_tmp
11235 UNION SELECT v
FROM provsql_reachability_edges_tmp
11236 UNION SELECT unnest($
1)),
11238 SELECT x, (row_number()
OVER (
ORDER BY x))::
INT AS id
FROM verts)
11239 SELECT array_agg(iu.id), array_agg(iv.id),
11240 array_agg(e.token),
11244 (
SELECT array_agg(i.id
ORDER BY ev.ord)
11245 FROM unnest($
1)
WITH ORDINALITY
AS ev(x, ord)
11246 JOIN ids i
ON i.x = ev.x),
11247 (
SELECT array_agg(x
ORDER BY id)
FROM ids)
11249 (row_number()
OVER (
PARTITION BY t.bkey))::
INT AS bidx
11250 FROM provsql_reachability_edges_tmp t) e
11251 JOIN ids iu
ON iu.x = e.u
11252 JOIN ids iv
ON iv.x = e.v
11253 $
sql$, sel_probs, sel_bkeys, sel_bidx)
11254 INTO sources, destinations, tokens, probabilities, block_keys,
11255 block_indices, extra_ids, vertices
11256 USING extra_vertices;
11258 DROP TABLE provsql_reachability_edges_tmp;
11259 DROP TABLE IF EXISTS provsql_reachability_support_tmp;
11264$$
LANGUAGE plpgsql
SET client_min_messages = warning;
11283CREATE OR REPLACE
FUNCTION gather_reachability_sources(
11285 IN source_attribute TEXT,
11286 OUT source_values TEXT[],
11287 OUT source_tokens UUID[],
11288 OUT source_probabilities
DOUBLE PRECISION[])
11296 SELECT 1 FROM pg_attribute
11297 WHERE attrelid = rel
AND attname =
'provsql'
11298 AND atttypid =
'UUID'::REGTYPE
AND NOT attisdropped)
11306 tkind := (get_table_info(rel::oid)).kind;
11307 IF tkind =
'bid' THEN
11308 RAISE EXCEPTION
'reachability: % is block-independent (repair_key); block-correlated source sets are not supported', rel
11309 USING ERRCODE =
'feature_not_supported',
11310 DETAIL =
'provsql-reason: reachability-block-correlated; scope: gap';
11314 DROP TABLE IF EXISTS provsql_reachability_sources_tmp;
11317 'CREATE TEMP TABLE provsql_reachability_sources_tmp AS '
11318 ||
'SELECT %1$I::TEXT AS x, provenance() AS token '
11319 ||
'FROM %2$s WHERE %1$I IS NOT NULL',
11320 source_attribute, rel);
11321 PERFORM remove_provenance(
'provsql_reachability_sources_tmp');
11322 IF tkind
IS DISTINCT FROM 'tid'
11323 AND EXISTS (
SELECT 1 FROM provsql_reachability_sources_tmp
11324 WHERE get_gate_type(token) <>
'input')
THEN
11325 DROP TABLE provsql_reachability_sources_tmp;
11326 RAISE EXCEPTION
'reachability: the provenance of % must consist of base input tokens (independent tuples); views or query results are not supported', rel
11327 USING ERRCODE =
'feature_not_supported',
11328 DETAIL =
'provsql-reason: reachability-not-base-inputs; scope: gap';
11330 SELECT array_agg(x), array_agg(token),
11331 array_agg(coalesce(get_prob(token),
1.
0))
11332 INTO source_values, source_tokens, source_probabilities
11333 FROM provsql_reachability_sources_tmp;
11334 DROP TABLE provsql_reachability_sources_tmp;
11337 'CREATE TEMP TABLE provsql_reachability_sources_tmp AS '
11338 ||
'SELECT DISTINCT %1$I::TEXT AS x FROM %2$s WHERE %1$I IS NOT NULL',
11339 source_attribute, rel);
11340 SELECT array_agg(x),
11341 array_agg(
'00000000-0000-0000-0000-000000000000'::UUID),
11342 array_agg(
1.
0::float8)
11343 INTO source_values, source_tokens, source_probabilities
11344 FROM provsql_reachability_sources_tmp;
11345 DROP TABLE provsql_reachability_sources_tmp;
11348$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp,public
SET client_min_messages = warning;
11392CREATE OR REPLACE
FUNCTION eval_reachability(
11394 source_attribute TEXT,
11395 destination_attribute TEXT,
11403 edge_quals TEXT
DEFAULT NULL,
11404 source_rel REGCLASS
DEFAULT NULL,
11405 source_rel_attribute TEXT
DEFAULT NULL,
11406 edge_sql TEXT
DEFAULT NULL,
11407 hop_bound
INT DEFAULT NULL,
11408 hop_seed
INT DEFAULT NULL,
11409 hops_position
INT DEFAULT NULL)
11416 sp
double precision[];
11417 verbosity
INT := coalesce(current_setting(
'provsql.verbose_level',
true)::
INT,
0);
11420 IF source_rel
IS NOT NULL THEN
11423 SELECT g.source_values, g.source_tokens, g.source_probabilities
11425 FROM provsql.gather_reachability_sources(source_rel,
11426 source_rel_attribute) g;
11428 sv :=
ARRAY[]::TEXT[];
11429 st :=
ARRAY[]::UUID[];
11430 sp :=
ARRAY[]::float8[];
11434 sv :=
ARRAY[source_value];
11435 st :=
ARRAY[
'00000000-0000-0000-0000-000000000000'::UUID];
11436 sp :=
ARRAY[
1.
0::float8];
11439 e := provsql.gather_reachability_edges(edge_rel, source_attribute,
11440 destination_attribute,
11441 sv, edge_quals, edge_sql);
11442 IF to_regclass(work_name)
IS NOT NULL THEN
11443 EXECUTE format(
'DROP TABLE %I', work_name);
11446 EXECUTE format(
'CREATE TEMP TABLE %I (%s, provsql UUID) ON COMMIT DROP',
11447 work_name, coldef);
11448 PERFORM provsql.planted_scope(work_name);
11449 IF hop_bound
IS NULL THEN
11451 'INSERT INTO %I SELECT ($1::TEXT[])[m.vertex]::%s, m.token '
11452 ||
'FROM provsql.reachability_materialize($2, $3, $4, $5, $6, $7, $8, $9, $10, $11) m',
11453 work_name, coltype)
11454 USING e.vertices, e.sources, e.destinations, e.tokens, e.probabilities,
11455 e.block_keys, e.block_indices, e.extra_ids, st, sp, directed;
11460 'INSERT INTO %I SELECT %s, m.token '
11461 ||
'FROM provsql.reachability_materialize_hops($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) m',
11463 CASE WHEN hops_position =
1
11464 THEN format(
'm.hops, ($1::TEXT[])[m.vertex]::%s', coltype)
11465 ELSE format(
'($1::TEXT[])[m.vertex]::%s, m.hops', coltype)
END)
11466 USING e.vertices, e.sources, e.destinations, e.tokens, e.probabilities,
11467 e.block_keys, e.block_indices, e.extra_ids, st, sp, directed,
11468 hop_bound, hop_seed;
11470 IF verbosity >=
20 THEN
11471 RAISE NOTICE
'ProvSQL: recursive CTE "%" compiled along a tree decomposition of %',
11472 regexp_replace(work_name,
'^provsql_rec_[0-9]+a?_',
''),
11473 coalesce(edge_rel::TEXT,
'the join-defined edge query');
11475 EXCEPTION
WHEN OTHERS
THEN
11476 IF verbosity >=
10 THEN
11479 RAISE NOTICE
'ProvSQL: reachability route for "%" fell back to the generic fixpoint (%)',
11480 regexp_replace(work_name,
'^provsql_rec_[0-9]+a?_',
''), SQLERRM;
11482 PERFORM provsql.eval_recursive_system(body_sql, work_name, colnames, coldef);
11485$$
LANGUAGE plpgsql;
11504CREATE OR REPLACE
FUNCTION view_circuit(
11506 token2desc REGCLASS,
11509 'provsql',
'view_circuit' LANGUAGE C;
11522CREATE OR REPLACE
FUNCTION compile_to_ddnnf_dot(
11524 compiler TEXT =
'')
11526 'provsql',
'compile_to_ddnnf_dot' LANGUAGE C;
11541CREATE OR REPLACE
FUNCTION compile_to_ddnnf(
11543 compiler TEXT =
'')
11545 'provsql',
'compile_to_ddnnf' LANGUAGE C;
11562CREATE OR REPLACE
FUNCTION ddnnf_stats(
11564 compiler TEXT =
'')
11566 'provsql',
'ddnnf_stats' LANGUAGE C;
11580CREATE OR REPLACE
FUNCTION circuit_canonical_form(token UUID)
11582 'provsql',
'circuit_canonical_form' LANGUAGE C STRICT;
11597CREATE OR REPLACE
FUNCTION circuit_island_key(token UUID)
11599 'provsql',
'circuit_island_key' LANGUAGE C STRICT;
11611CREATE OR REPLACE
FUNCTION prob_cache_stats()
11613 'provsql',
'prob_cache_stats' LANGUAGE C;
11619CREATE OR REPLACE
FUNCTION prob_cache_reset()
11621 'provsql',
'prob_cache_reset' LANGUAGE C;
11635CREATE OR REPLACE
FUNCTION tseytin_cnf(
11637 weighted
BOOLEAN =
TRUE,
11638 mapping
BOOLEAN =
TRUE)
11640 'provsql',
'tseytin_cnf' LANGUAGE C;
11654CREATE OR REPLACE
FUNCTION tseytin_cnf_mapping_json(token UUID)
11656 'provsql',
'tseytin_cnf_mapping_json' LANGUAGE C;
11658CREATE OR REPLACE
FUNCTION tseytin_cnf_mapping(token UUID)
11659 RETURNS TABLE(variable
INT, gate UUID, probability FLOAT8)
AS $$
11660 SELECT variable, gate, probability
11661 FROM jsonb_to_recordset(tseytin_cnf_mapping_json(token))
11662 AS x(variable
INT, gate UUID, probability FLOAT8)
11664$$
LANGUAGE SQL STABLE;
11676CREATE OR REPLACE
FUNCTION tree_decomposition_dot(
11679 'provsql',
'tree_decomposition_dot' LANGUAGE C;
11699CREATE OR REPLACE
FUNCTION tool_available(name TEXT)
11701 'provsql',
'tool_available' LANGUAGE C STRICT;
11731CREATE TABLE IF NOT EXISTS tool_overrides(
11732 name TEXT
PRIMARY KEY,
11733 removed
BOOLEAN NOT NULL DEFAULT false,
11737 input_formats TEXT[],
11738 output_format TEXT,
11742 dependencies TEXT[],
11744 argtpl_circuit TEXT,
11747SELECT pg_catalog.pg_extension_config_dump(
'tool_overrides',
'');
11759CREATE OR REPLACE
FUNCTION tool_registry_list()
11760 RETURNS TABLE(name TEXT, kind TEXT, executable TEXT, operations TEXT[],
11761 input_formats TEXT[], output_format TEXT, parser TEXT,
11762 preference
INT, enabled
BOOLEAN, argtpl TEXT,
11763 argtpl_circuit TEXT, endpoint TEXT, available
BOOLEAN)
AS
11764 'provsql',
'tool_registry_list' LANGUAGE C STABLE;
11769CREATE OR REPLACE VIEW tools
AS
11770 SELECT name, kind, executable, operations, input_formats, output_format,
11771 parser, preference, enabled, argtpl, argtpl_circuit, endpoint,
11773 FROM tool_registry_list();
11808CREATE OR REPLACE
FUNCTION register_tool(
11810 executable TEXT
DEFAULT NULL,
11811 kind TEXT
DEFAULT 'cli',
11812 operations TEXT[]
DEFAULT NULL,
11813 input_formats TEXT[]
DEFAULT NULL,
11814 output_format TEXT
DEFAULT NULL,
11815 parser TEXT
DEFAULT NULL,
11816 argtpl TEXT
DEFAULT NULL,
11817 argtpl_circuit TEXT
DEFAULT NULL,
11818 preference
INT DEFAULT 0,
11819 enabled
BOOLEAN DEFAULT true,
11820 endpoint TEXT
DEFAULT NULL)
11822 'provsql',
'tool_registry_register' LANGUAGE C;
11825CREATE OR REPLACE
FUNCTION unregister_tool(name TEXT)
11827 'provsql',
'tool_registry_unregister' LANGUAGE C STRICT;
11830CREATE OR REPLACE
FUNCTION set_tool_enabled(name TEXT, enabled
BOOLEAN)
11832 'provsql',
'tool_registry_set_enabled' LANGUAGE C STRICT;
11835CREATE OR REPLACE
FUNCTION set_tool_preference(name TEXT, preference
INT)
11837 'provsql',
'tool_registry_set_preference' LANGUAGE C STRICT;
11841REVOKE ALL ON FUNCTION register_tool(TEXT, TEXT, TEXT, TEXT[], TEXT[], TEXT, TEXT, TEXT, TEXT,
INT,
BOOLEAN, TEXT)
FROM PUBLIC;
11842REVOKE ALL ON FUNCTION unregister_tool(TEXT)
FROM PUBLIC;
11843REVOKE ALL ON FUNCTION set_tool_enabled(TEXT,
BOOLEAN)
FROM PUBLIC;
11844REVOKE ALL ON FUNCTION set_tool_preference(TEXT,
INT)
FROM PUBLIC;
11852CREATE OR REPLACE
FUNCTION to_provxml(
11854 token2desc REGCLASS =
NULL)
11856 'provsql',
'to_provxml' LANGUAGE C;
11859CREATE OR REPLACE
FUNCTION provenance()
RETURNS UUID
AS
11860 'provsql',
'provenance' LANGUAGE C;
11868CREATE OR REPLACE
FUNCTION where_provenance(token UUID)
11870 'provsql',
'where_provenance' LANGUAGE C;
11881CREATE OR REPLACE
FUNCTION reset_constants_cache()
11883 'provsql',
'reset_constants_cache' LANGUAGE C;
11885SELECT reset_constants_cache();
11887SELECT create_gate(gate_zero(),
'zero');
11888SELECT create_gate(gate_one(),
'one');
11889SELECT create_gate(gate_null(),
'value',
NULL,
NULL,
NULL,
'NULL');
11894CREATE TYPE QUERY_TYPE_ENUM
AS ENUM (
11913CREATE FUNCTION sr_formula(token ANYELEMENT, token2value REGCLASS =
NULL)
11917 IF token
IS NULL THEN
11920 RETURN provsql.provenance_evaluate_compiled(
11927$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
11947CREATE FUNCTION sr_formula_rows(
11948 token ANYELEMENT, token2value REGCLASS =
NULL, max_size
INT =
50)
11949 RETURNS TABLE(name TEXT, formula TEXT)
AS
11950 'provsql',
'sr_formula_rows' LANGUAGE C PARALLEL SAFE STABLE;
11953CREATE FUNCTION sr_counting(token ANYELEMENT, token2value REGCLASS)
11957 RETURN provsql.provenance_evaluate_compiled(
11964$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
11967CREATE FUNCTION sr_why(token ANYELEMENT, token2value REGCLASS)
11971 RETURN provsql.provenance_evaluate_compiled(
11978$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
11981CREATE FUNCTION sr_how(token ANYELEMENT, token2value REGCLASS)
11985 RETURN provsql.provenance_evaluate_compiled(
11992$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
11995CREATE FUNCTION sr_which(token ANYELEMENT, token2value REGCLASS)
11999 RETURN provsql.provenance_evaluate_compiled(
12006$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
12014CREATE FUNCTION sr_boolexpr(token ANYELEMENT, token2value REGCLASS =
NULL)
12018 IF token
IS NULL THEN
12021 RETURN provsql.provenance_evaluate_compiled(
12028$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
12037CREATE FUNCTION sr_boolean(token ANYELEMENT, token2value REGCLASS =
NULL)
12041 IF token
IS NULL THEN
12044 RETURN provsql.provenance_evaluate_compiled(
12051$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
12060CREATE FUNCTION plain_truth(token UUID)
12062 'provsql',
'plain_truth' LANGUAGE C PARALLEL SAFE STABLE;
12065CREATE FUNCTION true_nonzero(token UUID)
12067 'provsql',
'true_nonzero' LANGUAGE C PARALLEL SAFE STABLE;
12095CREATE FUNCTION nonzero(token UUID,
12096 semiring TEXT
DEFAULT NULL,
12097 mapping REGCLASS
DEFAULT NULL)
12101 IF token
IS NULL THEN
12104 IF semiring
IS NULL THEN
12105 RETURN provsql.true_nonzero(token);
12106 ELSIF semiring =
'BOOLEAN' THEN
12107 IF mapping
IS NULL THEN
12113 RETURN provsql.plain_truth(token);
12115 RETURN provsql.provenance_evaluate_compiled(token, mapping,
'BOOLEAN',
TRUE);
12116 ELSIF semiring =
'counting' THEN
12117 RETURN provsql.provenance_evaluate_compiled(token, mapping,
'counting',
1) <>
0;
12119 RAISE EXCEPTION
'nonzero: unsupported semiring "%" (supported: BOOLEAN, counting; NULL for the universal zero test)', semiring
12120 USING ERRCODE =
'feature_not_supported',
12121 DETAIL =
'provsql-reason: nonzero-semiring; scope: gap';
12124$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
12137CREATE FUNCTION present(token UUID)
12140 SELECT provsql.nonzero(token,
'BOOLEAN');
12141$$
LANGUAGE sql PARALLEL SAFE STABLE;
12155CREATE FUNCTION sr_tropical(token ANYELEMENT, token2value REGCLASS,
12156 nonnegative
BOOLEAN =
false)
12160 RETURN provsql.provenance_evaluate_compiled(
12163 CASE WHEN nonnegative
THEN 'tropical_nonneg' ELSE 'tropical' END,
12167$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
12182CREATE FUNCTION sr_regex(token ANYELEMENT, token2value REGCLASS)
12186 RETURN provsql.provenance_evaluate_compiled(
12193$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
12196CREATE FUNCTION provenance_evaluate_tropical_paths(
12197 token UUID, costs REGCLASS, labels REGCLASS,
12198 OUT cost
FLOAT,
OUT paths TEXT)
12199 AS 'provsql',
'provenance_evaluate_tropical_paths'
12200 LANGUAGE C PARALLEL SAFE STABLE;
12213CREATE FUNCTION sr_tropical_paths(token ANYELEMENT, costs REGCLASS,
12215 OUT cost
FLOAT,
OUT paths TEXT)
12218 SELECT *
FROM provsql.provenance_evaluate_tropical_paths(token, costs, labels);
12219$$
LANGUAGE sql STRICT PARALLEL SAFE STABLE;
12222CREATE FUNCTION provenance_evaluate_best_path(
12223 token UUID, costs REGCLASS, labels REGCLASS,
12224 OUT cost
FLOAT,
OUT path TEXT[])
12225 AS 'provsql',
'provenance_evaluate_best_path'
12226 LANGUAGE C PARALLEL SAFE STABLE;
12240CREATE FUNCTION sr_best_path(token ANYELEMENT, costs REGCLASS,
12242 OUT cost
FLOAT,
OUT path TEXT[])
12245 SELECT *
FROM provsql.provenance_evaluate_best_path(token, costs, labels);
12246$$
LANGUAGE sql STRICT PARALLEL SAFE STABLE;
12253CREATE FUNCTION sr_viterbi(token ANYELEMENT, token2value REGCLASS)
12257 RETURN provsql.provenance_evaluate_compiled(
12264$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
12273CREATE FUNCTION sr_lukasiewicz(token ANYELEMENT, token2value REGCLASS)
12277 RETURN provsql.provenance_evaluate_compiled(
12284$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
12302CREATE FUNCTION sr_minmax(token UUID, token2value REGCLASS, element_one ANYENUM)
12306 IF token
IS NULL OR token2value
IS NULL THEN
12309 RETURN provsql.provenance_evaluate_compiled(
12316$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
12331CREATE FUNCTION sr_maxmin(token UUID, token2value REGCLASS, element_one ANYENUM)
12335 IF token
IS NULL OR token2value
IS NULL THEN
12338 RETURN provsql.provenance_evaluate_compiled(
12345$$
LANGUAGE plpgsql PARALLEL SAFE STABLE;
12353CREATE OR REPLACE
FUNCTION subset_evaluate(
12354 token UUID, token2value REGCLASS, semiring TEXT, element_one ANYENUM)
12356 'provsql',
'provenance_evaluate_compiled' LANGUAGE C PARALLEL SAFE STABLE;
12359CREATE OR REPLACE
FUNCTION subset_labels(mask
bigint, element_one ANYENUM)
12360 RETURNS ANYARRAY
AS
12361 'provsql',
'subset_labels' LANGUAGE C IMMUTABLE PARALLEL SAFE;
12364CREATE OR REPLACE
FUNCTION subset_settling(mask
bigint, element_one ANYENUM)
12366 'provsql',
'subset_settling' LANGUAGE C IMMUTABLE PARALLEL SAFE;
12372CREATE OR REPLACE
FUNCTION subset_member(
12373 token UUID, token2value REGCLASS, semiring TEXT, label TEXT)
12375 'provsql',
'subset_member' LANGUAGE C PARALLEL SAFE STABLE;
12391CREATE OR REPLACE
FUNCTION sr_subset(
12392 token UUID, token2value REGCLASS, element_one ANYENUM)
12393 RETURNS ANYARRAY
AS
12395 SELECT provsql.subset_labels(
12396 provsql.subset_evaluate(token, token2value,
'subset', element_one),
12398 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12399$$
LANGUAGE sql PARALLEL SAFE STABLE;
12416CREATE OR REPLACE
FUNCTION sr_clearance(
12417 token UUID, token2value REGCLASS, element_one ANYENUM)
12418 RETURNS ANYARRAY
AS
12420 SELECT provsql.subset_labels(
12421 provsql.subset_evaluate(token, token2value,
'clearance', element_one),
12423 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12424$$
LANGUAGE sql PARALLEL SAFE STABLE;
12435CREATE OR REPLACE
FUNCTION visible_at(
12436 token UUID, token2value REGCLASS, level TEXT)
12439 SELECT provsql.subset_member(token, token2value,
'clearance', level);
12440$$
LANGUAGE sql STRICT PARALLEL SAFE STABLE;
12455CREATE OR REPLACE
FUNCTION clearance_settling(
12456 token UUID, token2value REGCLASS, element_one ANYENUM,
12457 OUT settles_at ANYENUM,
OUT visible_at_top
BOOLEAN)
AS
12459 SELECT provsql.subset_settling(m, element_one),
12460 enum_last(element_one) =
ANY(provsql.subset_labels(m, element_one))
12461 FROM provsql.subset_evaluate(token, token2value,
'clearance', element_one) m
12462 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12463$$
LANGUAGE sql PARALLEL SAFE STABLE;
12480CREATE OR REPLACE
FUNCTION sr_consent(
12481 token UUID, token2value REGCLASS, element_one ANYENUM,
12482 OUT purposes ANYARRAY,
OUT unrestricted
BOOLEAN)
AS
12484 SELECT provsql.subset_labels(m, element_one), m <
0
12485 FROM provsql.subset_evaluate(token, token2value,
'consent', element_one) m
12486 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12487$$
LANGUAGE sql PARALLEL SAFE STABLE;
12499CREATE OR REPLACE
FUNCTION consented_for(
12500 token UUID, token2value REGCLASS, purpose TEXT)
12503 SELECT provsql.subset_member(token, token2value,
'consent', purpose);
12504$$
LANGUAGE sql STRICT PARALLEL SAFE STABLE;
12516CREATE OR REPLACE
FUNCTION consent_purposes(
12517 token UUID, token2value REGCLASS, element_one ANYENUM)
12518 RETURNS ANYARRAY
AS
12520 SELECT CASE WHEN m <
0 THEN provsql.subset_labels(m, element_one)
12521 ELSE provsql.subset_labels(
0, element_one)
END
12522 FROM provsql.subset_evaluate(token, token2value,
'consent', element_one) m
12523 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12524$$
LANGUAGE sql PARALLEL SAFE STABLE;
12539CREATE OR REPLACE
FUNCTION consent_conflicts(
12540 token UUID, token2value REGCLASS, element_one ANYENUM)
12541 RETURNS ANYARRAY
AS
12543 SELECT CASE WHEN m <
0 THEN provsql.subset_labels(~m, element_one)
12544 ELSE provsql.subset_labels(m, element_one)
END
12545 FROM provsql.subset_evaluate(token, token2value,
'consent', element_one) m
12546 WHERE token
IS NOT NULL AND token2value
IS NOT NULL;
12547$$
LANGUAGE sql PARALLEL SAFE STABLE;
12558CREATE FUNCTION choose_function(state ANYELEMENT, data ANYELEMENT)
12559 RETURNS ANYELEMENT
AS
12562 IF state
IS NULL THEN
12568$$
LANGUAGE plpgsql PARALLEL SAFE IMMUTABLE;
12571CREATE AGGREGATE choose(ANYELEMENT) (
12572 SFUNC = choose_function,
12577CREATE FUNCTION array_collect_step(state ANYARRAY, data ANYNONARRAY)
12578 RETURNS ANYARRAY
AS
12579$$
SELECT array_append(state, data) $$
12580LANGUAGE sql PARALLEL SAFE IMMUTABLE;
12586CREATE AGGREGATE array_collect(ANYNONARRAY) (
12587 SFUNC = array_collect_step,
12604CREATE OR REPLACE
FUNCTION explode_table(_tbl TEXT, AGG_TOKEN TEXT)
12612 SELECT n.nspname
INTO _nsp
12613 FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
12614 WHERE c.oid = _tbl::REGCLASS;
12617 CREATE TABLE %1$I.temp_exploded AS
12620 provsql.get_extra(children[2]) AS new_t,
12621 provsql.provenance_times(children[1], provsql) AS new_provsql
12624 SELECT provsql.get_children(sm) AS children
12625 FROM UNNEST(provsql.agg_token_explode_children(%3$I)) AS sm
12626 ) AS sub', _nsp, _tbl, AGG_TOKEN);
12627 EXECUTE format(
'DROP TABLE %I.%I', _nsp, _tbl);
12628 EXECUTE format(
'ALTER TABLE %I.temp_exploded DROP COLUMN %I, DROP COLUMN provsql', _nsp, AGG_TOKEN);
12629 EXECUTE format(
'ALTER TABLE %I.temp_exploded RENAME COLUMN new_t TO %I', _nsp, AGG_TOKEN);
12630 EXECUTE format(
'ALTER TABLE %I.temp_exploded RENAME COLUMN new_provsql TO provsql', _nsp);
12631 EXECUTE format(
'ALTER TABLE %I.temp_exploded RENAME TO %I', _nsp, _tbl);
12633$$
LANGUAGE plpgsql;
12646CREATE OR REPLACE
FUNCTION build_id()
12648 AS 'provsql',
'build_id' LANGUAGE C STABLE STRICT PARALLEL SAFE;
12670CREATE OR REPLACE
FUNCTION setup_search_path()
12672 LANGUAGE plpgsql
AS $$
12674 db TEXT := current_database();
12680 SELECT s.setconfig
INTO cfg
12681 FROM pg_db_role_setting s
12682 JOIN pg_database d
ON d.oid = s.setdatabase
12683 WHERE d.datname = db
AND s.setrole =
0;
12685 IF cfg
IS NOT NULL THEN
12686 SELECT substr(e, length(
'search_path=') +
1)
INTO cur
12687 FROM unnest(cfg)
AS e
12688 WHERE e
LIKE 'search_path=%';
12691 IF cur
IS NULL THEN
12694 new_path :=
'"$user", public, provsql';
12695 EXECUTE format(
'ALTER DATABASE %I SET search_path = %s', db, new_path);
12696 RAISE NOTICE
'ProvSQL: set search_path = % for database "%" (no previous database-level setting). Only new sessions are affected.',
12703 SELECT 1 FROM unnest(string_to[](cur,
','))
AS p
12704 WHERE btrim(btrim(p),
'"') =
'provsql')
12706 RAISE NOTICE
'ProvSQL: search_path for database "%" already contains provsql (= %); no change.',
12711 new_path := cur ||
', provsql';
12712 EXECUTE format(
'ALTER DATABASE %I SET search_path = %s', db, new_path);
12713 RAISE NOTICE
'ProvSQL: appended provsql to search_path for database "%" (now: %). Only new sessions are affected.',
12719GRANT USAGE
ON SCHEMA provsql
TO PUBLIC;
12721SET search_path
TO public;
12736 has_provsql
BOOLEAN;
12738 SELECT reset_val
INTO rp
FROM pg_settings
WHERE name =
'search_path';
12739 SELECT bool_or(btrim(btrim(p),
'"') =
'provsql')
12741 FROM unnest(string_to[](coalesce(rp,
''),
','))
AS p;
12742 IF NOT coalesce(has_provsql,
false)
THEN
12743 SET LOCAL client_min_messages = notice;
12744 RAISE NOTICE
'ProvSQL: schema "provsql" is not in your default search_path (currently: %).', rp;
12745 RAISE NOTICE
'ProvSQL operators and functions are resolved through search_path. Run "SELECT provsql.setup_search_path();" to add it, or set it manually (e.g. ALTER DATABASE % SET search_path = "$user", public, provsql).', quote_ident(current_database());
12759SELECT provsql.reset_constants_cache();
12760SET search_path
TO provsql;
12789CREATE TABLE update_provenance (
12792 query_type QUERY_TYPE_ENUM,
12794 ts
TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
12795 valid_time TSTZMULTIRANGE
DEFAULT TSTZMULTIRANGE(tstzrange(
CURRENT_TIMESTAMP,
NULL)),
12814CREATE OR REPLACE
FUNCTION transaction_token()
12823 tok := current_setting(
'provsql.transaction_token',
true);
12824 IF tok
IS NOT NULL AND tok <>
'' THEN
12828 new_tok := public.uuid_generate_v4();
12829 PERFORM create_gate(new_tok,
'update');
12830 PERFORM set_config(
'provsql.transaction_token', new_tok::TEXT,
true);
12835 query_text :=
NULL;
12842 INSERT INTO update_provenance(provsql, query, query_type, username, ts,
12844 VALUES (new_tok, query_text,
'TRANSACTION',
current_user,
12845 CURRENT_TIMESTAMP,
'{(,)}'::TSTZMULTIRANGE,
12846 pg_current_xact_id());
12863CREATE OR REPLACE
FUNCTION stamp_commit_time()
12867 now_ts TIMESTAMPTZ := clock_timestamp();
12869 UPDATE update_provenance
12871 valid_time =
CASE WHEN query_type
IN (
'TRANSACTION',
'REPLACE')
12873 ELSE TSTZMULTIRANGE(tstzrange(now_ts,
NULL))
END
12874 WHERE provsql =
NEW.provsql;
12877$$
LANGUAGE plpgsql;
12880 IF NOT EXISTS (
SELECT 1 FROM pg_trigger
12881 WHERE tgrelid =
'provsql.update_provenance'::REGCLASS
12882 AND tgname =
'stamp_commit_time')
THEN
12883 CREATE CONSTRAINT TRIGGER stamp_commit_time
12884 AFTER
INSERT ON provsql.update_provenance
12885 DEFERRABLE INITIALLY DEFERRED
12886 FOR EACH ROW EXECUTE FUNCTION provsql.stamp_commit_time();
12893CREATE OR REPLACE
FUNCTION add_provenance(_tbl REGCLASS)
12901 SELECT 1 FROM pg_attribute
12902 WHERE attrelid = _tbl
AND attname =
'provsql' AND NOT attisdropped
12904 RAISE NOTICE
'table % already has provenance tracking', _tbl;
12910 EXECUTE format(
'ALTER TABLE %s ADD COLUMN provsql UUID', _tbl);
12912 'UPDATE %s SET provsql = public.uuid_generate_v4() WHERE provsql IS NULL',
12914 EXECUTE format(
'CREATE INDEX ON %s(provsql)', _tbl);
12916 'CREATE TRIGGER provenance_guard BEFORE INSERT OR UPDATE OF provsql '
12917 'ON %s FOR EACH ROW EXECUTE FUNCTION provsql.provenance_guard()',
12920 EXECUTE format(
'CREATE TRIGGER insert_statement AFTER INSERT ON %s REFERENCING NEW TABLE AS NEW_TABLE FOR EACH STATEMENT EXECUTE FUNCTION provsql.insert_statement_trigger()', _tbl);
12921 EXECUTE format(
'CREATE TRIGGER delete_statement AFTER DELETE ON %s REFERENCING OLD TABLE AS OLD_TABLE FOR EACH STATEMENT EXECUTE FUNCTION provsql.delete_statement_trigger()', _tbl);
12922 EXECUTE format(
'CREATE TRIGGER update_statement AFTER UPDATE ON %s REFERENCING OLD TABLE AS OLD_TABLE NEW TABLE AS NEW_TABLE FOR EACH STATEMENT EXECUTE FUNCTION provsql.update_statement_trigger()', _tbl);
12924 PERFORM provsql.set_table_info(_tbl::oid,
'tid');
12925 PERFORM provsql.set_ancestors(_tbl::oid,
ARRAY[_tbl::oid]);
12927$$
LANGUAGE plpgsql SECURITY DEFINER;
12933CREATE OR REPLACE
FUNCTION delete_statement_trigger()
12943 enable_trigger BOOL;
12945 enable_trigger := current_setting(
'provsql.update_provenance',
true);
12946 IF enable_trigger =
'f' THEN
12949 delete_token := public.uuid_generate_v4();
12951 PERFORM create_gate(delete_token,
'update');
12955 FROM pg_stat_activity
12956 WHERE pid = pg_backend_pid();
12958 tx_token := transaction_token();
12960 INSERT INTO update_provenance (provsql, query, query_type, username, ts,
12961 valid_time, xid, tx_token)
12962 VALUES (delete_token, query_text,
'DELETE',
current_user,
CURRENT_TIMESTAMP,
12963 TSTZMULTIRANGE(tstzrange(
CURRENT_TIMESTAMP,
NULL)),
12964 pg_current_xact_id(), tx_token);
12968 delete_token := provenance_times(tx_token, delete_token);
12970 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
12971 EXECUTE format(
'INSERT INTO %I.%I SELECT * FROM OLD_TABLE;', TG_TABLE_SCHEMA, TG_TABLE_NAME);
12972 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
12974 FOR r
IN (
SELECT *
FROM OLD_TABLE) LOOP
12975 old_token := r.provsql;
12976 new_token := provenance_monus(old_token, delete_token);
12978 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
12979 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2;', TG_TABLE_SCHEMA, TG_TABLE_NAME)
12980 USING new_token, old_token;
12981 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
12986$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp SECURITY DEFINER;
12995CREATE OR REPLACE
FUNCTION insert_statement_trigger()
13005 enable_trigger BOOL;
13007 enable_trigger := current_setting(
'provsql.update_provenance',
true);
13008 IF enable_trigger =
'f' THEN
13012 insert_token := public.uuid_generate_v4();
13014 PERFORM create_gate(insert_token,
'update');
13018 FROM pg_stat_activity
13019 WHERE pid = pg_backend_pid();
13021 tx_token := transaction_token();
13023 INSERT INTO update_provenance (provsql, query, query_type, username, ts,
13024 valid_time, xid, tx_token)
13025 VALUES (insert_token, query_text,
'INSERT',
current_user,
CURRENT_TIMESTAMP,
13026 TSTZMULTIRANGE(tstzrange(
CURRENT_TIMESTAMP,
NULL)),
13027 pg_current_xact_id(), tx_token);
13031 insert_token := provenance_times(tx_token, insert_token);
13033 FOR r
IN (
SELECT *
FROM NEW_TABLE) LOOP
13034 old_token := r.provsql;
13035 new_token := provenance_times(old_token, insert_token);
13036 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13037 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2;', TG_TABLE_SCHEMA, TG_TABLE_NAME)
13038 USING new_token, old_token;
13039 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
13044$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp SECURITY DEFINER;
13052CREATE OR REPLACE
FUNCTION update_statement_trigger()
13062 enable_trigger BOOL;
13064 enable_trigger := current_setting(
'provsql.update_provenance',
true);
13065 IF enable_trigger =
'f' THEN
13068 update_token := public.uuid_generate_v4();
13070 PERFORM create_gate(update_token,
'update');
13074 FROM pg_stat_activity
13075 WHERE pid = pg_backend_pid();
13077 tx_token := transaction_token();
13079 INSERT INTO update_provenance (provsql, query, query_type, username, ts,
13080 valid_time, xid, tx_token)
13081 VALUES (update_token, query_text,
'UPDATE',
current_user,
CURRENT_TIMESTAMP,
13082 TSTZMULTIRANGE(tstzrange(
CURRENT_TIMESTAMP,
NULL)),
13083 pg_current_xact_id(), tx_token);
13087 update_token := provenance_times(tx_token, update_token);
13089 FOR r
IN (
SELECT *
FROM NEW_TABLE) LOOP
13090 old_token := r.provsql;
13091 new_token := provenance_times(old_token, update_token);
13093 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13094 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2;', TG_TABLE_SCHEMA, TG_TABLE_NAME)
13095 USING new_token, old_token;
13096 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
13099 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13100 EXECUTE format(
'INSERT INTO %I.%I SELECT * FROM OLD_TABLE;', TG_TABLE_SCHEMA, TG_TABLE_NAME);
13101 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
13103 FOR r
IN (
SELECT *
FROM OLD_TABLE) LOOP
13104 old_token := r.provsql;
13105 new_token := provenance_monus(old_token, update_token);
13107 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13108 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2;', TG_TABLE_SCHEMA, TG_TABLE_NAME)
13109 USING new_token, old_token;
13110 PERFORM set_config(
'provsql.update_provenance',
'on',
true);
13115$$
LANGUAGE plpgsql
SET search_path=provsql,pg_temp SECURITY DEFINER;
13127SET search_path
TO provsql;
13141CREATE FUNCTION sr_temporal(token ANYELEMENT, token2value REGCLASS)
13142 RETURNS TSTZMULTIRANGE
AS
13145 RETURN provsql.provenance_evaluate_compiled(
13149 '{(,)}'::TSTZMULTIRANGE
13152$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
13168CREATE FUNCTION sr_interval_num(token ANYELEMENT, token2value REGCLASS)
13169 RETURNS nummultirange
AS
13172 RETURN provsql.provenance_evaluate_compiled(
13176 '{(,)}'::nummultirange
13179$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
13195CREATE FUNCTION sr_interval_int(token ANYELEMENT, token2value REGCLASS)
13196 RETURNS int4multirange
AS
13199 RETURN provsql.provenance_evaluate_compiled(
13203 '{(,)}'::int4multirange
13206$$
LANGUAGE plpgsql STRICT PARALLEL SAFE STABLE;
13217CREATE OR REPLACE
FUNCTION union_tstzintervals(
13219 token2value REGCLASS
13221RETURNS TSTZMULTIRANGE
AS
13223 SELECT sr_temporal(token, token2value)
13224$$
LANGUAGE SQL PARALLEL SAFE STABLE;
13234CREATE OR REPLACE
FUNCTION timetravel(
13236 at_time TIMESTAMPTZ
13238RETURNS SETOF RECORD
13243 RETURN QUERY
EXECUTE format(
13247 sr_temporal(provenance(), %2$L)
13251 sr_temporal(provenance(), %2$L) @> %3$L::TIMESTAMPTZ
13254 'provsql.time_validity_view',
13269CREATE OR REPLACE
FUNCTION timeslice(
13271 from_time TIMESTAMPTZ,
13272 to_time TIMESTAMPTZ
13274RETURNS SETOF RECORD
13279 RETURN QUERY
EXECUTE format(
13283 sr_temporal(provenance(), %2$L)
13287 sr_temporal(provenance(), %2$L)
13288 && tstzrange(%3$L::TIMESTAMPTZ, %4$L::TIMESTAMPTZ)
13291 'provsql.time_validity_view',
13308CREATE OR REPLACE
FUNCTION history(
13313RETURNS SETOF RECORD
13318 condition TEXT :=
'';
13321 IF array_length(col_names,
1)
IS NULL
13322 OR array_length(col_values,
1)
IS NULL
13323 OR array_length(col_names,
1) != array_length(col_values,
1)
13325 RAISE EXCEPTION
'col_names and col_values must have the same (non-null) length';
13328 FOR i
IN 1..array_length(col_names,
1)
13331 condition := condition ||
' AND ';
13333 condition := condition || format(
'%I = %L', col_names[i], col_values[i]);
13336 RETURN QUERY
EXECUTE format(
13340 sr_temporal(provenance(), %L)
13347 'provsql.time_validity_view',
13360CREATE OR REPLACE
FUNCTION get_valid_time(
13364RETURNS TSTZMULTIRANGE
13368 result TSTZMULTIRANGE;
13373 sr_temporal(provenance(), %L)
13379 'provsql.time_validity_view',
13398CREATE OR REPLACE
FUNCTION undo(
13412 tracking TEXT := current_setting(
'provsql.update_provenance');
13417 SELECT query
INTO undone_query
13418 FROM update_provenance
13423 RAISE NOTICE
'Unable to find % in update_provenance', c;
13429 FROM pg_stat_activity
13430 WHERE pid = pg_backend_pid();
13432 undo_token := public.uuid_generate_v4();
13433 PERFORM create_gate(undo_token,
'update');
13434 INSERT INTO update_provenance(provsql, query, query_type, username, ts,
13435 valid_time, xid, tx_token)
13442 TSTZMULTIRANGE(tstzrange(
CURRENT_TIMESTAMP,
NULL)),
13443 pg_current_xact_id(),
13444 transaction_token()
13449 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13454 WHERE nspname
NOT IN (
'pg_catalog',
'information_schema',
'pg_toast',
'pg_temp_1',
'pg_toast_temp_1')
13457 EXECUTE format(
'SELECT tablename AS tname FROM pg_tables WHERE schemaname = %L', schema_rec.nspname)
13461 FROM information_schema.columns
13462 WHERE table_schema = schema_rec.nspname
13463 AND table_name = table_rec.tname
13464 AND table_name <>
'update_provenance'
13465 AND column_name =
'provsql'
13468 EXECUTE format(
'SELECT provsql AS x FROM %I.%I', schema_rec.nspname, table_rec.tname)
13470 new_x := replace_the_circuit(row_rec.x, c, undo_token);
13471 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2',
13472 schema_rec.nspname, table_rec.tname)
13473 USING new_x, row_rec.x;
13479 PERFORM set_config(
'provsql.update_provenance', tracking,
true);
13495CREATE OR REPLACE
FUNCTION replace_the_circuit(
13507 ntype PROVENANCE_GATE;
13510 RETURN provenance_monus(c, u);
13512 ELSIF get_gate_type(x) =
'update' OR get_gate_type(x) =
'input' THEN
13516 FOREACH child
IN ARRAY get_children(x)
13518 nchildren := array_append(nchildren, replace_the_circuit(child, c, u));
13521 ntoken := public.uuid_generate_v4();
13522 ntype := get_gate_type(x);
13524 PERFORM create_gate(ntoken, ntype, nchildren);
13541CREATE OR REPLACE
FUNCTION substitute_gate(
13553 changed
BOOLEAN :=
false;
13555 ntype PROVENANCE_GATE;
13560 ntype := get_gate_type(x);
13562 IF ntype
IN (
'input',
'update',
'rv',
'value',
'zero',
'one')
THEN
13566 FOREACH child
IN ARRAY get_children(x)
13568 rewritten := substitute_gate(child,
old,
new);
13569 IF rewritten <> child
THEN
13572 nchildren := array_append(nchildren, rewritten);
13574 IF NOT changed
THEN
13577 ntoken := public.uuid_generate_v4();
13578 PERFORM create_gate(ntoken, ntype, nchildren);
13602CREATE OR REPLACE
FUNCTION replace_update(
13616 tracking TEXT := current_setting(
'provsql.update_provenance');
13618 IF old IS NULL OR p
IS NULL THEN
13619 RAISE EXCEPTION
'replace_update: neither argument may be NULL';
13621 IF get_gate_type(
old) <>
'update' THEN
13622 RAISE EXCEPTION
'replace_update: % is not an update gate',
old
13623 USING HINT =
'Use provsql.replace_input() for a tuple''s own input gate.';
13626 SELECT *
INTO old_row
FROM update_provenance
WHERE provsql =
old LIMIT
1;
13627 IF old_row
IS NULL THEN
13628 RAISE EXCEPTION
'replace_update: % is not recorded in update_provenance',
old;
13631 new_token := public.uuid_generate_v4();
13632 PERFORM create_gate(new_token,
'update');
13633 PERFORM set_prob(new_token, p);
13635 INSERT INTO update_provenance(provsql, query, query_type, username, ts,
13636 valid_time, xid, tx_token)
13637 VALUES (new_token, old_row.query,
'REPLACE',
current_user,
13641 old_row.valid_time,
13642 pg_current_xact_id(), transaction_token());
13646 PERFORM set_config(
'provsql.update_provenance',
'off',
true);
13651 WHERE nspname
NOT IN (
'pg_catalog',
'information_schema',
'pg_toast',
'pg_temp_1',
'pg_toast_temp_1')
13654 EXECUTE format(
'SELECT tablename AS tname FROM pg_tables WHERE schemaname = %L', schema_rec.nspname)
13658 FROM information_schema.columns
13659 WHERE table_schema = schema_rec.nspname
13660 AND table_name = table_rec.tname
13661 AND table_name <>
'update_provenance'
13662 AND column_name =
'provsql'
13665 EXECUTE format(
'SELECT provsql AS x FROM %I.%I', schema_rec.nspname, table_rec.tname)
13667 new_x := substitute_gate(row_rec.x,
old, new_token);
13668 IF new_x <> row_rec.x
THEN
13669 EXECUTE format(
'UPDATE %I.%I SET provsql = $1 WHERE provsql = $2',
13670 schema_rec.nspname, table_rec.tname)
13671 USING new_x, row_rec.x;
13678 PERFORM set_config(
'provsql.update_provenance', tracking,
true);
13688CREATE VIEW provsql.time_validity_view
AS
13689 SELECT valid_time
AS value, provsql
AS provenance
FROM provsql.update_provenance;
13693SET search_path
TO public;
13700SELECT provsql.reset_constants_cache();