-- The paths of the jsonb columns of an index with no paths option (or paths = 'auto') are read once, at CREATE INDEX, from a sample of their documents
-- (sift.suggest_paths), written to the option as 'auto: spec' so that a rebuild keeps them, and said in a NOTICE. The answers are the plain plan's, and an index with paths written by hand is untouched.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
CREATE EXTENSION pg_sift_helpers;
SET client_min_messages = notice;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
CREATE TABLE pa (id int, doc jsonb, tail jsonb) WITH (autovacuum_enabled = off);
INSERT INTO pa SELECT i, jsonb_build_object('name', 'n' || i, 'count', i % 100, 'meta', jsonb_build_object('lang', 'l' || i % 5),
	'parts', jsonb_build_array(jsonb_build_object('cat', 'c' || i % 4, 'score', i % 10))), jsonb_build_object('t', i % 7) FROM generate_series(1, 3000) i;
VACUUM (FREEZE, ANALYZE) pa;
CREATE FUNCTION pa_option(i text) RETURNS text LANGUAGE sql AS
$$ SELECT coalesce((SELECT o FROM pg_class c, unnest(c.reloptions) o WHERE c.relname = i AND o LIKE 'paths=%'), 'not set') $$;
CREATE FUNCTION pa_rows(q text, push bool) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.rows_agg_other_scans_off(q, push) $$;
CREATE FUNCTION pa_pushed(q text) RETURNS bool LANGUAGE sql AS $$ SELECT sift_helpers.plan_has_siftagg_with_scans_off(q) $$;
CREATE TABLE pa_q (n int, q text);
INSERT INTO pa_q VALUES
	(1, 'SELECT doc->''meta''->>''lang'', count(*) FROM pa GROUP BY 1'),
	(2, 'SELECT (doc->>''count'')::int, count(*) FROM pa WHERE doc->>''name'' > ''n5'' GROUP BY 1'),
	(3, 'SELECT e->>''cat'', count(*), sum((e->>''score'')::float8) FROM pa, jsonb_array_elements(doc->''parts'') e GROUP BY 1'),
	(4, 'SELECT tail->>''t'', count(*) FROM pa GROUP BY 1');
-- no paths option: decided at CREATE INDEX (a jsonb column needed paths, so this was an error): the NOTICE, the option, and the answers
CREATE INDEX pa_auto ON pa USING sift_col (id, doc, tail);
NOTICE:  sift_col index "pa_auto": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key;
paths = doc.count int4, doc.meta.lang text, and 4 more
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_auto');
SELECT pa_option('pa_auto');
                                                   pa_option                                                   
---------------------------------------------------------------------------------------------------------------
 paths=auto: doc.count int4, doc.meta.lang text, doc.name text, doc.parts[]{cat text, score int4}, tail.t int4
(1 row)

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

SELECT n, pa_rows(q, true) = pa_rows(q, false) AS same, pa_pushed(q) AS pushed FROM pa_q ORDER BY n;
 n | same | pushed 
---+------+--------
 1 | t    | t
 2 | t    | t
 3 | t    | f
 4 | t    | f
(4 rows)

-- a rebuild keeps the paths, whatever the table holds now
INSERT INTO pa SELECT i, jsonb_build_object('other', i), NULL FROM generate_series(3001, 6000) i;
REINDEX INDEX pa_auto;
NOTICE:  sift_col index "pa_auto": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key;
paths = doc.count int4, doc.meta.lang text, and 4 more
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_auto');
SELECT pa_option('pa_auto');
                                                   pa_option                                                   
---------------------------------------------------------------------------------------------------------------
 paths=auto: doc.count int4, doc.meta.lang text, doc.name text, doc.parts[]{cat text, score int4}, tail.t int4
(1 row)

-- the index still verifies after the table changed, and answers as the plain plan does
SELECT sift.col_verify('pa_auto', true);
 col_verify 
------------
 
(1 row)

SELECT n, pa_rows(q, true) = pa_rows(q, false) AS same FROM pa_q ORDER BY n;
 n | same 
---+------
 1 | t
 2 | t
 3 | t
 4 | t
(4 rows)

