#!/usr/bin/env bash
# run.sh — "invalid input syntax for type json" in PostgreSQL, measured. Now-Next, November 2026.
#
#   ./run.sh                     # PostgreSQL 16, 17 and 18, plus node-postgres and psycopg (a few minutes)
#   VERSIONS=18 ./run.sh         # one version
#   BENCH=0 ./run.sh             # skip the 1,000,000-row timings
#
# Needs: bash and docker. Pulls postgres:16, :17, :18, node:22-slim and python:3.13-slim,
# and installs the drivers from npm and PyPI inside throwaway containers. It starts
# containers named ${PREFIX}pg<version>, ${PREFIX}node and ${PREFIX}py on a network of
# its own (${PREFIX}net), publishes no ports, and removes all of them again.
# The password is the literal word "dummy": the containers are unreachable from outside.
#
# Output in results/ next to this script:
#   env.txt              machine, docker and the image digests
#   pg<v>-version.txt    version() and server_encoding
#   pg<v>.txt            the full psql output of setup.sql (errors in verbose form, timings)
#   driver-node.txt      node-postgres and Kysely against json and jsonb columns
#   driver-python.txt    psycopg 3 against json and jsonb columns
#
# We measured with PostgreSQL 16.15, 17.11 and 18.6, pg 8.23.1, kysely 0.29.8 and
# psycopg 3.3.8 on 12 November 2026. The image tags move; the driver versions are pinned
# below and can be changed with PG_NODE, KYSELY and PSYCOPG.
set -euo pipefail
export LC_ALL=C

HERE="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
OUT="$HERE/results"; mkdir -p "$OUT"
PREFIX="${PREFIX:-json-input-}"
NET="${PREFIX}net"
VERSIONS="${VERSIONS:-16 17 18}"
BENCH="${BENCH:-1}"
PG_NODE="${PG_NODE:-8.23.1}"
KYSELY="${KYSELY:-0.29.8}"
PSYCOPG="${PSYCOPG:-3.3.8}"
WORK="$(mktemp -d "${TMPDIR:-/tmp}/json-input.XXXXXX")"
chmod 755 "$WORK"

remove_containers() {
  for c in $(docker ps -aq --filter "name=^${PREFIX}"); do docker rm -f -v "$c" >/dev/null; done
  docker network rm "$NET" >/dev/null 2>&1 || true
}
cleanup() { remove_containers; rm -rf "$WORK"; }
trap cleanup EXIT
remove_containers
docker network create "$NET" >/dev/null

log() { echo "[$(date +%H:%M:%S)] $*"; }

{
  echo "date: $(date -Is)"
  echo "host: $(uname -srm)"
  echo "cpu: $(grep -m1 'model name' /proc/cpuinfo | cut -d: -f2 | sed 's/^ //') x $(nproc)"
  echo "mem: $(free -g | awk '/Mem:/ {print $2" GiB total, "$7" GiB available"}')"
  echo "load: $(cut -d' ' -f1-3 /proc/loadavg)"
  echo "docker: $(docker version --format '{{.Server.Version}}')"
} > "$OUT/env.txt"

for v in $VERSIONS; do
  c="${PREFIX}pg$v"
  log "PostgreSQL $v"
  docker run -d --name "$c" --network "$NET" -e POSTGRES_PASSWORD=dummy "postgres:$v" >/dev/null
  echo "image postgres:$v $(docker image inspect "postgres:$v" --format '{{index .RepoDigests 0}}')" >> "$OUT/env.txt"
  until docker exec "$c" psql -h 127.0.0.1 -U postgres -Atc 'select 1' >/dev/null 2>&1; do sleep 1; done
  docker cp "$HERE/setup.sql" "$c:/setup.sql" >/dev/null
  { docker exec "$c" psql -X -U postgres -Atc 'select version()'
    docker exec "$c" psql -X -U postgres -Atc 'show server_encoding'; } > "$OUT/pg$v-version.txt"
  # -t gives psql a terminal, so stdout is line-buffered and every ERROR lands under its own heading
  echo "load before: $(cut -d' ' -f1-3 /proc/loadavg)" > "$OUT/pg$v.txt"
  docker exec -t "$c" psql -X -U postgres -d postgres -v bench="$BENCH" -f /setup.sql 2>&1 | tr -d '\r' >> "$OUT/pg$v.txt"
done

DB="${PREFIX}pg$(echo $VERSIONS | awk '{print $NF}')"

