-- The dimension map of a dimension whose index keeps a journal of its changed keys (a child index is connected to it): the map is stamped with the horizon of the snapshot it
-- was read under and a statement reads it when no row was inserted into the dimension since and no key was changed in it, so a write that nobody has vacuumed away only
-- makes the next statement read the leaves again and keep a map of its own, where the pages of the table are the map's state otherwise. The answers are those of the normal plan.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET jit = off;
SET sift.agg_force = on;
SET sift.agg_lookup_cache_kb = 0;
SET sift.agg_lookup_shared_kb = 0;
CREATE TABLE dim (k int PRIMARY KEY, a int, b int, flag bool) WITH (autovacuum_enabled = false);
INSERT INTO dim SELECT i, i % 7, i % 3, i % 5 = 0 FROM generate_series(0, 4999) i;
CREATE INDEX dim_col ON dim USING sift_col (k, a, b, flag);
NOTICE:  sift_col index "dim_col": 2 automatic choices (rollup_columns, track_changes)
DETAIL:  rollup_columns = b, flag; track_changes = k
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('dim_col');
CREATE TABLE ev (id int PRIMARY KEY, k int, v int) WITH (autovacuum_enabled = false);
INSERT INTO ev SELECT i, CASE WHEN i % 211 = 0 THEN NULL ELSE i % 5100 END, i % 11 FROM generate_series(1, 40000) i;
CREATE INDEX ev_col ON ev USING sift_col (id, k, v) WITH (connect = 'k -> dim(k)');
NOTICE:  sift_col index "ev_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ev_col');
VACUUM (FREEZE, ANALYZE) ev, dim;
CREATE TABLE shapes (n int, sql text);
INSERT INTO shapes VALUES
 (1, $q$SELECT dim.a, count(*), sum(ev.v) FROM ev JOIN dim ON dim.k = ev.k GROUP BY 1 ORDER BY 1$q$),
 (2, $q$SELECT dim.a, dim.b, count(*) FROM ev LEFT JOIN dim ON dim.k = ev.k GROUP BY 1, 2 ORDER BY 1, 2$q$),
 (3, $q$SELECT ev.id % 4, sum(dim.a), count(dim.flag), max(dim.b), count(DISTINCT ev.k) FROM ev JOIN dim ON dim.k = ev.k GROUP BY 1 ORDER BY 1$q$);
CREATE FUNCTION answer(sql text, scan bool) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text; BEGIN
	PERFORM set_config('sift.enable_agg', scan::text, true);
	EXECUTE 'SELECT string_agg(t::text, '';'' ORDER BY t::text) FROM (' || sql || ') t' INTO r;
	RETURN r;
END $$;
-- the maps the statement shared and the ones it made, from EXPLAIN ANALYZE
CREATE FUNCTION maps(sql text, OUT shared int, OUT built int) LANGUAGE plpgsql AS $$
DECLARE l text; BEGIN
	shared := 0; built := 0;
	PERFORM set_config('sift.enable_agg', 'true', true);
	FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) ' || sql LOOP
		IF l ~ '(\d+) maps shared' THEN shared := substring(l from '(\d+) maps shared')::int; END IF;
		IF l ~ '(\d+) maps built' THEN built := substring(l from '(\d+) maps built')::int; END IF;
	END LOOP;
END $$;
CREATE FUNCTION report(stage text) RETURNS TABLE (stage text, shape int, same_answers bool, shared bool, built bool) LANGUAGE sql AS $$
	SELECT stage, n, answer(sql, true) = answer(sql, false), m.shared > 0, m.built > 0 FROM shapes, LATERAL maps(sql) m ORDER BY n
$$;
-- the first statement of a shape reads the leaves and keeps the map; the next ones share it
SELECT * FROM report('first');
 stage | shape | same_answers | shared | built 
-------+-------+--------------+--------+-------
 first |     1 | t            | f      | t
 first |     2 | t            | f      | t
 first |     3 | t            | f      | t
(3 rows)

SELECT * FROM report('shared');
 stage  | shape | same_answers | shared | built 
--------+-------+--------------+--------+-------
 shared |     1 | t            | t      | f
 shared |     2 | t            | t      | f
 shared |     3 | t            | t      | f
(3 rows)

-- a committed update, never vacuumed: the first statement after it reads the leaves again, the next ones share that map
UPDATE dim SET a = 6, flag = NULL WHERE k % 31 = 0;
SELECT * FROM report('after an update');
      stage      | shape | same_answers | shared | built 
