{"id":"postgres-create-table-idempotent","version":"1.0.0","primitive":"code_execution","description":"Set to \"true\" to drop the table after verification (useful for tests)","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\""},{"name":"TABLE_NAME","required":true,"description":"Name of the table to create e.g. \"users\""},{"name":"CLEANUP","default":"false","required":false,"description":"Set to \"true\" to drop the table after verification (useful for tests)"}],"executable":"# ============================================\n# checklist:     postgres-create-table-idempotent\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Create a PostgreSQL table idempotently using CREATE TABLE IF NOT EXISTS and verify the schema matches expectations\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#   - name: TABLE_NAME\n#     required: true\n#     description: Name of the table to create e.g. \"users\"\n#   - name: CLEANUP\n#     required: false\n#     default: \"false\"\n#     description: Set to \"true\" to drop the table after verification (useful for tests)\n#\n# OUTPUTS:\n#   table_name    — name of the table created\n#   already_existed — true if table existed before this run\n#   columns       — list of column names and types verified\n#   cleanup_done  — true if table was dropped after test\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-1.1 Disobey Task Specification        — IF NOT EXISTS prevents duplicate create errors on retry\n# FM-1.3 Step Repetition                   — idempotent by design, safe to run multiple times\n# FM-3.2 No or Incomplete Verification     — schema verified via information_schema after create\n# FM-3.3 Incorrect Verification            — exact column name + type match asserted\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\nsubprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\", \"psycopg2-binary>=2.9.9\"])\n\nimport psycopg2\n\n# 4. Validate inputs\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\nTABLE_NAME   = os.environ.get(\"TABLE_NAME\")\nCLEANUP      = os.environ.get(\"CLEANUP\", \"false\").lower() == \"true\"\n\nif not DATABASE_URL:\n    print(\"ABORT: DATABASE_URL env var not set\")\n    sys.exit(1)\nif not TABLE_NAME:\n    print(\"ABORT: TABLE_NAME 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# 5. Connect\nconn = psycopg2.connect(DATABASE_URL, connect_timeout=10)\nconn.autocommit = False\n\ntry:\n    with conn.cursor() as cur:\n\n        # 6. Check if table already exists\n        cur.execute(\"\"\"\n            SELECT EXISTS (\n                SELECT 1 FROM information_schema.tables\n                WHERE table_schema = 'public' AND table_name = %s\n            )\n        \"\"\", (TABLE_NAME,))\n        already_existed = cur.fetchone()[0]\n\n        # 7. Idempotent create\n        # FOOTGUN: always IF NOT EXISTS — agents retry, this must not fail on second run\n        cur.execute(f\"\"\"\n            CREATE TABLE IF NOT EXISTS {TABLE_NAME} (\n                id         SERIAL PRIMARY KEY,\n                name       TEXT NOT NULL,\n                value      INTEGER NOT NULL DEFAULT 0,\n                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n            )\n        \"\"\")\n        conn.commit()\n        print(f\"  table '{TABLE_NAME}' {'already existed' if already_existed else 'created'}\")\n\n        # 8. Verify schema via information_schema\n        # FOOTGUN: check actual columns, not just that the table exists\n        cur.execute(\"\"\"\n            SELECT column_name, data_type\n            FROM information_schema.columns\n            WHERE table_schema = 'public' AND table_name = %s\n            ORDER BY ordinal_position\n        \"\"\", (TABLE_NAME,))\n        columns = [{\"name\": row[0], \"type\": row[1]} for row in cur.fetchall()]\n        print(f\"  verified {len(columns)} columns: {[c['name'] for c in columns]}\")\n\n        # 9. Cleanup if requested\n        cleanup_done = False\n        if CLEANUP:\n            cur.execute(f\"DROP TABLE IF EXISTS {TABLE_NAME}\")\n            conn.commit()\n            cleanup_done = True\n            print(f\"  table '{TABLE_NAME}' dropped (CLEANUP=true)\")\n\nfinally:\n    conn.close()\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\nexpected_columns = {\"id\", \"name\", \"value\", \"created_at\"}\nactual_columns   = {c[\"name\"] for c in columns}\nassert expected_columns == actual_columns, (\n    f\"FAIL: schema mismatch — expected {expected_columns}, got {actual_columns}\"\n)\nassert len(columns) == 4, f\"FAIL: expected 4 columns, got {len(columns)}\"\n\nresult = {\n    \"table_name\":     TABLE_NAME,\n    \"already_existed\": already_existed,\n    \"columns\":        columns,\n    \"cleanup_done\":   cleanup_done,\n}\nprint(json.dumps(result, indent=2))\nprint(\"PASS\")\n"}