Prepared Statements Design

[SeanTAllen]

Goal

Add parameterized query support using PostgreSQL's extended query protocol. This eliminates SQL injection risks from string interpolation and enables type-safe parameter passing.

Scope

This Implementation

• Unnamed prepared statements (Parse + Bind + Execute per query)
• Text-format parameters (all values sent as strings, server handles type conversion)
• Describe(Portal) for result column metadata
• max_rows = 0 (complete result sets only)

Future Work

• Named prepared statements (prepare once, execute many)
• Binary-format parameters
• Portal suspension / cursor-style partial fetching
• Pipelining multiple extended query sequences
• Explicit Describe for metadata introspection

Public API

New Types

class val PreparedQuery
  """
  A parameterized query using PostgreSQL's extended query protocol.
  Parameters are referenced as $1, $2, ... in the query string.
  Values are sent in text format; use None for NULL.

  The query string must contain a single SQL statement. Multi-statement
  queries are not supported by the extended query protocol; use SimpleQuery
  for multi-statement execution.
  """
  let string: String
  let params: Array[(String | None)] val

  new val create(string': String, params': Array[(String | None)] val) =>
    string = string'
    params = params'

API Unification (Breaking Change)

Introduce a Query type alias:

type Query is (SimpleQuery | PreparedQuery)

Update Session, Result, and ResultReceiver to use Query:

// Session
be execute(query: Query, receiver: ResultReceiver)

// Result
trait val Result
  fun query(): Query

// ResultReceiver
interface tag ResultReceiver
  be pg_query_result(result: Result)
  be pg_query_failed(query: Query, failure: (ErrorResponseMessage | ClientQueryError))

Migration: Existing code changes SimpleQuery to Query in ResultReceiver implementations and Result.query() call sites. Since SimpleQuery is a member of the Query union, passing SimpleQuery values still works unchanged at call sites.

Alternative considered: Separate execute_prepared behavior. This avoids breaking changes but duplicates the queue/execution infrastructure and forces users to choose upfront which receiver interface to implement, even when they want to handle both query types identically.

Files Modified

The breaking change touches these files:

Public API types:

• simple_query.pony — no change (still class val SimpleQuery)
• New file: prepared_query.pony — PreparedQuery class
• New file: query.pony — Query type alias
• result.pony — Result trait and ResultSet, SimpleResult, RowModifying change SimpleQuery to Query
• result_receiver.pony — pg_query_failed parameter changes from SimpleQuery to Query

Session state machine:

• session.pony — Session.execute signature, _SessionState.execute signature, all concrete state execute methods, _SessionLoggedIn.query_queue type, _SessionLoggedIn.on_shutdown, new _SessionLoggedIn delegation methods, new _SessionState callbacks, _NotAuthenticated defaults, _QueryState new callbacks, _QueryNoQueryInFlight defaults, _QueryReady.try_run_query query-type dispatch, _SimpleQueryInFlight shutdown defaults for new callbacks, new _ExtendedQueryInFlight class

Protocol layer:

• _message_type.pony — new message type constants
• _backend_messages.pony — new message classes
• _response_parser.pony — junk detection fix, new match branches, _ResponseParserResult union update
• _frontend_message.pony — new message functions
• _response_message_parser.pony — routing for new message types

Tests:

• _test_frontend_message.pony — new frontend message tests
• _test_response_parser.pony — new parser tests, new test message builders
• _test_query.pony — new integration tests, update existing receivers (SimpleQuery to Query in pg_query_failed)
• _test.pony — register new tests, update test actors (_DoesntAnswerClient including its SetIs[SimpleQuery] field → SetIs[Query], _ZeroRowSelectTestClient, etc.)

Examples:

• examples/query/query-example.pony — update pg_query_failed parameter type

Wire Protocol

Extended Query Sequence

Client -> Server:  Parse("", query, []) + Bind("", "", params) + Describe("P", "") + Execute("", 0) + Sync
Server -> Client:  ParseComplete + BindComplete + RowDescription/NoData + DataRow* + CommandComplete + ReadyForQuery

All statements and portals are unnamed (lifetime = until next Parse/Bind).

All five frontend messages are sent as a single concatenated buffer via one send() call. This avoids Nagle's algorithm delays and matches how PostgreSQL pipelining works. Each _FrontendMessage function returns its own Array[U8] val for independent testability; _QueryReady.try_run_query concatenates them before sending.

Frontend Messages (additions to _FrontendMessage)

