#!/usr/bin/env bash
# Counts, not times, for the operations that CF 7365 touches or that pay for
# the new index (section 5 of our notes).  Single-user backend under perf
# stat (user-space instructions), as in fk-lookup-counts.sh.txt.
#
#   BINS="master=<bindir> v10=<bindir>"  CATS="master=master v10=idx"  WORK=<dir>
#
#   cat-setup <build> <mix> <K>   catalog with about K pg_constraint rows:
#                                 mix nn = 90% NOT NULL, 3% FK (David's mix)
#                                 mix fk = almost only FKs
#                                 plus the objects the operations below use
#   ops <build> <mix> <K> <run>   each operation, 50 times, in that catalog
#   write <build> <run>           cost of creating 100,000 NOT NULL
#                                 constraints (1,000 tables x 100 columns):
#                                 instructions, WAL bytes, sizes after
#   churn <build>                 create and drop those tables 5 times:
#                                 index size before/after, then VACUUM
set -euo pipefail
: "${WORK:?}" "${BINS:?}" "${CATS:?}"
mkdir -p "$WORK"
lookup() { local k=$1 kv; shift; for kv in $*; do [ "${kv%%=*}" = "$k" ] && { echo "${kv#*=}"; return; }; done
           echo "unknown build $k" >&2; exit 2; }
bin() { lookup "$1" $BINS; }
cat_of() { lookup "$1" $CATS; }
instr() { awk -F, '$3 ~ /^instructions/ {print $1}' "$1"; }
val() { sed -n "s/^[[:space:]]*[0-9]*: $2 = \"\([^\"]*\)\".*/\1/p" "$1" | tail -1; }
sb() { "$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none postgres; }
newdb() {
  rm -rf "$1"; "$2/initdb" -D "$1" -U postgres -A trust >/dev/null
  printf 'fsync = off\nautovacuum = off\nmax_locks_per_transaction = 1024\n' >>"$1/postgresql.conf"
}
# run <bin> <datadir> <out> <sql file>: instructions of the whole session
run() { perf stat -x, -e instructions:u -o "$3.perf" -- \
          "$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none postgres <"$4" >"$3.out" 2>&1
        local ne; ne=$(grep -c 'ERROR:' "$3.out" || true)
        [ "$ne" = 0 ] || { echo "errors=$ne"; grep -m3 'ERROR:' "$3.out"; exit 1; }
        instr "$3.perf"; }
rep() { local n=$1 i; shift; for i in $(seq "$n"); do printf "$@"; printf '\n\n'; done; }
size_sql="SELECT pg_relation_size('pg_constraint') AS heapb,
  coalesce((SELECT pg_relation_size(indexrelid) FROM pg_index WHERE indexrelid::regclass::text
            = 'pg_constraint_confrelid_index'), 0) AS idxb,
  (SELECT count(*) FROM pg_constraint) AS nrows;"

case ${1:?stage} in
cat-setup)
  C=${2:?}; MIX=${3:?}; K=${4:?}; B=$(bin "$C"); D=$WORK/cat-$(cat_of "$C")-$MIX-$K
  newdb "$D" "$B"
  {
    # the objects of the operations
    printf 'CREATE TABLE pk (id int PRIMARY KEY);\n\n'
    printf 'CREATE TABLE pp (id int PRIMARY KEY) PARTITION BY RANGE (id);\n\n'
    printf 'CREATE TABLE pp_0 PARTITION OF pp FOR VALUES FROM (0) TO (10);\n\n'
    printf 'CREATE TABLE ppref (p int REFERENCES pp);\n\n'
    printf 'CREATE TABLE ul (id int PRIMARY KEY);\n\nCREATE TABLE ulref (u int REFERENCES ul);\n\n'
    printf 'CREATE TABLE fkc (id int);\n\n'
    # bulk: 100-constraint tables up to K rows
    t=$(( K / 100 ))
    for i in $(seq "$t"); do
      if [ "$MIX" = nn ] && [ $(( i % 30 )) -ne 0 ]; then
        printf 'CREATE TABLE b%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT NULL' 1 100 | sed 's/,$//')"
      else
        printf 'CREATE TABLE m%s (id int PRIMARY KEY);\n\n' "$i"
        printf 'CREATE TABLE b%s (%s);\n\n' "$i" "$(seq -s, -f "c%g int REFERENCES m$i" 1 100 | sed 's/,$//')"
      fi
    done; } | sb "$B" "$D" >/dev/null
  printf '%s\n' "$size_sql" | sb "$B" "$D" >"$D.size" 2>/dev/null
  echo "cat-setup $(cat_of "$C") mix=$MIX K=$K rows=$(val "$D.size" nrows)" \
       "fk=$(sb "$B" "$D" <<<"SELECT count(*) AS f FROM pg_constraint WHERE contype = 'f';" 2>/dev/null | sed -n 's/.*f = "\([0-9]*\)".*/\1/p')" \
       "pg_constraint_bytes=$(val "$D.size" heapb) confrelid_index_bytes=$(val "$D.size" idxb)"
  ;;
