-- A dictionary of thousands of strings is searched in the stream (a bisection of its sorted order, a block of strings decompressed for each step), and the backend keeps
-- the positions it looked at (col/dictcache.c, the bisection cache) under the segment's generation, so that the next search does not read them again, and parses the
-- dictionary only when the searches have cost what the parse would. The answers never depend on it: the same keys, the missing ones, a range and a prefix,
-- before and after a promotion, a merge, a VACUUM, a delete and a REINDEX, all in one backend.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET sift.test_col_legacy_options = on;
CREATE TABLE bs (id int, s text) WITH (autovacuum_enabled = off);
INSERT INTO bs SELECT g, 'key ' || md5(g::text) FROM generate_series(1, 30000) g;
CREATE INDEX bs_idx ON bs USING sift_col (id sift.col_int4_ops, s sift.col_text_ops) WITH (point_columns = 's', write_buffer_limit = 4096);
NOTICE:  sift_col index "bs_idx": 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('bs_idx');
SELECT count(*) > 0 AS has_segment FROM sift.col_segments('bs_idx'::regclass) WHERE kind = 'immutable';
 has_segment 
-------------
 t
(1 row)

-- (few searches in the first stages, so that the cache is read, not the parsed dictionary the many searches bring)
-- the rows of the index for a key, a range or a prefix against the heap's, for keys that are there (some of every part of the order) and keys that are not
CREATE FUNCTION bs_check(tag text, m int) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
	k text;
	op text;
	got bigint;
	want bigint;
	bad int := 0;
	n int := 0;
BEGIN
	FOR k IN SELECT 'key ' || md5((1 + (g * 7919) % 33000)::text) FROM generate_series(1, m) g
			 UNION ALL SELECT 'key ' || md5((30000 + g * 37)::text) FROM generate_series(1, m) g
			 UNION ALL SELECT 'key ' || substr(md5(g::text), 1, 5 + g % 20) FROM generate_series(1, m / 6 + 1) g
			 UNION ALL SELECT * FROM (VALUES (''), ('a'), ('key '), ('key 0'), ('key f'), ('key ffffffffffffffffffffffffffffffff'), ('zzz'), ('key ' || md5('1'))) v WHERE m > 5 LOOP
		FOREACH op IN ARRAY ARRAY['eq', 'lt', 'ge', 'prefix'] LOOP
			SELECT rows INTO got FROM sift._test_col_counters('bs_idx', '1:' || op || ':' || k);
			SELECT count(*) INTO want FROM (SELECT s FROM bs OFFSET 0) x
			 WHERE CASE op WHEN 'eq' THEN s = k WHEN 'lt' THEN s < k COLLATE "C" WHEN 'ge' THEN s >= k COLLATE "C" ELSE left(s, length(k)) = k END;
			n := n + 1;
			IF got IS DISTINCT FROM want THEN
				bad := bad + 1;
				RAISE NOTICE '% % %: index %, heap %', tag, op, k, got, want;
			END IF;
		END LOOP;
	END LOOP;
	RETURN tag || ': ' || n || ' searches, ' || bad || ' differ';
END $$;
SELECT count(*) > 0 AS reset FROM sift._test_col_dict_cache(true);
 reset 
-------
 t
(1 row)

SELECT bs_check('first', 1);
           bs_check           
------------------------------
 first: 12 searches, 0 differ
(1 row)

SELECT bytes > 0 AS positions_kept FROM sift._test_col_dict_cache(false);
 positions_kept 
----------------
 t
(1 row)

SELECT bs_check('warm', 1);
          bs_check           
-----------------------------
 warm: 12 searches, 0 differ
(1 row)

-- written again: promotion of new rows, a merge, a delete and VACUUM, REINDEX: every one is a segment the cache has not seen, or one that it has, under another generation
INSERT INTO bs SELECT g, 'key ' || md5(g::text) FROM generate_series(30001, 36000) g;
SELECT sift.col_promote('bs_idx') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT bs_check('promoted', 1);
            bs_check             
---------------------------------
 promoted: 12 searches, 0 differ
(1 row)

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

SELECT bs_check('merged', 1);
           bs_check            
-------------------------------
 merged: 12 searches, 0 differ
(1 row)

DELETE FROM bs WHERE id % 7 = 0;
VACUUM bs;
SELECT bs_check('vacuumed', 1);
            bs_check             
---------------------------------
 vacuumed: 12 searches, 0 differ
(1 row)

INSERT INTO bs SELECT g, 'key ' || md5(g::text) FROM generate_series(1, 40000) g WHERE g % 7 = 0 AND g > 36000;
SELECT sift.col_promote('bs_idx') >= 0 AS promoted_again;
 promoted_again 
----------------
 t
(1 row)

SELECT bs_check('promoted again', 1);
               bs_check                
---------------------------------------
 promoted again: 12 searches, 0 differ
(1 row)

REINDEX INDEX bs_idx;
NOTICE:  sift_col index "bs_idx": 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('bs_idx');
SELECT bs_check('reindexed', 1);
             bs_check             
----------------------------------
 reindexed: 12 searches, 0 differ
(1 row)

UPDATE bs SET s = 'key ' || md5((id + 1)::text) WHERE id % 11 = 0;
VACUUM bs;
SELECT sift.col_merge('bs_idx', true) >= 0 AS merged_again;
 merged_again 
--------------
 t
(1 row)

SELECT bs_check('updated', 1);
            bs_check            
--------------------------------
 updated: 12 searches, 0 differ
(1 row)

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

-- many searches in one backend: the cache hands over to the parsed dictionary
SELECT bs_check('many', 120);
           bs_check            
-------------------------------
 many: 1076 searches, 0 differ
(1 row)

-- a cache of no room for the positions (one kilobyte): the same answers
SET sift.test_limits = 'dict_cache_kb=1';
SELECT bs_check('no room', 30);
            bs_check             
---------------------------------
 no room: 296 searches, 0 differ
(1 row)

RESET sift.test_limits;
DROP FUNCTION bs_check(text, int);
DROP TABLE bs;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;