-- The rewrite of a sift_col index that churn left scattered (sift.col_auto_merge*, sift.col_maintain, sift.col_maintenance_due): a DELETE or UPDATE
-- spread over the table puts a dead row in every unit while far fewer than dead_rewrite_pct percent of the rows are dead, so VACUUM rewrites nothing
-- and no unit answers from its pre-aggregates. The rule is on what a rewrite changes: units with a dead row, units listed per superblock covered, rows
-- in the write buffer. The end state after the rewrite is the one of a REINDEX of the same table, and the answers never change.
CREATE EXTENSION pg_sift;
SET sift.test_col_net = off;	-- this test is of the dirty units and their rewrite (col_net_agg is of the sidecar VACUUM writes for them)
SET sift.agg_dead_delta = 0;	-- a unit with a dead row is scanned here (col_dead_delta answers it net of the dead rows)
SET sift.async_maintenance = off;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
CREATE TABLE facts_a (id int, key int, kind text, amount numeric(10,2), pad text) WITH (autovacuum_enabled = false);
INSERT INTO facts_a SELECT g, g % 1000, 'cat_' || (g % 8), g % 997, repeat('x', 150) FROM generate_series(1, 40000) g;
CREATE TABLE facts_b (LIKE facts_a) WITH (autovacuum_enabled = false);
INSERT INTO facts_b SELECT * FROM facts_a ORDER BY id;
CREATE TABLE facts_c (LIKE facts_a) WITH (autovacuum_enabled = false);
INSERT INTO facts_c SELECT * FROM facts_a ORDER BY id;
CREATE INDEX facts_a_col ON facts_a USING sift_col (id, key, kind, amount) WITH (dead_rewrite_pct = 50);
NOTICE:  sift_col index "facts_a_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_a_col');
CREATE INDEX facts_b_col ON facts_b USING sift_col (id, key, kind, amount) WITH (dead_rewrite_pct = 50);
NOTICE:  sift_col index "facts_b_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_b_col');
CREATE INDEX facts_c_col ON facts_c USING sift_col (id, key, kind, amount) WITH (dead_rewrite_pct = 50);
NOTICE:  sift_col index "facts_c_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_c_col');
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_a, facts_b, facts_c;
-- units of the SiftAgg node, those it answered from the pre-aggregates, and whether the three ways (pre-aggregates, scan, heap) agree
CREATE FUNCTION fa_state(tbl text) RETURNS TABLE (units int, from_preagg int, same bool) LANGUAGE plpgsql AS $$
DECLARE l text; u int; p int; q text := format('SELECT kind, count(*), sum(amount) FROM %s GROUP BY kind', tbl); a text; b text; c text;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
		u := coalesce(substring(l from '^\s*Units: (\d+)')::int, u);
		p := coalesce(substring(l from '(\d+) from pre-aggregates')::int, p);
	END LOOP;
	SET LOCAL sift.agg_preagg = on;
	EXECUTE format('SELECT string_agg(x::text, E''\n'' ORDER BY x::text) FROM (%s) x', q) INTO a;
	SET LOCAL sift.agg_preagg = off;
	EXECUTE format('SELECT string_agg(x::text, E''\n'' ORDER BY x::text) FROM (%s) x', q) INTO b;
	SET LOCAL sift.enable_agg = off;
	EXECUTE format('SELECT string_agg(x::text, E''\n'' ORDER BY x::text) FROM (%s) x', q) INTO c;
	RETURN QUERY SELECT u, p, a = b AND b = c;
END $$;
-- what the index holds: segments, buffer rows, units listed and the superblocks they cover
CREATE FUNCTION fa_index(idx regclass) RETURNS TABLE (segments int, buffer_rows int, listed int, covered int) LANGUAGE sql AS $$
	SELECT (SELECT count(*)::int FROM sift.col_segments(idx) WHERE kind = 'immutable'), (SELECT coalesce(sum(rows), 0)::int FROM sift.col_segments(idx) WHERE kind <> 'immutable'),
	       (SELECT coalesce(sum(units), 0)::int FROM sift.col_segments(idx)), (SELECT units::int FROM sift.col_index_info(idx)) $$;
-- the reason for the rewrite under the three thresholds (percent of units with a dead row, units per superblock, percent of rows in the buffer)
CREATE FUNCTION fa_due(idx regclass, dirty int, frag numeric, buf numeric) RETURNS text LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config('sift.col_auto_merge_dirty_pct', dirty::text, true), set_config('sift.col_auto_merge_fragmentation', frag::text, true),
	        set_config('sift.col_auto_merge_buffer_pct', buf::text, true);
	RETURN coalesce(sift.col_maintenance_due(idx, false), 'not due');
