-- Bit-sliced key columns (DESIGN.md §51): the SLICE entries a column built
-- WITH (slices = on) keeps, lion_index_slice_agg() against the query it
-- stands for, verify, and the readers that must not see slices.
\set VERBOSITY terse
SET client_min_messages = warning;
CREATE EXTENSION IF NOT EXISTS pg_lion;
CREATE EXTENSION IF NOT EXISTS pageinspect;
RESET client_min_messages;
-- VACUUM can only set all-visible once the commit record is on disk
SET synchronous_commit = on;
-- The query lion_index_slice_agg(idx, filter, attno) stands for, with every
-- lion path off so that the code under test does not answer it:
--   SELECT count(x), sum(x), min(x), max(x) FROM t WHERE <pred> AND <filter>
-- x being key column attno, pred a partial index's predicate; sum only for
-- an integer column.
CREATE FUNCTION slc_ref(idx regclass, filter text, attno int2,
						OUT count bigint, OUT sum numeric, OUT min text,
						OUT max text)
LANGUAGE plpgsql
SET enable_indexscan = off SET enable_bitmapscan = off
SET pg_lion.enable_count_pushdown = off AS $$
DECLARE
	tbl regclass;
	col text;
	typ regtype;
	pred text;
	conds text[] := '{}';
BEGIN
	SELECT i.indrelid, pg_get_expr(i.indpred, i.indrelid)
	  INTO tbl, pred FROM pg_index i WHERE i.indexrelid = idx;
	col := pg_get_indexdef(idx, attno, true);
	SELECT a.atttypid INTO typ FROM pg_attribute a
	 WHERE a.attrelid = idx AND a.attnum = attno;
	IF pred IS NOT NULL THEN conds := conds || pred; END IF;
	IF filter IS NOT NULL THEN conds := conds || ('(' || filter || ')'); END IF;
	EXECUTE format('SELECT count(%1$s), %2$s, min(%1$s)::text, max(%1$s)::text FROM %3$s %4$s',
				   col,
				   CASE WHEN typ IN ('int2', 'int4', 'int8')
						THEN format('sum(%s)::numeric', col) ELSE 'NULL::numeric' END,
				   tbl,
				   CASE WHEN cardinality(conds) > 0
						THEN 'WHERE ' || array_to_string(conds, ' AND ') END)
	   INTO count, sum, min, max;
END $$;
-- The function's answer beside the query's: 'same' and the answer, or both.
CREATE FUNCTION slc_cmp(idx regclass, filter text DEFAULT NULL,
						attno int2 DEFAULT 1) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
	l record;
	r record;
BEGIN
	SELECT * INTO l FROM lion_index_slice_agg(idx, filter, attno);
	SELECT * INTO r FROM slc_ref(idx, filter, attno);
	IF (l.count, l.sum, l.min, l.max) IS NOT DISTINCT FROM (r.count, r.sum, r.min, r.max) THEN
		RETURN format('same: %s %s %s %s', l.count, coalesce(l.sum::text, '-'),
					  coalesce(l.min, '-'), coalesce(l.max, '-'));
	END IF;
	RETURN format('DIFFERENT: lion %s %s %s %s, query %s %s %s %s',
				  l.count, l.sum, l.min, l.max, r.count, r.sum, r.min, r.max);
