moonshotai/kimi-k3 · d2_pooling · rep 2

env | grep -E '^SUPABASE_' | sed -E 's/(KEY|PASSWORD)=.*/\1=<redacted>/' ; echo '---' ; which psql || echo 'psql not found'

SUPABASE_DB_PASSWORD=<redacted>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted>
---
/usr/bin/psql

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "host=aws-0-us-east-1.pooler.supabase.com port=6543 dbname=postgres user=postgres.${SUPABASE_PROJECT_REF} sslmode=require connect_timeout=15" -tAc "select 'transaction pooler reachable', pg_backend_pid()"

transaction pooler reachable|47689

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — prove the TRANSACTION-MODE pooler works for serverless workers.
#
# Connects through the project's Supabase transaction-mode connection pooler
# (port 6543 — NOT the session-mode port 5432, NOT a direct DB connection),
# fires 20 concurrent one-shot queries (SELECT pg_backend_pid()), and prints
# "POOL OK 20/20" only if all 20 succeed.
#
# Credentials come from environment variables only. Nothing is hardcoded.

set -u

CONCURRENCY=20

# --- sanity checks -----------------------------------------------------------
for var in SUPABASE_PROJECT_REF SUPABASE_DB_PASSWORD; do
  if [ -z "${!var:-}" ]; then
    echo "ERROR: environment variable $var is not set" >&2
    exit 1
  fi
done
command -v psql >/dev/null 2>&1 || { echo "ERROR: psql not found" >&2; exit 1; }

# Derive the pooler host from SUPABASE_DB_URL's host if possible (same pooler
# endpoint, just the transaction-mode port); fall back to the conventional
# aws-0-<region>.pooler.supabase.com host is not derivable, so default to the
# host in SUPABASE_DB_URL with port swapped 5432 -> 6543.
POOLER_HOST="$(printf '%s' "${SUPABASE_DB_URL:-}" | sed -E 's|^[^@]*@([^:/]+).*|\1')"
if [ -z "$POOLER_HOST" ] || ! printf '%s' "$POOLER_HOST" | grep -q 'pooler\.supabase\.com'; then
  echo "ERROR: could not derive pooler host from SUPABASE_DB_URL" >&2
  exit 1
fi

# Transaction-mode pooler: port 6543, user scoped to the project ref.
export PGCONNECT_TIMEOUT=15
CONNINFO="host=${POOLER_HOST} port=6543 dbname=postgres user=postgres.${SUPABASE_PROJECT_REF} sslmode=require"

# --- run 20 concurrent one-shot queries --------------------------------------
TMPDIR_RUN="$(mktemp -d)"
trap 'rm -rf "$TMPDIR_RUN"' EXIT

run_one() {
  local i="$1"
  PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$CONNINFO" \
    -X -q -t -A -c "SELECT pg_backend_pid()" \
    >"$TMPDIR_RUN/out.$i" 2>"$TMPDIR_RUN/err.$i"
}

for i in $(seq 1 "$CONCURRENCY"); do
  run_one "$i" &
done

fail=0
for job in $(jobs -p); do
  wait "$job" || fail=$((fail + 1))
done

# Also verify each query actually returned a backend pid.
ok=0
for i in $(seq 1 "$CONCURRENCY"); do
  if [ -s "$TMPDIR_RUN/out.$i" ] && grep -qE '^[0-9]+$' "$TMPDIR_RUN/out.$i"; then
    ok=$((ok + 1))
  else
    fail=$((fail + 1))
    [ -s "$TMPDIR_RUN/err.$i" ] && sed 's/^/  /' "$TMPDIR_RUN/err.$i" >&2
  fi
done

if [ "$ok" -eq "$CONCURRENCY" ] && [ "$fail" -eq 0 ]; then
  echo "POOL OK 20/20"
  exit 0
fi

echo "POOL FAIL $ok/$CONCURRENCY succeeded" >&2
exit 1
EOF
chmod +x pool_smoke.sh
ls -l pool_smoke.sh && bash -n pool_smoke.sh && echo 'syntax OK'

-rwxr-xr-x 1 root root 2501 Jul 24 14:32 pool_smoke.sh
syntax OK

time ./pool_smoke.sh; echo "exit code: $?"

exit code: 1

sed: -e expression #1, char 22: unterminated `s' command
ERROR: could not derive pooler host from SUPABASE_DB_URL

real	0m0.004s
user	0m0.002s
sys	0m0.002s

printf '%s' "$SUPABASE_DB_URL" | sed -E 's|^[^@]*@([^:/]+).*|\1'; echo "rc=$?"; echo '---'; grep -n 'sed -E' pool_smoke.sh | cat -A | head -5

rc=1
---
29:POOLER_HOST="$(printf '%s' "${SUPABASE_DB_URL:-}" | sed -E 's|^[^@]*@([^:/]+).*|\1')"$

sed: -e expression #1, char 22: unterminated `s' command

python3 - <<'PY'
import re
with open('pool_smoke.sh') as f:
    src = f.read()

