{"id":"postgres-connect-and-ping","version":"1.0.0","primitive":"code_execution","description":"PostgreSQL connection string e.g. \"postgresql://user:pass@host:5432/dbname\"","registry_refs":["psycopg2-binary"],"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\""}],"executable":"# ============================================\n# checklist:     postgres-connect-and-ping\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Connect to a PostgreSQL database, verify the connection is live, and surface the exact error message if it fails\n# registry_refs: psycopg2-binary\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#\n# OUTPUTS:\n#   connected     — true if connection succeeded\n#   server_version — PostgreSQL server version string\n#   latency_ms    — round-trip ping latency in milliseconds\n#   error         — exact error message if connection failed, null otherwise\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-2.4 Information Withholding           — exact error surfaced, not swallowed\n# FM-1.5 Unaware of Termination Conditions — explicit success/failure conditions\n# FM-3.2 No or Incomplete Verification     — server version verified after connect\n# FM-3.3 Incorrect Verification            — latency measured, not assumed\n#\n# ref: https://arxiv.org/abs/2503.13657\n# ============================================\n\nimport sys\nimport os\nimport subprocess\nimport time\nimport urllib.request\nimport json\n\n# ─────────────────────────────────────────\n# PRE_EXECUTION\n# ─────────────────────────────────────────\n\n# 1. Fetch registry entry\nREGISTRY_URL = \"https://checklist.day/api/registry/psycopg2-binary\"\n\nfor attempt in range(2):\n    try:\n        req = urllib.request.Request(REGISTRY_URL, headers={\"User-Agent\": \"checklist-agent/1.0\"})\n        with urllib.request.urlopen(req, timeout=10) as resp:\n            registry = json.loads(resp.read())\n            break\n    except Exception as e:\n        if attempt == 1:\n            print(f\"ABORT: registry unreachable after 2 attempts — {e}\")\n            sys.exit(1)\n        time.sleep(2)\n\n# 2. Surface warnings\nwarnings = registry.get(\"warnings\", [])\nif warnings:\n    print(\"[psycopg2-binary] WARNINGS:\")\n    for w in warnings if isinstance(warnings, list) else [warnings]:\n        print(f\"  ⚠ {w}\")\n\n# ─────────────────────────────────────────\n# EXECUTION\n# ─────────────────────────────────────────\n\n# 3. Install\n# FOOTGUN: use psycopg2-binary not psycopg2 — binary includes libpq, no build tools needed\nsubprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\", \"psycopg2-binary>=2.9.9\"])\n\nimport psycopg2\nfrom psycopg2 import OperationalError\n\n# 4. Validate inputs\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\nif not DATABASE_URL:\n    print(\"ABORT: DATABASE_URL env var not set\")\n    print(\"  format: postgresql://user:pass@host:5432/dbname\")\n    sys.exit(1)\n\n# FOOTGUN: psycopg2 does not accept postgres:// scheme — must be postgresql://\nif DATABASE_URL.startswith(\"postgres://\"):\n    DATABASE_URL = DATABASE_URL.replace(\"postgres://\", \"postgresql://\", 1)\n    print(\"  [fixed] postgres:// → postgresql://\")\n\n# 5. Connect and ping\nconnected = False\nserver_version = None\nlatency_ms = None\nerror = None\nconn = None\n\ntry:\n    t0 = time.perf_counter()\n    conn = psycopg2.connect(DATABASE_URL, connect_timeout=10)\n    latency_ms = round((time.perf_counter() - t0) * 1000, 1)\n\n    # FOOTGUN: use a cursor to actually verify the connection is usable, not just opened\n    with conn.cursor() as cur:\n        cur.execute(\"SELECT version()\")\n        server_version = cur.fetchone()[0]\n\n    connected = True\n    print(f\"  connected in {latency_ms}ms\")\n    print(f\"  server: {server_version}\")\n\nexcept OperationalError as e:\n    # Surface exact error — agents need the real message to diagnose\n    error = str(e).strip()\n    print(f\"  connection failed: {error}\")\n\nfinally:\n    if conn:\n        conn.close()\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\nresult = {\n    \"connected\":      connected,\n    \"server_version\": server_version,\n    \"latency_ms\":     latency_ms,\n    \"error\":          error,\n}\nprint(json.dumps(result, indent=2))\n\nif not connected:\n    print(f\"FAIL: could not connect — {error}\")\n    sys.exit(1)\n\nassert server_version is not None, \"FAIL: connected but server_version is null\"\nassert latency_ms is not None, \"FAIL: connected but latency_ms is null\"\nassert latency_ms < 5000, f\"FAIL: latency {latency_ms}ms exceeds 5s threshold\"\n\nprint(\"PASS\")\n"}