Building both sides of the Postgres protocol

Why we needed a client

We began with a routine need: a Flyology program had to connect to Postgres, authenticate, send a query, and consume its result. The same calls had to work from native and lightweight tasks without changing their synchronous Ada form.

That last requirement matters to Flyology. A native task may block its own pthread. When a lightweight task waits for socket readiness, Flyology suspends its fiber. The event loop can then run another ready lightweight task.

A Postgres client built directly over Flyology I/O preserves one call-level model across both lanes.

The first version therefore implemented the Postgres frontend/backend protocol in Ada rather than placing an unrelated blocking client on an event-loop thread. It framed startup and normal messages, added transport adapters for Flyology sockets and accepted connections, and supplied client operations that borrow rather than take ownership of their transport.

Framing bytes was only the visible beginning. A useful client also has to represent the conversation those bytes belong to.

What the conversation required

The names in this section are PostgreSQL wire messages and commands, not Ada declarations. A simple query does not return one generic result. It may produce a row description, data rows, a command completion, notices or an error, and finally ReadyForQuery. That final message includes the transaction state.

Multiple statements can produce several such sequences before the final ready message.

Flyology Postgres returns those items one at a time. A row remains binary-safe, NULL remains different from an empty value, and the caller decides how quickly to consume or display the stream. The high-level path does not silently collect an unlimited result in memory.

The extended-query protocol makes ordering more explicit. Parse, Bind, Describe, Execute, Flush, and Sync form a wire-level state machine. A portal can stop at PortalSuspended and resume later.

After an extended-query error, the client sends Sync and receives messages through ReadyForQuery. Only then can the application reuse the session. The library rejects invalid local ordering before it writes to the connection.

COPY adds three more streaming directions: IN, OUT, and BOTH. Each call reads or writes one bounded chunk. Completion, abort, cancellation, and error recovery stay in the session state rather than becoming undocumented obligations for every caller.

Even cancellation is a conversation of its own. Postgres sends backend credentials during startup; a client later opens a separate connection, writes a cancellation request carrying those credentials, and closes without waiting for a reply. The server routes that request to the current handler operation while making invalid, stale, and duplicate credentials externally indistinguishable.

What changed when we added the server

Once the client handled frontend and backend messages, we tested the complementary direction. A Flyology program could accept frontend messages and return backend messages.

The result is a generic protocol server. Flyology's structured server gives each accepted connection its own application handler task.

The Postgres layer performs startup and authentication. It dispatches every normal frontend command, constructs common responses, streams COPY frames, and routes cancellation. The application supplies the context and decides what each command means.

pgish makes those connection tasks lightweight by default. The handler keeps session state in ordinary Ada locals and calls synchronous-looking Flyology I/O. Many connection tasks can share one execution-group thread.

When one handler waits for socket readiness, only its fiber suspends. The event loop can then run another ready connection.

The choice remains explicit. Starting pgish with --task-mode native gives each connection a native task instead. Lightweight scheduling is cooperative.

If other connections on the same execution group must progress, a CPU-bound handler must suspend or use a fairness checkpoint. It can instead move blocking work across a native-task boundary.

A server also manages connection startup, authentication, session registration, backend keys, cancellation races, error recovery, shutdown, and response ownership.

The same protocol vocabulary now spans both sides. The library returns typed messages when it can provide a useful interpretation. It retains the original raw message for custom state machines and future tags.

Client side

Connect to a real Postgres server, authenticate, send simple or extended commands, and consume typed streams.

Server side

Give every accepted connection an ordinary Ada handler task, lightweight or native, and dispatch its Postgres commands.

Shared boundary

Keep framing, authentication flow, cancellation, protocol state, and bounded response construction in the library.

Application boundary

Define SQL meaning, data, transaction behavior, prepared-statement storage, and result metadata.

Why existing clients mattered

The server direction changes the reason to implement the protocol. A client is infrastructure for reaching a database. A server lets an application present itself through an interface that already has clients.

psql already establishes sessions, enters queries, displays typed rows, distinguishes diagnostics by SQLSTATE, recovers after errors, cancels work, and inspects familiar catalogs. Postgres client libraries provide the same protocol vocabulary to other programs.

