Consolidation plan: types, functions and translation

Status: 2026-09-19. A living plan; update it as phases land.

Inputs:

• the function-coverage sweep (bb fncov);
• three per-domain gap reports and two implementation audits (.internal/fncov-reports/, .internal/audit/; local, not committed; file:line references there drift);
• the leak patterns found while fixing #177–#180.

Every finding marked verified below was reproduced against the PostgreSQL 17.7 oracle.

Why things slipped through

1. Class-dispatched behaviour. Several PostgreSQL types share one JVM carrier:

   • money and numeric are both BigDecimal;
   • time and interval are kept as text;
   • date, timestamp and timestamptz are all instants;
   • bytea is hex text.

   Code that decides by the value's class instead of the static type is wrong for at least one of them.

2. Passthrough on failure. (catch … v), (str e) and (or (parse s) s) keep an unparsed value flowing as text wearing a type. A parse that correctly yields false is also taken for a failure.

3. Identity stubs accepted for syntax: AT TIME ZONE was one; has_*_privilege always answers true.

4. Duplicated paths that drift:

   • 8 text renderers;
   • 9 timestamp/date parsers, 7 boolean parsers, ~15 type-name tables;
   • 4 LIKE/regex implementations, 3 ANY/ALL paths;
   • a second expression evaluator for UPDATE SET, RETURNING and CHECK;
   • 3 settings stores and 10 function-lookup routes.

5. Shortcuts that bypass the translator: classify.clj single-function SELECTs, shape.clj catalog probes, lexical literal templating.

6. Tests asserting our output instead of PostgreSQL's. Admitted money.sql "strict slices" expected 79.83 where upstream says $79.83.

The remedy is structural rather than a list of patches:

• one registry per type, keyed by OID (input, output, binary, compare);
• one function-signature registry generated from pg_proc.dat;
• one expression evaluator;
• no fallbacks that return their input;
• expectations taken only from the oracle or upstream .out files.

Principles for every change

