PostgreSQL Array Type Support

[SeanTAllen]

Scope

1-dimensional arrays only. Multi-dimensional arrays are deferred to a future design. This covers both decoding arrays from query results and encoding arrays as query parameters.

PgArray Type

A single PgArray type serves both the decode path (result fields) and the encode path (query parameters). This matches the existing pattern where PgTimestamp, PgDate, PgTime, and PgInterval all use one type for both paths.

class val PgArray is (FieldData & FieldDataEquatable & Equatable[PgArray])

Fields:

• element_oid: U32 — the PostgreSQL OID of the element type (e.g., 23 for int4). Public field, needed for encoding and useful for users to inspect what type of array they received.
• elements: Array[(FieldData | None)] val — ordered element values. None represents SQL NULL. Uses the open FieldData interface so decoded arrays can contain any codec-produced type, including custom codec types.

Methods:

• create(element_oid', elements') — non-partial constructor. Validation happens at encode time (same pattern as PreparedQuery — accepts any params, validates at bind time).
• size(): USize — number of elements.
• apply(i: USize): (FieldData | None) ? — indexed access.
• eq(that: box->PgArray): Bool — element-wise equality using shared _FieldDataEq helper.
• field_data_eq(that: FieldData box): Bool — FieldDataEquatable implementation.
• string(): String iso^ — PostgreSQL array literal format: {1,2,NULL,4}.

Usage:

// Constructing for query parameters
let arr = PgArray(23,
  recover val [as (FieldData | None): I32(1); I32(2); None; I32(4)] end)
session.execute(PreparedQuery("SELECT $1::int4[]",
  recover val [as FieldDataTypes: arr] end), receiver)

// Extracting from results
match field.value
| let a: PgArray =>
  for elem in a.elements.values() do
    match elem
    | let v: I32 => // use v
    | None => // NULL element
    end
  end
end

FieldDataTypes Addition

Add PgArray to the closed union. This is NOT a recursive union — PgArray stores Array[(FieldData | None)] val where FieldData is a separate interface from FieldDataTypes.

type FieldDataTypes is
  ( Array[U8] val | Bool | F32 | F64 | I16 | I32 | I64 | None
  | PgArray | PgDate | PgInterval | PgTime | PgTimestamp | String )

All existing Codec.encode() implementations have else error branches, so they'll correctly reject PgArray without modification.

Element Equality Helper

Extract a shared _FieldDataEq primitive from the existing Field.eq() logic. Both Field.eq() and PgArray.eq() need element-level equality for (FieldData | None) values. Duplicating the match arms would be a maintenance hazard.

Field.eq() is refactored to delegate to _FieldDataEq(value, that.value) after the name check. PgArray gets an explicit match arm in _FieldDataEq (for consistency with all other built-in types, even though FieldDataEquatable catch-all would also work).

Array OID Map

_ArrayOidMap — a package-private primitive with static bidirectional mapping between element OIDs and array OIDs (23 entries covering all built-in types with registered codecs).

Element Type	Element OID	Array OID
bool	16	1000
bytea	17	1001
char	18	1002
name	19	1003
int8	20	1016
int2	21	1005
int4	23	1007
text	25	1009
oid	26	1028
json	114	199
xml	142	143
float4	700	1021
float8	701	1022
bpchar	1042	1014
varchar	1043	1015
date	1082	1182
time	1083	1183
timestamp	1114	1115
timestamptz	1184	1185
interval	1186	1187
numeric	1700	1231
uuid	2950	2951
jsonb	3802	3807

Methods: element_oid_for(array_oid): U32 ?, array_oid_for(element_oid): U32 ?, is_array_oid(oid): Bool.

Codec Architecture: Registry-Level Handling

Arrays are NOT implemented via the Codec interface. The Codec interface is designed for self-contained codecs — decode(data) takes only raw bytes. Array decoding requires the registry itself to decode elements recursively. Rather than breaking the public Codec interface (adding a registry parameter) or creating class-val codecs with circularity/stale-reference problems, CodecRegistry.decode() intercepts array OIDs before looking up per-OID codecs.

