Skip to content

AGENTS.md

Latest commit

 

History

History
266 lines (208 loc) · 12.8 KB

AGENTS.md

File metadata and controls

266 lines (208 loc) · 12.8 KB
Raw

pgtoon — Agent Context

This file provides context for AI coding agents working on the pgtoon project. It covers architecture, conventions, testing, and known constraints so you can contribute effectively without re-discovering decisions already made.

Project Summary

pgtoon is a pure PL/pgSQL PostgreSQL extension that encodes query results as TOON (Token-Oriented Object Notation) — a compact, line-oriented format designed for LLM prompt contexts. It conforms to TOON Specification v3.3.

Repository Layout

pgtoon--0.1.sql          # Canonical extension source (contains @extschema@ markers)
pgtoon.control           # PostgreSQL extension metadata (relocatable=false)
test_pgtoon.sql          # Regression suite
Makefile                 # Build: tle (default), local, install; test-*; clean, help
.github/workflows/test.yml  # CI: all three install paths on PG 14-18
create_pgtle_scripts.sh  # Vendored pg_tle helper (from github.com/aws/pg_tle)
README.md                # User-facing documentation
AGENTS.md                # This file

Architecture

Functions (public API)

Function Purpose
to_toon(anyelement, delim) Generic encoder: scalars, arrays, records → TOON
row_to_toon(anyelement, delim) Record → TOON object (key: value lines)
toon_agg(anyelement [, delim]) Aggregate → TOON tabular array with header + rows
rows_to_toon(anyarray [, delim]) Record array → TOON tabular array, single set-based pass (linear)

Internal helpers (not intended for direct use)

Function Purpose
toon_escape(text) §7.1 escape rules inside quoted strings
toon_quote_key(text) §7.3 key quoting
toon_quote_value(text, delim) §7.2 value quoting
toon_encode_field(raw_json, text_val, delim) Type-aware field encoding
toon_agg_sfunc(text[], rec, delim) Transition for the 2-arg toon_agg (validates delimiter, skips NULL records)
toon_agg_ffunc(text[]) Final function for the 2-arg toon_agg (header + rows)
toon_agg_rows_ffunc(anycompatiblearray) Finalizer for default toon_agg → rows_to_toon(state, ',')
toon_delim_ok(text) / toon_rows_ok(int, int) Scalar validation helpers (RAISE lives here; plpgsql cannot accept record[])

