#!/usr/bin/env bash
# Divergent partitions that already exist when the patch arrives.
# Usage: bash legacy.sh.txt OLD18=<bindir> NEW18=<bindir> [UP_LABEL=<bindir> ...]
#   OLD18  unpatched 18.x: creates the divergent partitions
#   NEW18  18.x with the REL_18 patch: same data directory, minor update
#   UP_*   newer major versions with the patch: pg_upgrade --check targets
# Output goes to stdout; every cluster lives in a temp dir removed at exit.
set -uo pipefail
old=${1#*=}; new18=${2#*=}; shift 2
tmp=$(mktemp -d "${TMPDIR:-/tmp}/vgdl.XXXXXX")
trap 'for d in "$tmp"/*/; do [ -f "$d/postmaster.pid" ] && \
  "$new18/pg_ctl" -D "$d" -m immediate -w stop >/dev/null 2>&1; done; rm -rf "$tmp"' EXIT
port=${PORT_BASE:-61600}
cat >"$tmp/legacy.sql" <<'EOF'
-- Divergent partitions created on an unpatched server (the state that
-- already exists in deployed databases).
create table p (a int,
  v int generated always as (nullif(a, 0)) virtual not null)
  partition by list (a);
create table p1 partition of p
  (v generated always as (a + 1) virtual) for values in (0);
insert into p values (0);
create table c (a int,
  v int generated always as (-a) virtual check (v > 0))
  partition by list (a);
create table c1 partition of c
  (v generated always as (a) virtual) for values in (5);
insert into c values (5);
-- divergent, but no constraint is violated through the parent
create table b (a int, v int generated always as (a * 2) virtual)
  partition by list (a);
create table b1 partition of b
  (v generated always as (a * 3) virtual) for values in (1);
insert into b values (1);
-- divergent, with a CHECK constraint that does not involve v
create table k (a int check (a > 0), v int generated always as (a * 2) virtual)
  partition by list (a);
create table k1 partition of k
  (v generated always as (a * 3) virtual) for values in (1);
insert into k values (1);
EOF
cat >"$tmp/legacy-check.sql" <<'EOF'
\pset null '(null)'
select current_setting('server_version') as server_version;
-- mismatches as the patch's query reports them
select c.oid::pg_catalog.regclass as partition,
       a.attname as column,
       pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) as partition_expr,
       pg_catalog.pg_get_expr(dp.adbin, dp.adrelid) as parent_expr
from pg_catalog.pg_class c
     join pg_catalog.pg_inherits i on i.inhrelid = c.oid
     join pg_catalog.pg_attribute a on a.attrelid = c.oid
     join pg_catalog.pg_attribute pa on pa.attrelid = i.inhparent
          and pa.attname = a.attname
     join pg_catalog.pg_attrdef dc on dc.adrelid = a.attrelid
          and dc.adnum = a.attnum
     join pg_catalog.pg_attrdef dp on dp.adrelid = pa.attrelid
          and dp.adnum = pa.attnum
where c.relispartition and a.attgenerated = 'v'
      and pg_catalog.pg_get_expr(dc.adbin, dc.adrelid) <>
          pg_catalog.pg_get_expr(dp.adbin, dp.adrelid)
order by 1, 2;
-- the wrong result: correct answer is 1 for both
set constraint_exclusion = partition;
select 'ce=partition' as setting, (select count(*) from p where v is null) as p_null,
       (select count(*) from c where v < 0) as c_neg;
set constraint_exclusion = on;
select 'ce=on' as setting, (select count(*) from p where v is null) as p_null,
       (select count(*) from c where v < 0) as c_neg;
reset constraint_exclusion;
EOF
start() { "$1/pg_ctl" -D "$2" -l "$2.log" -w \
            -o "-p $port -k $tmp -c listen_addresses=''" start >/dev/null; }
stop() { "$1/pg_ctl" -D "$2" -m fast -w stop >/dev/null; }
sql() { local bin=$1 db=$2; shift 2
        "$bin/psql" -X -h "$tmp" -p "$port" -U postgres -d "$db" "$@" 2>&1; }
say() { printf '\n### %s\n' "$*"; }

say "1. unpatched $("$old/postgres" --version): create divergent partitions"
"$old/initdb" -D "$tmp/old" -U postgres -A trust >/dev/null
start "$old" "$tmp/old"
"$old/createdb" -h "$tmp" -p "$port" -U postgres legacy
sql "$old" legacy -q -f "$tmp/legacy.sql"
sql "$old" legacy -f "$tmp/legacy-check.sql"

