#!/bin/sh
# Measure extra autovacuum/autoanalyze runs caused by VACUUM/ANALYZE
# double-counting the backend's own pending table stats.
#
# Needs only psql and a scratch server started with autovacuum_naptime=1s,
# for example:
#
#   docker run -d --name pg -e POSTGRES_HOST_AUTH_METHOD=trust postgres:18 \
#     -c autovacuum_naptime=1s -c log_autovacuum_min_duration=0
#   docker cp pending-stats-autovacuum.sh pg:/tmp/
#   docker exec -u postgres pg sh /tmp/pending-stats-autovacuum.sh
#
# Connection settings come from the usual PG* environment variables. The
# script creates and drops the database pending_stats_test.
#
# Each scenario runs on NTAB tables, each in its own short session:
#
#   1. a warm-up statement, whose idle-time flush sets the backend's last
#      flush time, so the next changes stay pending (flushes happen at most
#      once per second);
#   2. the data change;
#   3. ANALYZE or VACUUM.
#
# "ctl" scenarios add SELECT pg_sleep(1.5) before step 3, which lets the
# pending stats flush first.  Expected counters after each cycle, if the
# stats were exact:
#
#   insert+analyze  mod_since_analyze 0      -> no autoanalyze
#   delete+vacuum   dead_tuples 0            -> no autovacuum
#   insert+vacuum   ins_since_vacuum 0       -> no autovacuum
#
# Autovacuum thresholds use pg_class.reltuples, which is correct; the
# counters above are what it compares them with.

set -eu

NTAB=${NTAB:-20}
NROWS=${NROWS:-10000}
WAIT=${WAIT:-20}
DB=pending_stats_test

psql -X -q -v ON_ERROR_STOP=1 -d postgres <<EOF
DROP DATABASE IF EXISTS $DB;
CREATE DATABASE $DB;
EOF

P="psql -X -q -At -v ON_ERROR_STOP=1 -d $DB"

$P -c "SELECT 'server: ' || version()"
$P -c "SELECT 'autovacuum=' || current_setting('autovacuum') || ' naptime=' || current_setting('autovacuum_naptime')"
if [ "$($P -c "SELECT current_setting('autovacuum_naptime')")" != "1s" ]; then
    echo "warning: autovacuum_naptime is not 1s; raise WAIT accordingly" >&2
fi

SCENARIOS="ins_anl_bug ins_anl_ctl del_vac_bug del_vac_ctl ins_vac_bug ins_vac_ctl"

# Setup: delete+vacuum tables start with NROWS rows, the others empty.
# Every table gets VACUUM ANALYZE so all counters start at zero.
# Autovacuum stays disabled on the tables until the workload is done, so
# the counters can be read before it reacts.
for s in $SCENARIOS; do
    i=1
    while [ $i -le $NTAB ]; do
        $P -c "CREATE TABLE ${s}_$i (id int) WITH (autovacuum_enabled = false)"
        case $s in del_vac_*)
            $P -c "INSERT INTO ${s}_$i SELECT generate_series(1, $NROWS)";;
        esac
        i=$((i + 1))
    done
done
$P -c "CREATE TABLE warm (i int)"
$P -c "VACUUM ANALYZE"
sleep 3

$P <<EOF
CREATE TABLE baseline AS
SELECT relname, autovacuum_count, autoanalyze_count
  FROM pg_stat_user_tables;
EOF

# Workload.
for s in $SCENARIOS; do
    i=1
    while [ $i -le $NTAB ]; do
        t=${s}_$i
        case $s in
            ins_anl_*) change="INSERT INTO $t SELECT generate_series(1, $NROWS);"
                       maint="ANALYZE $t;";;
            del_vac_*) change="DELETE FROM $t WHERE id <= $NROWS / 2;"
                       maint="VACUUM $t;";;
            ins_vac_*) change="INSERT INTO $t SELECT generate_series(1, $NROWS);"
                       maint="VACUUM $t;";;
        esac
        case $s in
            *_ctl) pause="SELECT pg_sleep(1.5);";;
            *)     pause="";;
        esac
        printf '%s\n' "INSERT INTO warm VALUES (1);" "$change" "$pause" "$maint" \
            | $P >/dev/null
        i=$((i + 1))
    done
done

# Counters right after the workload.  All sessions have exited, so all
# pending stats are flushed.
$P -c "SELECT pg_sleep(0.5)" >/dev/null
$P <<'EOF'
\pset format aligned
\pset tuples_only off
\pset footer off
\echo
\echo Counters after the workload (averages per table):
SELECT split_part(relname, '_', 1) || '_' || split_part(relname, '_', 2) AS workload,
       split_part(relname, '_', 3) AS kind,
       round(avg(n_live_tup)) AS n_live_tup,
       round(avg(n_dead_tup)) AS n_dead_tup,
       round(avg(n_mod_since_analyze)) AS mod_since_analyze,
       round(avg(n_ins_since_vacuum)) AS ins_since_vacuum
  FROM pg_stat_user_tables
 WHERE relname ~ '_(bug|ctl)_'
 GROUP BY 1, 2 ORDER BY 1, 2 DESC;
EOF

for s in $SCENARIOS; do
    i=1
    while [ $i -le $NTAB ]; do
        $P -c "ALTER TABLE ${s}_$i RESET (autovacuum_enabled)"
        i=$((i + 1))
    done
done

echo
echo "Autovacuum enabled on the tables; waiting ${WAIT}s..."
sleep "$WAIT"

$P <<'EOF'
\pset format aligned
\pset tuples_only off
\pset footer off
\echo
\echo Autovacuum/autoanalyze runs after the workload (sum over all tables):
SELECT split_part(s.relname, '_', 1) || '_' || split_part(s.relname, '_', 2) AS workload,
       split_part(s.relname, '_', 3) AS kind,
       count(*) AS tables,
       sum(s.autovacuum_count - b.autovacuum_count) AS autovacuums,
       sum(s.autoanalyze_count - b.autoanalyze_count) AS autoanalyzes
  FROM pg_stat_user_tables s JOIN baseline b USING (relname)
 WHERE s.relname ~ '_(bug|ctl)_'
 GROUP BY 1, 2 ORDER BY 1, 2 DESC;
EOF

psql -X -q -d postgres -c "DROP DATABASE $DB"