Aggregate state & performance rules (issue #4)

Two hard-won rules, both measured on PG 16:

  1. Only a C transition function is linear. PostgreSQL flattens and re-expands SQL/plpgsql transition state on every call, so any custom transition accumulating an array is O(n²) no matter what its body does. The default toon_agg(anycompatible) therefore uses pg_catalog.array_append (STYPE anycompatiblearray, INITCOND '{}') and defers all encoding to the finalizer via rows_to_toon. The two-arg variant cannot use array_append (no third argument), so it keeps a plpgsql transition with text[] state (state[1] = header, state[2] = delimiter, state[3..] = encoded rows) and stays quadratic — documented, with rows_to_toon(array_agg(...), delim) as the linear escape hatch.

  2. Never subscript a large flat array in a loop. Flat varlena arrays have O(i) element access, so rows[i] over all i is O(n²). rows_to_toon serializes the array once with array_to_json and iterates json_array_elements. (Expanded arrays — like the finalizer's state — have O(1) access, but caller-supplied array_agg results arrive flat.)

Design Decisions

  • Uses row_to_json + json_each internally to introspect record field names and values, since PL/pgSQL cannot iterate over record fields natively.
  • Type detection uses dual scan: json_each (preserves JSON type markers in value::text) joined with json_each_text (provides unescaped text, NULL for JSON null) on ordinality. This avoids fragile JSON unescaping.
  • NaN/Infinity detection: uses pg_typeof(val) to distinguish float NaN (→ null per §3) from the string literal "NaN" (→ normal string). PG wraps float NaN as a JSON string "NaN", making them otherwise indistinguishable.

Supported Versions

PostgreSQL 14 and newer. Versions 12 and 13 are past upstream end-of-life and are not tested; don't add workarounds for them. When a new PostgreSQL major is released, add it to the matrix in .github/workflows/test.yml; when one goes EOL, drop it from the matrix and bump the floor here and in README.md.

CI builds pg_tle from a pinned commit (PG_TLE_REF in the workflow) because no tagged pg_tle release supports PG 18 yet. Switch to a release tag once one does.

CI installs postgresql-server-dev-N without its clang/llvm-dev dependencies and builds pg_tle with with_llvm=no (no JIT bitcode), which skips a ~75 MB download that used to stall jobs for 10–20 minutes. Jobs time out after 15 minutes.

Security Model

Every function has:

  • SET search_path = pg_catalog, pg_temp (locked, no user-controlled schemas)
  • Internal calls qualified with @extschema@ (substituted at CREATE EXTENSION)
  • CREATE FUNCTION (not OR REPLACE) to prevent pre-creation attacks

The extension is relocatable = false because @extschema@ substitution requires it for both filesystem and pg_tle installs.

Build & Install Paths

There are three install methods. The canonical source pgtoon--0.1.sql contains @extschema@ markers that get substituted at CREATE EXTENSION time.

Method Command How @extschema@ resolves
pg_tle (managed DB) make tle → psql -f .pgtle-pgtoon.sql → CREATE EXTENSION pgtoon pg_tle substitutes at install
Filesystem extension Copy to pg_config --sharedir/extension → CREATE EXTENSION pgtoon [SCHEMA x] PG substitutes at install
Standalone (no extension) make local SCHEMA=toon → psql -f pgtoon-local.sql sed replaces with literal schema

You cannot \i pgtoon--0.1.sql directly — the @extschema@ markers will cause a parse error. Always use one of the three paths above.

pg_tle

pg_tle (Trusted Language Extensions) must be built and installed into the server before make test-tle or a pg_tle install can work. Upstream docs (pinned to the release that covers PG 14–19):

Notes from building it for this repo (.github/workflows/test.yml does all of this and is the reference):

  • Build with make PG_CONFIG=/usr/lib/postgresql/N/bin/pg_config and install with sudo make install PG_CONFIG=.... Besides the server headers (postgresql-server-dev-N), the build needs flex, bison, and libkrb5-dev (for gssapi/gssapi.h).
  • Pass with_llvm=no to both make and make install if clang/llvm aren't installed; it skips JIT bitcode, which pg_tle doesn't need.
  • pg_tle must be in shared_preload_libraries and the server restarted, or CREATE EXTENSION pg_tle fails.
  • pg_tle refuses to register an extension that is also installed on the filesystem (control file already exists). Run make test-tle before make install, or make uninstall first.

Testing

Running tests

# Make targets (recreate $TESTDB, fail non-zero on any assertion failure):
make test              # standalone build
make test-tle          # pg_tle install; run BEFORE make install
make install && make test-extension

# Or by hand, against a standalone install:
make local
psql -f pgtoon-local.sql
psql -c "SET search_path = toon, pg_catalog, pg_temp" -f test_pgtoon.sql

# Against a CREATE EXTENSION install (set search_path to the extension schema):
psql -c "CREATE EXTENSION pgtoon"
psql -f test_pgtoon.sql  # functions are in public by default

Test framework

Tests are an assertion-based regression suite using a temp table + assert_toon(name, actual, expected) helper. Output is a summary row: passed | failed | total. Any failures also print the test name with expected vs actual values via RAISE NOTICE, and a final DO block raises an exception so psql exits non-zero (this is what CI keys on).

Test requirements

  • PostgreSQL 14+ (the supported floor; CI runs 14, 15, 16, 17 and 18)
  • The extension must be installed before running tests
  • Tests are self-contained (CREATE/DROP their own temp tables)

Conventions

Commits

Follow Conventional Commits: feat:, fix:, docs:, refactor:, chore:. Imperative mood, max 50-char subject. Body explains what/why.

Code style

  • SQL keywords lowercase in function bodies
  • 4-space indent inside function bodies
  • LANGUAGE sql preferred over plpgsql for pure-SQL functions
  • Volatility: pure string helpers (toon_escape, toon_quote_key, toon_quote_value, toon_encode_field) are IMMUTABLE. Anything built on to_json/row_to_json (to_toon, row_to_toon, the toon_agg support functions) must be STABLE — their output depends on session GUCs (TimeZone, DateStyle, extra_float_digits), exactly why PostgreSQL marks the json builtins STABLE. Marking them IMMUTABLE allows wrong results in expression indexes.
  • Every function must have SET search_path = pg_catalog, pg_temp
  • Internal calls must use @extschema@.function_name()

Adding a new function

  1. Add to pgtoon--0.1.sql with CREATE FUNCTION (no OR REPLACE)
  2. Add SET search_path = pg_catalog, pg_temp
  3. Qualify any calls to other pgtoon functions with @extschema@.
  4. Add tests in test_pgtoon.sql
  5. Run the suite on all three install paths: make test, make test-tle, make install && make test-extension — expect 0 failures

Before committing

  • Run make test (all tests pass)
  • Ideally also make test-tle and make install && make test-extension; CI runs all three on PostgreSQL 14–18 for every push and PR

TOON Spec Quick Reference (for encoders)

Key rules from the spec that affect implementation decisions:

  • §2: Numbers in canonical decimal for [1e-6, 1e21); -0→0; NaN/±Infinity→null
  • §7.1: Escapes in quoted strings: \\, \", \n, \r, \t, \uXXXX
  • §7.2: Quote strings if: empty, leading/trailing whitespace, true/false/null, numeric-like, contains :"\\{}[], contains active delimiter, starts with -
  • §7.3: Keys unquoted only if ^[A-Za-z_][A-Za-z0-9_.]*$
  • §8: Objects: key: value lines, indented for nesting
  • §9.1: Inline arrays: [N]: v1,v2,...; empty: []
  • §9.3: Tabular arrays: [N]{f1,f2}: + indented rows
  • §11: Delimiters: comma (default, no symbol), pipe (|), tab
  • §12: 2-space indent, LF line endings, no trailing whitespace/newline
  • §13.1: Encoder conformance checklist (all items implemented)

Known Limitations

  • Text values NaN/Infinity/-Infinity in records and arrays: encoded as null by row_to_toon, toon_agg, and the array path of to_toon (data loss). On those paths the encoder sees only row_to_json/JSON output, and PostgreSQL emits a float NaN and the string "NaN" identically ({"f1":"NaN"}), so the two are indistinguishable; the ambiguity is resolved toward §3's float rule (NaN → null). The scalar to_toon('NaN'::text) path has pg_typeof available and correctly returns the string. Workaround: cast such columns explicitly, e.g. 'x' || col, or pre-quote them.
  • Nested objects/arrays in record fields: rendered as quoted text representation, not as indented TOON nesting. PL/pgSQL lacks the type introspection needed.
  • Multi-dimensional arrays: flattened to quoted string, not §9.2 expanded list.
  • \uXXXX for U+0000-U+001F (other than \n\r\t): not yet implemented. PG text fields rarely contain these.
  • Number canonical form: delegated to PostgreSQL's numeric output. Generally conforms but edge cases with very small/large floats may differ from spec preference.

Roadmap (unimplemented, prioritized)

  1. toon_to_json(text) — TOON decoder (parse TOON → json value)
  2. array_to_toon(anyarray) — multi-dimensional array support (§9.2)
  3. toon_each - parse a TOON object & return key/value rows (analag of JSON_each)
  4. `toon_populate_record(anyrecord,text) - fill a record type from TOON row data
  5. Nested object encoding in row_to_toon for composite-typed fields
  6. \uXXXX control character escaping
  7. Spec compliance test vectors (from github.com/toon-format/spec/tree/main/tests)
  8. Performance benchmarks - compare toon_agg vs json_agg
  9. Version upgrade path (pgtoon--0.1--0.2.sql)