tencent/hy3 · d2_pooling · rep 3

echo "=== env (masked) ==="; for v in SUPABASE_URL SUPABASE_PROJECT_REF SUPABASE_DB_URL SUPABASE_DB_PASSWORD; do printf '%s=%s\n' "$v" "$(printenv "$v" | sed -E 's/(:.{0,}@)/:***@/')"; done; echo; echo "=== tools ==="; which psql pg_isready xargs awk sed 2>&1; echo "=== psql version ==="; psql --version 2>&1

=== env (masked) ===
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=postgresql:***@aws-0-us-east-1.pooler.supabase.com:5432/postgres
SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>

=== tools ===
/usr/bin/psql
/usr/bin/pg_isready
/usr/bin/xargs
/usr/bin/awk
/usr/bin/sed
=== psql version ===
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

echo "=== full DB_URL user@host (password hidden, user shown) ==="; printenv SUPABASE_DB_URL | sed -E 's#://([^:/]+):[^@]*@#://\1:***@#'; echo; echo "=== project ref ==="; printenv SUPABASE_PROJECT_REF; echo "=== host from DB_URL ==="; printenv SUPABASE_DB_URL | sed -E 's#.*@([^/]+)/.*#\1#'

=== full DB_URL user@host (password hidden, user shown) ===
postgresql://postgres.<ref>:***@aws-0-us-east-1.pooler.supabase.com:5432/postgres

=== project ref ===
<ref>
=== host from DB_URL ===
aws-0-us-east-1.pooler.supabase.com:5432

PASS="${SUPABASE_DB_PASSWORD:?}"; HOST=$(printenv SUPABASE_DB_URL | sed -E 's#.*@([^:/]+):.*#\1#'); USER=$(printenv SUPABASE_DB_URL | sed -E 's#^postgresql://([^:/]+):.*#\1#'); DB=$(printenv SUPABASE_DB_URL | sed -E 's#.*/([^/?]+).*#\1#'); echo "host=$HOST user=$USER db=$DB"; PGSSLMODE=require PGPASSWORD="$PASS" psql -h "$HOST" -p 6543 -U "$USER" -d "$DB" -t -A -c "SELECT pg_backend_pid();" 2>&1 | head; echo "exit=${PIPESTATUS[0]}"

host=aws-0-us-east-1.pooler.supabase.com user=postgres.<ref> db=postgres
197624
exit=0

cat > pool_smoke.sh <<'EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh — proves the project's TRANSACTION-MODE connection pooler
# can absorb many short-lived, concurrent one-shot queries without exhausting
# connections. Serverless workers should connect via this pooler (port 6543),
# NOT the session-mode port (5432) and NOT a direct DB connection.
#
# Credentials are taken ONLY from the environment. The DB password is never
# hardcoded.

set -u

# --- Derive connection parameters from the environment ----------------------
# SUPABASE_DB_URL is a session-mode pooler URI of the form:
#   postgresql://<user>:<pass>@<host>:<port>/<db>
# We reuse the same host/user/db but force the TRANSACTION-MODE port (6543).
DB_URL="${SUPABASE_DB_URL:?SUPABASE_DB_URL is not set}"
PASS="${SUPABASE_DB_PASSWORD:?SUPABASE_DB_PASSWORD is not set}"

HOST=$(printf '%s' "$DB_URL" | sed -E 's#.*@([^:/]+):.*#\1#')
USER=$(printf '%s' "$DB_URL" | sed -E 's#^postgresql://([^:/]+):.*#\1#')
DB=$(printf '%s' "$DB_URL"   | sed -E 's#.*/([^/?]+).*#\1#')

# Transaction-mode pooler port on Supabase.
POOL_PORT=6543

CONCURRENCY=20
export PGSSLMODE=require
export PGPASSWORD="$PASS"

# --- Work out a scratch dir for per-query results ---------------------------
OKDIR="$(mktemp -d "${TMPDIR:-/tmp}/pool_smoke.XXXXXX")" || { echo "could not make temp dir" >&2; exit 1; }
trap 'rm -rf "$OKDIR"' EXIT

# --- Launch N concurrent one-shot queries -----------------------------------
# Each query is fully independent: connect, run SELECT pg_backend_pid(),
# disconnect. We use the transaction-mode pooler so these don't consume a
# dedicated backend each for the whole session.
run_one() {
  local i="$1"
  if PGPASSWORD="$PASS" psql -h "$HOST" -p "$POOL_PORT" -U "$USER" -d "$DB" \
        -t -A -c "SELECT pg_backend_pid();" >/dev/null 2>&1; then
    : > "$OKDIR/ok.$i"
  fi
}

pids=()
for i in $(seq 1 "$CONCURRENCY"); do
  run_one "$i" &
  pids+=("$!")
done

# Wait for every concurrent query to finish.
for pid in "${pids[@]}"; do
  wait "$pid"
done

ok_count=$(find "$OKDIR" -type f -name 'ok.*' | wc -l | tr -d ' ')

if [ "$ok_count" -eq "$CONCURRENCY" ]; then
  echo "POOL OK ${ok_count}/${CONCURRENCY}"
  exit 0
else
  echo "POOL FAIL ${ok_count}/${CONCURRENCY} (some queries did not succeed)"
  exit 1
fi
EOF
chmod +x pool_smoke.sh
ls -l pool_smoke.sh

-rwxr-xr-x 1 root root 2259 Jul 26 02:55 pool_smoke.sh

./pool_smoke.sh; echo "exit=$?"

POOL OK 20/20
exit=0

echo "=== password never hardcoded? (expect no literal pw, only env refs) ==="; grep -nE 'SUPABASE_DB_PASSWORD|PGPASSWORD|5432' pool_smoke.sh | sed -E 's/(:[^ ]*PASS[^ ]*:).*/\1 <password-ref-hidden>/' ; echo; echo "=== explicit pooler port in use ==="; grep -n 'POOL_PORT=' pool_smoke.sh; echo; echo "=== re-run for stability ==="; ./pool_smoke.sh; echo "exit=$?"

=== password never hardcoded? (expect no literal pw, only env refs) ===
6:# NOT the session-mode port (5432) and NOT a direct DB connection.
18:PASS="${SUPABASE_DB_PASSWORD: <password-ref-hidden>
29:export PGPASSWORD="$PASS"
41:  if PGPASSWORD="$PASS" psql -h "$HOST" -p "$POOL_PORT" -U "$USER" -d "$DB" \

=== explicit pooler port in use ===
25:POOL_PORT=6543

=== re-run for stability ===
POOL OK 20/20
exit=0