Function	Type Byte	Purpose
parse(name, query, param_type_oids)	'P'	Create unnamed prepared statement
bind(portal, stmt, params)	'B'	Bind parameters to create portal
describe_portal(portal)	'D'	Request result column metadata
execute_msg(portal, max_rows)	'E'	Execute portal
sync()	'S'	Synchronization point

close and flush are not needed for unnamed objects but could be added for completeness.

Note: The frontend type bytes 'D', 'E', 'C' overlap with backend message types (DataRow, ErrorResponse, CommandComplete). This is not a conflict — the parser only handles backend messages. Frontend message functions use char literals directly rather than adding confusingly-named entries to _MessageType.

Parse Message Format

Byte1('P') Int32(len) String(stmt_name) String(query) Int16(num_param_types) Int32[](param_type_oids)

For unnamed statements: stmt_name = "". For server-inferred types: num_param_types = 0 (no OID array).

Bind Message Format

Byte1('B') Int32(len) String(portal_name) String(stmt_name) Int16(num_param_formats) Int16[](param_formats) Int16(num_params) [Int32(val_len) Byte[](val)]* Int16(num_result_formats) Int16[](result_formats)

Text-format shorthand: num_param_formats = 0 (all text), num_result_formats = 0 (all text). NULL parameters use val_len = -1 with no value bytes.

Describe Message Format

Byte1('D') Int32(len) Byte1('P') String(portal_name)

Describe portal (not statement) to get RowDescription without ParameterDescription.

Execute Message Format

Byte1('E') Int32(len) String(portal_name) Int32(max_rows)

max_rows = 0 means fetch all rows.

Sync Message Format

Byte1('S') Int32(4)

Fixed 5-byte message. Triggers ReadyForQuery response.

Backend Messages (additions to _backend_messages.pony)

Type	Byte	Class	Data
ParseComplete	'1'	_ParseCompleteMessage	None (sentinel)
BindComplete	'2'	_BindCompleteMessage	None (sentinel)
CloseComplete	'3'	_CloseCompleteMessage	None (sentinel)
NoData	'n'	_NoDataMessage	None (sentinel)
ParameterDescription	't'	_ParameterDescriptionMessage	param_oids: Array[U32] val
PortalSuspended	's'	_PortalSuspendedMessage	None (sentinel)

ParameterDescription only arrives from Describe(Statement), which we don't use. Including it in the parser for completeness and forward compatibility.

Response Parser Changes

_ResponseParserResult Update

All six new message types must be added to the _ResponseParserResult type union in _response_parser.pony. Without this, the new match branches cannot return the new types and the code will not compile:

type _ResponseParserResult is
  ( _AuthenticationMessages
  | _CommandCompleteMessage
  | _DataRowMessage
  | _EmptyQueryResponseMessage
  | _ReadyForQueryMessage
  | _RowDescriptionMessage
  | _ParseCompleteMessage
  | _BindCompleteMessage
  | _CloseCompleteMessage
  | _NoDataMessage
  | _ParameterDescriptionMessage
  | _PortalSuspendedMessage
  | _UnsupportedMessage
  | ErrorResponseMessage
  | None )

Junk Detection Fix

The current parser rejects non-letter message types as junk:

if ((message_type < 'A') or (message_type > 'z')) or
  ((message_type > 'Z') and (message_type < 'a'))
then
  error
end

This rejects '1' (49), '2' (50), '3' (51) as junk, which crashes the session when ParseComplete/BindComplete/CloseComplete arrive. Fix by also accepting digits:

// Digits ('0'-'9') are accepted for ParseComplete('1'), BindComplete('2'),
// CloseComplete('3'). Uppercase and lowercase ASCII letters cover all other
// valid PostgreSQL backend message types.
if (message_type < '0') or (message_type > 'z') or
  ((message_type > '9') and (message_type < 'A')) or
  ((message_type > 'Z') and (message_type < 'a'))
then
  error
end

New Match Branches

Add cases to _ResponseParser.apply():

• '1' -> skip message, return _ParseCompleteMessage
• '2' -> skip message, return _BindCompleteMessage
• '3' -> skip message, return _CloseCompleteMessage
• 'n' -> skip message, return _NoDataMessage
• 's' -> skip message, return _PortalSuspendedMessage
• 't' -> parse Int16(num_params) + Int32, return _ParameterDescriptionMessage

New match branches use character literals directly (e.g., '1', 'n') rather than adding entries to _MessageType. The existing _MessageType naming convention (authentication_request(), command_complete(), etc.) doesn't extend cleanly to names like "parse_complete" which could be confused with the Parse frontend message. Adding _MessageType constants for these is optional and can be decided during implementation.

