Design: Named Prepared Statements

[SeanTAllen]

Overview

The driver currently supports two query protocols:

• Simple query (SimpleQuery): Sends a SQL string directly. Can contain multiple statements.
• Extended query with unnamed statement (PreparedQuery): Parses, binds, and executes a parameterized single-statement query in one shot. The unnamed statement is destroyed by the next Parse or simple Query.

Named prepared statements add a third mode: pre-prepared named statements. The SQL is parsed once with session.prepare(), then executed multiple times with different parameters via session.execute(), and eventually cleaned up with session.close_statement(). The server retains the parsed and planned query, eliminating the Parse step on repeated executions.

Named statements are independent of the unnamed statement. Simple queries and unnamed PreparedQuery executions destroy the unnamed statement but have no effect on named statements. The three protocols can be freely interleaved.

Public API

New types

NamedPreparedQuery -- a val class representing an execution of a previously prepared named statement:

class val NamedPreparedQuery
  """
  An execution against a previously prepared named statement. The statement
  must have been created via `Session.prepare()` before executing. Parameters
  are referenced as $1, $2, ... and correspond to the parameter positions in
  the original prepared SQL string.
  """
  let name: String
  let params: Array[(String | None)] val

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

Unlike PreparedQuery, NamedPreparedQuery does not carry the SQL string -- the SQL was provided during session.prepare(). The name field identifies which server-side statement to execute.

PrepareReceiver -- callback interface for prepare operations:

interface tag PrepareReceiver
  be pg_statement_prepared(session: Session, name: String)
    """
    Called when a named prepared statement has been successfully created
    on the server. The statement is now ready for execution via
    NamedPreparedQuery.
    """

  be pg_prepare_failed(name: String,
    failure: (ErrorResponseMessage | ClientQueryError))
    """
    Called when a prepare operation fails. Failures include SQL syntax errors,
    invalid table/column references, or session-level errors (SessionClosed,
    SessionNeverOpened, SessionNotAuthenticated).
    """

Updated union type

type Query is (SimpleQuery | PreparedQuery | NamedPreparedQuery)

This is a breaking change. Any code that pattern-matches on Query must add a branch for NamedPreparedQuery. The Pony compiler enforces exhaustive matching, so all affected code is caught at compile time.

New Session behaviors

actor Session
  be prepare(name: String, sql: String, receiver: PrepareReceiver) =>
    """
    Create a named prepared statement on the server. The SQL string must
    contain a single statement with parameter placeholders ($1, $2, ...).
    The receiver is notified when the statement is ready for execution
    or if preparation fails.

    Named statements persist until explicitly closed via close_statement()
    or until the session ends. A statement name must be unique -- preparing
    a statement with a name that already exists on the server results in
    a server error delivered to the receiver.
    """
    state.prepare(s, name, sql, receiver)

  be close_statement(name: String) =>
    """
    Destroy a named prepared statement on the server. This is fire-and-forget:
    there is no completion callback. The close operation is queued and
    executes in order with other operations.

    Closing a non-existent statement is not an error (per PostgreSQL protocol).
    If the session shuts down before the close is processed, the server
    automatically cleans up all statements.
    """
    state.close_statement(s, name)

Usage example

actor Client is (SessionStatusNotify & PrepareReceiver & ResultReceiver)
  let _session: Session

  be pg_session_authenticated(session: Session) =>
    // Step 1: Prepare the statement once
    session.prepare("lookup_user",
      "SELECT name, email FROM users WHERE id = $1",
      this)

  be pg_statement_prepared(session: Session, name: String) =>
    // Step 2: Execute with different parameters -- no re-parsing
    session.execute(
      NamedPreparedQuery("lookup_user",
        recover val [as (String | None): "42"] end),
      this)
    session.execute(
      NamedPreparedQuery("lookup_user",
        recover val [as (String | None): "99"] end),
      this)

  be pg_query_result(result: Result) =>
    // Step 3: Process results as usual
    match result
    | let r: ResultSet => // handle rows
    end

  // Eventually clean up when the statement is no longer needed
  fun ref _cleanup() =>
    _session.close_statement("lookup_user")