cat > "$WORK/driver-node.mjs" <<'JS'
// node-postgres (pg) and Kysely against json and jsonb columns
import pg from 'pg';
import { readFileSync } from 'node:fs';
const version = (n) => JSON.parse(readFileSync(new URL(`./node_modules/${n}/package.json`, import.meta.url))).version;
const { Kysely, PostgresDialect, sql } = await import('kysely');

const cfg = { host: process.env.PGHOST, user: 'postgres', password: 'dummy', database: 'postgres' };
const client = new pg.Client(cfg);
await client.connect();
console.log(`node ${process.version} | pg ${version('pg')} | kysely ${version('kysely')}`);
console.log(`server: ${(await client.query('select version()')).rows[0].version}`);
await client.query('drop table if exists t; create table t (id serial primary key, j json, jb jsonb)');

// what pg itself turns the parameter into before it is sent (pg/lib/utils, not part of pg's public API)
import { createRequire } from 'node:module';
const { prepareValue } = createRequire(import.meta.url)('pg/lib/utils');
const sent = (v) => { try { return JSON.stringify(prepareValue(v)); } catch (e) { return `(prepareValue: ${e.message})`; } };

const cases = [
  ['JS array of numbers [1,2]', [1, 2]],
  ['JS array of strings ["a","b"]', ['a', 'b']],
  ['JS array of objects [{a:1},{b:2}]', [{ a: 1 }, { b: 2 }]],
  ['empty JS array []', []],
  ['nested array [[1,2],[3,4]]', [[1, 2], [3, 4]]],
  ['object {a:1, tags:["x"]}', { a: 1, tags: ['x'] }],
  ['JSON.stringify({a:1})', JSON.stringify({ a: 1 })],
  ['JSON.stringify([1,2])', JSON.stringify([1, 2])],
  ['JSON.stringify(JSON.stringify({a:1}))', JSON.stringify(JSON.stringify({ a: 1 }))],
  ['bare string "hello"', 'hello'],
  ['undefined', undefined],
  ['number NaN', NaN],
  ['object with NaN {a: NaN}', { a: NaN }],
  ['object with undefined {a: undefined}', { a: undefined }],
  ['string with \\u0000', JSON.stringify({ a: 'x\u0000y' })],
  ['Date', new Date('2026-11-12T00:00:00Z')],
  ['boolean true', true],
];

for (const col of ['j', 'jb']) {
  for (const [label, v] of cases) {
    try {
      const r = await client.query(`insert into t (${col}) values ($1) returning ${col}::text as stored, json_typeof(${col}::json) as type`, [v]);
      console.log(`[${col}] ${label}\n    sent: ${sent(v)}\n    OK  stored: ${r.rows[0].stored}  (type ${r.rows[0].type})`);
    } catch (e) {
      console.log(`[${col}] ${label}\n    sent: ${sent(v)}\n    ERROR code=${e.code} message=${JSON.stringify(e.message)} detail=${JSON.stringify(e.detail)} where=${JSON.stringify(e.where)}`);
    }
  }
}

console.log('\n--- explicit cast $1::jsonb with a JS array');
try { await client.query('select $1::jsonb as v', [[1, 2]]); } catch (e) { console.log(`ERROR code=${e.code} message=${JSON.stringify(e.message)} detail=${JSON.stringify(e.detail)}`); }
console.log('--- explicit cast $1::jsonb with an empty JS array');
console.log(JSON.stringify((await client.query('select $1::jsonb as v, jsonb_typeof($1::jsonb) as t', [[]])).rows[0]));
console.log('--- an empty array inside an object {a: []}, into json and jsonb');
for (const col of ['j', 'jb']) {
  const r = await client.query(`insert into t (${col}) values ($1) returning ${col}::text as stored`, [{ a: [] }]);
  console.log(`[${col}] sent: ${sent({ a: [] })}  stored: ${r.rows[0].stored}`);
}
console.log('--- the parameter in the error: log_parameter_max_length_on_error default, then -1');
for (const setting of [null, '-1']) {
  if (setting) await client.query(`set log_parameter_max_length_on_error = ${setting}`);
  try { await client.query('select $1::jsonb as v', [[1, 2]]); } catch (e) { console.log(`${setting ?? 'default'}: where=${JSON.stringify(e.where)}`); }
}
await client.end();