Performance: The interception adds one _ArrayOidMap.is_array_oid() call (a match that falls through immediately for non-array OIDs) to the decode hot path. This is ~2ns per column — negligible compared to the hash map lookup that follows.

CodecRegistry Changes

• Add _custom_array_element_oids: Map[U32, U32] val field — maps custom array OIDs to element OIDs for text decode of user-registered types
• Add with_array_type(array_oid, element_oid): CodecRegistry method — registers a custom array type mapping, chainable with with_codec()
• Modify decode() to check _ArrayOidMap then _custom_array_element_oids before normal codec dispatch
• Add _decode_binary_array() and _decode_text_array() private methods
• Update has_binary_codec() to return true for known array OIDs (built-in + custom)
• Update _with_codec constructor to copy _custom_array_element_oids
• No two-phase construction needed — _decode_binary_array calls self.decode() for elements, which works because CodecRegistry is val and these are non-mutating method calls

Binary Array Decode

PostgreSQL binary array wire format:

I32  ndim            (0 = empty, 1 = 1-D; we reject >1)
I32  has_null        (0 or 1)
I32  element_oid     (authoritative — used directly for element decoding)
I32  dimension_size  (number of elements)
I32  lower_bound     (usually 1, ignored)
Per element:
  I32  element_length (-1 = NULL, else byte count)
  Byte[length] data

The element OID from the binary header is used as-is (authoritative). This correctly handles domain types where the server reports the base element OID.

Security validations:

• ndim < 0 or ndim > 1 → error
• has_null not 0 or 1 → error
• dimension_size < 0 → error
• dimension_size * 4 > remaining_bytes → error (prevents allocation bombs; uses checked arithmetic via mul_partial)
• element_length < -1 → error (only -1 and >= 0 are valid)
• Offset + element_length exceeds data → error
• After parsing all elements, offset != data.size() → error (trailing garbage)

Elements decoded via recursive self.decode(header_element_oid, 1, elem_data). Element extraction uses data.trim() which is O(1) zero-copy on val arrays.

Text Array Decode

Iterative parser (no recursion → no stack overflow risk). Element OID comes from _ArrayOidMap.element_oid_for() since text wire data does not carry it.

Handles:

• {} — empty array
• {1,2,3} — simple elements
• {1,NULL,3} — NULL elements (case-insensitive unquoted NULL)
• {"hello, world","with \"quotes\""} — quoted elements with backslash escaping
• {""} — empty string element (distinct from NULL)
• {{1,2},{3,4}} — detected and rejected (multi-dimensional)

Elements decoded via self.decode(element_oid, 0, raw).

Encode Path

_ParamEncoder Changes

Add PgArray arm: _ArrayOidMap.array_oid_for(a.element_oid), falling back to U32(0) (server infers) for unknown element OIDs.

_FrontendMessage.bind() Changes