Internal Changes

Queue refactoring

The current queue Array[(Query, ResultReceiver)] is expanded to support three operation types:

class val _QueuedQuery
  let query: Query
  let receiver: ResultReceiver

class val _QueuedPrepare
  let name: String
  let sql: String
  let receiver: PrepareReceiver

class val _QueuedCloseStatement
  let name: String

type _QueueItem is (_QueuedQuery | _QueuedPrepare | _QueuedCloseStatement)

The queue becomes Array[_QueueItem]. All operations execute serially through the same FIFO queue, preserving ordering guarantees. This means a sequence like prepare("s1") -> execute(NamedPreparedQuery("s1")) -> close_statement("s1") -> prepare("s1") is guaranteed to execute in order.

New _SessionState methods

prepare and close_statement are added to _SessionState and implemented on each concrete state class. This follows the existing pattern where execute is implemented directly on each state class (not defaulted through the trait hierarchy), because the error type differs per state.

prepare per state:

• _SessionUnopened.prepare(...) calls receiver.pg_prepare_failed(name, SessionNeverOpened)
• _SessionClosed.prepare(...) calls receiver.pg_prepare_failed(name, SessionClosed)
• _SessionConnected.prepare(...) calls receiver.pg_prepare_failed(name, SessionNotAuthenticated)
• _SessionLoggedIn.prepare(...) queues a _QueuedPrepare and calls try_run_query

close_statement per state:

• _SessionUnopened.close_statement(...) -- silent no-op
• _SessionClosed.close_statement(...) -- silent no-op
• _SessionConnected.close_statement(...) -- silent no-op
• _SessionLoggedIn.close_statement(...) -- queues a _QueuedCloseStatement and calls try_run_query

New query sub-states

_PrepareInFlight -- handles the prepare cycle:

Wire protocol sent: Parse(name, sql, []) + Describe(statement, name) + Sync

Expected server response: ParseComplete + ParameterDescription + (RowDescription | NoData) + ReadyForQuery

Error case: ErrorResponse + ReadyForQuery

Message routing through _ResponseMessageParser:

• ParseComplete -- falls through (silently consumed, not routed)
• ParameterDescription -- falls through (silently consumed, not routed)
• RowDescription -- routed to on_row_description
• NoData -- falls through (silently consumed, not routed)
• ErrorResponse -- routed to on_error_response
• ReadyForQuery -- routed to on_ready_for_query

State behavior:

• on_row_description -- received from Describe(statement); ignored (not cached in this version)
• on_error_response -- sets an error flag and notifies receiver via pg_prepare_failed
• on_ready_for_query -- if no error was received, notifies receiver via pg_statement_prepared; dequeues item; transitions to _QueryReady (if idle) or _QueryNotReady
• on_data_row, on_command_complete, on_empty_query_response -- trigger shutdown (protocol anomaly; these should never arrive during a prepare cycle)
• try_run_query -- no-op (prepare is already in flight)

_CloseStatementInFlight -- handles the close cycle:

Wire protocol sent: Close(statement, name) + Sync

Expected server response: CloseComplete + ReadyForQuery

Error case: ErrorResponse + ReadyForQuery (the protocol spec says closing a non-existent object is not an error, so this path may be unreachable in practice for Close, but other server-side errors are theoretically possible)

Message routing:

• CloseComplete -- falls through (silently consumed, not routed)
• ReadyForQuery -- routed to on_ready_for_query
• ErrorResponse -- routed to on_error_response

State behavior:

• on_ready_for_query -- dequeues item; transitions to _QueryReady or _QueryNotReady
• on_error_response -- silently absorbed (fire-and-forget semantics; errors from close are ignored); the subsequent ReadyForQuery will dequeue and transition normally
• All data callbacks -- trigger shutdown (protocol anomaly)
• try_run_query -- no-op

Named query execution in _QueryReady

_QueryReady.try_run_query() is updated to handle all three queue item types:

match li.query_queue(0)?
| let qry: _QueuedQuery =>
  match qry.query
  | let sq: SimpleQuery =>
    // existing simple query path
  | let pq: PreparedQuery =>
    // existing unnamed extended query path: Parse + Bind + Describe(portal) + Execute + Sync
  | let nq: NamedPreparedQuery =>
    // NEW: Bind("", name, params) + Describe(portal, "") + Execute("", 0) + Sync
    // transitions to _ExtendedQueryInFlight (reused -- response handling is identical)
  end
| let prep: _QueuedPrepare =>
  // NEW: Parse(name, sql, []) + Describe(statement, name) + Sync
  // transitions to _PrepareInFlight
| let close: _QueuedCloseStatement =>
  // NEW: Close(statement, name) + Sync
  // transitions to _CloseStatementInFlight
end

Named query execution reuses _ExtendedQueryInFlight for response handling because the server response sequence is identical to unnamed extended query execution: BindComplete + (RowDescription | NoData) + DataRow* + CommandComplete + ReadyForQuery. Only the wire bytes sent differ (no Parse step, Bind references the named statement).

Existing in-flight state updates

The queue type change from Array[(Query, ResultReceiver)] to Array[_QueueItem] requires updating _SimpleQueryInFlight and _ExtendedQueryInFlight. Both states currently access the queue with tuple destructuring:

(let query, let receiver) = li.query_queue(0)?

This changes to unwrapping the _QueueItem union:

match li.query_queue(0)?
| let qry: _QueuedQuery =>
  // use qry.query and qry.receiver
end

Affected methods in both states: on_command_complete, on_empty_query_response, on_error_response, on_ready_for_query (for shift()). This is a mechanical change -- the logic within each method is unchanged.

New frontend messages

primitive _FrontendMessage
  // Existing methods unchanged...

  fun describe_statement(name: String): Array[U8] val =>
    """
    Build a Describe message for a statement.
    Format: Byte1('D') Int32(len) Byte1('S') String(name)
    """

  fun close_statement(name: String): Array[U8] val =>
    """
    Build a Close message for a statement.
    Format: Byte1('C') Int32(len) Byte1('S') String(name)
    """

Shutdown handling

_SessionLoggedIn.on_shutdown is updated to drain all queue item types:

for item in query_queue.values() do
  match item
  | let qry: _QueuedQuery =>
    qry.receiver.pg_query_failed(qry.query, SessionClosed)
  | let prep: _QueuedPrepare =>
    prep.receiver.pg_prepare_failed(prep.name, SessionClosed)
  | let close: _QueuedCloseStatement =>
    None  // No callback; server cleans up automatically
  end
end

Breaking Changes

Query union type expanded: type Query is (SimpleQuery | PreparedQuery | NamedPreparedQuery). All pattern matches on Query must add a NamedPreparedQuery branch. The Pony compiler enforces exhaustive matching, so all affected code is caught at compile time. This affects both public API consumers and internal test code (e.g., _AllSuccessQueryRunningClient.pg_query_failed has a match on Query to extract the query string for error messages).

The internal queue representation change from Array[(Query, ResultReceiver)] to Array[_QueueItem] has no public API impact beyond the Query union change.

Tests

Build and run commands:

• make ssl=3.0.x -- build and run all tests
• make unit-tests ssl=3.0.x -- unit tests only (no postgres needed)
• make integration-tests ssl=3.0.x -- integration tests (requires running PostgreSQL)

Unit tests

• _TestFrontendMessageDescribeStatement -- verify wire format of Describe(statement) message
• _TestFrontendMessageCloseStatement -- verify wire format of Close(statement) message

Integration tests

