-- The group table of a scan that cannot spill is held in memory: the planner leaves it to the normal plan when the estimate passes
-- group_nospill_factor times hash memory, and the scan ends with an error when the table it holds passes the same bytes (the estimate
-- of a plan made on old statistics, or of a prepared plan, is not the table). The factor is lowered so that a few thousand groups reach it.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET client_min_messages = warning;

CREATE TABLE events (id int, k1 int, val int);
INSERT INTO events SELECT i, (i * 7919) % 20000, i % 1000 FROM generate_series(1, 40000) i;
CREATE INDEX events_col ON events USING sift_col (id, k1, val);
VACUUM (FREEZE, ANALYZE) events;

SET sift.enable_agg = off;
CREATE TEMP TABLE want AS SELECT k1, bit_or(val) AS b FROM events GROUP BY k1;
RESET sift.enable_agg;

-- a generic aggregate cannot spill; the generic plan is made at the default factor, the table is held under a factor of 0
SET plan_cache_mode = force_generic_plan;
PREPARE q AS SELECT k1, bit_or(val) AS b FROM events GROUP BY k1;
SELECT arm, used, slug FROM sift.explain_why('SELECT k1, bit_or(val) FROM events GROUP BY k1');
CREATE TEMP TABLE got AS EXECUTE q;
SELECT count(*) AS groups, (SELECT count(*) FROM (TABLE want EXCEPT TABLE got) d) AS differ FROM got;
DROP TABLE got;

SET sift.test_limits = 'group_nospill_factor=0';
DO $$
DECLARE msg text; hint text;
BEGIN
	EXECUTE 'EXECUTE q';
	RAISE NOTICE 'no error';
EXCEPTION WHEN program_limit_exceeded THEN
	GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT, hint = PG_EXCEPTION_HINT;
	RAISE WARNING 'SQLSTATE % names the table: % hint: %', SQLSTATE, msg ~ 'group table of the aggregate holds [0-9]+ kB and cannot spill', hint;
END $$;
RESET sift.test_limits;
DEALLOCATE q;
RESET plan_cache_mode;

DROP TABLE events;
DROP EXTENSION pg_sift;