An application that serves the Postgres protocol can reuse those clients and tools. It does not first need a private transport, result format, interactive shell, and client library.

The protocol is useful when application state is tabular or queryable. Examples include runtime observations, administrative state, synthetic catalogs, test fixtures, and bounded domain-specific views. In those cases, one Postgres session can support machine access and interactive inspection.

A Flyology client can query a real Postgres server. Real psql can query a Flyology program. The two example programs can also communicate directly.

How the first Ada calls looked

Flyology.Postgres.Client keeps the protocol seam small. A client wraps a socket in a Socket_Transport, creates a Session, and calls Startup.

Send_Query begins a simple query. Each Receive_Query_Event call returns one Simple_Query_Event. The excerpt leaves row handling to the application.

The example uses Flyology.Postgres.Protocol to inspect each event. Response_Kind returns a backend message kind.

The cases use Row_Description_Response, Data_Row_Response, and Ready_For_Query_Response. Applications can read metadata with Description and row values with Row_Data.

with Flyology.IO.Sockets;
with Flyology.Postgres.Client;
with Flyology.Postgres.Protocol;
with Flyology.Postgres.Transports.Sockets;

procedure Query_Postgres (Secret : String) is
   package Client renames Flyology.Postgres.Client;
   package Protocol renames Flyology.Postgres.Protocol;
   package Sockets renames Flyology.IO.Sockets;
   package Transports renames Flyology.Postgres.Transports.Sockets;

   Server : constant Sockets.Endpoint := Sockets.Network_Endpoint
     (Sockets.Parse_IP_Address ("127.0.0.1"), Sockets.Port (5432));
   Socket  : aliased Sockets.Socket_Type;
   Channel : aliased Transports.Socket_Transport (Socket'Access);
   Session : Client.Session (Channel'Access);

begin
   Sockets.Create_Socket (Socket, Family => Server.Family);
   Sockets.Connect (Socket, Server, Timeout => 5.0);
   Client.Startup
     (Session, User => "app", Database => "app", Password => Secret);
   Client.Send_Query (Session, "select id, name from items");

   loop
      declare
         Event : constant Client.Simple_Query_Event :=
           Client.Receive_Query_Event (Session);
      begin
         case Protocol.Response_Kind (Event) is
            when Protocol.Row_Description_Response =>
               null; -- Inspect Protocol.Description (Event).
            when Protocol.Data_Row_Response =>
               null; -- Consume Protocol.Row_Data (Event).
            when Protocol.Ready_For_Query_Response =>
               exit;
            when others =>
               null;
         end case;
      end;
   end loop;
end Query_Postgres;

The server starts at the same level. Instantiate the generic Flyology.Postgres.Server package with application state and callbacks. Its handler answers through Server_Sessions.

The instance supplies the Server type and Serve operation. This example selects the SCRAM_SHA_256 authentication method.

with Application;
with Flyology;
with Flyology.IO.Sockets;
with Flyology.Postgres;
with Flyology.Postgres.Server;

procedure Serve_Application is
   package Sockets renames Flyology.IO.Sockets;
   package App_Server is new Flyology.Postgres.Server
     (Handler_Context       => Application.State,
      Authenticate          => Application.Authenticate,
      Lookup_SCRAM_Verifier => Application.Lookup_SCRAM_Verifier,
      Handle                => Application.Handle,
      Authentication        => Flyology.Postgres.SCRAM_SHA_256,
      Handler_Model         => Flyology.Lightweight_Task);

   State    : aliased Application.State;
   Server   : aliased App_Server.Server (Capacity => 64);
   Listener : Sockets.Socket_Type;

begin
   Sockets.Create_Socket (Listener, Family => Sockets.IPv4);
   Sockets.Set_Socket_Option
     (Listener, (Name => Sockets.Reuse_Address, Enabled => True));
   Sockets.Bind_Socket
     (Listener,
      Sockets.Network_Endpoint
        (Sockets.Parse_IP_Address ("127.0.0.1"), Sockets.Port (55432)));
   Sockets.Listen_Socket (Listener, Length => 64);
   App_Server.Serve (Server, Listener, State);
end Serve_Application;

Real applications still need timeout policy, shutdown, diagnostics, and response construction. The point of the excerpts is narrower: neither direction introduces a second asynchronous language. Both begin with packages, records, callbacks, sockets, and ordinary blocking-shaped Ada calls.

What pgish revealed

pgish is a small read-only server built on the production server API. It is deliberately not a database. Its bounded SQL subset exposes virtual tables describing the Flyology server, execution groups, stack pool, live sessions, repository commits, effective settings, selected environment values, and its own catalog.

After starting it on its loopback default, an ordinary psql session can run queries such as:

SELECT protocol_version, task_mode, repository_head
FROM flyology_server_info;

SELECT group_id, members, ready, waiting, dispatches
FROM flyology_runtime_groups
WHERE members > 0
ORDER BY group_id;

SELECT short_hash, author, subject, committed_at
FROM flyology_repo_commits
WHERE subject LIKE '%Postgres%'
ORDER BY committed_at DESC
LIMIT 10;

The example also answers the catalog query shapes used by psql's \dt and \d commands. That compatibility work is intentionally narrow. It showed that protocol framing alone was insufficient. Existing tools also expect specific metadata, errors, session reuse, and query shapes.

The companion psqlish example approaches from the other direction. It is a compact interactive client with multiline input, history, catalog commands, bounded table rendering, diagnostics, and recovery after SQL errors.

psqlish connects to ordinary Postgres and to pgish. Building both examples exposed gaps that packet fixtures did not show.

Those gaps included catalog naming, qualified descriptions, NULL and empty display, end-of-input behavior, and realistic session lifecycle.

Where the boundary remained

Application responsibilities

Flyology Postgres supplies protocol machinery. It does not supply a SQL engine, storage, transactions, optimizer, or a general catalog. Those remain application responsibilities.

The pgish parser accepts one bounded read-only statement. It supports a small set of predicates and projections, one-column ordering, and at most 32 result rows. It rejects joins, subqueries, DDL, DML, transactions, arbitrary functions, and other unsupported input. The server returns a normal error response and restores the ready state.

Transport security

At the pinned revision, the client starts in plaintext and the server refuses an SSL upgrade. TLS was deferred until Flyology could upgrade an accepted connection without losing ownership or buffered-byte safety.

Use trust or cleartext-password modes only on a trusted test or private network. pgish binds to loopback by default. Both directions implement SCRAM-SHA-256. They do not implement SCRAM channel binding or several other Postgres authentication methods.

Resource limits

The library bounds a protocol frame to 16 MiB. It validates lengths, counts, terminators, formats, and transaction-state values before it exposes typed data.

pgish uses smaller limits for query text, tokens, projections, predicates, rows, columns, values, and sessions. These are example limits for readable code and lightweight task stacks. They are not general Postgres limits.

Cooperative fairness

A continuously readable COPY OUT stream may keep a lightweight task running without a readiness suspension. If fairness on the same execution group matters, a long-running consumer must yield explicitly. It can instead arrange cancellation from an independently scheduled task.

Returning one frame per call gives the application a place to make that decision.

What interoperability tests established

The protocol does not count as interoperable because locally encoded frames can be decoded locally. The integration suite starts a real Postgres 18.4 SCRAM server and connects with the Ada client. It exercises typed simple and extended results, named and unnamed statements and portals, mixed text and binary values, errors and recovery, streaming COPY, and separate-connection cancellation.

The suite then starts the Flyology protocol server and connects with the freshly built real psql. That direction covers rows, NULL and empty values, COPY in both directions, prepared-statement commands, SCRAM failures, cancellation, and normal recovery. COPY BOTH, which would require replication setup for a live server exercise, remains an exact protocol fixture.

An allocation-free SPARK core handles the narrow wire boundary. It covers cursor reads, endian conversion, frame lengths, startup packets, format counts, COPY response structure, cancellation keys, and related contracts.

A second core covers bounded SCRAM derivation over proved hashing primitives. Heap-backed messages, transports, tasking, session routing, and socket adapters remain conventional Ada. The proof boundary covers code that benefits from total parsing and array-bound arguments.

We started because we needed a client. The result also lets Flyology programs answer Postgres clients within an explicit, tested slice of the protocol.