{"id":"postgres-connection-pool","version":"1.0.0","primitive":"code_execution","description":"Number of connections in the pool","registry_refs":["psycopg2-binary","sqlalchemy"],"tags":[],"solves":[],"auth_required":true,"verified":false,"last_verified":"null","next_check":"2026-07-30","eval_result":"null","eval_env":"null","mast":[],"ref":"https://arxiv.org/abs/2503.13657","inputs":[{"name":"DATABASE_URL","required":true,"description":"PostgreSQL connection string e.g. \"postgresql://user:pass@host:5432/dbname\""},{"name":"POOL_SIZE","default":"5","required":false,"description":"Number of connections in the pool"}],"executable":"# ============================================\n# checklist:     postgres-connection-pool\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Configure and verify a PostgreSQL connection pool using psycopg2 ThreadedConnectionPool and SQLAlchemy pool settings\n# registry_refs: psycopg2-binary, sqlalchemy\n# auth_required: true\n# verified:      false\n# last_verified: null\n# next_check:    2026-07-30\n# eval_result:   null\n# eval_env:      null\n#\n# inputs:\n#   - name: DATABASE_URL\n#     required: true\n#     description: PostgreSQL connection string e.g. \"postgresql://user:pass@host:5432/dbname\"\n#   - name: POOL_SIZE\n#     required: false\n#     default: \"5\"\n#     description: Number of connections in the pool\n#\n# OUTPUTS:\n#   pool_size         — configured pool size\n#   connections_used  — peak concurrent connections during test\n#   pool_exhausted    — true if pool was exhausted (expected if workers > pool_size)\n#   sqlalchemy_pool_ok — true if SQLAlchemy pool config verified\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-1.5 Unaware of Termination Conditions — pool exhaustion caught and reported, not silently hung\n# FM-3.2 No or Incomplete Verification     — concurrent connections measured, not assumed\n# FM-3.3 Incorrect Verification            — pool size verified via pool.status()\n#\n# ref: https://arxiv.org/abs/2503.13657\n# ============================================\n\nimport sys\nimport os\nimport subprocess\nimport time\nimport threading\nimport urllib.request\nimport json\n\n# ─────────────────────────────────────────\n# PRE_EXECUTION\n# ─────────────────────────────────────────\n\nfor attempt in range(2):\n    try:\n        for slug in [\"psycopg2-binary\", \"sqlalchemy\"]:\n            req = urllib.request.Request(\n                f\"https://checklist.day/api/registry/{slug}\",\n                headers={\"User-Agent\": \"checklist-agent/1.0\"}\n            )\n            with urllib.request.urlopen(req, timeout=10) as resp:\n                registry = json.loads(resp.read())\n                warnings = registry.get(\"warnings\", [])\n                if warnings:\n                    print(f\"[{slug}] WARNINGS:\")\n                    for w in warnings if isinstance(warnings, list) else [warnings]:\n                        print(f\"  ⚠ {w}\")\n        break\n    except Exception as e:\n        if attempt == 1:\n            print(f\"ABORT: registry unreachable — {e}\")\n            sys.exit(1)\n        time.sleep(2)\n\n# ─────────────────────────────────────────\n# EXECUTION\n# ─────────────────────────────────────────\n\nsubprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\",\n                       \"psycopg2-binary>=2.9.9\", \"sqlalchemy>=2.0.0\"])\n\nimport psycopg2\nfrom psycopg2 import pool as pg_pool\nfrom sqlalchemy import create_engine, text\n\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\nPOOL_SIZE    = int(os.environ.get(\"POOL_SIZE\", \"5\"))\n\nif not DATABASE_URL:\n    print(\"ABORT: DATABASE_URL env var not set\")\n    sys.exit(1)\n\nif DATABASE_URL.startswith(\"postgres://\"):\n    DATABASE_URL = DATABASE_URL.replace(\"postgres://\", \"postgresql://\", 1)\n\n# Method 1: psycopg2 ThreadedConnectionPool\n# FOOTGUN: use ThreadedConnectionPool for multithreaded apps, SimpleConnectionPool is not thread-safe\npool = pg_pool.ThreadedConnectionPool(minconn=1, maxconn=POOL_SIZE, dsn=DATABASE_URL)\n\nconnections_used = 0\nlock = threading.Lock()\npool_exhausted = False\nresults = []\n\ndef worker(worker_id):\n    global connections_used, pool_exhausted\n    try:\n        conn = pool.getconn()\n        with lock:\n            connections_used += 1\n        try:\n            with conn.cursor() as cur:\n                cur.execute(\"SELECT pg_sleep(0.05), %s\", (worker_id,))\n                results.append(cur.fetchone()[1])\n        finally:\n            # FOOTGUN: always putconn() — not putting back leaks connections\n            pool.putconn(conn)\n    except pg_pool.PoolError:\n        with lock:\n            pool_exhausted = True\n\nthreads = [threading.Thread(target=worker, args=(i,)) for i in range(POOL_SIZE)]\nfor t in threads:\n    t.start()\nfor t in threads:\n    t.join()\n\npool.closeall()\nprint(f\"  psycopg2 pool: {connections_used} peak connections, exhausted={pool_exhausted}\")\n\n# Method 2: SQLAlchemy pool config\n# FOOTGUN: pool_pre_ping=True prevents stale connection errors after idle timeout\n# FOOTGUN: pool_recycle avoids connections being killed by firewall/load balancer timeouts\nengine = create_engine(\n    DATABASE_URL,\n    pool_size=POOL_SIZE,\n    max_overflow=2,\n    pool_pre_ping=True,\n    pool_recycle=300,  # recycle after 5 minutes\n)\n\nsqlalchemy_pool_ok = False\nwith engine.connect() as conn:\n    result = conn.execute(text(\"SELECT 1\"))\n    sqlalchemy_pool_ok = result.fetchone()[0] == 1\n\nengine.dispose()\nprint(f\"  SQLAlchemy pool: pool_size={POOL_SIZE}, pre_ping=True, recycle=300s — ok={sqlalchemy_pool_ok}\")\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\nassert connections_used > 0, \"FAIL: no connections were established\"\nassert sqlalchemy_pool_ok, \"FAIL: SQLAlchemy pool test query failed\"\nassert len(results) > 0, f\"FAIL: no worker results returned\"\n\nresult = {\n    \"pool_size\":          POOL_SIZE,\n    \"connections_used\":   connections_used,\n    \"workers_completed\":  len(results),\n    \"pool_exhausted\":     pool_exhausted,\n    \"sqlalchemy_pool_ok\": sqlalchemy_pool_ok,\n}\nprint(json.dumps(result, indent=2))\nprint(\"PASS\")\n"}