END $$;
-- A statement's SQLSTATE, message and detail, or 'ok'.
CREATE FUNCTION slc_err(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
	detail text;
BEGIN
	EXECUTE q;
	RETURN 'ok';
EXCEPTION WHEN OTHERS THEN
	GET STACKED DIAGNOSTICS detail = PG_EXCEPTION_DETAIL;
	RETURN SQLSTATE || ': ' || SQLERRM ||
		CASE WHEN detail <> '' THEN ' (' || detail || ')' ELSE '' END;
END $$;
-- The meta page's format version and sliced columns (DESIGN.md §51): the
-- little-endian words at 28 and 36 of block 0.
CREATE FUNCTION slc_meta(idx regclass, OUT version int8, OUT slice_cols int8)
LANGUAGE sql AS $$
	SELECT get_byte(p, 28)::int8 + get_byte(p, 29)::int8 * 256,
		   get_byte(p, 36)::int8 + get_byte(p, 37)::int8 * 256
	  FROM get_raw_page(idx::text, 0) p
$$;
-- ---------- 1. every type of the allowlist, its extremes, NULLs ----------
CREATE TABLE slc (id int, g int, tags text[], s text,
				  i2 int2, i4 int4, i8 int8, d date, tm time, ts timestamp,
				  tz timestamptz)
	WITH (autovacuum_enabled = off, fillfactor = 70);
INSERT INTO slc
SELECT i, i % 7, ARRAY['t' || (i % 5), 'u' || (i % 3)], 's' || (i % 4),
	   ((i * 37) % 2001 - 1000)::int2,
	   (i * 7919) % 100003 - 50000,
	   (i::int8 * 1000003) * (CASE WHEN i % 2 = 0 THEN 1 ELSE -1 END),
	   date '2000-01-01' + (i * 13) % 20000 - 10000,
	   time '00:00' + ((i * 61) % 86400) * interval '1 s',
	   timestamp '2020-01-01' + (i * 977) * interval '1 s' * (CASE WHEN i % 3 = 0 THEN -1 ELSE 1 END),
	   timestamptz '2020-01-01 00:00+00' + (i * 331) * interval '1 min'
  FROM generate_series(1, 6000) i;
-- NULLs in every sliced column, and each type's extremes
UPDATE slc SET i2 = NULL, i4 = NULL, i8 = NULL, d = NULL, tm = NULL, ts = NULL,
			   tz = NULL
 WHERE id % 50 = 0;
INSERT INTO slc VALUES
	(6001, 1, '{t1}', 's1', -32768, -2147483648, -9223372036854775808,
	 '-infinity', '00:00', '-infinity', '-infinity'),
	(6002, 1, '{t1}', 's1', 32767, 2147483647, 9223372036854775807,
	 'infinity', '24:00', 'infinity', 'infinity'),
	(6003, 2, '{t2}', 's2', 0, 0, 0, '2000-01-01', '12:00', '2000-01-01',
	 '2000-01-01 00:00+00'),
	(6004, 2, '{t2}', 's2', -1, -1, -1, '1999-12-31', '00:00:00.000001',
	 '1999-12-31 23:59:59.999999', '1999-12-31 23:59:59.999999+00');
CREATE INDEX slc_idx ON slc USING lion (i2, i4, i8, d, tm, ts, tz)
	WITH (slices = on);
CREATE INDEX slc_g ON slc USING lion (g);
CREATE INDEX slc_tags ON slc USING lion (tags);
VACUUM (FREEZE, ANALYZE) slc;
SELECT * FROM slc_meta('slc_idx');
 version | slice_cols 
---------+------------
       1 |        127
(1 row)

SELECT attno, sliced, entries, ntids, null_tids, slice_entries, slice_tids
  FROM lion_index_stats('slc_idx');
 attno | sliced | entries | ntids | null_tids | slice_entries | slice_tids 
-------+--------+---------+-------+-----------+---------------+------------
     1 | t      |    2004 |  6004 |       120 |            17 |      52954
     2 | t      |    5885 |  6004 |       120 |            33 |     100037
     3 | t      |    5885 |  6004 |       120 |            65 |     196026
     4 | t      |    5885 |  6004 |       120 |            33 |     101388
     5 | t      |    5885 |  6004 |       120 |            34 |      99680
     6 | t      |    5885 |  6004 |       120 |            65 |     138649
     7 | t      |    5885 |  6004 |       120 |            65 |     134390
(7 rows)

SELECT lion_index_verify('slc_idx');
 lion_index_verify 
-------------------
 
(1 row)

SELECT lion_index_verify('slc_idx', true);
 lion_index_verify 
-------------------
 
(1 row)

SELECT a AS attno, f AS filter, slc_cmp('slc_idx', f, a::int2)
  FROM generate_series(1, 7) a,
	   unnest(ARRAY[NULL, 'g = 3', 'g IN (1, 4)', 'g = 2 OR tags @> ''{t1}''',
					'g = 5 AND tags @> ''{u2}''', 'g IS NULL', 'g = 99',
					'i4 = 0', 'i4 IN (-1, -2147483648, 2147483647)']) f
 ORDER BY a, f NULLS FIRST;
 attno |               filter                |                                slc_cmp                                
-------+-------------------------------------+-----------------------------------------------------------------------
     1 |                                     | same: 5884 760 -32768 32767
     1 | g = 2 OR tags @> '{t1}'             | same: 1873 3086 -32768 32767
     1 | g = 3                               | same: 840 -436 -999 999
     1 | g = 5 AND tags @> '{u2}'            | same: 281 -1135 -995 997
     1 | g = 99                              | same: 0 - - -
     1 | g IN (1, 4)                         | same: 1682 2870 -32768 32767
     1 | g IS NULL                           | same: 0 - - -
     1 | i4 = 0                              | same: 1 0 0 0
     1 | i4 IN (-1, -2147483648, 2147483647) | same: 3 -2 -32768 32767
     2 |                                     | same: 5884 -121795 -2147483648 2147483647
     2 | g = 2 OR tags @> '{t1}'             | same: 1873 -223880 -2147483648 2147483647
     2 | g = 3                               | same: 840 127174 -49871 49965
     2 | g = 5 AND tags @> '{u2}'            | same: 281 -274458 -49702 49517
     2 | g = 99                              | same: 0 - - -
     2 | g IN (1, 4)                         | same: 1682 -201098 -2147483648 2147483647
     2 | g IS NULL                           | same: 0 - - -
     2 | i4 = 0                              | same: 1 0 0 0
     2 | i4 IN (-1, -2147483648, 2147483647) | same: 3 -2 -2147483648 2147483647
     3 |                                     | same: 5884 -360001080002 -9223372036854775808 9223372036854775807
     3 | g = 2 OR tags @> '{t1}'             | same: 1873 -46293138881 -9223372036854775808 9223372036854775807
     3 | g = 3                               | same: 840 -53149159447 -5995017985 5988017964
     3 | g = 5 AND tags @> '{u2}'            | same: 281 -12247036741 -5969017907 5990017970
     3 | g = 99                              | same: 0 - - -
     3 | g IN (1, 4)                         | same: 1682 -99447298342 -9223372036854775808 9223372036854775807
     3 | g IS NULL                           | same: 0 - - -
     3 | i4 = 0                              | same: 1 0 0 0
     3 | i4 IN (-1, -2147483648, 2147483647) | same: 3 -2 -9223372036854775808 9223372036854775807
     4 |                                     | same: 5884 - -infinity infinity
     4 | g = 2 OR tags @> '{t1}'             | same: 1873 - -infinity infinity
     4 | g = 3                               | same: 840 - 08-23-1972 05-07-2027
     4 | g = 5 AND tags @> '{u2}'            | same: 281 - 10-19-1972 05-13-2027
     4 | g = 99                              | same: 0 - - -
     4 | g IN (1, 4)                         | same: 1682 - -infinity infinity
     4 | g IS NULL                           | same: 0 - - -
     4 | i4 = 0                              | same: 1 - 01-01-2000 01-01-2000
     4 | i4 IN (-1, -2147483648, 2147483647) | same: 3 - -infinity infinity
     5 |                                     | same: 5884 - 00:00:00 24:00:00
     5 | g = 2 OR tags @> '{t1}'             | same: 1873 - 00:00:00 24:00:00
     5 | g = 3                               | same: 840 - 00:00:26 23:58:11
     5 | g = 5 AND tags @> '{u2}'            | same: 281 - 00:05:05 23:57:47
     5 | g = 99                              | same: 0 - - -
     5 | g IN (1, 4)                         | same: 1682 - 00:00:00 24:00:00
     5 | g IS NULL                           | same: 0 - - -
     5 | i4 = 0                              | same: 1 - 12:00:00 12:00:00
     5 | i4 IN (-1, -2147483648, 2147483647) | same: 3 - 00:00:00 24:00:00
     6 |                                     | same: 5884 - -infinity infinity
     6 | g = 2 OR tags @> '{t1}'             | same: 1873 - -infinity infinity
     6 | g = 3                               | same: 840 - Fri Oct 25 06:55:24 2019 Sun Mar 08 18:58:35 2020
     6 | g = 5 AND tags @> '{u2}'            | same: 281 - Wed Jan 01 01:21:25 2020 Sun Mar 08 17:37:10 2020
     6 | g = 99                              | same: 0 - - -
     6 | g IN (1, 4)                         | same: 1682 - -infinity infinity
     6 | g IS NULL                           | same: 0 - - -
     6 | i4 = 0                              | same: 1 - Sat Jan 01 00:00:00 2000 Sat Jan 01 00:00:00 2000
     6 | i4 IN (-1, -2147483648, 2147483647) | same: 3 - -infinity infinity
     7 |                                     | same: 5884 - -infinity infinity
     7 | g = 2 OR tags @> '{t1}'             | same: 1873 - -infinity infinity
     7 | g = 3                               | same: 840 - Wed Jan 01 08:33:00 2020 PST Mon Oct 09 17:25:00 2023 PDT
     7 | g = 5 AND tags @> '{u2}'            | same: 281 - Wed Jan 01 19:35:00 2020 PST Sun Oct 08 13:50:00 2023 PDT
     7 | g = 99                              | same: 0 - - -
     7 | g IN (1, 4)                         | same: 1682 - -infinity infinity
     7 | g IS NULL                           | same: 0 - - -
     7 | i4 = 0                              | same: 1 - Fri Dec 31 16:00:00 1999 PST Fri Dec 31 16:00:00 1999 PST
     7 | i4 IN (-1, -2147483648, 2147483647) | same: 3 - -infinity infinity
(63 rows)

-- ---------- 2. what the function refuses ----------
CREATE INDEX slc_mc ON slc USING lion (i4, s) WITH (slices = on);
SELECT attno, sliced, slice_entries FROM lion_index_stats('slc_mc');
 attno | sliced | slice_entries 
-------+--------+---------------
     1 | t      |            33
     2 | f      |             0
(2 rows)

SELECT slc_cmp('slc_mc', 'g = 1');
                 slc_cmp                  
------------------------------------------
 same: 842 -183699 -2147483648 2147483647
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_mc', NULL, 2::int2)$$);
                                                                    slc_err                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------
 0A000: key column 2 of lion index "slc_mc" is not bit-sliced (Its type text cannot be sliced; integer, date, time and timestamp columns can.)
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_g')$$);
                           slc_err                           
-------------------------------------------------------------
 0A000: key column 1 of lion index "slc_g" is not bit-sliced
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_tags')$$);
                                                   slc_err                                                   
-------------------------------------------------------------------------------------------------------------
 0A000: key column 1 of index "slc_tags" has a multi-key operator class, whose entries are not column values
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_idx', NULL, 9::int2)$$);
                     slc_err                     
-------------------------------------------------
 22023: lion index "slc_idx" has no key column 9
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_idx', NULL, 0::int2)$$);
                     slc_err                     
-------------------------------------------------
 22023: lion index "slc_idx" has no key column 0
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_idx', 'i4 > 5')$$);
                                                                                    slc_err                                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0A000: slice filter condition (i4 > 5) cannot be answered from lion indexes (No lion index of the table answers this operator on this column; a partial index is never used.)
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_idx', 'NOT g = 1')$$);
                                                                                    slc_err                                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0A000: slice filter condition (g <> 1) cannot be answered from lion indexes (No lion index of the table answers this operator on this column; a partial index is never used.)
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_idx', 'g = 1; SELECT 1')$$);
                      slc_err                      
---------------------------------------------------
 42601: a slice filter must be one WHERE condition
(1 row)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc', NULL)$$);
           slc_err            
------------------------------
 42809: "slc" is not an index
(1 row)

SELECT * FROM lion_index_slice_agg(NULL);
 count | sum | min | max 
-------+-----+-----+-----
       |     |     | 
(1 row)

-- a column of a type that cannot be sliced is simply not sliced
CREATE TABLE slc_types (o oid, f float8, n numeric, iv interval, k int4);
INSERT INTO slc_types SELECT i, i, i, i * interval '1 s', i FROM generate_series(1, 100) i;
CREATE INDEX slc_types_idx ON slc_types USING lion (o, f, n, iv, k)
	WITH (slices = on);
SELECT attno, sliced, slice_entries, slice_tids FROM lion_index_stats('slc_types_idx');
 attno | sliced | slice_entries | slice_tids 
-------+--------+---------------+------------
     1 | f      |             0 |          0
     2 | f      |             0 |          0
     3 | f      |             0 |          0
     4 | f      |             0 |          0
     5 | t      |             9 |        519
(5 rows)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_types_idx', NULL, 2::int2)$$);
                                                                             slc_err                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0A000: key column 2 of lion index "slc_types_idx" is not bit-sliced (Its type double precision cannot be sliced; integer, date, time and timestamp columns can.)
(1 row)

SELECT slc_cmp('slc_types_idx', NULL, 5::int2);
       slc_cmp        
----------------------
 same: 100 5050 1 100
(1 row)

DROP TABLE slc_types;
-- ---------- 3. an index built empty, then inserts ----------
CREATE TABLE slc_e (id int, g int, v int8) WITH (autovacuum_enabled = off);
CREATE INDEX slc_e_v ON slc_e USING lion (v, g) WITH (slices = on);
CREATE INDEX slc_e_g ON slc_e USING lion (g);
SELECT * FROM slc_meta('slc_e_v');
 version | slice_cols 
---------+------------
       1 |          3
(1 row)

SELECT attno, sliced, slice_entries, slice_tids FROM lion_index_stats('slc_e_v');
 attno | sliced | slice_entries | slice_tids 
-------+--------+---------------+------------
     1 | t      |             0 |          0
     2 | t      |             0 |          0
(2 rows)

SELECT slc_cmp('slc_e_v');
    slc_cmp    
---------------
 same: 0 - - -
(1 row)

-- dense: a run of small keys, every row in the high slices
INSERT INTO slc_e SELECT i, i % 3, i % 8 FROM generate_series(1, 3000) i;
SELECT slc_cmp('slc_e_v'), slc_cmp('slc_e_v', 'g = 1');
       slc_cmp        |       slc_cmp       
----------------------+---------------------
 same: 3000 10500 0 7 | same: 1000 3500 0 7
(1 row)

-- sparse: a few rows with bits no other row has, and negative ones
INSERT INTO slc_e VALUES (3001, 0, 1099511627776), (3002, 1, -5), (3003, 2, NULL),
	(3004, 1, -9223372036854775807);
SELECT slc_cmp('slc_e_v'), slc_cmp('slc_e_v', 'g = 1'), slc_cmp('slc_e_v', 'g = 2');
                              slc_cmp                               |                        slc_cmp                         |       slc_cmp       
--------------------------------------------------------------------+--------------------------------------------------------+---------------------
 same: 3003 -9223370937343137536 -9223372036854775807 1099511627776 | same: 1002 -9223372036854772312 -9223372036854775807 7 | same: 1000 3500 0 7
(1 row)

-- into the containers the rows above made, one row at a time
INSERT INTO slc_e SELECT 4000 + i, 1, i * 1000 FROM generate_series(1, 40) i;
SELECT slc_cmp('slc_e_v', 'g = 1');
                          slc_cmp                           
------------------------------------------------------------
 same: 1042 -9223372036853952312 -9223372036854775807 40000
(1 row)

SELECT attno, sliced, slice_entries, slice_tids FROM lion_index_stats('slc_e_v');
 attno | sliced | slice_entries | slice_tids 
-------+--------+---------------+------------
     1 | t      |            65 |      10892
     2 | t      |             4 |       8131
(2 rows)

SELECT lion_index_verify('slc_e_v', true);
 lion_index_verify 
-------------------
 
(1 row)

-- an unlogged table's init fork records the sliced columns too (buildempty)
CREATE UNLOGGED TABLE slc_u (v int2);
CREATE INDEX slc_u_v ON slc_u USING lion (v) WITH (slices = on);
INSERT INTO slc_u SELECT (i % 200 - 100)::int2 FROM generate_series(1, 1000) i;
SELECT slc_cmp('slc_u_v');
         slc_cmp         
-------------------------
 same: 1000 -500 -100 99
(1 row)

SELECT lion_index_verify('slc_u_v', true);
 lion_index_verify 
-------------------
 
(1 row)

DROP TABLE slc_u;
-- ---------- 4. updates and deletes, VACUUM, this transaction's rows ----------
UPDATE slc SET i4 = -i4, i8 = i8 / 3, tz = tz + interval '1 year'
 WHERE id % 11 = 0;
DELETE FROM slc WHERE id % 13 = 0;
INSERT INTO slc (id, g, i2, i4, i8, d, tm, ts, tz)
SELECT 7000 + i, i % 7, i::int2, i * 100, i * 1000, date '1970-01-01' + i,
	   time '06:00' + i * interval '1 min', timestamp '1990-01-01' + i * interval '1 day',
	   timestamptz '1990-01-01 00:00+00' + i * interval '1 h'
  FROM generate_series(1, 500) i;
-- a dirty heap: dead TIDs in the slices, rows the map does not vouch for
SELECT a AS attno, f AS filter, slc_cmp('slc_idx', f, a::int2)
  FROM generate_series(1, 7) a, unnest(ARRAY[NULL, 'g = 3', 'g IN (0, 6)']) f
 ORDER BY a, f NULLS FIRST;
 attno |   filter    |                                slc_cmp                                 
-------+-------------+------------------------------------------------------------------------
     1 |             | same: 5932 114915 -32768 32767
     1 | g = 3       | same: 847 15264 -999 999
     1 | g IN (0, 6) | same: 1694 34255 -1000 1000
     2 |             | same: 5932 13425958 -2147483648 2147483647
     2 | g = 3       | same: 847 2190999 -49704 50000
     2 | g IN (0, 6) | same: 1694 4177542 -49983 49927
     3 |             | same: 5932 -307456006073 -9223372036854775808 9223372036854775807
     3 | g = 3       | same: 847 -41324016026 -5981017943 5988017964
     3 | g IN (0, 6) | same: 1694 -95419573364 -5999017997 5998017994
     4 |             | same: 5932 - -infinity infinity
     4 | g = 3       | same: 847 - 01-04-1970 05-07-2027
     4 | g IN (0, 6) | same: 1694 - 01-07-1970 04-18-2027
     5 |             | same: 5932 - 00:00:00 24:00:00
     5 | g = 3       | same: 847 - 00:00:26 23:58:11
     5 | g IN (0, 6) | same: 1694 - 00:02:15 23:59:49
     6 |             | same: 5932 - -infinity infinity
     6 | g = 3       | same: 847 - Thu Jan 04 00:00:00 1990 Sun Mar 08 18:58:35 2020
     6 | g IN (0, 6) | same: 1694 - Sun Jan 07 00:00:00 1990 Sun Mar 08 20:03:43 2020
     7 |             | same: 5932 - -infinity infinity
     7 | g = 3       | same: 847 - Sun Dec 31 19:00:00 1989 PST Wed Oct 09 17:25:00 2024 PDT
     7 | g IN (0, 6) | same: 1694 - Sun Dec 31 22:00:00 1989 PST Mon Oct 07 04:44:00 2024 PDT
(21 rows)

SELECT attno, slice_tids FROM lion_index_stats('slc_idx') WHERE attno IN (2, 3);
 attno | slice_tids 
-------+------------
     2 |     113445
     3 |     218832
(2 rows)

SELECT lion_index_verify('slc_idx', true);
 lion_index_verify 
-------------------
 
(1 row)

VACUUM (FREEZE) slc;
SELECT attno, slice_tids FROM lion_index_stats('slc_idx') WHERE attno IN (2, 3);
 attno | slice_tids 
-------+------------
     2 |      96625
     3 |     185911
(2 rows)

SELECT a AS attno, f AS filter, slc_cmp('slc_idx', f, a::int2)
  FROM generate_series(1, 7) a, unnest(ARRAY[NULL, 'g = 3', 'g IN (0, 6)']) f
 ORDER BY a, f NULLS FIRST;
 attno |   filter    |                                slc_cmp                                 
-------+-------------+------------------------------------------------------------------------
     1 |             | same: 5932 114915 -32768 32767
     1 | g = 3       | same: 847 15264 -999 999
     1 | g IN (0, 6) | same: 1694 34255 -1000 1000
     2 |             | same: 5932 13425958 -2147483648 2147483647
     2 | g = 3       | same: 847 2190999 -49704 50000
     2 | g IN (0, 6) | same: 1694 4177542 -49983 49927
     3 |             | same: 5932 -307456006073 -9223372036854775808 9223372036854775807
     3 | g = 3       | same: 847 -41324016026 -5981017943 5988017964
     3 | g IN (0, 6) | same: 1694 -95419573364 -5999017997 5998017994
     4 |             | same: 5932 - -infinity infinity
     4 | g = 3       | same: 847 - 01-04-1970 05-07-2027
     4 | g IN (0, 6) | same: 1694 - 01-07-1970 04-18-2027
     5 |             | same: 5932 - 00:00:00 24:00:00
     5 | g = 3       | same: 847 - 00:00:26 23:58:11
     5 | g IN (0, 6) | same: 1694 - 00:02:15 23:59:49
     6 |             | same: 5932 - -infinity infinity
     6 | g = 3       | same: 847 - Thu Jan 04 00:00:00 1990 Sun Mar 08 18:58:35 2020
     6 | g IN (0, 6) | same: 1694 - Sun Jan 07 00:00:00 1990 Sun Mar 08 20:03:43 2020
     7 |             | same: 5932 - -infinity infinity
     7 | g = 3       | same: 847 - Sun Dec 31 19:00:00 1989 PST Wed Oct 09 17:25:00 2024 PDT
     7 | g IN (0, 6) | same: 1694 - Sun Dec 31 22:00:00 1989 PST Mon Oct 07 04:44:00 2024 PDT
(21 rows)

SELECT lion_index_verify('slc_idx', true);
 lion_index_verify 
-------------------
 
(1 row)

-- this transaction's own rows: inserted, deleted, updated, then rolled back
BEGIN;
INSERT INTO slc (id, g, i4, i8) VALUES (9003, 3, 2147483647, -9223372036854775808),
	(9007, 3, -2147483648, 9223372036854775807);
DELETE FROM slc WHERE g = 3 AND id % 4 = 1;
UPDATE slc SET i4 = 7, i8 = 7 WHERE g = 3 AND id % 4 = 2;
SELECT slc_cmp('slc_idx', 'g = 3', 2::int2), slc_cmp('slc_idx', 'g = 3', 3::int2),
	   slc_cmp('slc_idx', NULL, 2::int2);
                 slc_cmp                 |                             slc_cmp                             |                  slc_cmp                   
-----------------------------------------+-----------------------------------------------------------------+--------------------------------------------
 same: 642 871893 -2147483648 2147483647 | same: 642 -14388987683 -9223372036854775808 9223372036854775807 | same: 5727 12106852 -2147483648 2147483647
(1 row)

ROLLBACK;
SELECT slc_cmp('slc_idx', 'g = 3', 2::int2), slc_cmp('slc_idx', 'g = 3', 3::int2),
	   slc_cmp('slc_idx', NULL, 2::int2);
            slc_cmp             |                    slc_cmp                    |                  slc_cmp                   
--------------------------------+-----------------------------------------------+--------------------------------------------
 same: 847 2190999 -49704 50000 | same: 847 -41324016026 -5981017943 5988017964 | same: 5932 13425958 -2147483648 2147483647
(1 row)

-- REPEATABLE READ: one snapshot for both; test/isolation/slices_snapshot.spec
-- has another session write beside it
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT slc_cmp('slc_idx', 'g IN (1, 2)', 4::int2), slc_cmp('slc_idx', NULL, 7::int2);
             slc_cmp             |             slc_cmp             
---------------------------------+---------------------------------
 same: 1698 - -infinity infinity | same: 5932 - -infinity infinity
(1 row)

COMMIT;
SELECT lion_index_verify('slc_idx', true);
 lion_index_verify 
-------------------
 
(1 row)

-- ---------- 5. a partial index, and verify of a broken slice ----------
CREATE INDEX slc_p ON slc USING lion (i8) WITH (slices = on) WHERE g < 3;
SELECT slc_cmp('slc_p'), slc_cmp('slc_p', 'g = 1'), slc_cmp('slc_p', 'g = 5');
                              slc_cmp                              |                             slc_cmp                             |    slc_cmp    
-------------------------------------------------------------------+-----------------------------------------------------------------+---------------
 same: 2545 -123470811907 -9223372036854775808 9223372036854775807 | same: 849 -36488812187 -9223372036854775808 9223372036854775807 | same: 0 - - -
(1 row)

SELECT lion_index_verify('slc_p', true);
 lion_index_verify 
-------------------
 
(1 row)

-- a row put into a slice its value does not name, by a test function: verify
-- finds it from the keys, and again from the heap
CREATE FUNCTION lion_debug_slice_insert(idx regclass, attno int2, pos int2, t tid)
	RETURNS void AS '$libdir/pg_lion', 'lion_debug_slice_insert' LANGUAGE C STRICT;
SELECT i8, ctid AS slc_t FROM slc WHERE id = 6003 \gset
SELECT lion_debug_slice_insert('slc_p', 1::int2, 5::int2, :'slc_t');
 lion_debug_slice_insert 
-------------------------
 
(1 row)

SELECT slc_err($$SELECT lion_index_verify('slc_p')$$);
                                                              slc_err                                                               
------------------------------------------------------------------------------------------------------------------------------------
 XX002: lion index "slc_p": heap tuple (130,24) is in the slice of bit 5 of key column 1, but its key's encoding has that bit clear
(1 row)

-- the existence slice holding a row the column has under no key (a NULL)
REINDEX INDEX slc_p;
SELECT ctid AS slc_t FROM slc WHERE id = 50 \gset
SELECT lion_debug_slice_insert('slc_p', 1::int2, 64::int2, :'slc_t');
 lion_debug_slice_insert 
-------------------------
 
(1 row)

SELECT slc_err($$SELECT lion_index_verify('slc_p')$$);
                                                              slc_err                                                               
------------------------------------------------------------------------------------------------------------------------------------
 XX002: lion index "slc_p": heap tuple (1,47) is in the existence slice of key column 1, but not under the key its bit slices spell
(1 row)

SELECT slc_err($$SELECT lion_debug_slice_insert('slc_p', 1::int2, 65::int2, '(0,1)')$$);
                          slc_err                          
-----------------------------------------------------------
 22023: key column 1 of lion index "slc_p" has no slice 65
(1 row)

SELECT slc_err($$SELECT lion_debug_slice_insert('slc_g', 1::int2, 0::int2, '(0,1)')$$);
                           slc_err                           
-------------------------------------------------------------
 22023: key column 1 of lion index "slc_g" is not bit-sliced
(1 row)

DROP FUNCTION lion_debug_slice_insert(regclass, int2, int2, tid);
DROP INDEX slc_p;
-- ---------- 6. the readers that walk keys never meet a slice ----------
-- Two tables of the same rows, one index each, with summaries (§32) and
-- with and without slices: every walk of keys gives the same answer from
-- both, and the plan shows that the walk ran.
CREATE TABLE slc_w1 (id int, k int4, g int4, ts timestamp)
	WITH (autovacuum_enabled = off);
INSERT INTO slc_w1 SELECT i, CASE WHEN i % 97 = 0 THEN NULL ELSE (i * 31) % 400 - 200 END,
	   i % 9, timestamp '2020-01-01' + i * interval '1 h'
  FROM generate_series(1, 8000) i;
CREATE TABLE slc_w2 AS SELECT * FROM slc_w1;
ALTER TABLE slc_w2 SET (autovacuum_enabled = off);
CREATE INDEX slc_w1_k ON slc_w1 USING lion (k, g, ts)
	WITH (slices = on, summaries = on, summary_tids = 64);
CREATE INDEX slc_w2_k ON slc_w2 USING lion (k, g, ts)
	WITH (summaries = on, summary_tids = 64);
VACUUM (FREEZE, ANALYZE) slc_w1, slc_w2;
SELECT * FROM slc_meta('slc_w1_k');
 version | slice_cols 
---------+------------
       1 |          7
(1 row)

SELECT * FROM slc_meta('slc_w2_k');
 version | slice_cols 
---------+------------
       1 |          0
(1 row)

-- the rows of a query over slc_w1 and over slc_w2, as text
CREATE FUNCTION slc_both(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE a text; b text;
BEGIN
	EXECUTE format('SELECT string_agg(x::text, '' '') FROM (' || q || ') x', 'slc_w1') INTO a;
	EXECUTE format('SELECT string_agg(x::text, '' '') FROM (' || q || ') x', 'slc_w2') INTO b;
	RETURN CASE WHEN a IS NOT DISTINCT FROM b THEN 'same' ELSE a || ' <> ' || b END;
END $$;
-- the lion paths, whatever the tables' sizes would choose
SET enable_seqscan = off;
-- GROUP BY a sliced column, two columns, its top k, count(DISTINCT)
EXPLAIN (COSTS OFF) SELECT k, count(*) FROM slc_w1 GROUP BY k;
           QUERY PLAN           
--------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.k (k)
   Group Key: k
(3 rows)

SELECT slc_both($$SELECT k, count(*) FROM %s GROUP BY k ORDER BY k$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT k, g, count(*) FROM %s WHERE g < 4 GROUP BY k, g ORDER BY k, g$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT k, count(*) FROM %s GROUP BY k ORDER BY 2 DESC, 1 LIMIT 3$$);
 slc_both 
----------
 same
(1 row)

EXPLAIN (COSTS OFF) SELECT count(DISTINCT k) FROM slc_w1;
           QUERY PLAN           
--------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.k (k)
   Distinct Key: k
(3 rows)

SELECT slc_both($$SELECT count(DISTINCT k) FROM %s$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(DISTINCT k) FROM %s WHERE g = 2$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(DISTINCT ts) FROM %s$$);
 slc_both 
----------
 same
(1 row)

-- the aggregates over keys (§37), a walk of the entries' headers on an
-- all-visible heap, which costs less than the slices' answer (§52) for every
-- row of the table: the plan with pg_lion.enable_count_slices off, and on
SET pg_lion.enable_count_slices = off;
EXPLAIN (COSTS OFF) SELECT sum(k), min(k), max(k), count(*) FROM slc_w1;
              QUERY PLAN               
---------------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.g (all rows)
   Aggregates Over Keys: slc_w1_k (k)
(3 rows)

SELECT slc_both($$SELECT sum(k), min(k), max(k), count(*) FROM %s$$);
 slc_both 
----------
 same
(1 row)

RESET pg_lion.enable_count_slices;
EXPLAIN (COSTS OFF) SELECT sum(k), min(k), max(k), count(*) FROM slc_w1;
              QUERY PLAN               
---------------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.g (all rows)
   Aggregates Over Keys: slc_w1_k (k)
(3 rows)

-- ranges, summed from the summaries, whose walk ends where the slices begin
-- (the bitmap scan off: the two cost about the same here); a range this
-- wide is answered from the slices themselves (§53, test/sql/slices_range.sql)
-- unless pg_lion.enable_slice_ranges is off - and under it the aggregates
-- come from the slices too (§52), as no walk of keys answers a WHERE
SET enable_bitmapscan = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM slc_w1 WHERE k > -150;
                      QUERY PLAN                      
------------------------------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.k (k > '-150'::integer)
   Range From Slices: k > '-150'::integer (32 slices)
(3 rows)

EXPLAIN (COSTS OFF) SELECT sum(k), min(k), max(k), count(*) FROM slc_w1 WHERE k > -150;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.k (k > '-150'::integer)
   Range From Slices: k > '-150'::integer (32 slices)
   Aggregates From Slices: slc_w1_k (k: every bit counted, min descent, max descent)
(4 rows)

SELECT slc_both($$SELECT sum(k), min(k), max(k), count(*) FROM %s WHERE k > -150$$);
 slc_both 
----------
 same
(1 row)

SET pg_lion.enable_slice_ranges = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM slc_w1 WHERE k > -150;
                    QUERY PLAN                    
--------------------------------------------------
 Custom Scan (LionCount)
   Lion Indexes: slc_w1_k.k (k > '-150'::integer)
(2 rows)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k > -150$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k >= -200$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k BETWEEN -50 AND 50 AND g = 1$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*) FROM %s WHERE ts >= '2020-03-01'$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k <> 3$$);
 slc_both 
