-- The options that 4.3 deprecated are accepted and ignored: the build decides by itself, says so once in its NOTICE (and sift.index_choices), and an index that carries
-- one keeps reading. sift.test_col_legacy_options (testing only) makes a build honor them, which is how the tests of their layouts run until 4.4 removes both.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
SET sift.async_maintenance = off;
\set VERBOSITY terse
CREATE TABLE dp (id int, tenant_id int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO dp SELECT i, (i::bigint * 7919 % 300)::int, md5(i::text) FROM generate_series(1, 40000) i;
VACUUM (FREEZE, ANALYZE) dp;
CREATE FUNCTION dp_units(ix regclass) RETURNS bigint LANGUAGE sql AS $$ SELECT sum(units)::bigint FROM sift.col_segments(ix) $$;
CREATE FUNCTION dp_choice(ix regclass, name text) RETURNS text LANGUAGE sql AS $$
	SELECT kind || ': ' || value FROM sift.index_choices(ix) WHERE choice = name $$;
-- key_cut_rows: the units aim at the rows of an average superblock, whatever the option says
SET sift.test_col_legacy_options = off;
CREATE INDEX dp_rows ON dp USING sift_col (id, tenant_id, pad) WITH (key_cut = 'tenant_id', key_cut_rows = 64);
NOTICE:  sift_col index "dp_rows": 2 advice (track_changes, key_cut_rows)
CREATE INDEX dp_plain ON dp USING sift_col (id, tenant_id, pad) WITH (key_cut = 'tenant_id');
NOTICE:  sift_col index "dp_plain": 1 advice (track_changes)
SELECT dp_units('dp_rows') = dp_units('dp_plain') AS same_units_as_without;
 same_units_as_without 
-----------------------
 t
(1 row)

SELECT dp_choice('dp_rows', 'key_cut_rows');
            dp_choice            
---------------------------------
 advice: ignored, removed in 4.4
(1 row)

SELECT dp_choice('dp_plain', 'key_cut_rows') IS NULL AS no_advice_without;
 no_advice_without 
-------------------
 t
(1 row)

SET sift.test_col_legacy_options = on;
CREATE INDEX dp_legacy ON dp USING sift_col (id, tenant_id, pad) WITH (key_cut = 'tenant_id', key_cut_rows = 64);
NOTICE:  sift_col index "dp_legacy": 1 advice (track_changes)
SELECT dp_units('dp_legacy') > 10 * dp_units('dp_plain') AS legacy_cuts_small_units;
 legacy_cuts_small_units 
-------------------------
 t
(1 row)

SELECT dp_choice('dp_legacy', 'key_cut_rows') IS NULL AS legacy_says_nothing;
 legacy_says_nothing 
---------------------
 t
(1 row)

-- an index built with the option under the legacy setting keeps its option and reads the same after a REINDEX that ignores it
SET sift.test_col_legacy_options = off;
SELECT reloptions FROM pg_class WHERE relname = 'dp_legacy';
                      reloptions                      
------------------------------------------------------
 {key_cut=tenant_id,key_cut_rows=64,key_columns=auto}
(1 row)

SELECT count(*), sum(id) FROM dp WHERE tenant_id = 42;
 count |   sum   
-------+---------
   134 | 2675712
(1 row)

REINDEX INDEX dp_legacy;
NOTICE:  sift_col index "dp_legacy": 2 advice (track_changes, key_cut_rows)
SELECT dp_units('dp_legacy') = dp_units('dp_plain') AS reindex_ignores_it;
 reindex_ignores_it 
--------------------
 t
(1 row)

SELECT dp_choice('dp_legacy', 'key_cut_rows');
            dp_choice            
---------------------------------
 advice: ignored, removed in 4.4
(1 row)

-- point_columns: its leaves are listed as heap addresses, which an equality reads with no unit read, where the rows form reads the unit of each row
CREATE TABLE dq (id int, uid int, pad text) WITH (autovacuum_enabled = false);
INSERT INTO dq SELECT i, (i::bigint * 7919 % 40009)::int, md5(i::text) FROM generate_series(1, 40000) i;
VACUUM (FREEZE, ANALYZE) dq;
SET sift.test_col_legacy_options = on;
CREATE INDEX dq_rows ON dq USING sift_col (id, uid, pad) WITH (point_columns = 'uid', key_cut = 'off');
NOTICE:  sift_col index "dq_rows": 1 advice (track_changes)
SELECT dp_choice('dq_rows', 'point_columns') IS NULL AS legacy_says_nothing;
 legacy_says_nothing 
---------------------
 t
(1 row)

SET sift.test_col_legacy_options = off;
CREATE INDEX dq_addr ON dq USING sift_col (id, uid, pad) WITH (point_columns = 'uid', key_cut = 'off');
NOTICE:  sift_col index "dq_addr": 2 advice (track_changes, point_columns)
NOTICE:  sift_col index "dq_addr" is column format minor 33, which a library before 3.7 cannot read
CREATE INDEX dq_tid ON dq USING sift_col (id, uid, pad) WITH (point_tid_columns = 'uid', key_cut = 'off');
NOTICE:  sift_col index "dq_tid": 1 automatic choice (point_tid_columns), 1 advice (track_changes)
NOTICE:  sift_col index "dq_tid" is column format minor 33, which a library before 3.7 cannot read
SELECT i AS idx, c.units > 0 AS reads_units, c.rows_scanned
FROM unnest(ARRAY['dq_rows', 'dq_addr', 'dq_tid']) i, LATERAL sift._test_col_counters(i::regclass, '1:eq:7919', 24) c ORDER BY 1;
   idx   | reads_units | rows_scanned 
---------+-------------+--------------
 dq_addr | f           |            0
 dq_rows | t           |        27392
 dq_tid  | f           |            0
(3 rows)

SELECT pg_relation_size('dq_addr') = pg_relation_size('dq_tid') AS same_size_as_point_tid;
 same_size_as_point_tid 
------------------------
 t
(1 row)

SELECT dp_choice('dq_addr', 'point_columns');
                   dp_choice                   
-----------------------------------------------
 advice: now point_tid_columns, removed in 4.4
(1 row)

SELECT dp_choice('dq_tid', 'point_columns') IS NULL AS no_advice_without;
 no_advice_without 
-------------------
 t
(1 row)

SELECT sift.col_verify('dq_addr');
 col_verify 
------------
 
(1 row)

SET enable_seqscan = off; SET enable_bitmapscan = off;
SELECT count(*), min(id) FROM dq WHERE uid = 7919;
 count | min 
-------+-----
     1 |   1
(1 row)

RESET enable_seqscan; RESET enable_bitmapscan;
DROP TABLE dq;
-- order_columns: no sorted run is written; the order arrays answer the top-N, and an index that has runs keeps reading them
CREATE TABLE dr (id int, ts timestamp, pad text) WITH (autovacuum_enabled = false);
INSERT INTO dr SELECT i, timestamp '2026-01-01' + (i::bigint * 7919 % 40009) * interval '1 minute', md5(i::text) FROM generate_series(1, 40000) i;
VACUUM (FREEZE, ANALYZE) dr;
CREATE FUNCTION dr_runs(ix regclass) RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM sift.col_sections(ix) WHERE section = 'runs' $$;
CREATE FUNCTION dr_top(n int) RETURNS text LANGUAGE plpgsql AS $$
DECLARE a text; b text;
BEGIN
	SET LOCAL sift.enable_agg = on; SET LOCAL enable_seqscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL enable_indexscan = off;
	SELECT string_agg(id::text, ',') INTO a FROM (SELECT id FROM dr ORDER BY ts DESC, id LIMIT n) x;
	SET LOCAL sift.enable_agg = off; SET LOCAL enable_seqscan = on;
	SELECT string_agg(id::text, ',') INTO b FROM (SELECT id FROM dr ORDER BY ts DESC, id LIMIT n) x;
	RETURN CASE WHEN a = b THEN 'same' ELSE 'different' END;
END $$;
SET sift.test_col_legacy_options = on;
CREATE INDEX dr_runs ON dr USING sift_col (id, ts, pad) WITH (order_columns = 'ts', key_cut = 'off');
NOTICE:  sift_col index "dr_runs": 1 advice (track_changes)
SELECT dr_runs('dr_runs') > 0 AS legacy_writes_runs;
 legacy_writes_runs 
--------------------
 t
(1 row)

SELECT dp_choice('dr_runs', 'order_columns') IS NULL AS legacy_says_nothing;
 legacy_says_nothing 
---------------------
 t
(1 row)

SET sift.test_col_legacy_options = off;
CREATE INDEX dr_off ON dr USING sift_col (id, ts, pad) WITH (order_columns = 'ts', key_cut = 'off');
NOTICE:  sift_col index "dr_off": 2 advice (track_changes, order_columns)
CREATE INDEX dr_arrays ON dr USING sift_col (id, ts, pad) WITH (order_columns = 'ts', order_array_columns = 'ts', key_cut = 'off');
NOTICE:  sift_col index "dr_arrays": 1 automatic choice, 2 advice
NOTICE:  sift_col index "dr_arrays" is column format minor 44, which a library before 4.3 cannot read
CREATE INDEX dr_list ON dr USING sift_col (id, ts, pad) WITH (order_array_columns = 'ts', key_cut = 'off');
NOTICE:  sift_col index "dr_list": 1 automatic choice (order_array_columns), 1 advice (track_changes)
NOTICE:  sift_col index "dr_list" is column format minor 44, which a library before 4.3 cannot read
SELECT i AS idx, dr_runs(i::regclass) AS runs, (SELECT count(*) FROM sift.col_sections(i::regclass) WHERE section LIKE 'order%' OR section LIKE 'gorder%') > 0 AS has_array
FROM unnest(ARRAY['dr_off', 'dr_arrays', 'dr_list']) i ORDER BY 1;
    idx    | runs | has_array 
-----------+------+-----------
 dr_arrays |    0 | t
 dr_list   |    0 | t
 dr_off    |    0 | f
(3 rows)

SELECT dp_choice('dr_off', 'order_columns');
            dp_choice            
---------------------------------
 advice: ignored, removed in 4.4
(1 row)

SELECT dp_choice('dr_list', 'order_columns') IS NULL AS no_advice_without;
 no_advice_without 
-------------------
 t
(1 row)

-- the index built with runs answers as before while the option is ignored, and its next build writes none
SELECT dr_top(20), sift.col_verify('dr_runs');
 dr_top | col_verify 
--------+------------
 same   | 
(1 row)

REINDEX INDEX dr_runs;
NOTICE:  sift_col index "dr_runs": 2 advice (track_changes, order_columns)
SELECT dr_runs('dr_runs') AS runs_after_reindex, dr_top(20), sift.col_verify('dr_runs');
 runs_after_reindex | dr_top | col_verify 
--------------------+--------+------------
                  0 | same   | 
(1 row)

DROP TABLE dr;
DROP FUNCTION dr_runs(regclass), dr_top(int);
DROP TABLE dp CASCADE;
DROP FUNCTION dp_units(regclass), dp_choice(regclass, text);
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;