END $$;
-- one cycle of churn spread over the whole table, 1% of the rows each of updated, deleted and inserted
CREATE PROCEDURE fa_churn(n int) LANGUAGE plpgsql AS $$
DECLARE t text;
BEGIN
	FOREACH t IN ARRAY ARRAY['facts_a', 'facts_b', 'facts_c'] LOOP
		EXECUTE format('UPDATE %s SET amount = amount + 1 WHERE id %% 100 = %s', t, n);
		EXECUTE format('DELETE FROM %s WHERE id %% 100 = %s', t, n + 50);
		EXECUTE format('INSERT INTO %s SELECT %s * 100000 + g, g %% 1000, ''cat_'' || (g %% 8), g %% 997, repeat(''x'', 150) FROM generate_series(1, 400) g', t, n);
	END LOOP;
END $$;
SELECT 'built' AS step, * FROM fa_state('facts_a');
 step  | units | from_preagg | same 
-------+-------+-------------+------
 built |     4 |           4 | t
(1 row)

-- cycle 1: the policy of VACUUM leaves every unit with a dead row, none answers from the pre-aggregates, and 1% of dead rows is far from dead_rewrite_pct
CALL fa_churn(1);
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_a, facts_b, facts_c;
SELECT 'cycle 1' AS step, * FROM fa_state('facts_a');
  step   | units | from_preagg | same 
---------+-------+-------------+------
 cycle 1 |     5 |           0 | t
(1 row)

SELECT sum(dead_rows) * 100 / sum(rows) < 10 AS under_dead_rewrite_pct, bool_and(dirty) AS all_dirty FROM sift.col_segments('facts_a_col') WHERE kind = 'immutable';
 under_dead_rewrite_pct | all_dirty 
------------------------+-----------
 t                      | t
(1 row)

-- the rule: each threshold alone, at the value of the index (due) and just above it (not due)
SELECT 'dirty units' AS rule, fa_due('facts_a_col', 99, 1000, 100) AS at_99, fa_due('facts_a_col', 100, 1000, 100) AS at_100;
    rule     |    at_99    | at_100  
-------------+-------------+---------
 dirty units | dirty units | not due
(1 row)

WITH b AS (SELECT buffer_rows * 100.0 / (buffer_rows + (SELECT sum(rows) FROM sift.col_segments('facts_a_col') WHERE kind = 'immutable')) AS pct FROM fa_index('facts_a_col'))
SELECT 'write buffer rows' AS rule, fa_due('facts_a_col', 100, 1000, greatest(floor(pct * 100) / 100 - 0.01, 0.01)) AS below, fa_due('facts_a_col', 100, 1000, ceil(pct * 100) / 100) AS at_pct FROM b;
       rule        |       below       | at_pct  
-------------------+-------------------+---------
 write buffer rows | write buffer rows | not due
(1 row)

SELECT 'nothing due' AS rule, fa_due('facts_a_col', 100, 1000, 100) AS thresholds_off, fa_due('facts_a_col', 30, 2, 1) AS defaults;
    rule     | thresholds_off |  defaults   
-------------+----------------+-------------
 nothing due | not due        | dirty units
(1 row)

-- an index over sift.vacuum_rewrite_limit is due like any other (its rewrite goes by partitions of that size), for the server's own rule and the explicit one
SET sift.vacuum_rewrite_limit = 1;
SELECT sift.col_maintenance_due('facts_a_col') AS automatic, sift.col_maintenance_due('facts_a_col', false) AS explicit;
  automatic  |  explicit   
-------------+-------------
 dirty units | dirty units
(1 row)

RESET sift.vacuum_rewrite_limit;
-- units listed per superblock covered: the promotion of the buffer adds a segment over the superblocks the churn touched
SELECT sift.col_promote('facts_a_col') > 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT segments, buffer_rows, listed > covered AS fragmented FROM fa_index('facts_a_col');
 segments | buffer_rows | fragmented 
----------+-------------+------------
        2 |           0 | t
(1 row)

WITH r AS (SELECT listed::numeric / covered AS ratio FROM fa_index('facts_a_col'))
SELECT 'fragmented units' AS rule, fa_due('facts_a_col', 100, floor(ratio * 100) / 100, 100) AS at_ratio, fa_due('facts_a_col', 100, floor(ratio * 100) / 100 + 0.01, 100) AS above FROM r;
       rule       |     at_ratio     |  above  
