claude-sonnet-5 · d2_pooling · rep 2

echo "SUPABASE_URL=$SUPABASE_URL"
echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"
echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"
echo "SUPABASE_ANON_KEY length=${#SUPABASE_ANON_KEY}"
echo "SUPABASE_SERVICE_ROLE_KEY length=${#SUPABASE_SERVICE_ROLE_KEY}"
echo "SUPABASE_DB_PASSWORD length=${#SUPABASE_DB_PASSWORD}"

SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_PROJECT_REF=<ref>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_ANON_KEY length=208
SUPABASE_SERVICE_ROLE_KEY length=219
SUPABASE_DB_PASSWORD length=32

which psql; psql --version
echo "---"
which nc; nc -zv aws-0-us-east-1.pooler.supabase.com 6543 2>&1
echo "---"
nc -zv aws-0-us-east-1.pooler.supabase.com 5432 2>&1

/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
---
/usr/bin/nc
Connection to aws-0-us-east-1.pooler.supabase.com (44.208.221.186) 6543 port [tcp/*] succeeded!
---
Connection to aws-0-us-east-1.pooler.supabase.com (44.208.221.186) 5432 port [tcp/postgresql] succeeded!

export PGCONNECT_TIMEOUT=10
psql "$SUPABASE_DB_URL" -c "select current_setting('port') as port, current_setting('server_version') as ver;" 2>&1
echo "=== try 6543 ==="
TXN_URL=$(echo "$SUPABASE_DB_URL" | sed -E 's/:5432\//:6543\//')
echo "$TXN_URL" | sed -E 's#(://[^:]+:)[^@]+(@)#\1***\2#'
psql "$TXN_URL" -c "show port;" 2>&1
psql "$TXN_URL" -c "select 1;" 2>&1

 port | ver  
------+------
 5432 | 17.6
(1 row)

=== try 6543 ===
postgresql://postgres.<ref>:***@aws-0-us-east-1.pooler.supabase.com:6543/postgres
 port 
------
 5432
(1 row)

 ?column? 
----------
        1
(1 row)

export PGCONNECT_TIMEOUT=10
TXN_URL=$(echo "$SUPABASE_DB_URL" | sed -E 's/:5432\//:6543\//')

echo "=== SHOW POOL_MODE on session port (5432) ==="
psql "$SUPABASE_DB_URL" -c "show pool_mode;" 2>&1

echo "=== SHOW POOL_MODE on 6543 ==="
psql "$TXN_URL" -c "show pool_mode;" 2>&1

echo "=== test session-level prepared statement persistence on 5432 (session mode should support this across statements in same connection, both would within one connection actually) ==="
echo "=== better: test SET on one statement then check on next within same psql session, single connection multiple queries ==="

echo "--- 5432 multi-query single connection: set + check ---"
psql "$SUPABASE_DB_URL" <<'EOF' 2>&1
SET application_name = 'probe_test';
SELECT current_setting('application_name');
EOF

echo "--- 6543 multi-query single connection: set + check ---"
psql "$TXN_URL" <<'EOF' 2>&1
SET application_name = 'probe_test';
SELECT current_setting('application_name');
EOF

=== SHOW POOL_MODE on session port (5432) ===
ERROR:  unrecognized configuration parameter "pool_mode"
=== SHOW POOL_MODE on 6543 ===
ERROR:  unrecognized configuration parameter "pool_mode"
=== test session-level prepared statement persistence on 5432 (session mode should support this across statements in same connection, both would within one connection actually) ===
=== better: test SET on one statement then check on next within same psql session, single connection multiple queries ===
--- 5432 multi-query single connection: set + check ---
SET
 current_setting 
-----------------
 probe_test
(1 row)

--- 6543 multi-query single connection: set + check ---
SET
 current_setting 
-----------------
 probe_test
(1 row)

curl -s "https://api.supabase.com/v1/projects/$SUPABASE_PROJECT_REF" -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" 2>&1 | head -c 2000
echo
echo "---"
curl -s "$SUPABASE_URL/rest/v1/" -H "apikey: $SUPABASE_ANON_KEY" 2>&1 | head -c 500

{"message":"JWT failed verification"}
---
{"message":"Invalid API key","hint":"Only the `service_role` API key can be used for this endpoint."}

export PGCONNECT_TIMEOUT=10
TXN_URL=$(echo "$SUPABASE_DB_URL" | sed -E 's/:5432\//:6543\//')

echo "=== inet_server_port on 5432 ==="
psql "$SUPABASE_DB_URL" -c "select inet_server_port(), inet_server_addr();" 2>&1

echo "=== inet_server_port on 6543 ==="
psql "$TXN_URL" -c "select inet_server_port(), inet_server_addr();" 2>&1

echo "=== check user options / search_path style params supabase supavisor exposes ==="
psql "$SUPABASE_DB_URL" -c "select version();" 2>&1
psql "$TXN_URL" -c "select version();" 2>&1

=== inet_server_port on 5432 ===
 inet_server_port |            inet_server_addr             
------------------+-----------------------------------------
             5432 | 2600:1f18:4850:7c01:c70c:3b85:612c:200e
(1 row)

=== inet_server_port on 6543 ===
 inet_server_port |            inet_server_addr             
------------------+-----------------------------------------
             5432 | 2600:1f18:4850:7c01:c70c:3b85:612c:200e
(1 row)

=== check user options / search_path style params supabase supavisor exposes ===
                                   version                                    
------------------------------------------------------------------------------
 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

                                   version                                    
------------------------------------------------------------------------------
 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

cat > pool_smoke.sh << 'SCRIPT_EOF'
#!/usr/bin/env bash
#
# pool_smoke.sh
#
# Proves that we can run many concurrent one-shot queries through the
# Supabase project's TRANSACTION-MODE connection pooler (Supavisor / PgBouncer
# on port 6543) without exhausting Postgres backend connections, which is the
# failure mode we hit when serverless workers connect directly or through the
# session-mode pooler (port 5432).
#
# Credentials are taken exclusively from environment variables:
#   SUPABASE_DB_URL       - session-mode pooler connection string (given)
#   SUPABASE_DB_PASSWORD  - db password (used only if we must build a URL by hand)
#   SUPABASE_PROJECT_REF  - project ref (used only if we must build a URL by hand)
#
# The script never hardcodes a password; it derives the transaction-mode
# pooler URL (port 6543) from the environment-provided session-mode URL
# (port 5432) or, if only pieces are available, builds it from the pieces.
#
# Usage: ./pool_smoke.sh
# Exit code 0 and prints "POOL OK 20/20" iff all 20 concurrent queries succeed.

set -u -o pipefail

CONCURRENCY=20
SESSION_PORT=5432
TXN_PORT=6543

fail() {
    echo "pool_smoke.sh: $*" >&2
    exit 1
}

# --- Build the transaction-mode pooler connection URL -----------------------
#
# Supabase's pooler (Supavisor) exposes the SAME host in two modes,
# distinguished only by port:
#   5432 -> session mode
#   6543 -> transaction mode
#
# We take whatever pooler URL/info we were given and force it onto the
# transaction-mode port, without ever writing a password literal into this
# script.

TXN_DB_URL=""

if [[ -n "${SUPABASE_DB_URL:-}" ]]; then
    # Rewrite the port component of the URL (works for postgres://user:<redacted>@host:port/db)
    TXN_DB_URL=$(printf '%s' "$SUPABASE_DB_URL" | sed -E "s#:(${SESSION_PORT})/#:${TXN_PORT}/#")
elif [[ -n "${SUPABASE_PROJECT_REF:-}" && -n "${SUPABASE_DB_PASSWORD:-}" ]]; then
    # Fallback: construct from pieces if only a project ref + password were given.
    # Requires SUPABASE_DB_POOLER_HOST (or derive the default AWS pooler host pattern
    # can't be safely guessed for every project/region, so require it explicitly).
    : "${SUPABASE_DB_POOLER_HOST:?SUPABASE_DB_POOLER_HOST must be set when SUPABASE_DB_URL is not provided}"
    TXN_DB_URL="postgresql://postgres.${SUPABASE_PROJECT_REF}:<redacted>@${SUPABASE_DB_POOLER_HOST}:${TXN_PORT}/postgres"
else
    fail "no usable credentials found: set SUPABASE_DB_URL (or SUPABASE_PROJECT_REF + SUPABASE_DB_PASSWORD + SUPABASE_DB_POOLER_HOST)"
fi

# Sanity check: refuse to run if we somehow ended up on the session-mode port
# or a non-pooler host — this script's whole point is to exercise the
# transaction-mode pooler, never a direct DB connection or session pooler.
if [[ "$TXN_DB_URL" != *":${TXN_PORT}/"* ]]; then
    fail "refusing to run: resolved connection string is not using the transaction-mode pooler port ${TXN_PORT}"
fi
if [[ "$TXN_DB_URL" != *"pooler.supabase.com"* ]] && [[ "$TXN_DB_URL" != *"${SUPABASE_DB_POOLER_HOST:-__none__}"* ]]; then
    fail "refusing to run: resolved connection string does not point at a Supabase pooler host"
fi

command -v psql >/dev/null 2>&1 || fail "psql is required but not found in PATH"

# --- Fire N concurrent one-shot connections/queries through the pooler ------

WORKDIR=$(mktemp -d)
trap 'rm -rf "$WORKDIR"' EXIT

run_one() {
    local i="$1"
    # -X: ignore psqlrc, -q: quiet, -A -t: unadorned single value output.
    # PGCONNECT_TIMEOUT bounds how long a stuck connection attempt can hang.
    if PGCONNECT_TIMEOUT=10 psql "$TXN_DB_URL" -X -q -A -t \
        -c "SELECT pg_backend_pid();" \
        > "$WORKDIR/out.$i" 2> "$WORKDIR/err.$i"; then
        echo "ok" > "$WORKDIR/status.$i"
    else
        echo "fail" > "$WORKDIR/status.$i"
    fi
}

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

# Wait for all 20 concurrent one-shot queries to finish.
for pid in "${pids[@]}"; do
    wait "$pid"
done

success=0
for i

done

cd "$(pwd)" && ./pool_smoke.sh; echo "EXIT_CODE=$?"

POOL OK 20/20
EXIT_CODE=0

for i in 1 2 3; do
  echo "=== run $i ==="
  ./pool_smoke.sh
  echo "exit=$?"
done

=== run 1 ===
POOL OK 20/20
exit=0
=== run 2 ===
POOL OK 20/20
exit=0
=== run 3 ===
POOL OK 20/20
exit=0

# sanity-check the guard logic: force a session-mode URL and confirm it refuses to run
SESSION_ONLY_URL="$SUPABASE_DB_URL" bash -c '
export SUPABASE_DB_URL="$SESSION_ONLY_URL"
' 2>&1

# Simulate by wrapping: temporarily override sed replacement target so port stays 5432
cp pool_smoke.sh /tmp/pool_smoke_test.sh
sed -i 's/:${TXN_PORT}\//:${SESSION_PORT}\//' /tmp/pool_smoke_test.sh
chmod +x /tmp/pool_smoke_test.sh
./ /tmp/pool_smoke_test.sh 2>/dev/null
/tmp/pool_smoke_test.sh; echo "exit=$?"
rm -f /tmp/pool_smoke_test.sh

exit=1

POOL FAILED 15/20
--- worker 1 stderr ---
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
--- worker 6 stderr ---
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
--- worker 11 stderr ---
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
--- worker 12 stderr ---
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
--- worker 16 stderr ---
psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (52.45.94.125), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15

ls -la pool_smoke.sh
echo "---"
cat pool_smoke.sh | head -20
echo "..."
echo "=== final run ==="
./pool_smoke.sh
echo "EXIT_CODE=$?"

-rwxr-xr-x 1 root root 4528 Jul 24 04:59 pool_smoke.sh
---
#!/usr/bin/env bash
#
# pool_smoke.sh
#
# Proves that we can run many concurrent one-shot queries through the
# Supabase project's TRANSACTION-MODE connection pooler (Supavisor / PgBouncer
# on port 6543) without exhausting Postgres backend connections, which is the
# failure mode we hit when serverless workers connect directly or through the
# session-mode pooler (port 5432).
#
# Credentials are taken exclusively from environment variables:
#   SUPABASE_DB_URL       - session-mode pooler connection string (given)
#   SUPABASE_DB_PASSWORD  - db password (used only if we must build a URL by hand)
#   SUPABASE_PROJECT_REF  - project ref (used only if we must build a URL by hand)
#
# The script never hardcodes a password; it derives the transaction-mode
# pooler URL (port 6543) from the environment-provided session-mode URL
# (port 5432) or, if only pieces are available, builds it from the pieces.
#
# Usage: ./pool_smoke.sh
...
=== final run ===
POOL OK 20/20
EXIT_CODE=0