-- sift.col_merge(index, true) seals the open write buffer first, so one call is col_promote + col_merge: the rows written since the last
-- promotion are in the one segment it makes, not left as in-memory units next to it. The plain col_merge does not seal.
CREATE EXTENSION pg_sift;
SET sift.async_maintenance = off;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE items (id int, kind text, amount numeric(10,2)) WITH (autovacuum_enabled = false);
INSERT INTO items SELECT g, 'cat_' || (g % 4), g % 997 FROM generate_series(1, 20000) g;
CREATE INDEX items_col ON items USING sift_col (id, kind, amount) WITH (write_buffer_limit = 4096);
NOTICE:  sift_col index "items_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('items_col');
VACUUM items;
-- rows written after the build sit in the open write buffer, below the limit that would seal it
INSERT INTO items SELECT g, 'cat_' || (g % 4), g % 997 FROM generate_series(20001, 20500) g;
SELECT kind, rows FROM sift.col_segments('items_col') ORDER BY kind, rows;
   kind    | rows  
-----------+-------
 buffer    |   500
 immutable | 20000
(2 rows)

-- the plain merge asks the policy only: nothing to merge, and the buffer stays open
SELECT sift.col_merge('items_col') AS merges;
 merges 
--------
      0
(1 row)

SELECT kind, rows FROM sift.col_segments('items_col') ORDER BY kind, rows;
   kind    | rows  
-----------+-------
 buffer    |   500
 immutable | 20000
(2 rows)

-- the forced merge: one segment of every row, the fresh open buffer empty
SELECT sift.col_merge('items_col', true) AS merges;
 merges 
--------
      1
(1 row)

SELECT kind, rows FROM sift.col_segments('items_col') ORDER BY kind, rows;
   kind    | rows  
-----------+-------
 buffer    |     0
 immutable | 20500
(2 rows)

SELECT buffers, rows, units FROM sift.col_index_info('items_col');
 buffers | rows  | units 
---------+-------+-------
       1 | 20500 |     1
(1 row)

-- the answers are those of the table
SET sift.enable_agg = on;
SELECT kind, count(*), sum(amount) FROM items GROUP BY kind ORDER BY kind;
 kind  | count |    sum     
-------+-------+------------
 cat_0 |  5125 | 2522010.00
 cat_1 |  5125 | 2521590.00
 cat_2 |  5125 | 2521730.00
 cat_3 |  5125 | 2521870.00
(4 rows)

SET sift.enable_agg = off;
SELECT kind, count(*), sum(amount) FROM items GROUP BY kind ORDER BY kind;
 kind  | count |    sum     
-------+-------+------------
 cat_0 |  5125 | 2522010.00
 cat_1 |  5125 | 2521590.00
 cat_2 |  5125 | 2521730.00
 cat_3 |  5125 | 2521870.00
(4 rows)

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

-- an empty buffer is not sealed again: a second forced merge finds one segment and merges nothing
SELECT sift.col_merge('items_col', true) AS merges;
 merges 
--------
      0
(1 row)

SELECT kind, rows FROM sift.col_segments('items_col') ORDER BY kind, rows;
   kind    | rows  
-----------+-------
 buffer    |     0
 immutable | 20500
(2 rows)

DROP TABLE items;
DROP EXTENSION pg_sift;