#!/usr/bin/env bash
#
# Reproducer for the 4-byte TOAST chunk_id search stall.
#
# Each out-of-line (TOAST) value is named by a 4-byte chunk_id taken from the
# cluster-wide OID counter and made unique in the TOAST table's index.  On a
# table holding a long, gap-free run of used chunk_ids, the counter eventually
# hands out ids that are already taken, and each toasted INSERT must step over
# the used ids one by one until it finds a free slot.  That linear search is the
# stall.  This script builds a dense run of chunk_ids, then uses pg_resetwal -o
# to point the counter back K ids before the end of the run, so the next toasted
# INSERT has to step over exactly K used ids.  Sweeping K shows the cost grow.
#
# With an 8-byte TOAST value id (a build that supports the toast_value_type=oid8
# reloption), the counter never comes back onto used ids, so there is no search
# and no stall; run the script with VALUE_TYPE=oid8 to compare.
#
# Requires initdb, pg_ctl, pg_resetwal, pg_controldata and psql on PATH.
# Creates a fresh throwaway cluster under PGDATA; nothing else is touched.
#
#   ./toast_oid.sh                 # 4-byte oid (default)
#   VALUE_TYPE=oid8 ./toast_oid.sh # 8-byte oid8 (needs a build with support)
#
# Scale the stall with ROWS: each row externalizes 10 columns, so the run holds
# about 10*ROWS chunk_ids.  ROWS=2000000 gives ~20M ids (a few GB) and a top
# step of tens of seconds; raise it (and the K list) for minute-scale stalls.

set -uo pipefail

PGDATA=${PGDATA:-$PWD/toast_oid_data}   # throwaway data dir
PGPORT=${PGPORT:-5678}
ROWS=${ROWS:-2000000}                        # 10 externalized cols per row
VALUE_TYPE=${VALUE_TYPE:-oid}                # oid (4-byte) or oid8 (8-byte)
WORKERS=${WORKERS:-8}                         # parallel load sessions
LOG=$PGDATA/server.log
PSQL="psql -p $PGPORT -d postgres -v ON_ERROR_STOP=1"
pgctl(){ pg_ctl -D "$PGDATA" -l "$LOG" -w -t 3600 "$@"; }

# a ~10 KB value: big enough to always be stored out of line (one chunk_id)
BIGVAL="repeat(md5(random()::text),300)"

echo "== init throwaway cluster in $PGDATA (VALUE_TYPE=$VALUE_TYPE) =="
pg_ctl -D "$PGDATA" -m immediate stop >/dev/null 2>&1 || true
rm -rf "$PGDATA"
initdb -D "$PGDATA" >/dev/null
cat >> "$PGDATA/postgresql.conf" <<EOF
port = $PGPORT
shared_buffers = 1GB
maintenance_work_mem = 1GB
autovacuum = off
synchronous_commit = off
EOF
pgctl start

# only emit the reloption when asking for oid8, so this stays parseable on
# builds that do not know toast_value_type at all.
VT_OPT=""
[ "$VALUE_TYPE" = oid8 ] && VT_OPT=", toast_value_type = oid8"

echo "== create table and load $ROWS rows (~$((10*ROWS/1000000))M chunk_ids) =="
$PSQL -q <<SQL
CREATE UNLOGGED TABLE toast_eat (
  c01 text, c02 text, c03 text, c04 text, c05 text,
  c06 text, c07 text, c08 text, c09 text, c10 text
) WITH (toast_tuple_target = 128, autovacuum_enabled = false${VT_OPT});
DO \$\$ BEGIN
  FOR i IN 1..10 LOOP
    EXECUTE format('ALTER TABLE toast_eat ALTER COLUMN c%s SET STORAGE EXTERNAL',
                   lpad(i::text, 2, '0'));
  END LOOP;
END \$\$;
SQL

TOAST=$($PSQL -tA -c "SELECT reltoastrelid::regclass FROM pg_class WHERE relname='toast_eat';")
echo "   toast table = $TOAST, chunk_id type = $($PSQL -tA -c "SELECT atttypid::regtype FROM pg_attribute WHERE attrelid='$TOAST'::regclass AND attnum=1;")"

VALS="repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7),repeat(md5(random()::text),7)"
PER=$(( ROWS / WORKERS ))
pids=()
for w in $(seq 1 "$WORKERS"); do
  $PSQL -q -c "INSERT INTO toast_eat SELECT $VALS FROM generate_series(1,$PER);" &
  pids+=($!)
done
for p in "${pids[@]}"; do wait "$p"; done
$PSQL -q -c "VACUUM (FREEZE) toast_eat;"

read LO HI < <($PSQL -tA -F' ' -c "SELECT min(chunk_id), max(chunk_id) FROM $TOAST;")
echo "   run: lo=$LO hi=$HI len=$(( HI - LO + 1 ))"

insert_big(){
  psql -p "$PGPORT" -d postgres -q \
    -c "SET statement_timeout='1800s';" -c "\timing on" \
    -c "INSERT INTO toast_eat(c01) VALUES ($BIGVAL);" 2>&1
}

echo
echo "== baseline: counter in fresh space, one toasted INSERT x3 =="
for i in 1 2 3; do
  echo "   $(insert_big | grep -oE 'Time: [0-9.]+ ms')"
done

echo
echo "== staircase: rewind counter to (max - K + 1) so one INSERT steps over K ids =="
echo "   K            insert            retries        outcome"
for K in 100000 1000000 5000000 10000000 20000000; do
  [ "$K" -gt "$(( HI - LO + 1 ))" ] && continue
  CURMAX=$($PSQL -tA -c "SELECT max(chunk_id) FROM $TOAST;")
  OFF=$(( CURMAX - K + 1 ))
  pgctl stop -m fast >/dev/null
  pg_resetwal -o "$OFF" -D "$PGDATA" >/dev/null
  GOT=$(pg_controldata "$PGDATA" | grep -oE 'NextOID:[[:space:]]+[0-9]+' | grep -oE '[0-9]+')
  pgctl start >/dev/null
  MARK=$(wc -l < "$LOG")
  OUT=$(insert_big)
  T=$(echo "$OUT" | grep -oE 'Time: [0-9.]+ ms')
  R=$(tail -n +$((MARK+1)) "$LOG" | grep -oE 'after [0-9]+ retries' | tail -1)
  echo "$OUT" | grep -qi ERROR && WHY="duplicate-key (no search)" || WHY="ok"
  printf "   %-12s %-17s %-14s %s\n" "$K" "${T:-n/a}" "${R:-none}" "$WHY"
done

echo
echo "== log lines (empty on the 8-byte build: no search happens) =="
grep -c "still searching for an unused OID" "$LOG" 2>/dev/null | sed 's/^/   still searching: /'
grep -E "new OID has been assigned" "$LOG" 2>/dev/null | tail -3

pgctl stop -m fast >/dev/null || true
echo
echo "== done.  remove the throwaway cluster with: rm -rf $PGDATA =="