DROP INDEX pa_auto;
DELETE FROM pa WHERE id > 3000;
-- paths by hand are not touched; 'auto' by name is the same as none
CREATE INDEX pa_hand ON pa USING sift_col (id, doc) WITH (paths = 'doc.name text');
NOTICE:  sift_col index "pa_hand": 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('pa_hand');
SELECT pa_option('pa_hand');
      pa_option      
---------------------
 paths=doc.name text
(1 row)

CREATE INDEX pa_none ON pa USING sift_col (id, doc) WITH (paths = 'auto');
NOTICE:  sift_col index "pa_none": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key;
paths = doc.count int4, doc.meta.lang text, and 3 more
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_none');
SELECT pa_option('pa_none');
                                            pa_option                                             
--------------------------------------------------------------------------------------------------
 paths=auto: doc.count int4, doc.meta.lang text, doc.name text, doc.parts[]{cat text, score int4}
(1 row)

-- a map of keys that are all different, of one type: a wildcard
CREATE TABLE pa_few (id int, doc jsonb) WITH (autovacuum_enabled = off);
INSERT INTO pa_few SELECT i, jsonb_build_object('k' || i, i) FROM generate_series(1, 2000) i;
CREATE INDEX pa_few_col ON pa_few USING sift_col (id, doc);
NOTICE:  sift_col index "pa_few_col": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; paths = doc.* int4
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_few_col');
SELECT pa_option('pa_few_col');
       pa_option        
------------------------
 paths=auto: doc.* int4
(1 row)

-- a table without rows has no key that qualifies: the refusal of a jsonb column without paths stands
CREATE TABLE pa_empty (id int, doc jsonb);
CREATE INDEX pa_empty_col ON pa_empty USING sift_col (id, doc);
ERROR:  the jsonb column "doc" of a sift_col index needs paths
HINT:  Declare them with the paths option: WITH (paths = 'doc.* float4'), or REINDEX to read them from the documents.
SELECT pa_option('pa_empty_col');
 pa_option 
-----------
 not set
(1 row)

-- ALTER INDEX ... SET (paths = 'auto') or RESET (paths) asks for the documents to be read again at the next REINDEX (as after any change of paths the
-- index has no layout until then, so the REINDEX follows at once)
CREATE TABLE pa_re (id int, doc jsonb) WITH (autovacuum_enabled = off);
INSERT INTO pa_re SELECT i, jsonb_build_object('a', i % 7) FROM generate_series(1, 2000) i;
CREATE INDEX pa_re_col ON pa_re USING sift_col (id, doc);
NOTICE:  sift_col index "pa_re_col": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; paths = doc.a int4
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_re_col');
SELECT pa_option('pa_re_col');
       pa_option        
------------------------
 paths=auto: doc.a int4
(1 row)

DELETE FROM pa_re;
INSERT INTO pa_re SELECT i, jsonb_build_object('b', i % 11, 'label', 'l' || i % 3) FROM generate_series(1, 2000) i;
ALTER INDEX pa_re_col SET (paths = 'auto');
SELECT pa_option('pa_re_col');
 pa_option  
------------
 paths=auto
(1 row)

REINDEX INDEX pa_re_col;
NOTICE:  sift_col index "pa_re_col": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; paths = doc.b int4, doc.label text
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_re_col');
SELECT pa_option('pa_re_col');
               pa_option                
----------------------------------------
 paths=auto: doc.b int4, doc.label text
(1 row)

DELETE FROM pa_re;
INSERT INTO pa_re SELECT i, jsonb_build_object('c', i % 5) FROM generate_series(1, 2000) i;
ALTER INDEX pa_re_col RESET (paths);
REINDEX INDEX pa_re_col;
NOTICE:  sift_col index "pa_re_col": 1 automatic choice (paths), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; paths = doc.c int4
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('pa_re_col');
SELECT pa_option('pa_re_col');
       pa_option        
------------------------
 paths=auto: doc.c int4
(1 row)

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

DROP TABLE pa, pa_q, pa_few, pa_empty, pa_re;
DROP FUNCTION pa_option(text), pa_rows(text, bool), pa_pushed(text);
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;