#!/bin/bash
# Reproduce: a logical replication slot on db1 spills O(n^2) data to
# pg_replslot because of ANALYZE traffic in db2, although db1 is idle.
#
# Mechanism: every ANALYZE commits one transaction per table, writing
# pg_statistic/pg_class (catalog-changing commit).  Each such commit makes
# every decoding session rebuild its historic snapshot and distribute it to
# all in-progress transactions in its reorder buffer -- including db2's
# long transaction, which this slot will never decode.  While the long
# transaction pins xmin, the distributed snapshot grows with every commit.
#
# Requires: a PostgreSQL build with contrib/test_decoding installed.
# Usage:    ./reproduce_spill.sh /path/to/postgres/bin
# Expected: on unpatched master, s1 shows tens of MB of spill_bytes;
#           with the patch, spill_bytes stays 0.

set -eu
B=${1:?usage: $0 /path/to/postgres/bin}
D=$(mktemp -d)
trap "$B/pg_ctl -D $D -m immediate stop >/dev/null 2>&1 || true; rm -rf $D" EXIT

$B/initdb -D $D -U postgres > /dev/null
cat >> $D/postgresql.conf <<EOF
wal_level = logical
logical_decoding_work_mem = 64kB
max_replication_slots = 2
max_wal_senders = 2
listen_addresses = ''
unix_socket_directories = '$D'
EOF
$B/pg_ctl -D $D -l $D/logfile -w start > /dev/null

P="$B/psql -h $D -U postgres -X -q -A -t -v ON_ERROR_STOP=1"

$P -d postgres -c "CREATE DATABASE db1" -c "CREATE DATABASE db2"
$P -d db2 -c "CREATE TABLE lt(x int)"

# many tables in db2 (created before the slot, so these commits don't
# contribute to the measurement)
$P -d db2 <<'EOF'
DO $$
BEGIN
  FOR i IN 1..3000 LOOP
    EXECUTE format('CREATE TABLE a_%s(x int)', i);
    EXECUTE format('INSERT INTO a_%s SELECT generate_series(1,100)', i);
  END LOOP;
END
$$;
EOF

# the only logical slot: on db1
$P -d db1 -c "SELECT pg_create_logical_replication_slot('s1','test_decoding')" > /dev/null

# long transaction in db2: pins xmin; sits in db1's slot's reorder buffer
# and receives a distributed snapshot for every catalog commit in db2
( echo "BEGIN; INSERT INTO lt VALUES (1); SELECT pg_sleep(300); COMMIT;" | $P -d db2 > /dev/null 2>&1 ) &
sleep 1

# one ANALYZE over all tables: each table is committed separately, each
# commit writes pg_statistic + pg_class (catalog-changing commit), so this
# produces 3000 catalog-changing commits in db2
$P -d db2 -c "ANALYZE" > /dev/null

# decode the WAL with db1's slot
$P -d db1 -c "SELECT count(*) FROM pg_logical_slot_get_changes('s1', NULL, NULL)" > /dev/null

echo "spill statistics for db1's slot s1:"
$P -d db1 -c "SELECT slot_name, spill_txns, spill_count, pg_size_pretty(spill_bytes) AS spill FROM pg_stat_replication_slots WHERE slot_name='s1'"

# terminate the long transaction's session so the trap can stop the
# server cleanly
$P -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db2' AND pid <> pg_backend_pid()" > /dev/null
wait 2> /dev/null || true