Implementation coordination: The parser changes (_ResponseParser + _ResponseParserResult) and the router changes (_ResponseMessageParser) must be done together. If the parser returns new types that the router doesn't handle, they will silently fall through the match in _ResponseMessageParser.apply(). The implementation phases list these in different phases for conceptual clarity, but they must be committed together.

Session State Machine

New Callbacks on _SessionState

fun ref on_parse_complete(s: Session ref)
fun ref on_bind_complete(s: Session ref)
fun ref on_close_complete(s: Session ref)
fun ref on_no_data(s: Session ref)
fun ref on_parameter_description(s: Session ref, msg: _ParameterDescriptionMessage)
fun ref on_portal_suspended(s: Session ref)

Default implementations via the trait hierarchy:

• _NotAuthenticated trait: _IllegalState() for all six new callbacks (consistent with existing query callbacks like on_command_complete, on_data_row, etc.)
• _SessionLoggedIn: delegates all six to query_state (same pattern as existing query callbacks)

New Callbacks on _QueryState

The _QueryState interface gains six new methods, mirroring the _SessionState additions:

fun ref on_parse_complete(s: Session ref, li: _SessionLoggedIn ref)
fun ref on_bind_complete(s: Session ref, li: _SessionLoggedIn ref)
fun ref on_close_complete(s: Session ref, li: _SessionLoggedIn ref)
fun ref on_no_data(s: Session ref, li: _SessionLoggedIn ref)
fun ref on_parameter_description(s: Session ref, li: _SessionLoggedIn ref,
  msg: _ParameterDescriptionMessage)
fun ref on_portal_suspended(s: Session ref, li: _SessionLoggedIn ref)

Default implementations:

• _QueryNoQueryInFlight trait: li.shutdown(s) for all six (consistent with existing defaults for on_command_complete, on_data_row, etc.)
• _SimpleQueryInFlight: li.shutdown(s) for all six — receiving extended query protocol messages during a simple query is a protocol anomaly

New _ExtendedQueryInFlight Query State

A new _QueryState implementation analogous to _SimpleQueryInFlight. Owns per-query accumulation data:

class _ExtendedQueryInFlight is _QueryState
  var _data_rows: Array[Array[(String|None)] val] iso
  var _row_description: (Array[(String, U32)] val | None)

  new create() =>
    _data_rows = recover iso Array[Array[(String|None)] val] end
    _row_description = None

Callback behavior:

• on_parse_complete: no-op acknowledgment
• on_bind_complete: no-op acknowledgment
• on_no_data: no-op — _row_description stays None (initialized by constructor), and CommandComplete uses _row_description presence to choose result type
• on_close_complete: li.shutdown(s) — we never send Close, so receiving this is anomalous
• on_parameter_description: li.shutdown(s) — we Describe portals not statements, so receiving this is anomalous
• on_portal_suspended: li.shutdown(s) — we use max_rows = 0, so receiving this is anomalous. Note: if future work enables max_rows > 0, this callback would need real handling and _ResponseMessageParser would need a yield point on PortalSuspended similar to ReadyForQuery

Data accumulation and result delivery (on_data_row, on_row_description, on_command_complete, on_empty_query_response, on_error_response, on_ready_for_query) are identical to _SimpleQueryInFlight. Both states accumulate rows and metadata in the same fields, and both deliver results to the receiver at query_queue(0). This is ~80 lines of shared logic. Factoring it into a shared trait is desirable but may be awkward in Pony — traits can't have fields, so the iso field access patterns (destructive reads in on_command_complete) would need accessor methods or a different factoring approach. If a clean trait extraction isn't feasible, parallel implementations are acceptable with a comment linking the two classes as intentional mirrors.

try_run_query is a no-op (same as _SimpleQueryInFlight — once a query is in flight, another cannot start).

_QueryReady.try_run_query Changes

Currently dispatches only simple queries:

fun ref try_run_query(s: Session ref, li: _SessionLoggedIn ref) =>
  try
    if li.query_queue.size() > 0 then
      (let query, _) = li.query_queue(0)?
      li.query_state = _SimpleQueryInFlight.create()
      s._connection().send(_FrontendMessage.query(query.string))
    end
  else
    _Unreachable()
  end

Updated to match on query type:

fun ref try_run_query(s: Session ref, li: _SessionLoggedIn ref) =>
  try
    if li.query_queue.size() > 0 then
      (let query, _) = li.query_queue(0)?
      match query
      | let q: SimpleQuery =>
        li.query_state = _SimpleQueryInFlight.create()
        s._connection().send(_FrontendMessage.query(q.string))
      | let q: PreparedQuery =>
        li.query_state = _ExtendedQueryInFlight.create()
        // Concatenate all five messages into a single send
        let buf: Array[U8] val = recover val
          let parse = _FrontendMessage.parse("", q.string, [])
          let bind = _FrontendMessage.bind("", "", q.params)
          let describe = _FrontendMessage.describe_portal("")
          let exec = _FrontendMessage.execute_msg("", 0)
          let sync = _FrontendMessage.sync()
          let out = Array[U8](
            parse.size() + bind.size() + describe.size()
            + exec.size() + sync.size())
          out.>append(parse).>append(bind).>append(describe)
            .>append(exec).>append(sync)
          out
        end
        s._connection().send(buf)
      end
    end
  else
    _Unreachable()
  end

_SessionLoggedIn Changes

• query_queue type: Array[(SimpleQuery, ResultReceiver)] changes to Array[(Query, ResultReceiver)]
• execute signature: SimpleQuery changes to Query. The same signature change applies to _SessionState.execute and all four concrete implementations: _SessionUnopened.execute, _SessionClosed.execute, _SessionConnected.execute, and _SessionLoggedIn.execute
• on_shutdown: loop variable destructure gets Query instead of SimpleQuery
• New delegation methods: six new methods that delegate to query_state, following the same pattern as existing callbacks:

fun ref on_parse_complete(s: Session ref) =>
  query_state.on_parse_complete(s, this)

fun ref on_bind_complete(s: Session ref) =>
  query_state.on_bind_complete(s, this)
// ... etc for all six

Error Recovery

Extended query error handling works naturally with existing logic:

1. If any step fails (Parse, Bind, Execute), the server sends ErrorResponse and discards all remaining messages in the sequence until it reaches the Sync.
2. on_error_response (delegated through _SessionLoggedIn to _ExtendedQueryInFlight) delivers the failure to the receiver — the implementation is the same as _SimpleQueryInFlight.
3. The server processes the Sync and sends ReadyForQuery.
4. on_ready_for_query dequeues the completed query and transitions to _QueryReady or _QueryNotReady — again, the same implementation as _SimpleQueryInFlight.

The acknowledgment messages (ParseComplete, BindComplete) that may arrive before the failing step are no-ops, so they don't interfere. If the first step (Parse) fails, no acknowledgments arrive at all — only ErrorResponse followed by ReadyForQuery.

EmptyQueryResponse is also possible for extended queries (e.g., PreparedQuery("", [])) and is handled correctly via the same on_empty_query_response logic shared with _SimpleQueryInFlight.

Transaction status caveat: The dequeue (query_queue.shift()) happens unconditionally in on_ready_for_query before the idle check, so the completed query is always properly removed. However, if the error occurs inside an explicit transaction (BEGIN/COMMIT), the server sends ReadyForQuery with failed-transaction status ('E'), and the state transitions to _QueryNotReady rather than _QueryReady. This means the next queued query won't be dispatched until an idle ReadyForQuery arrives (which requires the client to issue ROLLBACK). This is correct behavior — you can't execute queries in a failed transaction — but the session has no way to recover without the client knowing to send a ROLLBACK via SimpleQuery. This is a pre-existing limitation that affects both simple and extended queries and is out of scope for this work.

_ResponseMessageParser Routing

Add routing for new message types to _ResponseMessageParser.apply():

| _ParseCompleteMessage =>
  s.state.on_parse_complete(s)
| _BindCompleteMessage =>
  s.state.on_bind_complete(s)
| _NoDataMessage =>
  s.state.on_no_data(s)
| let msg: _ParameterDescriptionMessage =>
  s.state.on_parameter_description(s, msg)
| _CloseCompleteMessage =>
  s.state.on_close_complete(s)
| _PortalSuspendedMessage =>
  s.state.on_portal_suspended(s)

Testing

Build and Run Commands

make ssl=$VERSION                   # build and run all tests
make unit-tests ssl=$VERSION        # unit tests only (no postgres needed)
make integration-tests ssl=$VERSION # integration tests (needs postgres)

Integration tests require a running PostgreSQL 14.5 (make start-pg-container). See the Makefile for supported ssl values.

Unit Tests — Frontend Messages

• _TestFrontendMessageParse — Parse with empty name, query with $1 parameter, zero param type OIDs
• _TestFrontendMessageParseWithTypes — Parse with explicit param type OIDs
• _TestFrontendMessageBind — Bind with text params, unnamed portal/statement
• _TestFrontendMessageBindWithNull — Bind with NULL parameter (val_len = -1)
• _TestFrontendMessageDescribePortal — Describe with type byte 'P' and empty name
• _TestFrontendMessageExecute — Execute with unnamed portal, max_rows = 0
• _TestFrontendMessageSync — Fixed 5-byte Sync message