• Closed resolution. Functions, operators, casts and types resolve against tables generated from the pinned catalog (pg_proc.dat, pg_cast.dat, pg_operator.dat, pg_type.dat). Unknown means PostgreSQL's error (42883 / 42725 / 42704), never "try something". The catalog is src/datahike/pg/pg_catalog.edn, generated by bb gen-catalog; types/explicit-casts (#177) and types/aggregate-resolution (#180) read it.
• Type-directed values. Dispatch on the static OID first; class only when the type is unknown. Never .toString a Java value as PostgreSQL output.
• No passthrough. A value that fails input raises the type's SQLSTATE (22P02 / 22007 / 22008 / 22003).
• One implementation per semantics. When a second path is found, it calls the first or is deleted.
• Expectations from PostgreSQL. New tests take expected values from the oracle or from upstream expected/*.out, never from our output.

Phase 0 — stop verified silent wrong answers

These corrupt data or return wrong rows today, and they come before feature work. The items are ordered by dependency.

0.0 Catalog tables are generated, with a drift test. (Done in this PR.) explicit-casts and aggregate-resolution are pasted snapshots today. Commit the generator (it reads pg_proc.dat, pg_cast.dat, pg_type.dat and pg_operator.dat from a pinned PostgreSQL checkout) and a test that fails when a table and its generator disagree. Keep an explicit extension manifest for names that PostgreSQL doesn't have: pgvector distances, period predicates, uuidv4/uuidv7, date_add/date_diff. Closed resolution needs both.

#	Item	Verified behaviour	Fix	Gate
0.1	Scalar input functions (bool, int2/int4/int8, oid, float4/float8, numeric, uuid). This is Phase 1.3 pulled forward for the non-datetime types.	verified: • WHERE b = 'false' / 'no' → 0 rows; • WHERE i = 'abc' → 0 rows instead of 22P02; • '1.5'::int → 1 instead of 22P02, and the error names numeric; • '0x1F', '1_000' rejected (PostgreSQL 16+ accepts them); • the Java text-parameter decoder turns every bool other than t/true/1 into false; • int2/int4 have no 22003	One input function per OID, ported from int.c/int8.c/bool.c/float.c/numeric.c/uuid.c, raising 22P02 or 22003. Route every path through it: unknown literals, casts from text/unknown, INSERT/UPDATE, text parameters (PgParamCodec calls in or mirrors it, with a shared test vector) and array elements. coerce-unknown stops returning its input. Datetime types wait for the decoder in 1.3.	oracle matrix {bool,int2,int4,int8,oid,float8,numeric,uuid} × valid/invalid/edge inputs × {cast, WHERE literal, INSERT, text parameter, array element}; fuzzer literal class
0.2	CHECK and DEFAULT evaluation	verified: CHECK (name > 'm'), CHECK (d < '2030-01-01') and CHECK (s LIKE 'a%') accept violating rows. The CHECK evaluator treats anything it doesn't recognise as true, compares only numbers, and swallows exceptions. eval-default exists twice and has drifted (now differs between the copies, and DEFAULT '123' on a text column is stored as a Long). The DDL regex evaluator still folds now() AT TIME ZONE … to now.	Compile a CHECK or DEFAULT once through the expression translator, then bind each row's values. The result is three-valued (only false rejects) and errors propagate. One eval-default. Domain CHECKs use the same path.	CHECK over every operator × type must reject what PostgreSQL rejects; DEFAULT types match the oracle's pg_typeof
0.3	One evaluator for UPDATE SET / RETURNING	verified: • SET s = s \|\| '[1]' stores ["b", 1] (jsonb chosen because parse-jsonb happened to succeed); • RETURNING i + 1 → NULL; • interpret-form uses Clojure arithmetic (/ returns a Ratio, division by zero gives nil) and treats NULL = NULL as true	UPDATE translated once as SELECT eid, <set exprs> FROM t WHERE …, the same pattern INSERT's const-value uses. RETURNING goes through the same projection and renders by OID. interpret-form gets SQL arithmetic and NULL semantics, or is deleted once nothing calls it.	fuzzer: UPDATE … SET c = E RETURNING c ≡ SELECT E; pgbench tps must not drop
0.4	Closed function resolution	verified: an unknown SQL name calls any resolvable Clojure var	Delete the fallback at expr.clj ~1933 → 42883. interpret-form's clojure.core/resolve gets an allowlist or is deleted with 0.3. Bind Datahike's safe-symbol-resolver (#1058) and register-ns! our namespaces.	fuzzer "unknown identifier as function"; manifest test; client suites
0.5	ANY/ALL: one implementation (after 0.1)	verified: id = ANY('{1,2}') → 0 rows. There are 5 paths: value position (only = and <>), WHERE = (two-valued), translate-quantified-cmp (two-valued, throws on strings), WHERE <>, and the join rewrite. '{…}' is split on commas in two places.	One translation: the array literal goes through the real array parser with 0.1's element input, SQL comparators, three-valued; all operators in value position	oracle diff op × {literal, '{…}', column, param} × NULL × NOT
0.6	classify.clj / server shortcuts	verified: • nextval('a' \|\| 'b') advances a; • SELECT version(), 1 → 42883; • SELECT pg_get_keywords() returns one empty row though a real set-returning function exists; • now() renders without an OID	Real functions (a minimal volatility slice of the Phase 4 registry). nextval is evaluated per row, not once per statement. Delete the shortcuts.	asyncpg, pgjdbc, psycopg, Odoo startup; fuzzer "system function inside an expression"; SELECT nextval('s') FROM generate_series(1,3)
0.7	shape.clj probes	audit and review: FK names are made up (fk_<hash of SQL>); PK probe reads the table name with a regex and so misses $1; WHERE is ignored	Serve from the catalog tables; delete the probes	pgjdbc metadata tests; oracle diff of the probe SQL
0.8	LIMIT/OFFSET templating	audit	Template only as an AST transform	fuzzer templated-vs-untemplated identity
0.9	DDL slowdown	verified: the schema map grows with every DROP/CREATE	datahike #1089 (green), then bump the dependency	churn benchmark stays flat

Moved out of Phase 0:

• Recursive CTE UNION ALL deduplicates. Execution is a Datalog rule, and set semantics are built in. Keeping duplicates needs a working-table loop, a rewrite of its own, so it becomes a separate item after Phase 1.
• Binary results carrying text bytes. Binary encoding re-parses the stringified value. The real fix is the per-type binary send in Phase 1.2; until then, stop advertising binary for types without a correct send.

Phase 1 — one value registry (finish #179)

Target (value-layer audit §5): per-OID {:in :out :text :recv :send :compare :storage :typmod} and one resolve-type-name. Shippable steps:

1. Output complete. Every result path renders by OID. RETURNING and the shortcuts currently call value->string without one, and LocalTime/LocalDateTime go through str. Make ->pg-text total (bool via OID, bytea, bit, vector, NaN, json-null) and reduce value->string to it. Fix record_out quote doubling. to_jsonb dispatches by OID.

2. Registry for output and binary send, generated into the existing maps. The dh-type->oid and dh-type->pg-name tables already disagree (bytes maps to text in one and bytea in the other) so old callers keep working; the Java codec gets typlen/array maps through a register call.

3. Input, one family per PR:

   • bool → integers → float/numeric/money → uuid;
   • date/timestamp/timestamptz: one datetime decoder (datetime.c ParseDateTime/DecodeDateTime) replacing parse-timestamp-string and its 8 siblings, including COPY. This is the agreed first item: 'garbage'::timestamptz passes through, and a timestamp offset is converted instead of ignored;
   • time/timetz → bytea → arrays (typed elements).

   Each PR routes cast, INSERT/UPDATE, unknown literal, COPY, text parameter and pg_input_is_valid through the family's :in.

4. Comparison, equality and hashing from the registry. Merge order-cmp, sql-order-cmp, null-safe-order-cmp and insert-select-order-cmp. GROUP BY, DISTINCT, window partitions and IN-sets use the type's equality and hash, not Clojure =/group-by/contains?. For example, NaN = NaN in PostgreSQL, and '1 mon' = '30 days' for intervals. Fixes signed uuid ordering and array elements compared as strings.

5. Session TimeZone. Needs the single settings store (moved here from Phase 4) and a parse-cache key that includes TimeZone and DateStyle; today translation-cache-key includes only search_path. Storage convention:

   • timestamptz is stored as an absolute instant;
   • timestamp and date are stored as UTC wall-clock;
   • TimeZone affects only timestamptz input and output, and casts between timestamp and timestamptz.

   Also fix current_setting: it is case-sensitive and ignores SET. set_config doesn't store its value. is_superuser contradicts pg_roles. The role name is hard-coded three times.

Phase 2 — interval as a value

A PgInterval carrier (months, days, micros) with input, output, compare and arithmetic. Comparison, equality and hashing follow interval_cmp_value (timestamp.c): a month is 30 days and a day is 24 hours. A record's structural =/hash would split GROUP BY and joins. Unlocks about 90 sweep entries: timestamp ± interval, age, justify_*, make_interval, date_bin, OVERLAPS. Fixes interval comparison, which is wrong today ('10 days' < '9 days' is true).

Phase 3 — bytea end to end

Four fixes: the literal-cast fold, the column OID (17, not text), a strict bytea-in (hex and escape formats), and byte[] arms in the text functions and ->pg-text. Then encode/decode, sha2, get/set_byte, convert_*. Every driver reading bytea is affected, so this needs the client suites plus a dump round-trip.

Phase 4 — signature registry and resolvers

• pg_proc.dat registry for all functions: argument types, return type, strictness, volatility, kind. It drives resolution (42883/42725), return-type inference and strict NULL handling, and replaces sql-fn-arities, the sql-function-specs flags, oid_infer's function tables and classify's shortcuts. Translation audit §4.3 gives the extraction order.
• One name/OID resolver (relation, type, namespace, role, function) with PostgreSQL's errors. It fixes regclass/regtype/regnamespace, the ~78 has_*_privilege/pg_has_role entries (a single superuser role: true for existing objects, errors for unknown ones), to_reg* and pg_*_is_visible.
• Parameter types from the translator instead of the per-statement *-param-oids walkers.

Phase 5 — text semantics

• One pattern-matching layer: PostgreSQL ARE → java.util.regex translation, flags, LIKE ESCAPE (a trailing escape raises 22025), DOTALL, UNICODE_CASE, SIMILAR TO, 2201B. Replaces 4 copies, including two LIKE compilers.
  • A syntax translation can't reproduce ARE's longest-match alternation: 'a|ab' matches ab in PostgreSQL and a in Java. Either port the engine or document the divergence with a test.
• Set-returning functions implemented once: the correlated producer duplicates materialize-table-function. Its generate_series truncates to long, and a zero step returns [] where PostgreSQL raises 22023. regexp_split_to_table drops trailing empty strings.
• Code-point string functions (emoji and non-BMP characters).
• format() as a port of text_format; bpchar padding semantics.

Phase 6 — feature gaps

• the formatting.c template engine (to_char/to_date/to_timestamp over dates and timestamps);
• the now() family and transaction-stable time;
• float math precision (round half-even, stable asinh/atanh/acosh, glibc cbrt);
• missing aggregates (bool_or, bit_*, …) — the signature table already names them;
• operator-implementation functions as a generated alias table;
• remaining small functions per report.

Process per phase

• Oracle-verified tests, taken from the report's "tests to add" sections.
• A differential-fuzzer class for the area; the CI seed must stay clean, and extra seeds are swept before merging.
• bb fncov ratchet: the wrong-answer count must not rise; add it to CI as a report with a threshold.
• A review agent after each phase, checking consistency and deduplication against these principles. It gets the code, not our conclusions.
• Full surfaces before a PR:
  • unit tests, sqllogictest, the full curated pgjdbc suite (test/integration/pgjdbc/run.sh, not bb pgjdbc);
  • SQLAlchemy, asyncpg (manifest gate), node-postgres, pg_dump;
  • bb fuzz all.
• Strict regression slices take expectations from upstream expected/*.out, and the campaign inventory should check that.

Remaining passthroughs to remove (with their phase)

Location	Behaviour	Phase
jsonb/parse-jsonb	returns the input string	0.3
parse-timestamp-string	returns the input string	1.3
json_to_record cells (stmt ~1336)	keeps the raw value on failure	1.3
copy.clj ref values	keep the raw value on failure	1.3
bytea (stmt ~6983)	falls back to UTF-8 bytes	3
arrays.clj elements	uuid, numeric and date elements stay strings	0.1 / 1.3
interval cast	returns its input	2
cast-scalar	returns the value unchanged for targets it doesn't know	4 (resolver: 42704)

Tracked separately

• node-postgres flake, on main as well: "catalog changed while statement was being executed" under concurrent clients (2 of 6 runs), a race in catalog admission.
• Baseline DDL cost of 100–250 ms per statement even on a fresh database.
• Performance: drop-table-tx-data scans the whole schema and runs one query per attribute.