• PreparedStatement/Prepare -- prepare a statement, verify pg_statement_prepared callback
• PreparedStatement/PrepareAndExecute -- prepare, then execute with NamedPreparedQuery, verify results
• PreparedStatement/PrepareAndExecuteMultiple -- prepare once, execute twice with different params, verify both results
• PreparedStatement/PrepareAndClose -- prepare, close, then attempt execution, verify server error
• PreparedStatement/PrepareFails -- prepare with invalid SQL, verify pg_prepare_failed callback
• PreparedStatement/PrepareAfterClose -- prepare, close, re-prepare same name, verify success
• PreparedStatement/CloseNonexistent -- close a statement that was never prepared, verify no error
• PreparedStatement/PrepareDuplicateName -- prepare same name twice without closing, verify second prepare gets a server error via pg_prepare_failed
• PreparedStatement/MixedWithSimpleAndPrepared -- interleave named, unnamed, and simple queries in sequence

Mock server tests

• PreparedStatement/ShutdownDrainsPrepareQueue -- mock server that authenticates but never becomes ready; verify pending prepare operations get SessionClosed failures on shutdown

What's Deferred

• Statement metadata caching: During prepare, the server sends ParameterDescription and RowDescription. These are currently received but not stored. Future work could cache them per statement name to: (a) skip Describe(portal) during execution, (b) validate parameter counts/types client-side before sending.

• Client-side statement tracking: The session does not track which statement names are currently prepared. Invalid operations (executing a non-existent statement, re-preparing without closing) produce server-side errors delivered through the normal error callbacks. Future work could add a local registry for faster client-side validation and better error messages.

• PrepareReceiver metadata: The PrepareReceiver callback includes only the session and name. Future versions could include the ParameterDescription (parameter OIDs) and RowDescription (column names/types) from the prepare response.

Open Questions

1. close_statement callback: Should close_statement be fire-and-forget (current design), or should it accept a callback? Arguments for fire-and-forget: simpler API, queue ordering already guarantees sequencing with subsequent operations, closing non-existent statements isn't an error per protocol. Arguments for callback: user gets explicit confirmation that the name is available for reuse.

2. NamedPreparedQuery naming: Is NamedPreparedQuery the right name? Alternatives considered: BoundQuery (emphasizes the bind-only nature), NamedQuery (shorter but ambiguous). NamedPreparedQuery is verbose but maps directly to the PostgreSQL concept.

3. PrepareReceiver session parameter: The current design passes session: Session in pg_statement_prepared but not in pg_prepare_failed. This is asymmetric within the interface. Options: (A) include session in both callbacks -- internally consistent, useful for retry-on-failure, but inconsistent with ResultReceiver which omits session from both callbacks; (B) omit session from both -- consistent with ResultReceiver, but less ergonomic since the user typically wants to execute queries immediately after prepare; (C) keep current asymmetry -- pragmatic (session is useful on success, less so on failure). Note: ResultReceiver has an existing TODO about whether to add session to its callbacks (result_receiver.pony:1).

[SeanTAllen]

Decisions

Q1 — close_statement callback: Fire-and-forget. Per principle "it is easier to give than take away" — we can always add a callback later if needed, but can't easily remove one.

Q2 — NamedPreparedQuery naming: Keeping NamedPreparedQuery. No objections raised.

Q3 — PrepareReceiver session parameter: Omit session from both pg_statement_prepared and pg_prepare_failed. This is consistent with ResultReceiver (which also omits session from both callbacks), and per the same "easier to give than take away" principle, we can add it to both interfaces later.

Future note — session in callbacks for retry-on-failure: Both ResultReceiver and PrepareReceiver may want session added to their failure callbacks in the future to enable retry-on-failure patterns (e.g., a receiver that doesn't otherwise hold a session tag could retry a failed prepare or re-execute a failed query). This connects to the existing TODO on result_receiver.pony:1. When that work happens, it should add session to both interfaces' callbacks simultaneously for consistency.

[SeanTAllen]

Erratum: The Tests section includes ssl=3.0.x in build commands, which is environment-specific. The correct generic commands are make, make unit-tests, and make integration-tests (with ssl flag added per your local environment).

[SeanTAllen]

Implemented in PR #78. All design decisions from the previous comment were applied as specified. The implementation includes the full public API (NamedPreparedQuery, PrepareReceiver, Session.prepare(), Session.close_statement()), internal queue refactoring, two new in-flight states, an example program, and 12 new tests (3 unit, 1 mock server, 8 integration).