tencent/hy3 · d2_pooling · rep 1

env | grep -i supabase; echo "---"; which psql pg_isready; echo "---"; cat /etc/os-release 2>/dev/null | head -3

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
---
/usr/bin/psql
/usr/bin/pg_isready
---
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"

env | grep -i supabase | sed -E 's/(PASSWORD=).*/\1<hidden>/'

SUPABASE_DB_PASSWORD=<hidden>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

cd "$(pwd)"
# Derive the host from SUPABASE_DB_URL and test the transaction-mode port (6543)
netloc="${SUPABASE_DB_URL##*@}"
host="${netloc%%:*}"
echo "Pooler host: $host"
echo "--- pg_isready on session port 5432 ---"
pg_isready -h "$host" -p 5432 -U "postgres.${SUPABASE_PROJECT_REF}" 2>&1 || true
echo "--- pg_isready on transaction port 6543 ---"
pg_isready -h "$host" -p 6543 -U "postgres.${SUPABASE_PROJECT_REF}" 2>&1 || true

Pooler host: aws-0-us-east-1.pooler.supabase.com
--- pg_isready on session port 5432 ---
aws-0-us-east-1.pooler.supabase.com:5432 - accepting connections
--- pg_isready on transaction port 6543 ---
aws-0-us-east-1.pooler.supabase.com:6543 - accepting connections

cd "$(pwd)"
netloc="${SUPABASE_DB_URL##*@}"
POOLER_HOST="${netloc%%:*}"
echo "POOLER_HOST=$POOLER_HOST"
TX_URL="postgresql://postgres.${SUPABASE_PROJECT_REF}:<redacted>@${POOLER_HOST}:6543/postgres?sslmode=require"
echo "--- single one-shot query via transaction pooler (port 6543) ---"
PGCONNECT_TIMEOUT=10 psql "$TX_URL" -t -A -c "SELECT pg_backend_pid();" 2>&1

POOLER_HOST=aws-0-us-east-1.pooler.supabase.com
--- single one-shot query via transaction pooler (port 6543) ---
196909

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — prove the transaction-mode connection pooler is wired up
# correctly so serverless workers don't exhaust database connections.
#
# What it does:
#   * Connects ONLY through the project's TRANSACTION-MODE connection pooler
#     (port 6543 on the Supabase pooler host). It never touches the
#     session-mode port (5432) and never connects directly to the DB.
#   * Launches 20 concurrent one-shot queries (SELECT pg_backend_pid()),
#     waits for all of them, and prints "POOL OK 20/20" only if every one
#     succeeded, then exits 0.
#   * Reads all credentials from the environment — the DB password is never
#     hardcoded.
#
# Requirements: psql (from postgresql-client) on PATH.

set -u

# --- Config ---------------------------------------------------------------
N=20                                  # number of concurrent one-shot queries
POOLER_PORT=6543                      # transaction-mode pooler port (NOT 5432)

# --- Sanity-check environment ---------------------------------------------
required_vars=(SUPABASE_DB_URL SUPABASE_DB_PASSWORD SUPABASE_PROJECT_REF)
missing=0
for v in "${required_vars[@]}"; do
  if [ -z "${!v:-}" ]; then
    echo "ERROR: required environment variable $v is not set" >&2
    missing=1
  fi
done
[ "$missing" -eq 0 ] || exit 1

# --- Build the transaction-mode connection string from env -----------------
# Reuse the pooler host already present in SUPABASE_DB_URL, but force the
# transaction-mode port (6543). port 5432 would be session mode; a plain
# :5432 / direct host would bypass pooling entirely.
netloc="${SUPABASE_DB_URL##*@}"         # host:port/db  (and possibly ?opts)
pooler_host="${netloc%%:*}"             # strip port/db, keep host
pooler_host="${pooler_host%%/*}"        # just the hostname

POOLER_URI="postgresql://postgres.${SUPABASE_PROJECT_REF}:<redacted>@${pooler_host}:${POOLER_PORT}/postgres?sslmode=require"

# Guard against accidentally using the session-mode / direct port.
case "$POOLER_URI" in
  *:5432/*|*:5432) echo "ERROR: refusing to use session-mode / direct port 5432" >&2; exit 1 ;;
esac

# --- Run 20 concurrent one-shot queries ------------------------------------
tmpdir="$(mktemp -d "${TMPDIR:-/tmp}/pool_smoke.XXXXXX")"
trap 'rm -rf "$tmpdir"' EXIT

pids=()
for i in $(seq 1 "$N"); do
  (
    if PGCONNECT_TIMEOUT=10 psql "$POOLER_URI" -t -A -c "SELECT pg_backend_pid();" >/dev/null 2>&1; then
      echo 1 > "$tmpdir/$i"
    else
      echo 0 > "$tmpdir/$i"
    fi
  ) &
  pids+=($!)
done

# Wait for every background query to finish.
fail=0
for p in "${pids[@]}"; do
  wait "$p" || fail=1
done

# Tally results.
ok=0
for i in $(seq 1 "$N"); do
  [ "$(cat "$tmpdir/$i" 2>/dev/null)" = "1" ] && ok=$((ok+1))
done

if [ "$ok" -eq "$N" ]; then
  echo "POOL OK ${ok}/${N}"
  exit 0
else
  echo "POOL FAIL ${ok}/${N}" >&2
  exit 1
fi
EOF
chmod +x pool_smoke.sh
echo "written:"; ls -l pool_smoke.sh

written:
-rwxr-xr-x 1 root root 2882 Jul 26 02:50 pool_smoke.sh

cd "$(pwd)"; ./pool_smoke.sh; echo "exit code: $?"

POOL OK 20/20
exit code: 0

cd "$(pwd)"
# Simulate what would happen if someone forced the session port — prove the guard rejects it.
sed 's/POOLER_PORT=6543/POOLER_PORT=5432/' pool_smoke.sh > /tmp/pool_smoke_session.sh
chmod +x /tmp/pool_smoke_session.sh
/tmp/pool_smoke_session.sh; echo "session-mode run exit: $? (expected non-zero)"

session-mode run exit: 1 (expected non-zero)

ERROR: refusing to use session-mode / direct port 5432