console.log('\n--- Kysely (PostgresDialect over pg.Pool)');
const db = new Kysely({ dialect: new PostgresDialect({ pool: new pg.Pool(cfg) }) });
const kcases = [
  ['JS array [1,2]', [1, 2]],
  ['empty JS array []', []],
  ['object {a:1}', { a: 1 }],
  ['JSON.stringify([1,2])', JSON.stringify([1, 2])],
];
for (const [label, v] of kcases) {
  const q = db.insertInto('t').values({ jb: v }).returning(sql`jb::text`.as('stored'));
  const params = JSON.stringify(q.compile().parameters);
  try {
    const r = await q.executeTakeFirst();
    console.log(`[kysely jb] ${label}\n    params=${params}\n    OK  stored: ${r.stored}`);
  } catch (e) {
    console.log(`[kysely jb] ${label}\n    params=${params}\n    ERROR code=${e.code} message=${JSON.stringify(e.message)} detail=${JSON.stringify(e.detail)}`);
  }
}
await db.destroy();
JS

cat > "$WORK/driver-python.py" <<'PY'
# psycopg 3 against json and jsonb columns
import json, os, sys
import psycopg
from psycopg.types.json import Json, Jsonb

print(f"python {sys.version.split()[0]} | psycopg {psycopg.__version__} | libpq {psycopg.pq.version()} ({psycopg.pq.__impl__})")
conn = psycopg.connect(host=os.environ["PGHOST"], user="postgres", password="dummy", dbname="postgres", autocommit=True)
print("server:", conn.execute("select version()").fetchone()[0])
conn.execute("drop table if exists tp; create table tp (id serial primary key, j json, jb jsonb)")

d = {"a": 1, "ok": True, "none": None}
cases = [
    ("str(dict)", str(d)),
    ("json.dumps(dict)", json.dumps(d)),
    ("Json(dict)", Json(d)),
    ("Jsonb(dict)", Jsonb(d)),
    ("dict without a wrapper", d),
    ("list without a wrapper [1,2]", [1, 2]),
    ("list without a wrapper ['a','b']", ["a", "b"]),
    ("empty list []", []),
    ("Jsonb([1,2])", Jsonb([1, 2])),
    ("json.dumps with float('nan')", json.dumps({"x": float("nan")})),
    ("Jsonb({'x': nan})", Jsonb({"x": float("nan")})),
    ("json.dumps inside extra single quotes", f"'{json.dumps(d)}'"),
    ("json.dumps twice", json.dumps(json.dumps(d))),
]
for col in ("j", "jb"):
    for label, v in cases:
        shown = v if isinstance(v, str) else repr(v)
        try:
            r = conn.execute(f"insert into tp ({col}) values (%s) returning {col}::text, json_typeof({col}::json)", (v,)).fetchone()
            print(f"[{col}] {label}\n    sent: {shown}\n    OK  stored: {r[0]}  (type {r[1]})")
        except psycopg.Error as e:
            diag = e.diag
            print(f"[{col}] {label}\n    sent: {shown}\n    ERROR {type(e).__module__}.{type(e).__name__} sqlstate={e.sqlstate} message={diag.message_primary!r} detail={diag.message_detail!r} context={diag.context!r}")
            if e.sqlstate is None:
                print(f"    str(e)={str(e)!r}")

print("\n--- which dumper psycopg picks for a list, and what it produces (client-side, not the wire)")
from psycopg.adapt import PyFormat, Transformer
tx = Transformer(conn)
for v in ([], [1, 2], ["a", "b"], Jsonb({"x": float("nan")})):
    dumper = tx.get_dumper(v, PyFormat.AUTO)
    print(f"{v!r:<28} {type(dumper).__name__} oid {dumper.oid}  {bytes(dumper.dump(v))!r}")
conn.close()
PY

log "node-postgres $PG_NODE and Kysely $KYSELY against $DB"
docker run --rm --name "${PREFIX}node" --network "$NET" -e PGHOST="$DB" \
  -e PG_NODE="$PG_NODE" -e KYSELY="$KYSELY" \
  -v "$WORK/driver-node.mjs:/src/driver-node.mjs:ro" node:22-slim \
  sh -c 'mkdir /app && cd /app && npm init -y >/dev/null && npm install --silent --no-audit --no-fund "pg@$PG_NODE" "kysely@$KYSELY" >/dev/null && cp /src/driver-node.mjs . && node driver-node.mjs' \
  > "$OUT/driver-node.txt" 2>&1

log "psycopg $PSYCOPG against $DB"
docker run --rm --name "${PREFIX}py" --network "$NET" -e PGHOST="$DB" -e PSYCOPG="$PSYCOPG" \
  -v "$WORK/driver-python.py:/src/driver-python.py:ro" python:3.13-slim \
  sh -c 'pip install -q --disable-pip-version-check --root-user-action=ignore "psycopg[binary]==$PSYCOPG" && python -I /src/driver-python.py' \
  > "$OUT/driver-python.txt" 2>&1

log "done: $OUT"