docker run --rm --network=host ubuntu:22.04 bash -c '
  apt-get update -qq && apt-get install -y -qq curl xz-utils >/dev/null
  curl -fsSL "https://github.com/ProxySQL/dbdeployer/releases/download/v2.2.1/dbdeployer-2.2.1.linux_amd64.tar.gz" | tar -xz -C /usr/local/bin/
  chmod +x /usr/local/bin/dbdeployer*
  ln -sf /usr/local/bin/dbdeployer-2.2.1.linux_amd64 /usr/local/bin/dbdeployer
  echo "=== available flavors ==="; dbdeployer downloads list | grep -i -E "postgres|pg" || echo "NO postgres downloads"
  echo "=== deploy help (flavor/type flags) ==="; dbdeployer deploy replication --help 2>&1 | grep -i -E "flavor|type|postgres" || echo "NO postgres deploy flags"
'

dbdeployer downloads get-unpack <postgres-17-tarball>     # or: dbdeployer unpack <tarball>
dbdeployer deploy replication 17 --topology=... --nodes=3 --bind-address=0.0.0.0 --base-port=5432
dbdeployer sandboxes    # confirm 3 nodes, note the ports

git add test/pg-compat/SPIKE-dbdeployer-pg.md
git commit -m "spike(pg-compat): capture exact dbdeployer PostgreSQL deploy command + ports"

cp -r test/infra/infra-pgsql17-repl test/infra/infra-pgsql17-repl-3node

cp -r test/infra/infra-dbdeployer-mysql84-gr test/infra/infra-dbdeployer-pgsql17-repl

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

# monitor role for automatic replication_hostgroups (must match pgsql-monitor_username/password)
docker exec "${CONTAINER}" psql -X -Upostgres -c "SET client_min_messages='error';" \
  -c "DROP USER IF EXISTS monitor;" -c "CREATE USER monitor WITH PASSWORD 'monitor';"
# pg_stat_statements extension (routing oracle) — create in each app DB
for DB in postgres testuser; do
  docker exec "${CONTAINER}" psql -X -Upostgres -d "$DB" -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
done

export INFRA_TYPE="infra-pgsql17-repl-3node"   # or infra-dbdeployer-pgsql17-repl
export PGCOMPAT_PRIMARY_HOST="pgsql1.${INFRA_ID}"
export PGCOMPAT_PRIMARY_PORT="5432"
export PGCOMPAT_REPLICA1_HOST="pgsql2.${INFRA_ID}"
export PGCOMPAT_REPLICA1_PORT="5432"
export PGCOMPAT_REPLICA2_HOST="pgsql3.${INFRA_ID}"
export PGCOMPAT_REPLICA2_PORT="5432"

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=pg-compat test/infra/control/ensure-infras.bash
# verify from a throwaway container on the network:
docker run --rm --network "dev-$USER_backend" postgres:17 bash -c '
  PGPASSWORD=testuser psql -h pgsql1.dev-'"$USER"' -U testuser -d testuser -c "SELECT pg_is_in_recovery();"   # f (primary)
  PGPASSWORD=testuser psql -h pgsql2.dev-'"$USER"' -U testuser -d testuser -c "SELECT pg_is_in_recovery();"   # t (replica)
  PGPASSWORD=testuser psql -h pgsql3.dev-'"$USER"' -U testuser -d testuser -c "SELECT pg_is_in_recovery();"   # t (replica)
'

git add test/infra/infra-pgsql17-repl-3node test/tap/groups/pg-compat   # (or the dbdeployer dir)
git commit -m "infra(pg-compat): primary + 2-replica PG17 infra with pg_stat_statements"

  toxiproxy:
    hostname: toxiproxy.${INFRA}
    image: ghcr.io/shopify/toxiproxy:2.9.0
    container_name: ${COMPOSE_PROJECT}-toxiproxy-1
    command: ["-host", "0.0.0.0"]
    networks:
      backend:
        aliases:
          - toxiproxy.${INFRA}

