Use the protocol, replication, server, and SQL APIs

Prepare Flyology before adding the crate

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.

Add the crate to an Alire application

Configure the Flyology organization index, then let Alire resolve Flyology and Flyology Postgres as regular dependencies.

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

Connect, verify TLS, and complete startup

Flyology.Postgres.Client provides the client session. Its Session borrows a transport, so the transport must outlive the session.

The Transports.TLS_Sockets adapter owns the socket after an upgrade. Its TLS_Socket_Transport implements the transport interface.

Startup_TLS requires TLS, verifies the certificate chain and DNS name, authenticates, and leaves the session ready for commands.

with Flyology.IO.Sockets;
with Flyology.IO.TLS.OpenSSL;
with Flyology.Postgres.Client;
with Flyology.Postgres.Transports.TLS_Sockets;

package Sockets renames Flyology.IO.Sockets;
package OpenSSL renames Flyology.IO.TLS.OpenSSL;
package Client renames Flyology.Postgres.Client;
package Transports renames Flyology.Postgres.Transports.TLS_Sockets;

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

OpenSSL.Initialize_Client (Backend, CA_File => "root-ca.pem");
Sockets.Create_Socket (Socket, Family => Server.Family);
Sockets.Connect (Socket, Server, Timeout => 5.0);
Client.Startup_TLS
  (Session,
   Backend,
   Server_Name      => "db.example.com",
   User             => "app",
   Database         => "app",
   Password         => "secret",
   Application_Name => "flyology_app",
   Timeout          => 5.0);

Compose PostgreSQL work with other Flyology operations

The client operation overloads register limited operations in a caller-owned completion set. Use Connection_Transport for these overloads. The socket-specific transport adapters support only the synchronous APIs.

The connection transport borrows a Flyology connection and an optional cancellation token. The connection, transport, session, and completion set must outlive every operation that uses them.

Use the operation-producing Connections.Connect overload when connection setup must join the completion set. Declare the managed objects in ownership order.