-----------------+-------+--------------+--------+-------
 after an update |     1 | t            | f      | t
 after an update |     2 | t            | f      | t
 after an update |     3 | t            | f      | t
(3 rows)

SELECT * FROM report('shared again');
    stage     | shape | same_answers | shared | built 
--------------+-------+--------------+--------+-------
 shared again |     1 | t            | t      | f
 shared again |     2 | t            | t      | f
 shared again |     3 | t            | t      | f
(3 rows)

-- a committed insert (events of these keys had no row), and a committed delete
INSERT INTO dim SELECT i, 1, 1, true FROM generate_series(5000, 5049) i;
SELECT * FROM report('after an insert');
      stage      | shape | same_answers | shared | built 
-----------------+-------+--------------+--------+-------
 after an insert |     1 | t            | f      | t
 after an insert |     2 | t            | f      | t
 after an insert |     3 | t            | f      | t
(3 rows)

DELETE FROM dim WHERE k % 53 = 0;
SELECT * FROM report('after a delete');
     stage      | shape | same_answers | shared | built 
----------------+-------+--------------+--------+-------
 after a delete |     1 | t            | f      | t
 after a delete |     2 | t            | f      | t
 after a delete |     3 | t            | f      | t
(3 rows)

SELECT * FROM report('shared after the delete');
          stage          | shape | same_answers | shared | built 
-------------------------+-------+--------------+--------+-------
 shared after the delete |     1 | t            | t      | f
 shared after the delete |     2 | t            | t      | f
 shared after the delete |     3 | t            | t      | f
(3 rows)

-- the reader's own transaction: its changes are in the answers and the map it makes is nobody else's
BEGIN;
UPDATE dim SET b = 2 WHERE k % 17 = 0;
SELECT * FROM report('in the transaction');
       stage        | shape | same_answers | shared | built 
--------------------+-------+--------------+--------+-------
 in the transaction |     1 | t            | f      | t
 in the transaction |     2 | t            | f      | t
 in the transaction |     3 | t            | f      | t
(3 rows)

SELECT * FROM report('in the transaction again');
          stage           | shape | same_answers | shared | built 
--------------------------+-------+--------------+--------+-------
 in the transaction again |     1 | t            | f      | t
 in the transaction again |     2 | t            | f      | t
 in the transaction again |     3 | t            | f      | t
(3 rows)

ROLLBACK;
SELECT * FROM report('after the rollback');
       stage        | shape | same_answers | shared | built 
--------------------+-------+--------------+--------+-------
 after the rollback |     1 | t            | t      | f
 after the rollback |     2 | t            | t      | f
 after the rollback |     3 | t            | t      | f
(3 rows)

SELECT * FROM report('shared after the rollback');
           stage           | shape | same_answers | shared | built 
---------------------------+-------+--------------+--------+-------
 shared after the rollback |     1 | t            | t      | f
 shared after the rollback |     2 | t            | t      | f
 shared after the rollback |     3 | t            | t      | f
(3 rows)

-- the connection switched off: the journal of the dimension stays, and so does its stamp
ALTER INDEX ev_col SET (connect = 'off');
REINDEX INDEX ev_col;
NOTICE:  sift_col index "ev_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ev_col');
UPDATE dim SET a = 5 WHERE k = 11;
SELECT * FROM report('connection off, written');
          stage          | shape | same_answers | shared | built 
-------------------------+-------+--------------+--------+-------
 connection off, written |     1 | t            | f      | t
 connection off, written |     2 | t            | f      | t
 connection off, written |     3 | t            | f      | t
(3 rows)

SELECT * FROM report('connection off, shared');
         stage          | shape | same_answers | shared | built 
------------------------+-------+--------------+--------+-------
 connection off, shared |     1 | t            | t      | f
 connection off, shared |     2 | t            | t      | f
 connection off, shared |     3 | t            | t      | f
(3 rows)

VACUUM (INDEX_CLEANUP ON) dim;
SELECT * FROM report('connection off, vacuumed');
          stage           | shape | same_answers | shared | built 
--------------------------+-------+--------------+--------+-------
 connection off, vacuumed |     1 | t            | t      | f
 connection off, vacuumed |     2 | t            | t      | f
 connection off, vacuumed |     3 | t            | t      | f
(3 rows)

DROP FUNCTION report(text), maps(text), answer(text, bool);
DROP TABLE shapes, ev, dim;
DROP EXTENSION pg_sift;