qwen/qwen3-coder-plus · d2_pooling · rep 3

env | grep SUPABASE

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>

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

# Check required environment variables
if [ -z "$SUPABASE_DB_URL" ]; then
    echo "Error: SUPABASE_DB_URL is not set"
    exit 1
fi

# Prepare the database URL with transaction pooling parameters
DB_URL="$SUPABASE_DB_URL"

# Array to hold background process PIDs
PIDS=()

echo "Running 20 concurrent queries..."

# Launch 20 concurrent queries
for i in {1..20}; do
    # Run the query in background and capture PID
    psql "$DB_URL" -t -c "SELECT pg_backend_pid();" > "/tmp/query_result_$i" 2> "/tmp/query_error_$i" &
    PIDS+=($!)
done

# Wait for all background processes to complete
for pid in ${PIDS[@]}; do
    wait $pid || ALL_SUCCEEDED=false
done

# Check if all queries succeeded
ALL_SUCCEEDED=true
SUCCESS_COUNT=0

for i in {1..20}; do
    if [ -f "/tmp/query_result_$i" ] && [ -s "/tmp/query_result_$i" ] && [ ! -s "/tmp/query_error_$i" ]; then
        SUCCESS_COUNT=$((SUCCESS_COUNT + 1))
    else
        ALL_SUCCEEDED=false
        break
    fi
done

if [ "$ALL_SUCCEEDED" = true ] && [ $SUCCESS_COUNT -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: $SUCCESS_COUNT out of 20 queries succeeded"
    for i in {1..20}; do
        if [ -s "/tmp/query_error_$i" ]; then
            echo "Query $i error: $(cat /tmp/query_error_$i)"
        fi
    done
    exit 1
fi

# Clean up temp files
rm -f /tmp/query_result_* /tmp/query_error_*
EOF

chmod +x pool_smoke.sh

which psql || echo "psql not found"

/usr/bin/psql

./pool_smoke.sh

Running 20 concurrent queries...
Failed: 6 out of 20 queries succeeded
Query 7 error: psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
Query 10 error: psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
Query 11 error: 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
Query 18 error: psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.208.221.186), port 5432 failed: FATAL:  (EMAXCONNSESSION) max clients reached in session mode - max clients are limited to pool_size: 15
Query 19 error: 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

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

# Check required environment variables
if [ -z "$SUPABASE_URL" ] || [ -z "$SUPABASE_ANON_KEY" ] || [ -z "$SUPABASE_PROJECT_REF" ]; then
    echo "Error: Required Supabase environment variables are not set" 
    exit 1
fi

# For transaction mode, we should use the anon key with a constructed URL that points to the project
# We'll connect to the main supabase domain but through the transaction pooler
# Construct a URL to connect to postgres directly via the pooler using the project ref
DB_HOST="aws-0-us-east-1.pooler.supabase.com"
DB_NAME="postgres"
DB_USER="postgres.${SUPABASE_PROJECT_REF}"

# Use the existing DB_URL that has password included, as it seems to be the connection that includes credentials
DB_URL="$SUPABASE_DB_URL"

# Verify that we're connecting to the pooler host
if [[ ! "$DB_URL" =~ \.pooler\. ]]; then
    echo "Error: DB_URL does not appear to use the connection pooler"
    exit 1
fi

# Array to hold temporary files
QUERY_FILES=()
ERROR_FILES=()

echo "Running 20 concurrent queries..."

# Prepare file names
for i in {1..20}; do
    QUERY_FILES[i]="/tmp/pool_query_result_$i"
    ERROR_FILES[i]="/tmp/pool_query_error_$i"
done

# Launch 20 concurrent queries with explicit cleanup on failures
success_count=0
failure_count=0

for i in {1..20}; do
    psql "$DB_URL" -t -c "SELECT pg_backend_pid();" > "${QUERY_FILES[$i]}" 2> "${ERROR_FILES[$i]}" &
    pids[i]=$!
done

# Wait for all processes
for i in {1..20}; do
    if wait ${pids[$i]}; then
        ((success_count++))
    else
        ((failure_count++))
    fi
done

# Determine if all succeedeed 
if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: $success_count out of 20 queries succeeded"
    for i in {1..20}; do
        if [ -s "${ERROR_FILES[$i]}" ] && [ $i -le 20 ]; then
            echo "Query $i error: $(cat ${ERROR_FILES[$i]})"
        fi
    done
    exit 1
fi

# Clean up
for i in {1..20}; do
    rm -f "${QUERY_FILES[$i]}" "${ERROR_FILES[$i]}"
done
EOF

psql --version

psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

# Check required environment variables
if [ -z "$SUPABASE_DB_URL" ]; then
    echo "Error: SUPABASE_DB_URL is not set"
    exit 1
fi

# Make sure we're using the pooler URL
DB_URL="$SUPABASE_DB_URL"

# Temporarily store results - use arrays
declare -a pids