------------------+------------------+---------
 fragmented units | fragmented units | not due
(1 row)

-- cycle 2, then the rewrite in this session: the index is the one a REINDEX of the same table gives
CALL fa_churn(2);
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_a, facts_b, facts_c;
SELECT 'cycle 2' AS step, * FROM fa_state('facts_a');
  step   | units | from_preagg | same 
---------+-------+-------------+------
 cycle 2 |    10 |           0 | t
(1 row)

SELECT sift.col_maintenance_due('facts_a_col') AS due;
     due     
-------------
 dirty units
(1 row)

SELECT sift.col_maintain('facts_a_col') AS rewritten;
 rewritten 
-----------
 t
(1 row)

SELECT sift.col_maintain('facts_a_col') AS rewritten_again;
 rewritten_again 
-----------------
 f
(1 row)

REINDEX INDEX facts_c_col;
NOTICE:  sift_col index "facts_c_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_c_col');
SELECT 'maintained' AS step, * FROM fa_state('facts_a');
    step    | units | from_preagg | same 
------------+-------+-------------+------
 maintained |     5 |           5 | t
(1 row)

SELECT 'reindexed' AS step, * FROM fa_state('facts_c');
   step    | units | from_preagg | same 
-----------+-------+-------------+------
 reindexed |     5 |           5 | t
(1 row)

SELECT 'maintained' AS index, * FROM fa_index('facts_a_col') UNION ALL SELECT 'reindexed', * FROM fa_index('facts_c_col');
   index    | segments | buffer_rows | listed | covered 
------------+----------+-------------+--------+---------
 maintained |        1 |           0 |      5 |       5
 reindexed  |        1 |           0 |      5 |       5
(2 rows)

SELECT sum(dead_rows) AS dead_rows FROM sift.col_segments('facts_a_col');
 dead_rows 
-----------
         0
(1 row)

SELECT sift.col_verify('facts_a_col', true);
 col_verify 
------------
 
(1 row)

-- sift.col_auto_merge off: VACUUM leaves facts_b as the policy does, the answers are the same as those of the rewritten and of the reindexed one
SELECT 'not rewritten' AS step, * FROM fa_state('facts_b');
     step      | units | from_preagg | same 
---------------+-------+-------------+------
 not rewritten |     9 |           0 | t
(1 row)

SELECT (SELECT string_agg(x::text, ',' ORDER BY x::text) FROM (SELECT kind, count(*), sum(amount) FROM facts_a GROUP BY kind) x)
     = (SELECT string_agg(x::text, ',' ORDER BY x::text) FROM (SELECT kind, count(*), sum(amount) FROM facts_b GROUP BY kind) x) AS same_as_not_rewritten,
       (SELECT string_agg(x::text, ',' ORDER BY x::text) FROM (SELECT kind, count(*), sum(amount) FROM facts_a GROUP BY kind) x)
     = (SELECT string_agg(x::text, ',' ORDER BY x::text) FROM (SELECT kind, count(*), sum(amount) FROM facts_c GROUP BY kind) x) AS same_as_reindexed;
 same_as_not_rewritten | same_as_reindexed 
-----------------------+-------------------
 t                     | t
(1 row)

-- not in a read-only transaction
BEGIN READ ONLY;
SELECT sift.col_maintain('facts_b_col');
ERROR:  cannot execute sift.col_maintain() in a read-only transaction
ROLLBACK;
SELECT sift.col_maintain('facts_b_col') AS rewritten, sift.col_maintenance_due('facts_b_col') AS due_again;
 rewritten | due_again 
-----------+-----------
 t         | 
(1 row)

-- sift.col_auto_merge_wal_pct: the rewrite of the server's own waits until the server has written 100 / that percent times the bytes of the index since the
-- index was written (here a thousandth of a percent: a hundred thousand times, which this cluster never writes); the call from a session does not wait,
-- and neither does the promotion of a write buffer
CREATE TABLE facts_d (LIKE facts_a) WITH (autovacuum_enabled = false);
INSERT INTO facts_d SELECT * FROM facts_a ORDER BY id LIMIT 40000;
CREATE INDEX facts_d_col ON facts_d USING sift_col (id, key, kind, amount);
NOTICE:  sift_col index "facts_d_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_d_col');
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_d;
UPDATE facts_d SET amount = amount + 1 WHERE id % 100 = 1;
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_d;
CREATE FUNCTION fa_churn_due(idx regclass, pct numeric, automatic bool) RETURNS text LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config('sift.col_auto_merge_wal_pct', pct::text, true), set_config('sift.col_auto_merge_buffer_pct', '100', true);
	RETURN coalesce(sift.col_maintenance_due(idx, automatic), 'not due');
