Teaching programs to speak Postgres.

The immediate need was routine: a Flyology program needed to talk to Postgres. It had to open a connection, authenticate, send a query, and consume the result. The same client calls also needed to work from native tasks and lightweight tasks without changing their ordinary synchronous Ada shape.

That last requirement matters to Flyology. A native task may block its own pthread. A lightweight task should suspend while its event loop waits for socket readiness, leaving the loop available to run other lightweight tasks. A Postgres client built directly over Flyology I/O can preserve 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.

The conversation is the API.

A simple query does not return one generic result. It may produce a row description, any number of data rows, a command completion, notices or an error, and finally ReadyForQuery with the transaction state. Multiple statements can produce several such sequences before that 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 state machine. A portal can stop at PortalSuspended and resume later. After an extended-query error, the client must synchronize and receive the corresponding ready message before the session is reusable. The library exposes those states and rejects invalid local ordering before writing it 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.

Then the protocol turned around.

Once a client could write frontend messages and understand backend messages, the complementary question became difficult to ignore. Could a Flyology program accept the frontend side and answer with the backend side?

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, dispatches every normal frontend command, provides constructors for 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. This preserves the direct task-per-connection shape without requiring one pthread per connection: the handler can keep session state in ordinary Ada locals and call synchronous-looking Flyology I/O, while many connection tasks share an execution-group thread. When one handler waits for socket readiness, only its fiber suspends and the event loop can 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, so a CPU-bound handler must suspend, yield at a fairness checkpoint, or move blocking work across a native-task boundary if other connections on the same execution group must progress.

This is not simply a client with its arrows reversed. A server must manage connection startup, authentication, session registration, backend keys, cancellation races, error recovery, shutdown, and response ownership. But the same protocol vocabulary now spans both sides: typed messages where the library can provide a useful interpretation, with the original raw message retained 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.

Postgres is a useful lingua franca.

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 knows how to establish a session, enter queries, display typed rows, distinguish diagnostics by SQLSTATE, recover after an error, cancel work, and inspect familiar catalogs. Postgres client libraries carry the same vocabulary into other programs. An application that answers in Postgres can reuse part of that surrounding practice instead of first inventing a private transport, a result format, an interactive shell, and a client library.

That does not make the protocol a universal answer. It is most attractive when an application's state already looks tabular or queryable: runtime observations, administrative state, synthetic catalogs, test fixtures, or a bounded domain-specific view. In those cases, a Postgres session can be both a machine interface and an exploratory one.

The useful symmetry is now concrete. A Flyology client can ask a real Postgres server questions. Real psql can ask a Flyology program questions. The two example programs can also talk directly to each other.

The first calls are ordinary Ada.

The public API keeps the protocol seam small. A client wraps a Flyology socket in a transport, starts a session, sends a query, and consumes one event at a time. This excerpt leaves row handling to the application but includes the complete connection and streaming shape.

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. Supply an application state and three callbacks, choose the handler task model, then serve an ordinary listening socket. The handler receives each frontend command and answers through Server_Sessions.

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.

pgish answers in Postgres.

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 ask questions 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, but it reveals an important property of a lingua franca: a protocol alone is not the whole conversation. Existing tools bring expectations about 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. It connects to ordinary Postgres and to pgish. Building both examples exposed compatibility gaps that a packet fixture would not: catalog naming, qualified descriptions, NULL and empty display, end-of-input behavior, and realistic session lifecycle.

Speaking Postgres is not becoming Postgres.

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, 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 with a normal error response before returning the session to ready state.

The transport boundary is similarly explicit. TLS is deferred until Flyology can upgrade an accepted connection without losing ownership or buffered-byte safety. The current client starts in plaintext and the server refuses an SSL upgrade. Trust and cleartext-password modes therefore belong only on a trusted test or private network; pgish binds to loopback by default. SCRAM-SHA-256 is implemented in both directions, but SCRAM channel binding and several other Postgres authentication methods are not.

The library bounds a protocol frame to 16 MiB and validates lengths, counts, terminators, formats, and transaction-state values before exposing typed data. pgish adds much smaller limits for query text, tokens, projections, predicates, rows, columns, values, and sessions. These are choices for a readable example and for lightweight task stacks, not claims about general Postgres limits.

Cooperative scheduling remains visible too. A continuously readable COPY OUT stream may keep a lightweight task running without a readiness suspension. A long-running consumer must yield explicitly when fairness on the same execution group matters, or arrange cancellation from an independently scheduled task. Returning one frame per call gives the application a natural place to make that decision.

The other implementation is part of the test.

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: 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 follows the part that benefits from total parsing and array-bound arguments rather than trying to redescribe the whole networked program as a proof exercise.

The project started because a client was needed. The more useful result is not merely that Flyology programs can now ask Postgres questions. They can also answer in Postgres, within an explicit, testable slice of the language that existing tools already understand.