# Count successful and failed operations
success_count=0
failure_count=0

echo "Running 20 concurrent queries..."

# Launch 20 concurrent queries
for i in {1..20}; do
    # Execute query via psql with minimal output
    {
        result=$(psql "$DB_URL" -t -A -c "SELECT pg_backend_pid();" 2>/dev/null)
        if [ $? -eq 0 ] && [ -n "$result" ]; then
            echo "$result" > "/tmp/query_result_$i.success"
        else
            echo "failed" > "/tmp/query_result_$i.failed"
        fi
    } &
    pids[$i]=$!
done

# Wait for all background jobs to finish
for pid_idx in {1..20}; do
    wait ${pids[$pid_idx]}
done

# Count successes
for i in {1..20}; do
    if [ -f "/tmp/query_result_$i.success" ]; then
        ((success_count++))
    else
        ((failure_count++))
    fi
done

# Output result
if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: Only $success_count out of 20 queries succeeded"
    
    # Print sample errors if available
    for i in {1..20}; do
        if [ -f "/tmp/query_result_$i.failed" ]; then
            # Try to get error detail separately in another attempt
            psql "$DB_URL" -t -c "SELECT pg_backend_pid();" 2>&1
            break
        fi
    done
    exit 1
fi

# Cleanup
rm -f /tmp/query_result_*
EOF

psql "$SUPABASE_DB_URL" -t -c "SELECT version();"

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

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

set -e  # exit immediately if a command exits with a non-zero status

if [ -z "$SUPABASE_DB_URL" ]; then
    echo "Error: SUPABASE_DB_URL is not set"
    exit 1
fi

# Define arrays for tracking PIDs
pids=()
results_dir="/tmp/pool_test_$$"
mkdir -p "$results_dir"

echo "Running 20 concurrent queries..."

# Launch 20 concurrent one-shot queries
for i in {1..20}; do
    # Capture both output and errors separately - redirect stderr to a unique file
    {
        psql "$SUPABASE_DB_URL" -t -c "SELECT pg_backend_pid();" > "$results_dir/result_$i.out" 2> "$results_dir/result_$i.err"
        exit_code=$?
        if [ $exit_code -eq 0 ]; then
            echo $exit_code > "$results_dir/status_$i.code"
        else
            echo $exit_code > "$results_dir/status_$i.code"  
        fi
    } & 
    pids[$i]=$!
done

# Wait for all PIDs to complete
for pid in "${pids[@]}"; do
    if [ -n "$pid" ]; then
        wait "$pid"
    fi
done

# Count successful executions
success_count=0
total_queries=20

for i in $(seq 1 $total_queries); do
    status=$(cat "$results_dir/status_$i.code" 2>/dev/null || echo "1")
    output_exists=$(test -s "$results_dir/result_$i.out" && echo "yes" || echo "no")
    error_exists=$(test -s "$results_dir/result_$i.err" && echo "yes" || echo "no")
    
    if [ "$status" -eq 0 ] && [ "$output_exists" = "yes" ] && [ "$error_exists" = "no" ]; then
        ((success_count++))
    fi
done

# Output final result only if exactly 20 succeeded
if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    cat "$results_dir/result_"*".err" 2>/dev/null | head -20  # Show sample errors
    exit 1
fi

# Cleanup happens after exit due to set -e and script termination
rm -rf "$results_dir"
EOF

chmod +x pool_smoke.sh

./pool_smoke.sh

Running 20 concurrent queries...

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

if [ -z "$SUPABASE_DB_URL" ]; then
    echo "Error: SUPABASE_DB_URL is not set"
    exit 1
fi

# Define arrays for tracking PIDs
pids=()
results_dir="/tmp/pool_test_$$"
mkdir -p "$results_dir"

echo "Running 20 concurrent queries..."

counter=0
# Launch 20 concurrent one-shot queries
for i in {1..20}; do
    # Execute the query in background with dedicated output files
    (
        # Run psql with proper flags - use -A for unaligned output and -t for tuples only
        psql "$SUPABASE_DB_URL" -A -t -q -c "SELECT pg_backend_pid();" > "$results_dir/output_$i.txt" 2> "$results_dir/error_$i.txt"
        echo $? > "$results_dir/exitcode_$i.txt"
    ) & 
    pids+=($!)
done

# Wait for all processes to complete
for pid in "${pids[@]}"; do
    wait "$pid"
done

# Count successes - check for successful execution based on exit codes and absence of error content
success_count=0
for i in {1..20}; do
    exit_code=$(cat "$results_dir/exitcode_$i.txt" 2>/dev/null || echo "1")
    has_error_content=$(test -s "$results_dir/error_$i.txt" && echo "true" || echo "false") 
    has_output=$(test -s "$results_dir/output_$i.txt" && echo "true" || echo "false")
    
    if [ "$exit_code" -eq 0 ] && [ "$has_error_content" = "false" ] && [ "$has_output" = "true" ]; then
        ((success_count++))
    fi
