{"id":"postgres-connect-crud","version":"1.0.0","primitive":"code_execution","description":"Table name to use for CRUD test (will be created and dropped)","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":[],"executable":"# ============================================\n# checklist:     postgres-connect-crud\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Connect to a PostgreSQL database and perform create, read, update, delete operations\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: TABLE_NAME\n#     required: false\n#     default: \"checklist_test\"\n#     description: Table name to use for CRUD test (will be created and dropped)\n#\n# OUTPUTS:\n#   table_name    — table used for test\n#   rows_inserted — number of rows inserted\n#   rows_read     — number of rows read back\n#   update_ok     — whether update succeeded\n#   delete_ok     — whether delete succeeded\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-2.2 Fail to Ask for Clarification     — registry fetch provides ground truth imports\n# FM-2.4 Information Withholding           — warnings surfaced before execution\n# FM-2.6 Reasoning-Action Mismatch         — correct imports from registry enforced\n# FM-1.1 Disobey Task Specification        — idempotent table create (CREATE TABLE IF NOT EXISTS)\n# FM-1.3 Step Repetition                   — abort on registry fail after max 2 retries\n# FM-1.5 Unaware of Termination Conditions — explicit abort + finally cleanup defined\n# FM-3.2 No or Incomplete Verification     — row count verified after each operation\n# FM-3.3 Incorrect Verification            — exact match assertion on read-back values\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 entries via hard URLs\nREGISTRY_URLS = [\n    \"https://checklist.day/api/registry/psycopg2-binary\",\n    \"https://checklist.day/api/registry/sqlalchemy\",\n]\n\nregistry_data = {}\nfor url in REGISTRY_URLS:\n    slug = url.split(\"/\")[-1]\n    for attempt in range(2):\n        try:\n            req = urllib.request.Request(url, headers={\"User-Agent\": \"checklist-agent/1.0\"})\n            with urllib.request.urlopen(req, timeout=10) as resp:\n                data = json.loads(resp.read())\n                registry_data[slug] = data\n                break\n        except Exception as e:\n            if attempt == 1:\n                print(f\"ABORT: registry unreachable for {slug} after 2 attempts — {e}\")\n                sys.exit(1)\n            time.sleep(2)\n\n# 2. Assert required fields\nfor slug, data in registry_data.items():\n    for field in [\"imports\", \"install\", \"warnings\"]:\n        if not data.get(field):\n            print(f\"ABORT: registry entry '{slug}' missing required field '{field}'\")\n            sys.exit(1)\n\n# 3. Surface warnings\nfor slug, data in registry_data.items():\n    warnings = data.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\n# ─────────────────────────────────────────\n# EXECUTION\n# ─────────────────────────────────────────\n\n# 4. Auto-install deps\n# FOOTGUN: install psycopg2-binary, NOT psycopg2\n# psycopg2 requires libpq-dev + gcc build tools; psycopg2-binary is self-contained\nsubprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\",\n                       \"psycopg2-binary>=2.9.9\", \"sqlalchemy>=2.0.0\"])\n\n# 5. Correct imports per registry\n# FOOTGUN: SQLAlchemy 2.x — use sqlalchemy.text() for raw SQL, not plain strings\n# FOOTGUN: engine.execute() removed in SQLAlchemy 2.x — use engine.connect() + conn.execute()\n# FOOTGUN: psycopg2 connect string uses host/dbname kwargs, not URL — use SQLAlchemy for URL parsing\nfrom sqlalchemy import create_engine, text\nfrom sqlalchemy.exc import OperationalError, ProgrammingError\n\n# 6. Validate inputs\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\nTABLE_NAME   = os.environ.get(\"TABLE_NAME\", \"checklist_test\")\n\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: SQLAlchemy 2.x requires postgresql:// not postgres://\n# Heroku/Render/Supabase often give postgres:// URLs\nif DATABASE_URL.startswith(\"postgres://\"):\n    DATABASE_URL = DATABASE_URL.replace(\"postgres://\", \"postgresql://\", 1)\n    print(\"  [fixed] postgres:// → postgresql:// (SQLAlchemy 2.x requirement)\")\n\n# 7. Connect\n# FOOTGUN: use pool_pre_ping=True to detect stale connections\nengine = create_engine(DATABASE_URL, pool_pre_ping=True)\n\nconn = None\ntry:\n    conn = engine.connect()\n\n    # 8. Idempotent table create\n    # FOOTGUN: always use IF NOT EXISTS — agents may retry\n    conn.execute(text(f\"\"\"\n        CREATE TABLE IF NOT EXISTS {TABLE_NAME} (\n            id SERIAL PRIMARY KEY,\n            name TEXT NOT NULL,\n            value INTEGER NOT NULL\n        )\n    \"\"\"))\n    conn.commit()\n\n    # Clean slate for test — delete any existing test rows\n    conn.execute(text(f\"DELETE FROM {TABLE_NAME} WHERE name LIKE 'checklist_test_%'\"))\n    conn.commit()\n\n    # 9. INSERT\n    conn.execute(text(f\"\"\"\n        INSERT INTO {TABLE_NAME} (name, value) VALUES\n        ('checklist_test_alpha', 100),\n        ('checklist_test_beta',  200)\n    \"\"\"))\n    conn.commit()\n\n    # 10. READ — verify insert\n    # FOOTGUN: result.fetchall() must be called before conn.execute() again (cursor consumed)\n    rows = conn.execute(text(f\"SELECT name, value FROM {TABLE_NAME} WHERE name LIKE 'checklist_test_%' ORDER BY name\")).fetchall()\n    rows_inserted = len(rows)\n\n    # 11. UPDATE\n    conn.execute(text(f\"UPDATE {TABLE_NAME} SET value = 999 WHERE name = 'checklist_test_alpha'\"))\n    conn.commit()\n\n    updated = conn.execute(text(f\"SELECT value FROM {TABLE_NAME} WHERE name = 'checklist_test_alpha'\")).fetchone()\n    update_ok = updated[0] == 999\n\n    # 12. DELETE\n    conn.execute(text(f\"DELETE FROM {TABLE_NAME} WHERE name = 'checklist_test_beta'\"))\n    conn.commit()\n\n    remaining = conn.execute(text(f\"SELECT COUNT(*) FROM {TABLE_NAME} WHERE name LIKE 'checklist_test_%'\")).fetchone()\n    delete_ok = remaining[0] == 1\n\n    # Cleanup test rows\n    conn.execute(text(f\"DELETE FROM {TABLE_NAME} WHERE name LIKE 'checklist_test_%'\"))\n    conn.commit()\n\nfinally:\n    if conn:\n        conn.close()\n    engine.dispose()\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\n# 13. Verify all operations\nassert rows_inserted == 2, f\"FAIL: expected 2 rows inserted, got {rows_inserted}\"\nassert rows[0][0] == \"checklist_test_alpha\" and rows[0][1] == 100, f\"FAIL: read-back mismatch — {rows[0]}\"\nassert rows[1][0] == \"checklist_test_beta\"  and rows[1][1] == 200, f\"FAIL: read-back mismatch — {rows[1]}\"\nassert update_ok, \"FAIL: UPDATE did not persist\"\nassert delete_ok, \"FAIL: DELETE row count wrong after delete\"\n\n# 14. Structured result\nresult = {\n    \"table_name\":    TABLE_NAME,\n    \"rows_inserted\": rows_inserted,\n    \"rows_read\":     rows_inserted,\n    \"update_ok\":     update_ok,\n    \"delete_ok\":     delete_ok,\n}\nprint(json.dumps(result, indent=2))\nprint(\"PASS\")\n"}