-- The space step of VACUUM moves the highest runs of a segment larger than sift.vacuum_rewrite_limit down into the free blocks below it, a round at a time,
-- and cuts the file: a merge writes its one segment above the blocks of the segments it replaced, and the file stays twice the live bytes until then.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET sift.async_maintenance = off;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE sh (id int, k int, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO sh SELECT i, i % 500, i % 100, md5(i::text) || md5((i + 1)::text) || md5((i + 2)::text) FROM generate_series(1, 60000) i;
CREATE INDEX sh_idx ON sh USING sift_col (id, k, amount, pad);
INSERT INTO sh SELECT i, i % 500, i % 100, md5(i::text) || md5((i + 1)::text) || md5((i + 2)::text) FROM generate_series(60001, 120000) i;
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) sh;
SELECT sift.col_promote('sh_idx') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT sift.col_merge('sh_idx', true) > 0 AS merged;
 merged 
--------
 t
(1 row)

CREATE TEMP TABLE want AS SELECT count(*) AS n, sum(amount) AS s, sum(length(pad)) AS l, count(DISTINCT k) AS d FROM sh;
CREATE FUNCTION sh_state() RETURNS TABLE (segments int, file_x100 int, answers bool) LANGUAGE sql AS $$
	SELECT (SELECT count(*)::int FROM sift.col_segments('sh_idx') WHERE kind = 'immutable'),
	       (SELECT (100 * relation_blocks * 8192 / (SELECT sum(bytes) FROM sift.col_sections('sh_idx') WHERE section <> 'free'))::int FROM sift.col_index_info('sh_idx')),
	       (SELECT (n, s, l, d) = (SELECT count(*), sum(amount), sum(length(pad)), count(DISTINCT k) FROM sh) FROM want) $$;
-- one segment, the file about twice its bytes
SELECT segments, file_x100 > 170 AS file_twice_the_segment, answers FROM sh_state();
 segments | file_twice_the_segment | answers 
----------+------------------------+---------
        1 | t                      | t
(1 row)

-- a VACUUM with a one-megabyte allowance: the segment is a dozen times larger, it cannot move whole, it shifts
SET sift.vacuum_rewrite_limit = 1;
VACUUM sh;
SELECT segments, file_x100 < 115 AS file_near_the_segment, answers FROM sh_state();
 segments | file_near_the_segment | answers 
----------+-----------------------+---------
        1 | t                     | t
(1 row)

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

-- the shifted segment reads, updates and vacuums like any other
UPDATE sh SET amount = amount + 1 WHERE id % 7 = 0;
TRUNCATE want;
INSERT INTO want SELECT count(*), sum(amount), sum(length(pad)), count(DISTINCT k) FROM sh;
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) sh;
SELECT answers FROM sh_state();
 answers 
---------
 t
(1 row)

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

-- a merge writes its segment above the blocks of its inputs, and a segment that took the first blocks of those leaves the output more blocks than the hole below it:
-- the header goes first to the lowest free block, or the data runs would take every block of the hole and the header, the last of the segment, would hold the file
CREATE TABLE sh2 (id int, k int, amount int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO sh2 SELECT i, i % 500, i % 100, md5(i::text) || md5((i + 1)::text) || md5((i + 2)::text) FROM generate_series(1, 60000) i;
CREATE INDEX sh2_idx ON sh2 USING sift_col (id, k, amount, pad);
INSERT INTO sh2 SELECT i, i % 500, i % 100, md5(i::text) || md5((i + 1)::text) || md5((i + 2)::text) FROM generate_series(60001, 120000) i;
VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) sh2;
SELECT sift.col_promote('sh2_idx') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT sift.col_merge('sh2_idx', true) > 0 AS merged;
 merged 
--------
 t
(1 row)

INSERT INTO sh2 SELECT i, i % 500, i % 100, md5(i::text) FROM generate_series(120001, 120200) i;
SELECT sift.col_promote('sh2_idx') >= 0 AS small_segment_in_the_hole;
 small_segment_in_the_hole 
---------------------------
 t
(1 row)

SELECT count(*) AS segments, (SELECT (100 * relation_blocks * 8192 / (SELECT sum(bytes) FROM sift.col_sections('sh2_idx') WHERE section <> 'free'))::int > 170 FROM sift.col_index_info('sh2_idx')) AS file_twice_the_bytes
  FROM sift.col_segments('sh2_idx') WHERE kind = 'immutable';
 segments | file_twice_the_bytes 
----------+----------------------
        4 | t
(1 row)

SET sift.vacuum_rewrite_limit = 1;
VACUUM sh2;
SELECT (SELECT (100 * relation_blocks * 8192 / (SELECT sum(bytes) FROM sift.col_sections('sh2_idx') WHERE section <> 'free'))::int < 115 FROM sift.col_index_info('sh2_idx')) AS file_near_the_bytes;
 file_near_the_bytes 
---------------------
 t
(1 row)

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

SELECT (SELECT count(*) FROM sh2) = 120200 AS answers;
 answers 
---------
 t
(1 row)

DROP TABLE sh, sh2;
DROP FUNCTION sh_state();
DROP EXTENSION pg_sift;