say "2. pg_dump of the unpatched server: how the partitions are emitted"
"$old/pg_dump" -h "$tmp" -p "$port" -U postgres legacy >"$tmp/dump-old.sql"
grep -nE 'CREATE TABLE|ATTACH PARTITION|GENERATED ALWAYS' "$tmp/dump-old.sql" \
  | sed 's/^/  /'
stop "$old" "$tmp/old"

for spec in "$@"; do
  label=${spec%%=*}; bin=${spec#*=}
  say "3. pg_upgrade --check, old cluster as created in 1 -> $label"
  "$bin/initdb" -D "$tmp/new-$label" -U postgres -A trust >/dev/null
  ( cd "$tmp" && "$bin/pg_upgrade" --check -U postgres -b "$old" -B "$bin" \
      -d "$tmp/old" -D "$tmp/new-$label" -p $((port + 1)) -P $((port + 2)) \
      -s "$tmp" ) 2>&1 | grep -vE '^\s*$' | sed 's/^/  /'
  f=$(find "$tmp/new-$label" -name partition_virtual_generated_columns.txt | head -1)
  [ -n "$f" ] && { echo "  report file:"; sed 's/^/    /' "$f"; }
done

say "4. same data directory started with $("$new18/postgres" --version) + patch"
start "$new18" "$tmp/old"
sql "$new18" legacy -f "$tmp/legacy-check.sql"

say "5. restore the unpatched dump (2) into the patched server"
"$new18/createdb" -h "$tmp" -p "$port" -U postgres restored_old
sql "$new18" restored_old -q -f "$tmp/dump-old.sql" | grep -E 'ERROR|DETAIL' \
  | sed 's/^/  /'
sql "$new18" restored_old -f "$tmp/legacy-check.sql"

say "6. dump with the patched pg_dump, restore into the patched server"
"$new18/pg_dump" -h "$tmp" -p "$port" -U postgres legacy >"$tmp/dump-new.sql"
"$new18/createdb" -h "$tmp" -p "$port" -U postgres restored_new
sql "$new18" restored_new -q -f "$tmp/dump-new.sql" | grep -E 'ERROR|DETAIL' \
  | sed 's/^/  /'

say "7. remedy suggested by the patch: SET EXPRESSION on the parent"
for t in "b:(a * 2)" "p:(nullif(a, 0))" "c:(-a)" "k:(a * 2)"; do
  echo "  $t"
  sql "$new18" legacy -c "alter table ${t%%:*} alter column v set expression as ${t#*:}" \
    | sed 's/^/    /'
done
say "7b. after deleting the rows that violate the parent's constraints"
sql "$new18" legacy -c 'delete from p1' -c 'delete from c1' | sed 's/^/  /'
for t in "p:(nullif(a, 0))" "c:(-a)"; do
  echo "  $t"
  sql "$new18" legacy -c "alter table ${t%%:*} alter column v set expression as ${t#*:}" \
    | sed 's/^/    /'
done
say "7c. drop the CHECK, SET EXPRESSION, add the CHECK back"
sql "$new18" legacy -c 'begin' \
  -c 'alter table c drop constraint c_v_check' \
  -c 'alter table c alter column v set expression as (-a)' \
  -c 'alter table c add constraint c_v_check check (v > 0)' \
  -c 'alter table k drop constraint k_a_check' \
  -c 'alter table k alter column v set expression as (a * 2)' \
  -c 'alter table k add constraint k_a_check check (a > 0)' \
  -c 'commit' | sed 's/^/  /'
sql "$new18" legacy -f "$tmp/legacy-check.sql"
stop "$new18" "$tmp/old"

for spec in "$@"; do
  label=${spec%%=*}; bin=${spec#*=}
  say "8. pg_upgrade --check after 7c -> $label"
  rm -rf "$tmp/new-$label"
  "$bin/initdb" -D "$tmp/new-$label" -U postgres -A trust >/dev/null
  ( cd "$tmp" && "$bin/pg_upgrade" --check -U postgres -b "$new18" -B "$bin" \
      -d "$tmp/old" -D "$tmp/new-$label" -p $((port + 1)) -P $((port + 2)) \
      -s "$tmp" ) 2>&1 | grep -iE 'virtual|clusters are compatible|fatal' \
      | sed 's/^/  /'
done