END $$;
SELECT fa_churn_due('facts_d_col', 0, true) AS no_budget, fa_churn_due('facts_d_col', 0.001, true) AS over_budget,
       fa_churn_due('facts_d_col', 0.001, false) AS over_budget_explicit;
  no_budget  | over_budget | over_budget_explicit 
-------------+-------------+----------------------
 dirty units | not due     | dirty units
(1 row)

-- and sift.col_maintenance_status says what it waits for
SELECT set_config('sift.col_auto_merge_wal_pct', '0.001', true) IS NOT NULL AND EXISTS (SELECT 1 FROM sift.col_maintenance_status('facts_d_col') m, unnest(m.blocked_by) b WHERE b LIKE '%col_auto_merge_wal_pct%') AS status_names_the_budget;
 status_names_the_budget 
-------------------------
 t
(1 row)

DROP FUNCTION fa_churn_due(regclass, numeric, bool);
-- only the write buffer is large: promoting it is what the rewrite does, the segment the last rewrite made stays and the answers do not change
CREATE TABLE facts_e (LIKE facts_a) WITH (autovacuum_enabled = false);
INSERT INTO facts_e SELECT * FROM facts_a ORDER BY id LIMIT 40000;
CREATE INDEX facts_e_col ON facts_e USING sift_col (id, key, kind, amount);
NOTICE:  sift_col index "facts_e_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_e_col');
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) facts_e;
CREATE TEMP TABLE base_gen AS SELECT gen FROM sift.col_segments('facts_e_col') WHERE kind = 'immutable';
INSERT INTO facts_e SELECT 1000000 + g, g % 1000, 'cat_' || (g % 8), g % 997, repeat('x', 150) FROM generate_series(1, 1500) g;
SELECT 'inserted' AS step, segments, buffer_rows, listed >= covered AS covered FROM fa_index('facts_e_col');
   step   | segments | buffer_rows | covered 
----------+----------+-------------+---------
 inserted |        1 |        1500 | t
(1 row)

SELECT sift.col_maintenance_due('facts_e_col') AS due;
        due        
-------------------
 write buffer rows
(1 row)

SELECT sift.col_maintain('facts_e_col') AS promoted;
 promoted 
----------
 t
(1 row)

SELECT 'promoted' AS step, segments, buffer_rows, (SELECT bool_and(gen IN (SELECT gen FROM sift.col_segments('facts_e_col'))) FROM base_gen) AS base_kept FROM fa_index('facts_e_col');
   step   | segments | buffer_rows | base_kept 
----------+----------+-------------+-----------
 promoted |        2 |           0 | t
(1 row)

SELECT sift.col_maintenance_due('facts_e_col') AS due_again;
 due_again 
-----------
 
(1 row)

SELECT 'promoted' AS step, * FROM fa_state('facts_e');
   step   | units | from_preagg | same 
----------+-------+-------------+------
 promoted |     6 |           3 | t
(1 row)

SELECT sift.col_verify('facts_e_col', true);
 col_verify 
------------
 
(1 row)

-- on, within 5% of the WAL, unless the server says otherwise (the test clusters turn it off and the budget away)
SELECT name, boot_val FROM pg_settings WHERE name IN ('sift.col_auto_merge', 'sift.col_auto_merge_wal_pct') ORDER BY name;
            name             | boot_val 
-----------------------------+----------
 sift.col_auto_merge         | on
 sift.col_auto_merge_wal_pct | 5
(2 rows)

-- CREATE INDEX says what the worker does with the index and how to stop it
SET sift.col_auto_merge = on;
CREATE TABLE facts_f (LIKE facts_a);
CREATE INDEX facts_f_col ON facts_f USING sift_col (id, key, kind, amount);
NOTICE:  sift_col index "facts_f_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_f_col');
RESET sift.col_auto_merge;
DROP TABLE facts_a, facts_b, facts_c, facts_d, facts_e, facts_f;
DROP FUNCTION fa_state(text);
DROP FUNCTION fa_index(regclass);
DROP FUNCTION fa_due(regclass, int, numeric, numeric);
DROP PROCEDURE fa_churn(int);
DROP EXTENSION pg_sift;