Manager    : aliased Connections.Server (Capacity => 1);
Connection : aliased Connections.Connection (Manager'Access);
Channel    : aliased Connection_Transports.Connection_Transport
  (Connection'Access, null);
Session    : aliased Client.Session (Channel'Access);
Set        : aliased Operations.Completion_Set (Capacity => 2);

declare
   Attempt : Connections.Connect_Operation :=
     Connections.Connect
       (Set'Access,
        Manager'Access,
        Database_Endpoint,
        Timeout => 5.0);
begin
   Operations.Wait_All (Set);
   Connections.Finish (Attempt, Connection);
end;

The managed connect temporarily uses two completion-set slots: one parent and one hidden socket operation. It retains the socket and admission permit until Finish transfers them to the closed connection.

Use the synchronous Connections.Connect procedure when connection setup does not need composition. Use Connections.Take when the application already owns a connected or specially configured socket.

A connection permits one in-flight PostgreSQL operation. Use a separate connection and session for each database operation that must be active in the same completion set. On one session, start the next operation only after the previous operation becomes terminal.

Each active operation uses one completion-set slot. A receive operation owns one Backend_Message result until Finish.

Event   : Protocol.Backend_Message;
Receive : Client.Receive_Operation (Set'Access);

declare
   Send : Client.Send_Operation :=
     Client.Send_Query
       (Set'Access,
        Session'Access,
        "select id, value from items",
        Timeout => 5.0);
begin
   Operations.Wait_All (Set);
   Client.Finish (Send);
end;

loop
   Client.Receive_Query_Event
     (Session'Access, Timeout => 5.0, Operation => Receive);
   Operations.Wait_All (Set);
   Client.Finish (Receive, Event);
   Consume (Event);
   exit when Protocol.Response_Kind (Event) =
     Protocol.Ready_For_Query_Response;
end loop;

The example reuses one receive operation to avoid creating a new operation object for each message. Session is aliased and uses an aliased connection transport.

Each operation retains a timeout, cancellation, or provider failure until Finish. The client reports a PostgreSQL query error as an Error_Response event.

For TLS, first start and finish a Negotiate_TLS operation. Then start and finish the Flyology connection TLS upgrade on the same connection. Finally, start and finish a Startup operation. Both client calls return a Startup_Operation.

Use the synchronous APIs when the application does not need composition. They drive the same bounded PostgreSQL protocol state.

Receive simple-query results one event at a time

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.

Use prepared statements and bounded portals

The extended path models named or unnamed statements and portals. Prepare_Statement creates statement state. Bind_Portal binds values such as Text_Parameter.

Describe_Portal requests metadata, and Execute_Portal applies a row limit. Flush requests pending output without ending the cycle.

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);

Receive_Extended_Event returns one response. The example stops on Command_Complete_Response, Portal_Suspended_Response, or the error response linked above.

Synchronize sends Sync to end or recover the cycle. Continue receiving events through ReadyForQuery before reusing the session.

Pipeline batches without waiting for each result

The extended path normally finishes one Sync-terminated batch before it writes the next batch. Every batch then costs a round trip.

Enter_Pipeline_Mode removes that wait. After Synchronize, the next batch-opening call starts the following batch immediately, so several batches stay in flight. These calls open a batch:

• Prepare_Statement
• Bind_Portal
• Describe_Statement and Describe_Portal
• Execute_Portal
• Close_Statement and Close_Portal

Flush does not open a batch. It only asks the server for the output that the open batch has already produced.

Pending_Synchronizations counts the batches whose ReadyForQuery has not arrived. In_Pipeline_Mode reports the mode. Exit_Pipeline_Mode returns the session to one batch at a time. The session must be idle first, with no batch open and no response outstanding.

Client.Enter_Pipeline_Mode (Session);
Client.Prepare_Statement
  (Session, "insert_row", "insert into items (value) values ($1)",
   (1 => 25));

for Value of Values loop
   Client.Bind_Portal
     (Session,
      Portal_Name    => "",
      Statement_Name => "insert_row",
      Parameters     => (1 => Protocol.Text_Parameter (Value)));
   Client.Execute_Portal (Session, "");
   Client.Synchronize (Session);
end loop;

--  Every batch is already written before the first response is read.
while Client.Pending_Synchronizations (Session) > 0 loop
   Event := Client.Receive_Extended_Event (Session);
   --  Each Ready_For_Query_Response ends one batch, in send order.
end loop;

Client.Exit_Pipeline_Mode (Session);

The example prepares insert_row inside the first batch, so every later batch depends on it. If that statement fails to parse, each following bind fails as well. When the SQL is not already known to be valid, prepare it in a batch that the loop drains first.

Responses arrive in the order the client wrote the batches, and each ReadyForQuery ends exactly one batch.

The server confines a failure to the batch that caused it, provided that batch has already written its Sync. The server abandons the rest of that batch and sends its ReadyForQuery. It then processes the batch behind it normally, so the session does not enter the recovery state. A batch that fails before its Sync is written still requires Synchronize, exactly as it does outside pipeline mode.

PostgreSQL sends RowDescription only in reply to Describe_Statement or Describe_Portal. A portal that the client executes without one returns bare rows, and Receive_Extended_Event accepts them. It checks the column count only against a description that the server sent, so describe a batch when you want that check.

Stream COPY frames without an accumulator

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

The example reads the format with Protocol.Copy_Formats. It sends chunks with Send_Copy_Data and completes input with Finish_Copy.

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.

Stream physical or logical replication in either direction

Flyology.Postgres.Replication handles startup, LSN text, commands, physical stream envelopes, keepalives, status updates, and hot-standby feedback.

Flyology.Postgres.Replication.Logical adds typed pgoutput messages. Logical.Producer validates and encodes them in the primary direction. Both paths deliver one COPY BOTH frame per call.

Consume a logical stream without buffering WAL

Replication startup uses Client.Startup with an explicit replication mode. Logical Option values preserve empty and quoted text.

Send the command with Client.Send_Command. Configure the stateful decoder with Logical.Configure and the negotiated Parallel mode.

Receive_Copy_Event returns one frame. A Copy_Data_Response contains replication data.

The example uses the backend-event Original_Message overload, then calls Replication.Decode.

The stream-message Kind identifies XLog_Data. Pass its Data to the stateful Logical.Decode overload.

Do not acknowledge an LSN when its bytes arrive. Advance the durable acknowledgement only after the complete transaction is durably applied.

Client.Startup
  (Session,
   User             => "replicator",
   Database         => "app",
   Password         => Password,
   Replication_Mode => Protocol.Logical_Replication_Connection);

Client.Send_Command
  (Session,
   Replication.Start_Logical
     ("subscriber_1",
      Start_LSN,
      (Replication.Option ("proto_version", "4"),
       Replication.Option ("publication_names", "app_publication"),
       Replication.Option ("streaming", "parallel"))));

-- Receive and validate CopyBothResponse before reading COPY events.
Started := Client.Receive_Query_Event (Session);
Logical.Configure (Decoder, Version => 4, Streaming => Logical.Parallel);

loop
   Event := Client.Receive_Copy_Event (Session);
   if Protocol.Response_Kind (Event) = Protocol.Copy_Data_Response then
      Frame := Replication.Decode (Protocol.Original_Message (Event));

      if Replication.Kind (Frame) = Replication.XLog_Data then
         Change := Logical.Decode (Decoder, Replication.Data (Frame));
         Stage (Change);  -- Application-owned transaction state.

         if Logical.Kind (Change) = Logical.Commit_Message then
            Durable_End := Logical.End_LSN (Change);

            -- Atomically commit the target changes and persist Durable_End,
            -- or make replay idempotent if those use separate stores.
            Commit_Target_And_Checkpoint (Durable_End);

            Client.Send_Command
              (Session,
               Replication.Make_Standby_Status_Update
                 (Received_LSN => Durable_End,
                  Flushed_LSN  => Durable_End,
                  Applied_LSN  => Durable_End,
                  Sent_At      => Now));
         end if;
      end if;
   end if;
end loop;

The message Logical.Kind identifies a Commit_Message. Read its durable endpoint with End_LSN, then construct feedback with Make_Standby_Status_Update.

The abbreviated example shows the regular Commit path. Apply the same rule at StreamCommit.

For two-phase traffic, Prepared_Consumer coordinates durable preparation, target application, an applied marker, source acknowledgement, and removal. Unless the target commit and applied marker share one atomic transaction, the target callback must remain idempotent.

Compose a managed primary from application state

The managed-primary generic accepts the persistence interfaces and an application callback for typed logical changes. Its Primary composes those dependencies.

Managed.Initialize sets the system identity. Managed.Handle processes one decoded replication command.

package Managed is new
  Flyology.Postgres.Replication.Managed_Primary
  (Logical_Context => Application_Changes,
   Next_Logical    => Next_Logical);

Primary : Managed.Primary
  (Slots          => Slot_Store'Access,
   WAL            => WAL_Store'Access,
   Timelines      => Timeline_Store'Access,
   Logical_Source => Changes'Access);

Managed.Initialize
  (Primary, System_Id => System_Id, Database => "app");

Command := Replication.Decode_Command (Message);
Managed.Handle (Primary, Client, Command);

The generic Persistence.Memory child is a bounded reference implementation for tests and ephemeral single-owner servers. It is volatile and does not provide process locking or crash recovery.

Control COPY BOTH directly when needed

The lower-level path begins with Decode_Command. The command Kind overload distinguishes values such as Identify_System_Command and Start_Physical_Command.

Reply to identification with Send_Identify_System. For physical replication, Begin_Streaming starts COPY BOTH, and Send_XLog_Data writes one WAL frame.

Read feedback with Read_Standby_Message. The Position accessor returns the requested starting LSN.

Command := Replication.Decode_Command (Message);

case Replication.Kind (Command) is
   when Replication.Identify_System_Command =>
      Replication_Sessions.Send_Identify_System
        (Client, System_Id, Timeline, Current_WAL, Timeout => 10.0);

   when Replication.Start_Physical_Command =>
      Replication_Sessions.Begin_Streaming (Client, Timeout => 10.0);
      Replication_Sessions.Send_XLog_Data
        (Client,
         WAL_Start => Replication.Position (Command),
         WAL_End   => Current_WAL,
         Sent_At   => Now,
         Data      => WAL_Chunk,
         Timeout   => 10.0);

      Feedback := Replication_Sessions.Read_Standby_Message
        (Client, Timeout => 10.0);
      Apply_Feedback (Feedback);

   when others =>
      -- Handle another supported replication command.
      null;
end case;

For graceful shutdown, call Finish_Streaming to close the primary-to-standby direction. Continue reading feedback until the frontend CopyDone arrives, then call Complete_Streaming. Send an error response after a frontend CopyFail.

Promotion belongs to the application-owned timeline and WAL stores. First, persist the new timeline and its history. Retain WAL across the fork point before advertising the timeline as current.

The follower can then request TIMELINE_HISTORY and reconnect on the promoted timeline. The PostgreSQL 18 test promotes a real standby and verifies replay of WAL written only on timeline 2.

Search the generated replication API reference

Take a physical base backup without pg_basebackup

Flyology.Postgres.Replication.Base_Backups exposes the replication-protocol operation that pg_basebackup uses. The application still owns transport setup, storage, durability, and extraction policy.

The package models the distinct PostgreSQL 14 and 15+ wire streams. It adds PostgreSQL 17+ manifest upload and incremental options. Each receive operation returns one owned event.

Open a physical replication session

BASE_BACKUP requires a physical replication connection. Authenticate through the normal client startup path. Set Replication_Mode to Physical_Replication_Connection.

Construct Options with the actual server major. The major selects a capability boundary, not a preferred syntax. PostgreSQL 14 options cannot enable compression or incremental mode. PostgreSQL 15 options cannot upload a prior manifest.

Client.Startup_TLS
  (Session,
   Backend,
   Server_Name      => "db.example.com",
   User             => "backup_agent",
   Database         => "postgres",
   Password         => Password,
   Replication_Mode => Protocol.Physical_Replication_Connection,
   Timeout          => 10.0);

Settings := Base_Backups.Defaults (Server_Major);
Base_Backups.Set_Label (Settings, "nightly-2026-08-13");
Base_Backups.Set_Checkpoint
  (Settings, Base_Backups.Spread_Checkpoint);
Base_Backups.Include_WAL (Settings);
Base_Backups.Set_Progress (Settings);
Base_Backups.Include_Tablespace_Map (Settings);
Base_Backups.Set_Manifest
  (Settings,
   Base_Backups.Include_Manifest,
   Base_Backups.SHA256_Checksum);

Include_WAL asks PostgreSQL to place the WAL required for recovery in the backup. If this option is disabled, the application must retain the required WAL independently.

Wait_For_Archive controls whether PostgreSQL waits for the ending segment to reach its WAL archive. This option does not make local storage durable. Set_Maximum_Rate accepts zero or 32 through 1,048,576 KiB/s.

Stage each archive and manifest

Create a private staging destination before the backup starts. Tablespace rows arrive before archive bytes and preserve SQL NULL. The base directory has no OID or external location. A size can be absent when progress was not requested.

Use Stream_Index as the stable event key. On PostgreSQL 15+, Archive_Name and Archive_Location expose the multiplexed marker. On PostgreSQL 14, map each stream ordinal to its preceding tablespace row.

Backup : Base_Backups.Receiver (Session'Access);

Base_Backups.Start (Backup, Settings);

loop
   declare
      Event : constant Base_Backups.Event := Base_Backups.Receive (Backup);
   begin
      case Base_Backups.Kind (Event) is
         when Base_Backups.Backup_Start =>
            Start_LSN := Base_Backups.Start_LSN (Event);
            Timeline := Base_Backups.Timeline (Event);

         when Base_Backups.Tablespace =>
            Register_Tablespace
              (Has_Oid      => Base_Backups.Has_Tablespace_Oid (Event),
               Has_Location =>
                 Base_Backups.Has_Tablespace_Location (Event),
               Has_Size     => Base_Backups.Has_Tablespace_Size (Event));

         when Base_Backups.Archive_Start =>
            Begin_Archive
              (Index => Base_Backups.Stream_Index (Event),
               Name  =>
                 (if Server_Major >= 15
                  then Base_Backups.Archive_Name (Event)
                  else Legacy_Archive_Name
                    (Base_Backups.Stream_Index (Event))));

         when Base_Backups.Archive_Data =>
            Write_Archive_Chunk
              (Base_Backups.Stream_Index (Event),
               Base_Backups.Data (Event));

         when Base_Backups.Manifest_Start =>
            Begin_Manifest;
         when Base_Backups.Manifest_Data =>
            Write_Manifest_Chunk (Base_Backups.Data (Event));
         when Base_Backups.Progress =>
            Report_Archive_Progress
              (Base_Backups.Bytes_Completed (Event));

         when Base_Backups.Backup_End =>
            End_LSN := Base_Backups.End_LSN (Event);
            Saw_Consistent_End :=
              Base_Backups.Timeline (Event) = Timeline
              and then End_LSN >= Start_LSN;

         when Base_Backups.Notice =>
            Log_Notice (Base_Backups.Diagnostic (Event));
         when Base_Backups.Parameter_Status =>
            Apply_Status (Base_Backups.Status (Event));
         when Base_Backups.Error =>
            Failed := True;
            Log_Error (Base_Backups.Diagnostic (Event));
            -- Keep receiving so ReadyForQuery can restore the session.
         when Base_Backups.Complete =>
            exit;
      end case;
   end;
end loop;

Each Receive call returns at most one PostgreSQL CopyData payload. The 16 MiB protocol limit bounds each payload. Write the current chunk, or give it to another bounded queue, before the next call.

A progress event reports bytes completed in the current archive or tablespace. It does not report a whole-backup total. The receiver validates marker order, archive counts, tablespace counts, manifest presence, signed progress values, timelines, and LSN order.

PostgreSQL streams ustar data without the two terminal zero blocks. Append the blocks before using a strict reader, or use a reader that accepts this convention.

Close and sync each sink after Backup_End. Publish the staging destination only after Complete confirms that the server is ready. On another outcome, quarantine the destination or remove it through the application's recoverable cleanup policy.

Upload the prior manifest before an incremental backup

PostgreSQL 17 and 18 calculate an incremental backup from a complete prior backup manifest. Retain that manifest as a separate durable object.

Call Begin_Manifest_Upload, then send bounded chunks with Send_Manifest_Chunk. Call Finish_Manifest_Upload, then drain COPY and query completion. PostgreSQL validates the manifest chain and required WAL summaries.

Base_Backups.Begin_Manifest_Upload
  (Session, Server_Major, Timeout => 10.0);

while Prior_Manifest.Has_More loop
   Base_Backups.Send_Manifest_Chunk
     (Session, Prior_Manifest.Next_Chunk (32 * 1_024), Timeout => 10.0);
end loop;
Base_Backups.Finish_Manifest_Upload (Session, Timeout => 10.0);

while not Client.Is_Ready (Session) loop
   if Client.State (Session) in
     Client.Copy_In_Active | Client.Copy_Completion_Active
   then
      Check (Client.Receive_Copy_Event (Session, Timeout => 10.0));
   else
      Check (Client.Receive_Query_Event (Session, Timeout => 10.0));
   end if;
end loop;

Incremental := Base_Backups.Defaults (Server_Major);
Base_Backups.Set_Incremental (Incremental);
Base_Backups.Set_Manifest
  (Incremental, Base_Backups.Include_Manifest);
Base_Backups.Start (Backup, Incremental, Timeout => 10.0);

After the upload completes, enable Set_Incremental on a new option set. Store the new manifest with its backup for the next link in the chain.

An incremental stream is not a standalone data directory. PostgreSQL recovery tooling must combine it with its ancestor backups.

Cancel separately, then drain the active session

Cancellation does not travel on the connection that carries archive data. Open another transport to the same server and apply the same TLS policy. Pass that transport to Base_Backups.Cancel.

PostgreSQL closes the cancellation transport without a response. Continue the main receive loop until Complete. A cancellation diagnostic normally has SQLSTATE 57014.

Use the same sequence after a local disk error. Stop publishing data, request cancellation from another task, and drain the protocol before session reuse.

Keep server semantics above the framing layer

Replication.Decode_Command classifies BASE_BACKUP and PostgreSQL 17+ UPLOAD_MANIFEST. It preserves the original owned query for routing.

Base_Backups.Server_Sessions sends positions, nullable tablespace rows, COPY streams, multiplexed markers, progress, and manifest COPY IN. The package does not walk a live data directory or claim universal server support.

Replace a disposable pg_basebackup sidecar

For an example such as psqlbench, keep the primary container and its readiness checks. Remove the container or subprocess that only invokes pg_basebackup.

Run the client-target flow into an example-owned staging directory. Include WAL and map external tablespaces only beneath disposable roots. Publish the directory after consistent end and completion events. Then start the disposable PostgreSQL instance from that directory.

This checkout has no examples/psqlbench tree. The protocol slice therefore documents the migration without adding a machine-specific substitute.

Read the full compatibility, incremental-backup, cancellation, security, and migration guide

Cancel on a separate connection

Postgres cancellation does not travel on the active query connection. Startup stores the backend process ID and variable-length secret.

Flyology.Postgres.Client_Sockets supplies socket-based cancellation. Cancel_TLS verifies a separate encrypted connection, sends the credentials, and closes without waiting for a response.

Client_Sockets.Cancel_TLS
  (Session, Server, Backend, "db.example.com", 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.

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 one Flyology handler task.

Search the generated server and session API

Parse, analyze, and transform SQL

The versioned SQL crates parse PostgreSQL 14 through 18 syntax with native Ada parsers generated from each pinned upstream grammar. Select the crate for the PostgreSQL major that the application accepts.

Use one of these owned packages for ordinary application code:

• SQL.AST.V14 for PostgreSQL 14
• SQL.AST.V15 for PostgreSQL 15
• SQL.AST.V16 for PostgreSQL 16
• SQL.AST.V17 for PostgreSQL 17
• SQL.AST.V18 for PostgreSQL 18

alr with flyology_postgres_sql_v18

Import sql_v18.gpr from the application project. To support several majors, depend on and import each desired flyology_postgres_sql_vNN/sql_vNN.gpr pair; they link the shared parser core once and keep distinct versioned AST types. Use flyology_postgres_sql and sql.gpr only as the compatibility umbrella when all five majors must be available at runtime.

alr with flyology_postgres_sql_v14
alr with flyology_postgres_sql_v18

# In the application GPR project:
with "sql_v14.gpr";
with "sql_v18.gpr";

The example stores the result in Owned_Syntax_Tree and calls the V18 Parse operation. The tree's Valid, Diagnostic_Message, and Diagnostic_Position fields report failure.

with Flyology.Postgres.SQL.AST.V18;

declare
   package AST renames Flyology.Postgres.SQL.AST.V18;
   Tree : AST.Owned_Syntax_Tree;
begin
   AST.Parse
     ("SELECT id, payload FROM events WHERE archived = false",
      Tree);

   if not Tree.Valid then
      Report_Parse_Error (Tree.Diagnostic_Message, Tree.Diagnostic_Position);
   end if;
end;

Walk the complete tree with a generated visitor

Each owned AST has a generated visitor child package. For V18, use SQL.AST.V18.Visitors.

Derive an analysis type from Visitor. Override typed hooks such as Enter_Select_Stmt. This hook receives a Select_Stmt and a Traversal_Control value.

The example counts expressions through the statement's Target_List field.

The owned-tree Traverse overload follows each present child and each message element in a typed vector.

with Flyology.Postgres.SQL.AST.V18;
with Flyology.Postgres.SQL.AST.V18.Visitors;

declare
   package AST renames Flyology.Postgres.SQL.AST.V18;
   package Visitors renames Flyology.Postgres.SQL.AST.V18.Visitors;

   type Analysis is new Visitors.Visitor with record
      Selects : Natural := 0;
      Targets : Natural := 0;
   end record;

   overriding procedure Enter_Select_Stmt
     (Self    : in out Analysis;
      Item    : AST.Select_Stmt;
      Control : in out Visitors.Traversal_Control)
   is
      pragma Unreferenced (Control);
   begin
      Self.Selects := Self.Selects + 1;
      Self.Targets := Self.Targets + Natural (Item.Target_List.Length);
   end Enter_Select_Stmt;

   Tree   : AST.Owned_Syntax_Tree;
   Report : Analysis;
begin
   AST.Parse
     ("WITH q AS (SELECT id FROM events) SELECT id FROM q",
      Tree);

   if Tree.Valid then
      Visitors.Traverse (Report, Tree);
   end if;
end;

An enter hook may set Skip_Children when descendants cannot add facts. It may set Stop_Traversal when the analysis is complete. Matching leave hooks support scopes, stacks, and post-order summaries.

Plan transformations during traversal, then apply them

Visitor callback records are borrowed, read-only views of the owned graph. For a safe rewrite, collect child access values in visitor state, finish the traversal, then mutate scalar payloads through those references. This keeps vectors and graph topology stable while the walker is active.

The rewrite example overrides Enter_A_Const and receives an A_Const record.

Its Sval field is an optional string access value. Check its Present field before reading its Value.

The referenced string record has its own Sval field. Its text Present and Value fields control the replacement text.

overriding procedure Enter_A_Const
  (Self    : in out Rewrite_Planner;
   Item    : AST.A_Const;
   Control : in out Visitors.Traversal_Control)
is
   pragma Unreferenced (Control);
begin
   if Item.Sval.Present and then Item.Sval.Value /= null then
      Self.String_Literals.Append (Item.Sval.Value);
   end if;
end Enter_A_Const;

Visitors.Traverse (Plan, Tree);

for Literal of Plan.String_Literals loop
   if Literal.Sval.Present then
      Literal.Sval.Value := To_Unbounded_String ("[redacted]");
   end if;
end loop;

Run the analysis and transformation examples

The repository includes strict Ada 2022 examples that count relations, joins, columns, and function calls, then demonstrate a two-pass relation rename and literal redaction.

cd sql/examples
alr -n build
./scripts/test.sh

./bin/analyze_sql "SELECT count(*) FROM audit.events"

Read the complete runnable visitor examples

Read the complete parser API and architecture guide

Search the generated SQL API reference

Run the example crates

The examples are independent nested Alire crates. pgish can require TLS from a configured certificate and key; psqlish supports verified TLS with separate certificate and connection host settings. Their integration uses an ephemeral CA in both directions.

cd examples/psqlish
alr run

cd ../pgish
alr run

Check the current boundaries before evaluation

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