done

# Output the desired message if all 20 successfully connected
if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: $success_count out of 20 succeeded"
    # Show some error details if they exist 
    for i in {1..20}; do
        if [ -s "$results_dir/error_$i.txt" ]; then
            echo "Sample error: $(head -1 "$results_dir/error_$i.txt")"
            break
        fi
    done
    exit 1
fi

# Cleanup
rm -rf "$results_dir"
EOF

chmod +x pool_smoke.sh && ./pool_smoke.sh

Running 20 concurrent queries...
Failed: 15 out of 20 succeeded
Sample error: 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

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

if [ -z "$SUPABASE_DB_URL" ]; then
    echo "Error: SUPABASE_DB_URL is not set"
    exit 1
fi

# Results directory to track query outcomes
results_dir="/tmp/pool_test_$$"
mkdir -p "$results_dir"
trap 'rm -rf "$results_dir"' EXIT

success_count=0
total_queries=20

echo "Running 20 concurrent queries..."

# Launch all 20 queries concurrently 
for i in {1..20}; do
    {
        if psql "$SUPABASE_DB_URL" -qt -v ON_ERROR_STOP=1 \
             -c "SELECT pg_backend_pid();" \
             > "$results_dir/output_$i" 2> "$results_dir/error_$i"; then
            echo "SUCCESS" > "$results_dir/status_$i"
        else
            echo "FAILURE" > "$results_dir/status_$i"
        fi
    } &
done

# Wait for all background jobs to finish
wait

# Count successful responses
for i in {1..20}; do
    if [ -f "$results_dir/status_$i" ] && [ "$(cat $results_dir/status_$i)" = "SUCCESS" ] && \
       [ -f "$results_dir/output_$i" ] && [ -s "$results_dir/output_$i" ] && \
       [ ! -s "$results_dir/error_$i" ]; then
        ((success_count++))
    fi
done

# Print result 
if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: $success_count/$total_queries succeeded"
    # Output first few errors encountered if any
    for i in {1..20}; do
        if [ -s "$results_dir/error_$i" ]; then
            cat "$results_dir/error_$i"
            break
        fi
    done
    exit 1
fi
EOF

# Check the available information about different formats
echo "Project Ref: $SUPABASE_PROJECT_REF"

Project Ref: <ref>

SUPABASE_ANON_KEY_JWT=$(echo $SUPABASE_ANON_KEY | cut -d. -f2)
echo "Testing with anon key JWT portion: ${SUPABASE_ANON_KEY_JWT:0:20}..."
# Actually, we need full JWT for connection

Testing with anon key JWT portion: eyJpc3MiOiJzdXBhYmFz...

cat > pool_smoke.sh << 'EOF'
#!/bin/bash

if [ -z "$SUPABASE_PROJECT_REF" ] || [ -z "$SUPABASE_SERVICE_ROLE_KEY" ]; then
    echo "Error: Required environment variables for transaction mode are not set"
    exit 1
fi

# Construct a transaction-pool accessible URL
# Using the SUPABASE_SERVICE_ROLE_KEY as password with postgres user for project reference
# Format: postgresql://[user:<redacted>@host:port/database][?options]
DB_URL="postgresql://${SUPABASE_PROJECT_REF}.service_role:<redacted>@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

# Ensure the constructed URL uses pooler
if [[ ! "$DB_URL" =~ \.pooler\. ]]; then
    echo "Error: Cannot form a valid transaction-mode pooler URL"
    exit 1
fi

results_dir="/tmp/pool_test_$$"
mkdir -p "$results_dir"
trap 'rm -rf "$results_dir"' EXIT

success_count=0
total_queries=20

echo "Running 20 concurrent queries..."

# Start 20 concurrent queries using transaction-mode connection pool
for i in {1..20}; do
    {
        # Execute query with minimal options to complete transaction quickly
        psql "$DB_URL" -qt -c "SELECT pg_backend_pid();" > "$results_dir/output_$i" 2> "$results_dir/error_$i"
        echo $? > "$results_dir/exit_$i"
    } &
done

# Wait for all to complete
wait

# Process results
for i in {1..20}; do
    exit_code=$(cat "$results_dir/exit_$i" 2>/dev/null || echo "1")
    has_error=$(test -s "$results_dir/error_$i" && echo "true" || echo "false")
    has_output=$(test -s "$results_dir/output_$i" && echo "true" || echo "false")
    
    if [ "$exit_code" -eq 0 ] && [ "$has_error" = "false" ] && [ "$has_output" = "true" ]; then
        ((success_count++))
    fi
done

if [ $success_count -eq 20 ]; then
    echo "POOL OK 20/20"
    exit 0
else
    echo "Failed: $success_count/$total_queries succeeded"
    # Show a sample error
    for i in {1..20}; do
        if [ -s "$results_dir/error_$i" ]; then 
            cat "$results_dir/error_$i"
            break
        fi
    done
    exit 1
fi
EOF