old = '''POOLER_HOST="$(printf '%s' "${SUPABASE_DB_URL:-}" | sed -E 's|^[^@]*@([^:/]+).*|\\1')"'''
new = '''# Parse with pure bash parameter expansion (no sed/regex pitfalls):
#   postgresql://user:<redacted>@host:5432/db -> strip scheme, then creds, then :port/path
_url_noscheme="${SUPABASE_DB_URL#*://}"   # user:pass@host:5432/db
_url_nocreds="${_url_noscheme##*@}"       # host:5432/db
POOLER_HOST="${_url_nocreds%%[:/]*}"      # host'''

assert old in src, 'pattern not found'
src = src.replace(old, new)
with open('pool_smoke.sh', 'w') as f:
    f.write(src)
print('patched')
PY
bash -n pool_smoke.sh && echo 'syntax OK'

patched
syntax OK

time ./pool_smoke.sh; echo "exit code: $?"

POOL OK 20/20
exit code: 0

real	0m1.003s
user	0m0.501s
sys	0m0.232s

sed -i 's|_url_noscheme="${SUPABASE_DB_URL#\*://}"|_url_noscheme="${SUPABASE_DB_URL:-}"; _url_noscheme="${_url_noscheme#*://}"|' pool_smoke.sh
bash -n pool_smoke.sh && echo 'syntax OK'
for run in 1 2 3; do ./pool_smoke.sh; echo "run $run exit: $?"; done

syntax OK
POOL OK 20/20
run 1 exit: 0
POOL OK 20/20
run 2 exit: 0
POOL OK 20/20
run 3 exit: 0

cat pool_smoke.sh

#!/usr/bin/env bash
#
# pool_smoke.sh — prove the TRANSACTION-MODE pooler works for serverless workers.
#
# Connects through the project's Supabase transaction-mode connection pooler
# (port 6543 — NOT the session-mode port 5432, NOT a direct DB connection),
# fires 20 concurrent one-shot queries (SELECT pg_backend_pid()), and prints
# "POOL OK 20/20" only if all 20 succeed.
#
# Credentials come from environment variables only. Nothing is hardcoded.

set -u

CONCURRENCY=20

# --- sanity checks -----------------------------------------------------------
for var in SUPABASE_PROJECT_REF SUPABASE_DB_PASSWORD; do
  if [ -z "${!var:-}" ]; then
    echo "ERROR: environment variable $var is not set" >&2
    exit 1
  fi
done
command -v psql >/dev/null 2>&1 || { echo "ERROR: psql not found" >&2; exit 1; }

# Derive the pooler host from SUPABASE_DB_URL's host if possible (same pooler
# endpoint, just the transaction-mode port); fall back to the conventional
# aws-0-<region>.pooler.supabase.com host is not derivable, so default to the
# host in SUPABASE_DB_URL with port swapped 5432 -> 6543.
# Parse with pure bash parameter expansion (no sed/regex pitfalls):
#   postgresql://user:<redacted>@host:5432/db -> strip scheme, then creds, then :port/path
_url_noscheme="${SUPABASE_DB_URL:-}"; _url_noscheme="${_url_noscheme#*://}"   # user:pass@host:5432/db
_url_nocreds="${_url_noscheme##*@}"       # host:5432/db
POOLER_HOST="${_url_nocreds%%[:/]*}"      # host
if [ -z "$POOLER_HOST" ] || ! printf '%s' "$POOLER_HOST" | grep -q 'pooler\.supabase\.com'; then
  echo "ERROR: could not derive pooler host from SUPABASE_DB_URL" >&2
  exit 1
fi

# Transaction-mode pooler: port 6543, user scoped to the project ref.
export PGCONNECT_TIMEOUT=15
CONNINFO="host=${POOLER_HOST} port=6543 dbname=postgres user=postgres.${SUPABASE_PROJECT_REF} sslmode=require"

# --- run 20 concurrent one-shot queries --------------------------------------
TMPDIR_RUN="$(mktemp -d)"
trap 'rm -rf "$TMPDIR_RUN"' EXIT

run_one() {
  local i="$1"
  PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "$CONNINFO" \
    -X -q -t -A -c "SELECT pg_backend_pid()" \
    >"$TMPDIR_RUN/out.$i" 2>"$TMPDIR_RUN/err.$i"
}

for i in $(seq 1 "$CONCURRENCY"); do
  run_one "$i" &
done

fail=0
for job in $(jobs -p); do
  wait "$job" || fail=$((fail + 1))
done

# Also verify each query actually returned a backend pid.
ok=0
for i in $(seq 1 "$CONCURRENCY"); do
  if [ -s "$TMPDIR_RUN/out.$i" ] && grep -qE '^[0-9]+$' "$TMPDIR_RUN/out.$i"; then
    ok=$((ok + 1))
  else
    fail=$((fail + 1))
    [ -s "$TMPDIR_RUN/err.$i" ] && sed 's/^/  /' "$TMPDIR_RUN/err.$i" >&2
  fi
done

if [ "$ok" -eq "$CONCURRENCY" ] && [ "$fail" -eq 0 ]; then
  echo "POOL OK 20/20"
  exit 0
fi

echo "POOL FAIL $ok/$CONCURRENCY succeeded" >&2
exit 1

test -x ./pool_smoke.sh && echo 'executable: yes' && ./pool_smoke.sh && echo "final exit code: $?"

executable: yes
POOL OK 20/20
final exit code: 0