{"id":"postgres-json-column-query","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-json-column-query\n# version:       1.0.0\n# primitive:     code_execution\n# description:   Query PostgreSQL JSONB columns using -> and ->> operators and avoid the -> vs ->> type footgun\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#   arrow_returns_jsonb   — true: -> returns JSONB (not text)\n#   double_arrow_returns_text — true: ->> returns TEXT\n#   contains_operator_ok  — true: @> containment operator works\n#   index_used            — true if GIN index was created successfully\n#\n# MAST FAILURE MODES ADDRESSED:\n# FM-2.6 Reasoning-Action Mismatch         — demonstrates -> vs ->> type difference explicitly\n# FM-3.3 Incorrect Verification            — pg_typeof() used to verify return types\n#\n# ref: https://arxiv.org/abs/2503.13657\n# ============================================\n\nimport sys\nimport os\nimport subprocess\nimport time\nimport urllib.request\nimport json as pyjson\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 = pyjson.loads(resp.read())\n            warnings = registry.get(\"warnings\", [])\n            if warnings:\n                print(\"[psycopg2-binary] 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\", \"psycopg2-binary>=2.9.9\"])\n\nimport psycopg2\nimport psycopg2.extras\n\nDATABASE_URL = os.environ.get(\"DATABASE_URL\")\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\nTABLE = \"checklist_jsonb_test\"\nconn = psycopg2.connect(DATABASE_URL, connect_timeout=10)\n\ntry:\n    with conn.cursor() as cur:\n        # Setup\n        cur.execute(f\"\"\"\n            CREATE TABLE IF NOT EXISTS {TABLE} (\n                id   SERIAL PRIMARY KEY,\n                data JSONB NOT NULL\n            )\n        \"\"\")\n        # GIN index for fast JSONB queries\n        cur.execute(f\"CREATE INDEX IF NOT EXISTS idx_{TABLE}_data ON {TABLE} USING GIN (data)\")\n        cur.execute(f\"DELETE FROM {TABLE}\")\n\n        # FOOTGUN: use psycopg2.extras.Json to insert JSONB, not plain string\n        records = [\n            {\"name\": \"alice\", \"score\": 90, \"tags\": [\"admin\", \"user\"]},\n            {\"name\": \"bob\",   \"score\": 75, \"tags\": [\"user\"]},\n        ]\n        for rec in records:\n            cur.execute(\n                f\"INSERT INTO {TABLE} (data) VALUES (%s)\",\n                (psycopg2.extras.Json(rec),)\n            )\n        conn.commit()\n\n        # 1. -> returns JSONB (object/array), ->> returns TEXT\n        # FOOTGUN: comparing -> result to a string always fails — must use ->>\n        cur.execute(f\"SELECT pg_typeof(data -> 'name'), pg_typeof(data ->> 'name') FROM {TABLE} LIMIT 1\")\n        arrow_type, double_arrow_type = cur.fetchone()\n        arrow_returns_jsonb = arrow_type == \"jsonb\"\n        double_arrow_returns_text = double_arrow_type == \"text\"\n        print(f\"  -> returns: {arrow_type} (expected jsonb)\")\n        print(f\"  ->> returns: {double_arrow_type} (expected text)\")\n\n        # 2. Correct way to filter by JSONB text value — use ->>\n        cur.execute(f\"SELECT data->>'name' FROM {TABLE} WHERE data->>'name' = %s\", (\"alice\",))\n        found = cur.fetchone()\n        print(f\"  ->> filter found: {found[0] if found else None}\")\n\n        # 3. @> containment operator\n        cur.execute(\n            f\"SELECT COUNT(*) FROM {TABLE} WHERE data @> %s\",\n            (psycopg2.extras.Json({\"tags\": [\"admin\"]}),)\n        )\n        contains_count = cur.fetchone()[0]\n        contains_operator_ok = contains_count == 1\n        print(f\"  @> containment: {contains_count} match (expected 1)\")\n\n        # 4. Verify GIN index exists\n        cur.execute(f\"\"\"\n            SELECT COUNT(*) FROM pg_indexes\n            WHERE tablename = '{TABLE}' AND indexname = 'idx_{TABLE}_data'\n        \"\"\")\n        index_used = cur.fetchone()[0] == 1\n        print(f\"  GIN index exists: {index_used}\")\n\n        # Cleanup\n        cur.execute(f\"DROP TABLE IF EXISTS {TABLE}\")\n        conn.commit()\n\nfinally:\n    conn.close()\n\n# ─────────────────────────────────────────\n# POST_EXECUTION\n# ─────────────────────────────────────────\n\nassert arrow_returns_jsonb, \"FAIL: -> should return jsonb\"\nassert double_arrow_returns_text, \"FAIL: ->> should return text\"\nassert contains_operator_ok, f\"FAIL: @> containment returned {contains_count}, expected 1\"\nassert index_used, \"FAIL: GIN index was not created\"\n\nresult = {\n    \"arrow_returns_jsonb\":       arrow_returns_jsonb,\n    \"double_arrow_returns_text\": double_arrow_returns_text,\n    \"contains_operator_ok\":      contains_operator_ok,\n    \"index_used\":                index_used,\n}\nprint(pyjson.dumps(result, indent=2))\nprint(\"PASS\")\n"}