Guide · Flyology Postgres

STEP 01

Start with Flyology and Alire.

Flyology Postgres is an Alire library crate. It uses Flyology task-aware sockets and depends on hmac_ada plus system_random for SCRAM-SHA-256.

Flyology: Use the runtime and toolchain preparation documented by the main project.
Postgres: Interoperability is exercised against Postgres 18.4 and the protocol 3.0/3.2 startup shapes.
Transport: The included adapters accept a connected Flyology socket or an accepted Flyology connection.

STEP 02

Add the crate to an Alire application.

During development, use the Flyology organization index or pin a sibling checkout. The repository itself uses a sibling pin so both crates can evolve together.

development checkout

alr index --reset-community
alr index --add=git+https://github.com/flyology-ada/alire-index.git \
  --name=flyology --before=community
alr with flyology_postgres

sibling source checkout

alr -n with flyology --use=../flyology
alr build

STEP 03

Connect a socket and complete startup.

A client session borrows its transport. The socket and transport must therefore outlive the session. Startup performs Postgres startup and authentication, then leaves the session ready for commands.

client startup

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

package Sockets renames Flyology.IO.Sockets;
package Client renames Flyology.Postgres.Client;
package Transports renames Flyology.Postgres.Transports.Sockets;

Server  : constant Sockets.Endpoint :=
  Sockets.Network_Endpoint (Sockets.Loopback_IPv4, 5_432);
Socket  : aliased Sockets.Socket_Type;
Channel : aliased Transports.Socket_Transport (Socket'Access);
Session : Client.Session (Channel'Access);

Sockets.Create_Socket (Socket, Family => Server.Family);
Sockets.Connect (Socket, Server, Timeout => 5.0);
Client.Startup
  (Session,
   User             => "app",
   Database         => "app",
   Password         => "secret",
   Application_Name => "flyology_app",
   Timeout          => 5.0);

STEP 04

Receive simple-query results one event at a time.

Send_Query starts the operation. Repeated Receive_Query_Event calls return row descriptions, data rows, command completions, diagnostics, parameter status, and the final ready state without accumulating a result set.

bounded simple-query loop

Client.Send_Query (Session, "select id, value 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 =>
            Inspect (Protocol.Description (Event));
         when Protocol.Data_Row_Response =>
            Consume (Protocol.Row_Data (Event));
         when Protocol.Error_Response | Protocol.Notice_Response =>
            Report (Protocol.Diagnostic_Data (Event));
         when others =>
            null;
      end case;
      exit when Protocol.Response_Kind (Event) =
        Protocol.Ready_For_Query_Response;
   end;
end loop;

Multiple SQL statements produce multiple event sequences before the final ReadyForQuery. A present zero-length column remains distinct from SQL NULL.

STEP 05

Use prepared statements and bounded portals.

The extended path models named or unnamed statements and portals directly. Commands can be pipelined before Sync; Flush requests pending output without ending the cycle.

a portal limited to 50 rows

Client.Prepare_Statement
  (Session, "items", "select id, value from items where id > $1", (1 => 23));
Client.Bind_Portal
  (Session,
   Portal_Name    => "items_page",
   Statement_Name => "items",
   Parameters     => (1 => Protocol.Text_Parameter ("100")));
Client.Describe_Portal (Session, "items_page");
Client.Execute_Portal (Session, "items_page", Maximum_Rows => 50);
Client.Flush (Session);

loop
   Event := Client.Receive_Extended_Event (Session);
   exit when Protocol.Response_Kind (Event) in
     Protocol.Command_Complete_Response |
     Protocol.Portal_Suspended_Response |
     Protocol.Error_Response;
end loop;

Client.Synchronize (Session);

STEP 06

Stream COPY frames without an accumulator.

A COPY response changes the session state to COPY IN, COPY OUT, or COPY BOTH. Each call sends or receives one bounded chunk. The same typed path works after simple or extended queries.

COPY IN

Client.Send_Query
  (Session, "copy measurements from stdin (format text)");

declare
   Started : constant Client.Simple_Query_Event :=
     Client.Receive_Query_Event (Session);
begin
   Inspect (Protocol.Copy_Formats (Started));
   Client.Send_Copy_Data (Session, First_Chunk);
   Client.Send_Copy_Data (Session, Second_Chunk);
   Client.Finish_Copy (Session);
end;

-- Receive CommandComplete, then ReadyForQuery.

Abort_Copy sends CopyFail with a server-visible reason. For extended COPY, synchronization remains mandatory before the session returns to ready.

STEP 07

Cancel on a separate connection.

Postgres cancellation never travels on the active query connection. Startup stores the backend process ID and variable-length secret. The socket helper opens a separate connection, sends those credentials, and closes it without waiting for a response.

cancel an active operation

Client_Sockets.Cancel (Session, Server, Timeout => 5.0);

-- Keep receiving on Session until the server's ErrorResponse and
-- ReadyForQuery complete normal recovery.

Issue cancellation from an independently scheduled task when a lightweight receiver may be continuously runnable.

STEP 08

Build a Postgres protocol server.

Flyology.Postgres.Server is generic over application context, authentication callbacks, SCRAM verifier lookup, and a command handler. Each accepted connection receives its own Flyology handler task.

Commands: The handler receives every normal frontend command as a typed or raw-preserving Protocol.Message.
Responses: Session helpers send row descriptions, rows, diagnostics, completion, readiness, and COPY streams.
Authentication: SCRAM lookup returns Postgres verifier text. The server does not request or retain the user's plaintext password.
Cancellation: A fresh per-command token reports matching cancellation and forced structured-server shutdown.

Search the generated server and session API

STEP 09

Exercise both ends with the example crates.

The examples are independent nested Alire crates and share loopback defaults. Run a real Postgres-compatible client against pgish, or use psqlish against real Postgres.

example directories

cd examples/psqlish
alr run

cd ../pgish
alr run

STEP 10

Evaluate the current boundaries.

The library is experimental. Treat these constraints as part of its public contract, not as incidental implementation details.

TLS: Deferred until Flyology can upgrade accepted connections without losing ownership or buffered-byte safety.
Authentication: Trust, cleartext password, and SCRAM-SHA-256 only. No MD5, GSSAPI, SSPI, certificates, or channel binding.
Cleartext: Use cleartext-password authentication only on trusted test or private networks.
Fairness: A continuously readable COPY socket may require an explicit Flyology fairness point so another task on the same group can run.
Limits: SCRAM messages are bounded to 4 KiB; iteration counts range from 4,096 through 1,000,000.