• Add registry: CodecRegistry parameter (no default — all call sites explicitly pass the session's registry)
• Pre-encoding approach: Before the recover val block, pre-encode all PgArray parameters into Array[U8] val via _ArrayEncoder. Store in a val array that can enter the recover block. This avoids needing codec access inside recover and avoids double-encoding.
• Three match sites in bind() all need PgArray arms:
  1. params_data_size calculation loop — use pre-encoded byte array's size
  2. Per-parameter format codes loop — PgArray gets format code 1 (binary)
  3. Per-parameter data encoding loop — write pre-encoded bytes

Session.pony Call Sites

All 6 _FrontendMessage.bind() call sites in session.pony updated to pass li.codec_registry. This ensures custom array types registered via with_array_type() work correctly.

_ArrayEncoder Primitive

Encodes PgArray to binary array wire format. Dispatches element encoding on Pony runtime types (matching the pattern _FrontendMessage.bind() already uses for scalars). String elements routed by element_oid: uuid → _UuidBinaryCodec, jsonb → _JsonbBinaryCodec, oid → _OidBinaryCodec; all others → raw UTF-8 bytes.

Coupling comment on _ArrayEncoder: element encoding must stay in sync with _FrontendMessage.bind() and _binary_codecs.pony — changes to scalar binary encoding must be mirrored here.

Changes to Existing Files

File	Change
field_data_types.pony	Add PgArray to union
field_data.pony	Add PgArray to docstring's built-in type list
field.pony	Delegate value comparison to _FieldDataEq; add PgArray to docstring
codec_registry.pony	Array interception in decode(), with_array_type(), _custom_array_element_oids, has_binary_codec()
_param_encoder.pony	PgArray arm
_frontend_message.pony	registry parameter, pre-encoding, three PgArray match arms
session.pony	Pass registry to all 6 bind() call sites
_test_frontend_message.pony	Pass CodecRegistry to 2 bind() call sites
_test_codecs.pony	Pass CodecRegistry to 4 bind() call sites
_test.pony	Register all new test classes
postgres.pony	Package docstring: document PgArray, add array example
examples/README.md	Add array example entry
CLAUDE.md	PgArray in Public API Types, array OID table in Type Conversion, _ArrayOidMap/_ArrayEncoder/_FieldDataEq in Codec Architecture, with_array_type()

New Files

File	Contents
pg_array.pony	PgArray class
_array_oid_map.pony	_ArrayOidMap primitive
_array_encoder.pony	_ArrayEncoder primitive
_field_data_eq.pony	_FieldDataEq primitive
_test_array.pony	Array-specific tests
examples/array/array-example.pony	Example program

Implementation Steps

1. Create _ArrayOidMap primitive
2. Create _FieldDataEq primitive
3. Refactor Field.eq() to delegate to _FieldDataEq
4. Create PgArray type
5. Add PgArray to FieldDataTypes
6. Create _ArrayEncoder primitive
7. Add _decode_binary_array, _decode_text_array, _custom_array_element_oids, with_array_type() to CodecRegistry; update decode() and has_binary_codec()
8. Update _ParamEncoder.oids_for() with PgArray arm
9. Update _FrontendMessage.bind() — add registry parameter, pre-encoding, all three match loops
10. Update all bind() call sites — 6 in session.pony, 2 in _test_frontend_message.pony, 4 in _test_codecs.pony
11. Write unit tests (_test_array.pony) — binary/text decode roundtrip per element type, NULL handling, empty arrays, validation errors, PgArray equality, _ParamEncoder, bind() wire format, property-based roundtrip tests
12. Register all new tests in _test.pony
13. Write integration tests (in _test_array.pony) — SELECT/INSERT arrays via PreparedQuery and SimpleQuery, multiple element types, roundtrip
14. Create example (examples/array/array-example.pony)
15. Update examples/README.md
16. Update postgres.pony package docstring
17. Update CLAUDE.md
18. Load /pony-release-notes and create release notes file — classification: added, label: changelog - added

Test Strategy

Unit Tests

• Binary decode roundtrip per element type: bool[], int2[], int4[], int8[], float4[], float8[], text[], bytea[], date[], time[], timestamp[], timestamptz[], interval[], uuid[], jsonb[], numeric[]
• Binary decode with NULLs — mixed NULL/non-NULL elements
• Binary decode empty array — ndim=0
• Binary decode validation errors — ndim > 1, bad has_null, truncated data, negative element_length, trailing garbage
• Text decode — simple, quoted, escaped chars, NULL (case-insensitive), empty string, empty array, multi-dimensional rejection
• PgArray equality — same/different element_oid, same/different elements, NULLs
• _ArrayEncoder roundtrip — encode then decode produces original values
• _ParamEncoder with PgArray — verify correct array OIDs
• _FrontendMessage.bind() with PgArray — verify wire format
• _FieldDataEq extraction — verify Field.eq() still works after refactoring

Property-Based Tests (PonyCheck)

• Valid/invalid binary decode generators — valid data always produces PgArray, invalid always errors
• Encode/decode roundtrip property — generate random PgArray, encode, decode, verify equality

Integration Tests (requires PostgreSQL)

• SELECT ARRAY[1,2,3]::int4[] via PreparedQuery (binary decode)
• SELECT ARRAY[1,2,3]::int4[] via SimpleQuery (text decode)
• SELECT $1::int4[] with PgArray parameter (encode roundtrip)
• NULL handling, empty arrays
• Multiple element types: int2[], int8[], float4[], float8[], text[], bool[], date[], timestamp[], uuid[]

Test Commands

make unit-tests ssl=3.0.x
make test-one t=Codec/Binary/Array/Int4/Roundtrip ssl=3.0.x
make integration-tests ssl=3.0.x
make examples ssl=3.0.x

Open Questions

1. Text format whitespace tolerance — PostgreSQL output is well-defined, but should the parser tolerate leading/trailing whitespace? Proposal: strict parsing matching server output. Adjust if integration tests reveal issues.

2. Empty array element_oid from server — Does PostgreSQL 14.5 always include a valid element_oid in the binary header for empty arrays? Proposal: use header value, fall back to mapped OID if header says 0. Verify empirically during integration testing.

3. Numeric array encode — _NumericBinaryCodec.encode() currently stubs with error. This means numeric[] parameters will fail at encode time. Acceptable for v1? (Numeric decode works fine.)

4. Custom array types and _ParamEncoder — _ParamEncoder uses _ArrayOidMap (static). For custom element OIDs, it falls back to OID 0 (server infers). Acceptable, or should _ParamEncoder become registry-aware?

[SeanTAllen]

Open Question Resolutions

1. Text format whitespace tolerance

Decision: strict parsing. PostgreSQL's text array output is well-defined ({1,2,3}, no extra whitespace). The server is the only producer of text-format array data in this context. Adjust if integration tests reveal issues.

2. Empty array element_oid from server

Decision: use header value, fall back to mapped OID if 0. Verify empirically during integration testing with SELECT '{}'::int4[] in binary format.

3. Numeric array encode

Decision: implement _NumericBinaryCodec.encode() as part of this work. Shipping array support that decodes numeric[] but fails on encode is not acceptable. The underlying issue is that scalar numeric binary encode also stubs with error — this needs to be fixed regardless.

This adds a step to the plan: implement numeric binary encode (parsing decimal string → PostgreSQL base-10000 digit format). The format is the inverse of the existing decode logic in _NumericBinaryCodec — parse the string into sign, integer digits, and fractional digits, convert to base-10000 words, compute weight and dscale, write the header and digit words in big-endian.

This also requires tests: roundtrip for positive/negative integers, fractional values, zero, NaN, Infinity/-Infinity, and values with trailing zeros (dscale preservation). Integration test: INSERT a numeric value via PreparedQuery parameter, SELECT it back, verify equality.

4. Custom array types and _ParamEncoder

Decision: make _ParamEncoder registry-aware. If with_array_type() is a public API, the encode path must work end-to-end without requiring manual casts. _ParamEncoder.oids_for() gains a CodecRegistry parameter and consults CodecRegistry.array_oid_for(element_oid) (a new public method wrapping _ArrayOidMap + custom mappings) before falling back to U32(0).

The change flows naturally: _FrontendMessage.parse() calls _ParamEncoder.oids_for() and already receives the registry (per the bind() signature change). Thread it through to oids_for().

Updated implementation steps affected:

• Step 7 (CodecRegistry): add array_oid_for(element_oid): U32 public method
• Step 8 (_ParamEncoder): oids_for() gains registry: CodecRegistry parameter, uses registry.array_oid_for() instead of _ArrayOidMap.array_oid_for() directly
• New step between 6 and 7: implement _NumericBinaryCodec.encode() with tests
• Step 9 (_FrontendMessage): parse() passes registry to _ParamEncoder.oids_for()

[SeanTAllen]

Implemented in #156