----------
 same
(1 row)

RESET pg_lion.enable_slice_ranges;
RESET enable_bitmapscan;
-- IS NOT NULL and a range by bitmap scans: a walk of the whole column
SET pg_lion.enable_count_pushdown = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM slc_w1 WHERE k IS NOT NULL;
                QUERY PLAN                 
-------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on slc_w1
         Recheck Cond: (k IS NOT NULL)
         ->  Bitmap Index Scan on slc_w1_k
               Index Cond: (k IS NOT NULL)
(5 rows)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k IS NOT NULL$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*) FROM %s WHERE k > 150$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT count(*), min(id) FROM %s WHERE ts IS NOT NULL$$);
 slc_both 
----------
 same
(1 row)

RESET enable_seqscan;
RESET pg_lion.enable_count_pushdown;
-- the facets
SELECT slc_both($$SELECT * FROM lion_index_facets('%s_k', 5)$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT * FROM lion_index_facets('%s_k', NULL, 'g = 4')$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT * FROM lion_index_facets('%s_k', 3, NULL, 2::int2)$$);
 slc_both 
----------
 same
(1 row)

-- the index's statistics and the planner's distinct keys (§33)
SELECT slc_both($$SELECT attno, entries, ntids, null_tids, summary_entries, summary_tids, ndistinct
	FROM lion_index_stats('%s_k')$$);
 slc_both 
