z-ai/glm-5.2 · d2_pooling · rep 2

env | grep -E '^SUPABASE_' | sed 's/\(KEY\|PASSWORD\|ROLE_KEY\)=.*/\1=<redacted>/'

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>

which psql pgx_executor python3 node 2>/dev/null; echo "---"; psql --version 2>/dev/null; python3 --version 2>/dev/null

/usr/bin/psql
/usr/bin/python3
/usr/bin/node
---
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
Python 3.11.2

python3 -c "import psycopg2; print('psycopg2 ok')" 2>/dev/null; python3 -c "import psycopg; print('psycopg3 ok')" 2>/dev/null; echo "done"

done

echo "$SUPABASE_DB_URL" | sed -E 's|://([^:]+):([^@]+)@([^:]+):([0-9]+)/(.*)|user=\1 pass_len=${#2} host=\3 port=\4 db=\5|'

postgresqluser=postgres.<ref> pass_len=${#2} host=aws-0-us-east-1.pooler.supabase.com port=5432 db=postgres

python3 -c "import psycopg2" 2>&1; echo "==="; python3 -c "import psycopg" 2>&1; echo "==="; pip3 list 2>/dev/null | grep -iE 'psycopg|asyncpg|sqlalchemy' ; echo "==="; node -e "const {Pool}=require('pg'); console.log('pg ok')" 2>&1

Traceback (most recent call last):
  File "<string>", line 1, in <module>
ModuleNotFoundError: No module named 'psycopg2'
===
Traceback (most recent call last):
  File "<string>", line 1, in <module>
ModuleNotFoundError: No module named 'psycopg'
===
===
node:internal/modules/cjs/loader:1143
  throw err;
  ^

Error: Cannot find module 'pg'
Require stack:
- /work/[eval]
    at Module._resolveFilename (node:internal/modules/cjs/loader:1140:15)
    at Module._load (node:internal/modules/cjs/loader:981:27)
    at Module.require (node:internal/modules/cjs/loader:1231:19)
    at require (node:internal/modules/helpers:177:18)
    at [eval]:1:14
    at runScriptInThisContext (node:internal/vm:143:10)
    at node:internal/process/execution:100:14
    at [eval]-wrapper:6:24
    at runScript (node:internal/process/execution:83:62)
    at evalScript (node:internal/process/execution:114:10) {
  code: 'MODULE_NOT_FOUND',
  requireStack: [ '/work/[eval]' ]
}

Node.js v18.20.4

cd /work
# Parse the session-mode URL to extract components, rebuild for transaction-mode (port 6543)
DB_URL="$SUPABASE_DB_URL"
# Extract user@host:port/db
PROTO="${DB_URL%%://*}"
REST="${DB_URL#*://}"
USER_PASS="${REST%%@*}"
HOSTPORT_DB="${REST#*@}"
USER="${USER_PASS%%:*}"
HOSTPORT="${HOSTPORT_DB%%/*}"
DB="${HOSTPORT_DB#*/}"
HOST="${HOSTPORT%%:*}"
echo "user=$USER host=$HOST db=$DB"
# Test transaction-mode pooler (port 6543)
PGPASSWORD="$SUPABASE_DB_PASSWORD" psql "host=$HOST port=6543 user=$USER dbname=$DB" -c "SELECT pg_backend_pid();" 2>&1

user=postgres.<ref> host=aws-0-us-east-1.pooler.supabase.com db=postgres
 pg_backend_pid 
----------------
          55124
(1 row)

cat > /work/pool_smoke.sh << 'SCRIPT'
#!/usr/bin/env bash
#
# pool_smoke.sh — Prove the Supabase transaction-mode connection pooler works.
#
# Connects exclusively via the TRANSACTION-MODE pooler (port 6543), never
# the session-mode pooler (5432) or a direct DB connection.  Spawns 20
# concurrent one-shot queries, waits for all of them, and prints
# "POOL OK 20/20" iff every one succeeded.
#
# All credentials are read from environment variables — the password is
# never hardcoded.

set -euo pipefail

# ---------------------------------------------------------------------------
# 1.  Derive connection parameters from SUPABASE_DB_URL
#     (session-mode, port 5432) and re-target the transaction-mode pooler
#     (port 6543).
# ---------------------------------------------------------------------------

if [[ -z "${SUPABASE_DB_URL:-}" ]]; then
    echo "ERROR: SUPABASE_DB_URL is not set" >&2
    exit 1
fi
if [[ -z "${SUPABASE_DB_PASSWORD:-}" ]]; then
    echo "ERROR: SUPABASE_DB_PASSWORD is not set" >&2
    exit 1
fi

DB_URL="$SUPABASE_DB_URL"

# Strip scheme
REST="${DB_URL#*://}"
# user:password@host:port/dbname?params
USERPASS_HOSTPORT="${REST%%@*}"
HOSTPORT_DB="${REST#*@}"

# user (may contain a dot, e.g. postgres.<project-ref>)
POOL_USER="${USERPASS_HOSTPORT%%:*}"
# skip password — we use SUPABASE_DB_PASSWORD instead

# host:port/dbname
HOSTPORT="${HOSTPORT_DB%%/*}"
DBNAME="${HOSTPORT_DB#*/}"
DBNAME="${DBNAME%%\?*}"   # drop query params if any
POOL_HOST="${HOSTPORT%%:*}"

# *** Hard-code port 6543 — the Supavisor TRANSACTION-MODE pooler. ***
# (Port 5432 is session-mode; we must NOT use that.)
POOL_PORT=6543

CONCURRENCY=20

echo "Connecting via transaction-mode pooler: ${POOL_HOST}:${POOL_PORT}" >&2

# ---------------------------------------------------------------------------
# 2.  Fire 20 concurrent one-shot psql queries in the background.
# ---------------------------------------------------------------------------

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

success=0
declare -a pids=()

for i in $(seq 1 "$CONCURRENCY"); do
    (
        # Run a single query and write the exit code to a per-worker file.
        PGPASSWORD="$SUPABASE_DB_PASSWORD" \
        psql \
            --host="$POOL_HOST" \
            --port="$POOL_PORT" \
            --username="$POOL_USER" \
            --dbname="$DBNAME" \
            --no-psqlrc \
            --tuples-only \
            --quiet \
            --command="SELECT pg_backend_pid();" \
            >"$TMPDIR_WORK/out_$i.txt" 2>"$TMPDIR_WORK/err_$i.txt"
        echo "$?" > "$TMPDIR_WORK/rc_$i.txt"
    ) &
    pids+=($!)
done

# ---------------------------------------------------------------------------
# 3.  Wait for all background jobs to finish.
# ---------------------------------------------------------------------------

fail=0
for pid in "${pids[@]}"; do
    if ! wait "$pid"; then
        fail=1
    fi
done

# Count successes from the per-worker result files
for i in $(seq 1 "$CONCURRENCY"); do
    rc_file="$TMPDIR_WORK/rc_$i.txt"
    if [[ -f "$rc_file" && "$(cat "$rc_file")" == "0" ]]; then
        success=$((success + 1))
    else
        echo "Worker $i FAILED:" >&2
        cat "$TMPDIR_WORK/err_$i.txt" >&2 2>/dev/null || true
    fi
done

# ---------------------------------------------------------------------------
# 4.  Report.
# ---------------------------------------------------------------------------

if [[ "$success" -eq "$CONCURRENCY" ]]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "POOL FAIL ${success}/${CONCURRENCY}" >&2
    exit 1
fi
SCRIPT
chmod +x /work/pool_smoke.sh

cd /work && ./pool_smoke.sh

POOL OK 20/20

Connecting via transaction-mode pooler: aws-0-us-east-1.pooler.supabase.com:6543

# Verify the script only uses port 6543 (transaction-mode) and never 5432
echo "=== Port references ==="; grep -n '6543\|5432\|POOL_PORT' /work/pool_smoke.sh
echo "=== Password hardcoding check ==="; grep -in 'password' /work/pool_smoke.sh
echo "=== Direct DB check ==="; grep -in 'supabase.co:5432\|db.supabase\|direct' /work/pool_smoke.sh || echo "none"

=== Port references ===
5:# Connects exclusively via the TRANSACTION-MODE pooler (port 6543), never
6:# the session-mode pooler (5432) or a direct DB connection.  Spawns 20
17:#     (session-mode, port 5432) and re-target the transaction-mode pooler
18:#     (port 6543).
48:# *** Hard-code port 6543 — the Supavisor TRANSACTION-MODE pooler. ***
49:# (Port 5432 is session-mode; we must NOT use that.)
50:POOL_PORT=6543
54:echo "Connecting via transaction-mode pooler: ${POOL_HOST}:${POOL_PORT}" >&2
72:            --port="$POOL_PORT" \
=== Password hardcoding check ===
10:# All credentials are read from environment variables — the password is
25:if [[ -z "${SUPABASE_DB_PASSWORD:-}" ]]; then
26:    echo "ERROR: SUPABASE_DB_PASSWORD is not set" >&2
34:# user:password@host:port/dbname?params
40:# skip password — we use SUPABASE_DB_PASSWORD instead
69:        PGPASSWORD="$SUPABASE_DB_PASSWORD" \
=== Direct DB check ===
6:# the session-mode pooler (5432) or a direct DB connection.  Spawns 20