#!/usr/bin/env bash
# The VACUUM that churn and scans ask for (sift.col_auto_merge, off by default): after churn spread over a table every heap page is not all-visible, so a
# scan of a sift_col index checks the rows in the heap one by one. A backend that inserts a row version, or whose scan checked many rows, looks at the
# statistics of the table and starts a worker that vacuums it; the next scan checks almost none. Here: off, the scan checks the rows and nothing is
# vacuumed; on, the table is vacuumed and the next scan is cheap and answers as the heap does; a change of a few rows (under 50) asks for nothing; a table
# with autovacuum_enabled = false is left alone, and vacuumed once it is not; CREATE INDEX on such a table says so. CLUSTER and VACUUM FULL rebuild the heap
# and the index and leave the visibility map empty (PostgreSQL's), and the first scan that checks the rows in the heap has the table vacuumed.
. "$(dirname "$0")/lib.bash"

plan 14
# The least time between two VACUUMs of a table is five naptimes before PostgreSQL 18 (from 18, ten times the average one): the sleeps before a rebuild
# are longer than that (1.5 s) and the naptime is not shorter
node_init av 22 "autovacuum = off
sift.maintenance_naptime = 300"
node_start av || bail "start failed"
sql av "CREATE EXTENSION pg_sift" "CREATE TABLE facts (id int, key int, kind text, amount numeric(10,2), pad text)" \
	"INSERT INTO facts SELECT g, g % 1000, 'cat_' || (g % 8), g % 997, repeat('x', 150) FROM generate_series(1, 30000) g" \
	"CREATE INDEX facts_idx ON facts USING sift_col (id, key, kind, amount)" "VACUUM facts" >/dev/null || bail "setup failed"

# when the table was last vacuumed (the statistics are written when the VACUUM ends)
vacuumed() { sql av "SELECT coalesce(extract(epoch FROM last_vacuum)::text, '-') FROM pg_stat_user_tables WHERE relname = 'facts'"; }
# the scan the churn slows down; the rows it checked in the heap
SCAN="SELECT kind, count(*), sum(amount) FROM facts GROUP BY kind"
# (statements to run first, in the same session: the rows checked right after a rebuild, before the worker it asked has vacuumed)
vetted() { sql av "$@" "SET max_parallel_workers_per_gather = 0" "EXPLAIN (ANALYZE, COSTS OFF) $SCAN" | sed -n 's/.* \([0-9][0-9]*\) vetted by heap.*/\1/p' | grep . || echo 0; }
# until a scan checks few rows: a VACUUM leaves a page not all-visible when it could not take its cleanup lock or the commit of its rows was not flushed yet
# (the hint bits wait for it), and the scan that finds them asks for the next, a few naptimes later
cheap() { for _ in $(seq 150); do [[ $(vetted) -lt 2000 ]] && return 0; sleep 0.2; done; return 1; }
groups() { sql av "SET sift.enable_agg = $1" "SET max_parallel_workers_per_gather = 0" "SELECT string_agg(x::text, ',' ORDER BY x::text) FROM ($SCAN) x" | tail -1; }
# until the table is vacuumed after $1 (what vacuumed printed then), asking with a scan every so often
await_vacuum() {
	for _ in $(seq 100); do
		vetted >/dev/null
		[[ $(vacuumed) == "$1" ]] || return 0
		sleep 0.2
	done
	return 1
}
# stays as it was for 0.75 s (two and a half naptimes) although scans ask
stays() {
	for _ in $(seq 3); do vetted >/dev/null; sleep 0.25; done
	[[ $(vacuumed) == "$1" ]]
}
churn() { sql av "UPDATE facts SET amount = amount + 1 WHERE id % 20 = $1" >/dev/null; }

# 1. off (the default)
T0=$(vacuumed)
churn 1
check "off: the scan checks the rows in the heap (the churn is spread over every page)" test "$(vetted)" -gt 20000
check "off: and the table is not vacuumed" stays "$T0"

# 2. on
sql av "ALTER DATABASE postgres SET sift.col_auto_merge = on" >/dev/null
check "on: the table is vacuumed after a scan that checked its rows" await_vacuum "$T0"
check "on: the next scan checks few rows" cheap
check "on: and answers as the heap does" test "$(groups on)" == "$(groups off)"

# 3. a few rows changed: fewer than the 50 under which a table is clean
sleep 0.5
T1=$(vacuumed)
sql av "UPDATE facts SET amount = amount + 1 WHERE id % 3000 = 1" >/dev/null
check "a change of ten rows (under 50): not vacuumed" stays "$T1"

# 4. autovacuum_enabled = false is left alone, and vacuumed once it is not
sql av "ALTER TABLE facts SET (autovacuum_enabled = false)" >/dev/null
churn 2
check "autovacuum_enabled = false: not vacuumed" stays "$T1"
sql av "ALTER TABLE facts RESET (autovacuum_enabled)" >/dev/null
check "reset: vacuumed" await_vacuum "$T1"
check "CREATE INDEX on a table with autovacuum_enabled = false: a notice" test "$(sql av "SET client_min_messages = notice" "CREATE TABLE facts_off (id int) WITH (autovacuum_enabled = false)" \
	"CREATE INDEX facts_off_idx ON facts_off USING sift_col (id)" 2>&1 | grep -c 'does not vacuum')" == 1
# 5. CLUSTER and VACUUM FULL write a new heap (an empty visibility map) and rebuild the index: the scan checks every row in the heap until the worker, which that
# scan asked, has vacuumed the table. No VACUUM by hand.
repaired() { cheap && [[ $(groups on) == "$(groups off)" ]]; }
sql av "CREATE INDEX facts_key ON facts (key)" >/dev/null
# A rebuild asks for the VACUUM itself (the index it builds leaves pages out of the visibility map), so the worker can vacuum the table before the scan below looks:
# the rebuild is repeated, up to 12 times, until a scan finds the map empty (the sleep is longer than the least time between two VACUUMs of a table)
emptied() { local n=0 try; for try in $(seq 12); do sleep 2; n=$(vetted "$1"); [[ $n -gt 20000 ]] && break; done; echo "$n"; }
for rebuild in "CLUSTER facts USING facts_key" "VACUUM FULL facts"; do
	check "$rebuild: the visibility map is empty, the scan checks the rows in the heap" test "$(emptied "$rebuild")" -gt 20000
	check "$rebuild: the worker vacuums the table, the next scan checks few rows and answers as the heap does" repaired
done
check "the index verifies" test "$(sql av "SELECT sift.col_verify('facts_idx', true)" 2>&1 | grep -c ERROR)" == 0

finish