----------
 same
(1 row)

SELECT attno, sliced, slice_entries FROM lion_index_stats('slc_w1_k');
 attno | sliced | slice_entries 
-------+--------+---------------
     1 | t      |            33
     2 | t      |             6
     3 | t      |            40
(3 rows)

-- a key of no row: its entry and slices emptied by VACUUM
DELETE FROM slc_w1 WHERE k = 0 OR id > 7990;
DELETE FROM slc_w2 WHERE k = 0 OR id > 7990;
VACUUM (FREEZE, ANALYZE) slc_w1, slc_w2;
SELECT slc_both($$SELECT attno, entries, ntids, null_tids, summary_entries, ndistinct
	FROM lion_index_stats('%s_k')$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_both($$SELECT k, count(*) FROM %s GROUP BY k ORDER BY k$$);
 slc_both 
----------
 same
(1 row)

SELECT slc_cmp('slc_w1_k'), slc_cmp('slc_w1_k', 'g = 8'), slc_cmp('slc_w1_k', NULL, 3::int2);
          slc_cmp          |         slc_cmp         |                            slc_cmp                             
---------------------------+-------------------------+----------------------------------------------------------------
 same: 7888 -3826 -200 199 | same: 876 -668 -200 199 | same: 7970 - Wed Jan 01 01:00:00 2020 Sat Nov 28 22:00:00 2020
(1 row)

SELECT lion_index_verify('slc_w1_k', true);
 lion_index_verify 
-------------------
 
(1 row)

-- ---------- 7. the option, REINDEX, and the format ----------
-- read at build time only: ALTER INDEX changes the next REINDEX
ALTER INDEX slc_w1_k SET (slices = off);
SELECT attno, sliced FROM lion_index_stats('slc_w1_k');
 attno | sliced 
-------+--------
     1 | t
     2 | t
     3 | t
(3 rows)

REINDEX INDEX slc_w1_k;
SELECT * FROM slc_meta('slc_w1_k');
 version | slice_cols 
---------+------------
       1 |          0
(1 row)

SELECT attno, sliced, slice_entries FROM lion_index_stats('slc_w1_k');
 attno | sliced | slice_entries 
-------+--------+---------------
     1 | f      |             0
     2 | f      |             0
     3 | f      |             0
(3 rows)

SELECT slc_err($$SELECT * FROM lion_index_slice_agg('slc_w1_k')$$);
                            slc_err                             
----------------------------------------------------------------
 0A000: key column 1 of lion index "slc_w1_k" is not bit-sliced
(1 row)

ALTER INDEX slc_w1_k SET (slices = on);
REINDEX INDEX slc_w1_k;
SELECT * FROM slc_meta('slc_w1_k');
 version | slice_cols 
---------+------------
       1 |          7
(1 row)

SELECT slc_cmp('slc_w1_k', 'g = 4');
         slc_cmp         
-------------------------
 same: 877 -985 -200 199
(1 row)

SELECT lion_index_verify('slc_w1_k', true);
 lion_index_verify 
-------------------
 
(1 row)

SELECT slc_err($$CREATE INDEX slc_bad ON slc_w2 USING lion (k) WITH (slices = maybe)$$);
                         slc_err                         
---------------------------------------------------------
 22023: invalid value for boolean option "slices": maybe
(1 row)

-- A meta page of another format version: refused by every reader.  Written
-- on the file under the server, before anything has read the new index's
-- meta page into the buffers.
CREATE FUNCTION slc_poke(idx regclass, pos int8, val int8)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
	path text := pg_relation_filepath(idx);
	bs int := current_setting('block_size')::int;
	f bytea := pg_read_binary_file(path);
	page bytea := substring(f FROM 1 FOR bs);
	ck int;
	lo oid;
BEGIN
	-- the uint16 at pos of block 0, little-endian; pd_checksum is the uint16
	-- at byte 8 (data checksums are on by default from PostgreSQL 18)
	page := overlay(page PLACING set_byte(set_byte('\x0000'::bytea, 0, (val & 255)::int),
										  1, ((val >> 8) & 255)::int)
					FROM pos::int + 1);
	ck := page_checksum(page, 0)::int & 65535;
	page := overlay(page PLACING set_byte(set_byte('\x0000'::bytea, 0, ck & 255), 1, ck >> 8)
					FROM 9);
	f := overlay(f PLACING page FROM 1);
	lo := lo_from_bytea(0, f);
	PERFORM lo_export(lo, path);
	PERFORM lo_unlink(lo);
END $$;
CREATE TABLE slc_f (k int);
INSERT INTO slc_f SELECT generate_series(1, 100);
CREATE INDEX slc_f_k ON slc_f USING lion (k) WITH (slices = on);
SELECT slc_poke('slc_f_k', 28, 2);
 slc_poke 
----------
 
(1 row)

SELECT slc_err($$SELECT lion_index_verify('slc_f_k')$$);
                                                       slc_err                                                       
---------------------------------------------------------------------------------------------------------------------
 XX002: index "slc_f_k" is not a valid lion index (Meta page magic 52424931 version 2, expected 52424931 version 1.)
(1 row)

DROP TABLE slc_f;
DROP TABLE slc, slc_e, slc_w1, slc_w2;
DROP FUNCTION slc_both(text);
DROP FUNCTION slc_poke(regclass, int8, int8);
DROP FUNCTION slc_meta(regclass);
DROP FUNCTION slc_err(text);
DROP FUNCTION slc_cmp(regclass, text, int2);
DROP FUNCTION slc_ref(regclass, text, int2);