{"id":"postgres-insert-or-upsert","version":"1.0.0","primitive":"code_execution","description":"Table to use for upsert test (created and dropped automatically)","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","default":"checklist_upsert_test","required":false,"description":"Table to use for upsert test (created and dropped automatically)"}],"executable":"# ============================================\n# checklist:     postgres-insert-or-upsert\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Insert a row into PostgreSQL or update on conflict using ON CONFLICT DO UPDATE with parameterized queries\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: false\n#     default: \"checklist_upsert_test\"\n#     description: Table to use for upsert test (created and dropped automatically)\n#\n# OUTPUTS:\n#   insert_count  — rows inserted on first run\n#   upsert_count  — rows upserted on second run (conflict resolved)\n#   final_value   — value after upsert confirms update took effect\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-1.1 Disobey Task Specification        — parameterized queries prevent injection\n# FM-1.3 Step Repetition                   — ON CONFLICT DO UPDATE makes repeated inserts safe\n# FM-3.2 No or Incomplete Verification     — value verified after upsert, not just row count\n# FM-3.3 Incorrect Verification            — exact value 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\nfor attempt in range(2):\n    try:\n        req = urllib.request.Request(\n            \"https://checklist.day/api/registry/psycopg2-binary\",\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            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\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\nsubprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\", \"psycopg2-binary>=2.9.9\"])\n\nimport psycopg2\n\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\nTABLE_NAME   = os.environ.get(\"TABLE_NAME\", \"checklist_upsert_test\")\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\nconn = psycopg2.connect(DATABASE_URL, connect_timeout=10)\n\ntry:\n    with conn.cursor() as cur:\n        # Setup — idempotent table with unique constraint on key column\n        cur.execute(f\"\"\"\n            CREATE TABLE IF NOT EXISTS {TABLE_NAME} (\n                id    SERIAL PRIMARY KEY,\n                key   TEXT UNIQUE NOT NULL,\n                value INTEGER NOT NULL,\n                updated_at TIMESTAMPTZ DEFAULT NOW()\n            )\n        \"\"\")\n        conn.commit()\n\n        # Clean slate\n        cur.execute(f\"DELETE FROM {TABLE_NAME}\")\n        conn.commit()\n\n        # 1. INSERT — plain insert, no conflict expected\n        # FOOTGUN: always use parameterized queries — never f-string user data into SQL\n        cur.execute(\n            f\"INSERT INTO {TABLE_NAME} (key, value) VALUES (%s, %s)\",\n            (\"agent_key\", 100)\n        )\n        conn.commit()\n        insert_count = cur.rowcount\n        print(f\"  inserted {insert_count} row (value=100)\")\n\n        # 2. UPSERT — same key, new value — ON CONFLICT DO UPDATE\n        # FOOTGUN: must reference EXCLUDED.column to get the new value, not the old one\n        cur.execute(f\"\"\"\n            INSERT INTO {TABLE_NAME} (key, value)\n            VALUES (%s, %s)\n            ON CONFLICT (key) DO UPDATE\n                SET value      = EXCLUDED.value,\n                    updated_at = NOW()\n        \"\"\", (\"agent_key\", 999))\n        conn.commit()\n        upsert_count = cur.rowcount\n        print(f\"  upserted {upsert_count} row (value=100 → 999)\")\n\n        # 3. Verify update took effect\n        cur.execute(f\"SELECT value FROM {TABLE_NAME} WHERE key = %s\", (\"agent_key\",))\n        final_value = cur.fetchone()[0]\n        print(f\"  verified final value: {final_value}\")\n\n        # Cleanup\n        cur.execute(f\"DROP TABLE IF EXISTS {TABLE_NAME}\")\n        conn.commit()\n\nfinally:\n    conn.close()\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\nassert insert_count == 1, f\"FAIL: expected 1 insert, got {insert_count}\"\nassert upsert_count == 1, f\"FAIL: expected 1 upsert, got {upsert_count}\"\nassert final_value == 999, f\"FAIL: expected value=999 after upsert, got {final_value}\"\n\nresult = {\n    \"insert_count\": insert_count,\n    \"upsert_count\": upsert_count,\n    \"final_value\":  final_value,\n}\nprint(json.dumps(result, indent=2))\nprint(\"PASS\")\n"}