#!/usr/bin/env bash
set -euo pipefail
TOXI="toxiproxy.${INFRA_ID}:8474"
mk() {  # name  listen_port  upstream_host
  curl -fsS -XPOST "http://${TOXI}/proxies" -d "{\"name\":\"$1\",\"listen\":\"0.0.0.0:$2\",\"upstream\":\"$3:5432\",\"enabled\":true}"
}
mk pg_primary  6001 "pgsql1.${INFRA_ID}"
mk pg_replica1 6002 "pgsql2.${INFRA_ID}"
mk pg_replica2 6003 "pgsql3.${INFRA_ID}"

export PGCOMPAT_TOXI_ADMIN="toxiproxy.${INFRA_ID}:8474"
export PGCOMPAT_TOXI_PRIMARY="toxiproxy.${INFRA_ID}:6001"
export PGCOMPAT_TOXI_REPLICA1="toxiproxy.${INFRA_ID}:6002"
export PGCOMPAT_TOXI_REPLICA2="toxiproxy.${INFRA_ID}:6003"

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=pg-compat test/infra/control/start-proxysql-isolated.bash
docker run --rm --network "dev-$USER_backend" postgres:17 \
  bash -c 'PGPASSWORD=testuser psql -h toxiproxy.dev-'"$USER"' -p 6001 -U testuser -d testuser -c "SELECT 1"'

git add test/infra/*/docker-compose.yml test/infra/*/bin/toxiproxy-bootstrap.sh test/infra/*/bin/docker-proxy-post.bash test/tap/groups/pg-compat/env.sh
git commit -m "infra(pg-compat): Toxiproxy sidecar with one proxy per backend"

DELETE FROM pgsql_servers WHERE comment LIKE '%${INFRA}%';
-- All three backends in the WRITER hostgroup, addressed via Toxiproxy.
INSERT INTO pgsql_servers (hostgroup_id, hostname, port, max_connections, comment) VALUES
  (${WHG}, 'toxiproxy.${INFRA}', 6001, 200, 'pg_primary ${INFRA}'),
  (${WHG}, 'toxiproxy.${INFRA}', 6002, 200, 'pg_replica1 ${INFRA}'),
  (${WHG}, 'toxiproxy.${INFRA}', 6003, 200, 'pg_replica2 ${INFRA}');

-- Automatic writer/reader assignment via pg_is_in_recovery().
DELETE FROM pgsql_replication_hostgroups WHERE writer_hostgroup=${WHG};
INSERT INTO pgsql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
  VALUES (${WHG}, ${RHG}, 'read_only', 'pg auto rw-split ${INFRA}');

LOAD PGSQL SERVERS TO RUNTIME;   -- loads replication_hostgroups too
SAVE PGSQL SERVERS TO DISK;

DELETE FROM pgsql_users WHERE comment LIKE '%${INFRA}%';
REPLACE INTO pgsql_users (username, password, active, default_hostgroup, comment) VALUES
  ('postgres', '${ROOT_PASSWORD}', 1, ${WHG}, '${INFRA}'),
  ('testuser', 'testuser',        1, ${WHG}, '${INFRA}');
LOAD PGSQL USERS TO RUNTIME;
SAVE PGSQL USERS TO DISK;

-- Read/write split query rules (route SELECTs to reader HG).
DELETE FROM pgsql_query_rules WHERE destination_hostgroup IN (${WHG}, ${RHG});
INSERT INTO pgsql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (${WHG}01, 1, '^SELECT.*FOR UPDATE', ${WHG}, 1),
  (${RHG}01, 1, '^SELECT', ${RHG}, 1);
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;

-- Enable the monitor (drives the automatic split).
UPDATE global_variables SET variable_value='true'    WHERE variable_name='pgsql-monitor_enabled';
UPDATE global_variables SET variable_value='monitor'  WHERE variable_name IN ('pgsql-monitor_username','pgsql-monitor_password');
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=pg-compat test/infra/control/start-proxysql-isolated.bash
# after ~monitor_read_only_interval, check runtime hostgroups over the PG admin (port 6132):
docker exec proxysql.dev-$USER env PGPASSWORD=admin psql -h127.0.0.1 -p6132 -Uadmin -dadmin \
  -c "SELECT hostgroup_id, hostname, port FROM runtime_pgsql_servers ORDER BY hostgroup_id;"

git add test/infra/*/conf/proxysql/infra-config.sql
git commit -m "config(pg-compat): automatic pgsql_replication_hostgroups via Toxiproxy"