ops)
  S=${2:?}; MIX=${3:?}; K=${4:?}; R=${5:?}; B=$(bin "$S"); D=$WORK/cat-$(cat_of "$S")-$MIX-$K
  O=$WORK/ops-$S-$MIX-$K-$R; mkdir -p "$O"
  N=50
  # attach: CREATE + ATTACH (CloneFkReferenced) + DETACH (GetParentedForeignKeyRefs) + DROP
  rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nALTER TABLE pp ATTACH PARTITION pp_x FOR VALUES FROM (10) TO (20);\n\nALTER TABLE pp DETACH PARTITION pp_x;\n\nDROP TABLE pp_x;' >"$O/attach.sql"
  rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nDROP TABLE pp_x;' >"$O/attach0.sql"
  # partof: CREATE TABLE ... PARTITION OF a referenced table (CloneFkReferenced),
  #         DETACH, DROP
  rep $N 'CREATE TABLE pp_x PARTITION OF pp FOR VALUES FROM (10) TO (20);\n\nALTER TABLE pp DETACH PARTITION pp_x;\n\nDROP TABLE pp_x;' >"$O/partof.sql"
  # detachc: ATTACH + DETACH CONCURRENTLY
  rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nALTER TABLE pp ATTACH PARTITION pp_x FOR VALUES FROM (10) TO (20);\n\nALTER TABLE pp DETACH PARTITION pp_x CONCURRENTLY;\n\nDROP TABLE pp_x;' >"$O/detachc.sql"
  # unlogged: SET UNLOGGED (confrelid lookup) + SET LOGGED, on a referenced table
  #           (ul is referenced by ulref, so both must be switched together)
  rep $N 'ALTER TABLE ulref SET UNLOGGED;\n\nALTER TABLE ul SET UNLOGGED;\n\nALTER TABLE ul SET LOGGED;\n\nALTER TABLE ulref SET LOGGED;' >"$O/unlogged.sql"
  # fkadd: ADD + DROP of a FOREIGN KEY constraint
  rep $N 'ALTER TABLE fkc ADD CONSTRAINT fkc_f FOREIGN KEY (id) REFERENCES pk;\n\nALTER TABLE fkc DROP CONSTRAINT fkc_f;' >"$O/fkadd.sql"
  # dropfk: CREATE + DROP of a table with 10 FKs
  rep $N 'CREATE TABLE dfk (%s);\n\nDROP TABLE dfk;' "$(seq -s, -f 'c%g int REFERENCES pk' 1 10 | sed 's/,$//')" >"$O/dropfk.sql"
  line="ops $S mix=$MIX K=$K run=$R x$N:"
  for op in attach attach0 partof detachc unlogged fkadd dropfk; do
    line+=" $op=$(run "$B" "$D" "$O/$op" "$O/$op.sql")"
  done
  echo "$line"
  ;;
write)
  S=${2:?}; R=${3:?}; B=$(bin "$S"); D=$WORK/write-$S-$R; O=$WORK/wr-$S-$R
  newdb "$D" "$B"
  { printf 'SELECT pg_current_wal_insert_lsn() AS l0;\n\n'
    for i in $(seq 1000); do
      printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT NULL' 1 100 | sed 's/,$//')"
    done
    printf 'SELECT pg_current_wal_insert_lsn() AS l1;\n\n%s\n\n' "$size_sql"; } >"$O.sql"
  { for i in $(seq 1000); do
      printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int' 1 100 | sed 's/,$//')"
    done; } >"$O-ctl.sql"
  i1=$(run "$B" "$D" "$O" "$O.sql")
  wal=$(sb "$B" "$D" <<<"SELECT pg_wal_lsn_diff('$(val "$O.out" l1)', '$(val "$O.out" l0)')::bigint AS w;" 2>/dev/null | sed -n 's/.*w = "\([0-9]*\)".*/\1/p')
  newdb "$D" "$B"
  i0=$(run "$B" "$D" "$O-ctl" "$O-ctl.sql")
  echo "write $S run=$R 1000 tables x 100 NOT NULL: instructions=$i1 control(nullable)=$i0" \
       "per constraint=$(( (i1 - i0) / 100000 )) wal_bytes=$wal pg_constraint_rows=$(val "$O.out" nrows)" \
       "pg_constraint_bytes=$(val "$O.out" heapb) confrelid_index_bytes=$(val "$O.out" idxb)"
  rm -rf "$D"
  ;;
churn)
  S=${2:?}; B=$(bin "$S"); D=$WORK/churn-$S; O=$WORK/ch-$S
  newdb "$D" "$B"
  mk() { for i in $(seq 1000); do
           printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT NULL' 1 100 | sed 's/,$//')"; done; }
  dr() { for i in $(seq 1000); do printf 'DROP TABLE nn%s;\n\n' "$i"; done; }
  mk | sb "$B" "$D" >/dev/null
  printf '%s\n' "$size_sql" | sb "$B" "$D" >"$O.0" 2>/dev/null
  for c in 1 2 3 4 5; do dr | sb "$B" "$D" >/dev/null; mk | sb "$B" "$D" >/dev/null; done
  printf '%s\n' "$size_sql" | sb "$B" "$D" >"$O.5" 2>/dev/null
  printf 'VACUUM pg_constraint;\n\n%s\n' "$size_sql" | sb "$B" "$D" >"$O.v" 2>/dev/null
  echo "churn $S 100,000 NOT NULL created, then 5 x (drop all, create all):" \
       "start heap=$(val "$O.0" heapb) idx=$(val "$O.0" idxb);" \
       "after heap=$(val "$O.5" heapb) idx=$(val "$O.5" idxb);" \
       "after VACUUM heap=$(val "$O.v" heapb) idx=$(val "$O.v" idxb)"
  ;;
*) echo "stage?"; exit 2;;
esac