Unit Tests — Response Parser

• _TestResponseParserParseCompleteMessage — Verify '1' parsed correctly
• _TestResponseParserBindCompleteMessage — Verify '2' parsed correctly
• _TestResponseParserNoDataMessage — Verify 'n' parsed correctly
• _TestResponseParserCloseCompleteMessage — Verify '3' parsed correctly
• _TestResponseParserParameterDescriptionMessage — Verify 't' with param OIDs
• _TestResponseParserPortalSuspendedMessage — Verify 's' parsed correctly
• _TestResponseParserDigitMessageTypeNotJunk — Verify '1' is accepted by junk detection (and '/' is still rejected)
• _TestResponseParserMultipleMessagesParseCompleteFirst — ParseComplete + BindComplete + CommandComplete + ReadyForQuery sequence (verify buffer advancement across the full extended query response)

Test Message Builders

New _Incoming*TestMessage classes for constructing raw protocol bytes:

• _IncomingParseCompleteTestMessage — '1' + Int32(4)
• _IncomingBindCompleteTestMessage — '2' + Int32(4)
• _IncomingNoDataTestMessage — 'n' + Int32(4)
• _IncomingCloseCompleteTestMessage — '3' + Int32(4)
• _IncomingParameterDescriptionTestMessage — 't' + Int32(len) + Int16(count) + Int32[](oids)
• _IncomingPortalSuspendedTestMessage — 's' + Int32(4)

Integration Tests

• _TestPreparedQueryResults — SELECT $1::text with one parameter, verify result value matches
• _TestPreparedQueryInsert — INSERT INTO ... VALUES ($1, $2) with text params
• _TestPreparedQueryNullParam — Parameter with None value, verify NULL handling
• _TestPreparedQueryMultipleParams — Query with 3+ parameters of different types
• _TestPreparedQueryNonExistentTable — SELECT * FROM non_existent_table WHERE id = $1 with param, verify error delivery
• _TestPreparedQueryAfterSessionClosed — Execute after close, verify SessionClosed error
• _TestPreparedQueryEmptyParams — Query with $-parameters but empty params array (server error)
• _TestPreparedQueryZeroParams — Query with no $-parameters, empty params array (extended protocol exercised with no parameters)
• _TestPreparedQueryMixedWithSimple — Alternate simple and prepared queries on same session

Implementation Phases

These phases are for conceptual organization. In practice, the protocol layer (phase 1) and session integration (phase 4) must be committed together so the parser and router stay in sync.

1. Protocol layer: _MessageType additions, _backend_messages new classes, _ResponseParserResult union update, _ResponseParser junk fix + new branches, _FrontendMessage new functions
2. Protocol tests: Unit tests for all new frontend and backend messages
3. Public API: PreparedQuery, Query type alias, update ResultReceiver, Result, all Result implementations, all existing test actors
4. Session integration: _SessionState new callbacks + _NotAuthenticated defaults, _QueryState new callbacks + _QueryNoQueryInFlight defaults, _SimpleQueryInFlight shutdown defaults for new callbacks, new _ExtendedQueryInFlight class, _SessionLoggedIn delegation + type changes, _QueryReady.try_run_query query-type dispatch, _ResponseMessageParser routing
5. Integration tests: End-to-end tests with PostgreSQL
6. Documentation: Docstrings on public types, update CLAUDE.md architecture section

Decision Points

These are areas where multiple valid approaches exist. The plan takes a position on each, but alternatives are noted.

1. Unified execute vs separate execute_prepared: The plan recommends unifying under a single execute(Query, ResultReceiver) (breaking change to ResultReceiver signature). The alternative — a separate execute_prepared behavior — avoids breaking changes but duplicates infrastructure.

2. Including close and flush frontend messages: Not needed for unnamed objects. Could add now for forward compatibility with named statements, or defer until needed.

3. Single send vs multiple sends: The plan recommends concatenating all five extended query messages into a single buffer before calling send() once. The alternative — five separate send() calls — would work functionally but is less efficient. The individual message functions are still separate for testability; concatenation happens in _QueryReady.try_run_query.

4. Shared data accumulation logic: _ExtendedQueryInFlight and _SimpleQueryInFlight have ~80 lines of identical logic. A shared trait is preferred but may conflict with Pony's trait limitations (no fields, awkward iso accessor patterns). If a clean trait extraction isn't feasible, parallel implementations with cross-referencing comments are acceptable. This decision is deferred to implementation since the right answer depends on what Pony's type system allows.

[SeanTAllen]

Implemented in PR #70.