psycopg[binary]==3.2.*
asyncpg==0.30.*
pytest==8.*
tomli==2.*

FROM python:3.11-slim
RUN apt-get update && apt-get install -y --no-install-recommends libpq5 curl && rm -rf /var/lib/apt/lists/*
WORKDIR /pg-compat
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
ENTRYPOINT ["pytest", "-q"]

#!/usr/bin/env bash
set -euo pipefail
: "${INFRA_ID:?}"; : "${WORKSPACE:?}"
NETWORK="${INFRA_ID}_backend"
source "${WORKSPACE}/test/tap/groups/pg-compat/env.sh"
docker build -t proxysql-pg-compat:latest "${WORKSPACE}/test/pg-compat"
docker run --rm --network "${NETWORK}" \
  -e INFRA_ID -e PGCOMPAT_PRIMARY_HOST -e PGCOMPAT_PRIMARY_PORT \
  -e PGCOMPAT_REPLICA1_HOST -e PGCOMPAT_REPLICA1_PORT \
  -e PGCOMPAT_REPLICA2_HOST -e PGCOMPAT_REPLICA2_PORT \
  -e PGCOMPAT_TOXI_ADMIN -e PGCOMPAT_TOXI_PRIMARY \
  -e PGCOMPAT_TOXI_REPLICA1 -e PGCOMPAT_TOXI_REPLICA2 \
  -e PGCOMPAT_PROXY_HOST="proxysql" -e PGCOMPAT_PROXY_PORT="6133" \
  -e PGCOMPAT_ADMIN_HOST="proxysql" -e PGCOMPAT_ADMIN_PORT="6132" \
  proxysql-pg-compat:latest "$@"

import os
import psycopg

def _admin_dsn():
    host = os.environ["PGCOMPAT_ADMIN_HOST"]; port = os.environ["PGCOMPAT_ADMIN_PORT"]
    # ProxySQL admin speaks the PG protocol on 6132; user/pass = admin/admin.
    return f"host={host} port={port} user=admin password=admin dbname=admin sslmode=disable"

class Admin:
    def __init__(self):
        self.conn = psycopg.connect(_admin_dsn(), autocommit=True)

    def query(self, sql):
        with self.conn.cursor() as cur:
            cur.execute(sql)
            return cur.fetchall() if cur.description else None

    def set_var(self, name, value):
        self.query(f"SET {name}={value!r}" if isinstance(value, str) else f"SET {name}={value}")

    def load_vars(self):
        self.query("LOAD PGSQL VARIABLES TO RUNTIME")

    def snapshot(self, var_names):
        rows = self.query(
            "SELECT variable_name, variable_value FROM global_variables WHERE variable_name IN (%s)"
            % ",".join(repr(v) for v in var_names))
        return dict(rows)

    def restore(self, saved):
        for name, value in saved.items():
            self.set_var(name, value)
        self.load_vars()

import os
import psycopg
import pytest
from harness.proxysql import Admin

@pytest.fixture(scope="session")
def admin():
    return Admin()

def _proxy_dsn(dbname="testuser"):
    h = os.environ["PGCOMPAT_PROXY_HOST"]; p = os.environ["PGCOMPAT_PROXY_PORT"]
    return f"host={h} port={p} user=testuser password=testuser dbname={dbname} sslmode=disable"

@pytest.fixture
def proxy_conn():
    conn = psycopg.connect(_proxy_dsn(), autocommit=True)
    yield conn
    conn.close()

def test_proxy_select_one(proxy_conn):
    with proxy_conn.cursor() as cur:
        cur.execute("SELECT 1")
        assert cur.fetchone()[0] == 1

def test_admin_reconfig_roundtrip(admin):
    saved = admin.snapshot(["pgsql-authentication_method"])
    admin.set_var("pgsql-authentication_method", 1); admin.load_vars()
    val = admin.query("SELECT variable_value FROM global_variables WHERE variable_name='pgsql-authentication_method'")
    assert val[0][0] == "1"
    admin.restore(saved)

[pytest]
testpaths = tests

WORKSPACE=$(pwd) INFRA_ID=dev-$USER TAP_GROUP=pg-compat test/infra/control/ensure-infras.bash
WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_smoke.py

git add test/pg-compat
git commit -m "test(pg-compat): pytest harness skeleton + admin config primitive + smoke"

import glob, os, pytest
from harness import targets, diff

CASE_FILES = sorted(glob.glob(os.path.join(os.path.dirname(__file__), "..", "cases", "*.sql")))

@pytest.mark.parametrize("case_file", CASE_FILES, ids=[os.path.basename(f) for f in CASE_FILES])
def test_case_is_transparent(admin, case_file):
    tgts = targets.all_targets(admin)
    results = diff.run_case(case_file, tgts)
    ok, detail = diff.compare(results)
    assert ok, f"{os.path.basename(case_file)} diverged:\n{detail}"

import os
from dataclasses import dataclass
from typing import Callable
import psycopg

def _dsn(host, port, dbname="testuser", user="testuser", pw="testuser"):
    return f"host={host} port={port} user={user} password={pw} dbname={dbname} sslmode=disable"

def _proxy():   return _dsn(os.environ["PGCOMPAT_PROXY_HOST"], os.environ["PGCOMPAT_PROXY_PORT"])
def _direct():  return _dsn(os.environ["PGCOMPAT_PRIMARY_HOST"], os.environ["PGCOMPAT_PRIMARY_PORT"])

@dataclass
class Target:
    name: str
    dsn: str
    binary: bool
    native_backend: bool | None   # None = direct (not applicable)

def all_targets(admin):
    # proxy targets exist twice: native backend OFF and ON.
    return [
        Target("proxy_libpq_text",    _proxy(),  False, False),
        Target("proxy_libpq_binary",  _proxy(),  True,  False),
        Target("proxy_native_text",   _proxy(),  False, True),
        Target("proxy_native_binary", _proxy(),  True,  True),
        Target("direct_text",         _direct(), False, None),
        Target("direct_binary",       _direct(), True,  None),
    ]

import re
from fnmatch import fnmatchcase

import psycopg

def _parse(case_file):
    sql = open(case_file).read()
    # Metadata regexes run on the ORIGINAL text, comment lines included.
    skip = set(re.findall(r"--\s*skip-targets:\s*(.+)", sql))
    only = set(re.findall(r"--\s*only-targets:\s*(.+)", sql))
    # Strip comment lines PER LINE *before* splitting on ";". Filtering
    # ";"-delimited chunks that start with "--" instead discards an ENTIRE case
    # whose first line is a metadata comment: with one trailing ";" the whole
    # file is a single chunk beginning with "--", so the comment AND the SQL are
    # thrown away together and zero statements run -- a vacuous pass. Every
    # shipped case starts with such a comment, so this is not a corner case.
    body = "\n".join(l for l in sql.splitlines() if not l.strip().startswith("--"))
    stmts = [s.strip() for s in body.split(";") if s.strip()]
    return stmts, (skip.pop().split() if skip else []), (only.pop().split() if only else [])

def _run_on(target, stmts, admin):
    if target.native_backend is not None:
        admin.set_var("pgsql-use_native_backend_protocol", "true" if target.native_backend else "false")
        admin.load_vars()
    out = []
    with psycopg.connect(target.dsn, autocommit=True) as conn:
        for s in stmts:
            with conn.cursor(binary=target.binary) as cur:
                cur.execute(s)
                cols = [(d.name, d.type_code) for d in (cur.description or [])]
                rows = cur.fetchall() if cur.description else None
                out.append((cur.statusmessage, cols, rows))
    return out

def run_case(case_file, targets, admin=None):
    stmts, skip, only = _parse(case_file)
    results = {}
    for t in targets:
        # Glob matching, so a documented pattern such as "proxy_native_*"
        # actually selects the native targets; a plain name with no
        # metacharacter still compares as an exact match.
        if any(fnmatchcase(t.name, p) for p in skip): continue
        if only and not any(fnmatchcase(t.name, p) for p in only): continue
        results[t.name] = _run_on(t, stmts, admin)
    return results

def compare(results):
    # Every proxy_* result must equal its format-matched direct_* baseline.
    def base(name): return "direct_binary" if name.endswith("binary") else "direct_text"
    diffs = []
    for name, res in results.items():
        if name.startswith("direct"): continue
        b = results.get(base(name))
        if res != b:
            diffs.append(f"{name} != {base(name)}\n  got:  {res}\n  base: {b}")
    return (not diffs, "\n".join(diffs))

-- transactional: false
SELECT true, 2147483647::int4, 9223372036854775807::int8, 1.5::float8, 12345.6789::numeric, 'héllo'::text;

-- transactional: false
SELECT '\xdeadbeef'::bytea, '{"a":1}'::jsonb, ARRAY[1,2,3]::int4[], '192.168.0.1'::inet, '00000000-0000-0000-0000-000000000001'::uuid;

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_differential.py

from harness import targets, diff

def test_engine_detects_divergence(admin):
    # Install a query rule that rewrites the result, making proxy != direct.
    admin.query("INSERT INTO pgsql_query_rules (rule_id,active,match_digest,replace_pattern,re_modifiers,apply) "
                "VALUES (990001,1,'SELECT 1 AS canary','SELECT 2 AS canary','CASELESS',1)")
    admin.query("LOAD PGSQL QUERY RULES TO RUNTIME")
    try:
        tgts = targets.all_targets(admin)
        results = diff.run_case_sql("SELECT 1 AS canary", tgts, admin)   # inline-SQL helper variant
        ok, _ = diff.compare(results)
        assert not ok, "differential engine FAILED to detect an injected rewrite divergence"
    finally:
        admin.query("DELETE FROM pgsql_query_rules WHERE rule_id=990001")
        admin.query("LOAD PGSQL QUERY RULES TO RUNTIME")

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_differential_selfcheck.py
git add test/pg-compat/harness/targets.py test/pg-compat/harness/diff.py test/pg-compat/cases test/pg-compat/tests/test_differential.py test/pg-compat/tests/test_differential_selfcheck.py
git commit -m "test(pg-compat): 6-target differential engine + cases + divergence self-check"

from harness import oracle

def test_select_lands_on_a_reader(proxy_conn):
    oracle.reset_all()
    for _ in range(20):
        with proxy_conn.cursor() as cur:
            cur.execute("SELECT 42 AS oracle_probe")
            cur.fetchone()
    counts = oracle.calls_for("%oracle_probe%")
    # read/write split: SELECTs must hit readers (replicas), not the primary/writer.
    assert counts["primary"] == 0, f"SELECT hit the primary: {counts}"
    assert counts["replica1"] + counts["replica2"] == 20, f"reads not on replicas: {counts}"

import os
import psycopg

def _c(host, port):
    return psycopg.connect(
        f"host={host} port={port} user=testuser password=testuser dbname=testuser sslmode=disable",
        autocommit=True)

# Each backend carries its own port: under dbdeployer all three share a host
# and differ only by port, so keying on host alone cannot reach the replicas.
_BACKENDS = {
    "primary":  ("PGCOMPAT_PRIMARY_HOST",  "PGCOMPAT_PRIMARY_PORT"),
    "replica1": ("PGCOMPAT_REPLICA1_HOST", "PGCOMPAT_REPLICA1_PORT"),
    "replica2": ("PGCOMPAT_REPLICA2_HOST", "PGCOMPAT_REPLICA2_PORT"),
}

def reset_all():
    for host_env, port_env in _BACKENDS.values():
        with _c(os.environ[host_env], os.environ[port_env]) as conn, conn.cursor() as cur:
            cur.execute("SELECT pg_stat_statements_reset()")

def calls_for(pattern):
    out = {}
    for name, env in _BACKENDS.items():
        with _c(os.environ[env]) as conn, conn.cursor() as cur:
            cur.execute("SELECT COALESCE(SUM(calls),0) FROM pg_stat_statements WHERE query LIKE %s", (pattern,))
            out[name] = int(cur.fetchone()[0])
    return out

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_routing_oracle.py

def test_write_pins_to_primary(proxy_conn):
    oracle.reset_all()
    with proxy_conn.cursor() as cur:
        cur.execute("CREATE TEMP TABLE oracle_w (id int)")
        cur.execute("INSERT INTO oracle_w VALUES (1)")
    counts = oracle.calls_for("%oracle_w%")
    assert counts["replica1"] == 0 and counts["replica2"] == 0, f"write leaked to a replica: {counts}"

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_routing_oracle.py
git add test/pg-compat/harness/oracle.py test/pg-compat/tests/test_routing_oracle.py
git commit -m "test(pg-compat): pg_stat_statements routing oracle + write-pin self-check"

import pytest
from drivers.python.adapter import PsycopgAdapter
from behaviors import connect, transactions, prepared, session_isolation

BEHAVIORS = [connect, transactions, prepared, session_isolation]

@pytest.mark.parametrize("behavior", BEHAVIORS, ids=[b.__name__.split(".")[-1] for b in BEHAVIORS])
def test_behavior_python(behavior):
    behavior.run(PsycopgAdapter)

import os
import psycopg

class PsycopgAdapter:
    def __init__(self, dbname="testuser"):
        h = os.environ["PGCOMPAT_PROXY_HOST"]; p = os.environ["PGCOMPAT_PROXY_PORT"]
        self.conn = psycopg.connect(
            f"host={h} port={p} user=testuser password=testuser dbname={dbname} sslmode=disable",
            autocommit=True)

    def exec_simple(self, sql):
        with self.conn.cursor() as cur:
            cur.execute(sql)
            return cur.fetchall() if cur.description else None

    def exec_params(self, sql, params, binary=False):
        with self.conn.cursor(binary=binary) as cur:
            cur.execute(sql, params)
            return cur.fetchall() if cur.description else None

    def begin(self):    self.conn.autocommit = False
    def commit(self):   self.conn.commit(); self.conn.autocommit = True
    def rollback(self): self.conn.rollback(); self.conn.autocommit = True
    def close(self):    self.conn.close()

def run(Adapter):
    a = Adapter()
    assert a.exec_simple("SELECT 1")[0][0] == 1
    a.close()

def run(Adapter):
    a = Adapter()
    a.exec_simple("DROP TABLE IF EXISTS tx_t")
    a.exec_simple("CREATE TABLE tx_t (id int)")
    a.begin()
    a.exec_simple("INSERT INTO tx_t VALUES (1)")
    a.rollback()
    assert a.exec_simple("SELECT count(*) FROM tx_t")[0][0] == 0, "rollback did not discard the insert"
    a.begin(); a.exec_simple("INSERT INTO tx_t VALUES (2)"); a.commit()
    assert a.exec_simple("SELECT count(*) FROM tx_t")[0][0] == 1
    a.close()

def run(Adapter):
    a = Adapter()
    # Reuse a parameterized statement many times across multiplexed backends.
    for i in range(50):
        r = a.exec_params("SELECT $1::int + $2::int", (i, 1))
        assert r[0][0] == i + 1
    a.close()

def run(Adapter):
    a = Adapter()
    a.exec_simple("SET application_name = 'behavior_A'")
    assert a.exec_simple("SHOW application_name")[0][0] == "behavior_A"
    b = Adapter()
    assert b.exec_simple("SHOW application_name")[0][0] != "behavior_A", "session state leaked across connections"
    a.close(); b.close()

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash tests/test_behaviors.py
git add test/pg-compat/drivers test/pg-compat/behaviors test/pg-compat/tests/test_behaviors.py
git commit -m "test(pg-compat): shared behavior set + Python (psycopg3) driver adapter"

# Each entry documents a KNOWN divergence discovered in the discovery phase (spec §2.1).
# strict=false semantics: a listed test that starts PASSING is reported as xpass (fix landed).
[[xfail]]
test_id = "tests/test_differential.py::test_case_is_transparent[002_bytea_json_array.sql]"
mode = "native"          # libpq | native | both
reason = "proxy_native_binary bytea OID mismatch on the young native backend path"
ref = "PR #5882"

import os, tomli

def load():
    path = os.path.join(os.path.dirname(__file__), "..", "xfail.toml")
    if not os.path.exists(path):
        return []
    with open(path, "rb") as f:
        return tomli.load(f).get("xfail", [])

import pytest
from harness import xfail as _xfail

_XFAILS = { e["test_id"]: e for e in _xfail.load() }

def pytest_collection_modifyitems(config, items):
    for item in items:
        rel = item.nodeid
        entry = _XFAILS.get(rel)
        if entry:
            item.add_marker(pytest.mark.xfail(reason=f'{entry["reason"]} ({entry["ref"]})', strict=False))

WORKSPACE=$(pwd) INFRA_ID=dev-$USER test/pg-compat/run-pg-compat.bash -rxX

git add test/pg-compat/xfail.toml test/pg-compat/harness/xfail.py test/pg-compat/conftest.py
git commit -m "test(pg-compat): xfail catalogue with xpass reporting (discovery phase)"

name: CI-pg-compat
on:
  schedule:
    - cron: '0 3 * * *'
  pull_request:
    types: [opened, synchronize, reopened, labeled]
  workflow_dispatch:

jobs:
  pg-compat:
    if: >-
      github.event_name == 'schedule' ||
      github.event_name == 'workflow_dispatch' ||
      contains(github.event.pull_request.labels.*.name, 'pg-compat')
    permissions: write-all
    uses: sysown/proxysql/.github/workflows/ci-pg-compat.yml@GH-Actions
    secrets: inherit
    with:
      trigger: ${{ toJson(github) }}

name: CI-pg-compat
on:
  workflow_call:
    inputs:
      trigger: { required: false, type: string }

jobs:
  pg-compat:
    runs-on: ubuntu-22.04
    steps:
      - uses: actions/checkout@v4
      - name: Build ProxySQL (debug, PROXYSQL31)
        run: PROXYSQL31=1 make -j$(nproc) debug
      - name: Stand up infra (backends + Toxiproxy + ProxySQL)
        env: { INFRA_ID: ci-${{ github.run_id }}, WORKSPACE: ${{ github.workspace }}, TAP_GROUP: pg-compat }
        run: test/infra/control/ensure-infras.bash
      - name: Run pg-compat suite (non-gating, discovery phase)
        env: { INFRA_ID: ci-${{ github.run_id }}, WORKSPACE: ${{ github.workspace }} }
        run: test/pg-compat/run-pg-compat.bash --junitxml=/tmp/pg-compat.xml -rxX || true   # non-gating
      - name: Publish report
        if: always()
        uses: actions/upload-artifact@v4
        with: { name: pg-compat-report, path: /tmp/pg-compat.xml }

# reusable first, on GH-Actions:
git add gh-actions-reusable/ci-pg-compat.yml
git commit -m "ci(pg-compat): reusable workflow (inline build + infra + pytest, non-gating)"
# caller on v3.0:
git add .github/workflows/CI-pg-compat.yml
git commit -m "ci(pg-compat): nightly + pg-compat-label caller workflow"