PG docs, part 2

ToC

• About & Installation
• The Client
• Connection Pool
• Arrays
• Notifications
• SSL
• COPY
• HoneySQL

In this chapter, we’ll discuss how to reach Postgres using the Client library.

• Basic usage
• Queries
• Execute
• Prepared Statements
• Processing result with :fn-result
• Column names
• Column duplicates
• Reducers and bundles
  • Java
  • Kebab-keys
  • Matrix
  • Index by
  • Group by
  • Key-value
  • Custom reducers
• Transactions
  • Always Rollback
  • Read-only
  • Isolation level
  • Manual transactions and status check
• Configuration
• Authorization
• Cloning a connection
• Cancelling a query
• Notices
• Thread safety
• Debugging

Basic usage

Here is a brief example of using the client library:

(ns scratch
  (:require
   [pg.client :as pg]))

(def config
  {:host "127.0.0.1"
   :port 5432
   :user "test"
   :password "test"
   :database "test"})

(pg/with-connection [conn config]
  (pg/query conn "select 1 as one"))

;; [{:one 1}]

First, you import the pg.client namespace which brings the top-level API functions to interact with Postgres. The config map above specifies the minimal configuration; it might have more fields which we will explore in a separate section.

The with-connection macro establishes a new connection, binds it to the conn symbol, and executes the body. The connection is closed afterward, even if an exception pops up.

Technically you can open and terminate a connection manually like this:

(let [conn (pg/connect config)
      data (pg/query conn "select 1 as one")]
  (pg/terminate conn)
  data)

;; [{:one 1}]

but it’s not recommended. Also, since the Connection object implements java.io.Closeable, it’s possible to use it in with-open:

(with-open [conn (pg/connect config)]
  (pg/query conn "select 1 as one"))

;; [{:one 1}]

Queries

The pg/query function above runs a query using a Simple Wire protocol (Postgres has two kinds of protocols to communicate each having its own pros and cons). The function takes a connection object, a string query, and a map of non-required options:

(pg/query conn "select 1 as one")
(pg/query conn "select 2 as two" {...})

Pay attention: There is no way to pass parameters because the Simple Wire protocol doesn’t support them! In other words, pg/query doesn’t allow you to write something like this:

(pg/query conn "select * from users where id = $1" [42])

This behavior is held by pg/execute and statements (see below).

What is the benefit of Simple Wire queries then? They allow you to send multiple expressions in one separating them with a semicolon:

(pg/query conn
   "select s.x from generate_series(1, 5) as s(x);
    select s.x from generate_series('2008-03-01 00:00'::timestamp,
                                    '2008-03-04 12:00',
                                    '10 hours') as s(x)")

[[{:x 1} {:x 2} {:x 3} {:x 4} {:x 5}]
 [{:x #object[j.t.LocalDateTime "2008-03-01T00:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-01T10:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-01T20:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-02T06:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-02T16:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-03T02:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-03T12:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-03T22:00"]}
  {:x #object[j.t.LocalDateTime "2008-03-04T08:00"]}]]

The result will be a vector of two results, each for the corresponding expression. Each expression has its own column set and doesn’t depend on other expressions.

Obviously, it’s better to avoid mixing DDL expressions like CREATE TABLE with data selection.

Execute

The pg/execute function implements the Extended Wire protocol for Postgres. Although it doesn’t allow you to pass multiple expressions separated by a semicolon, it supports passing parameters.

First, let’s prepare a table:

(pg/query conn "CREATE TABLE users (id serial primary key, name text, age integer)")

Let’s insert a couple of users:

(pg/execute conn
            "INSERT INTO users (name, age)
             VALUES ($1, $2), ($3, $4)
             RETURNING id"
            ["Ivan" 37 "Juan" 38])

;; [{:id 1} {:id 2}]

Above, “Ivan” becomes $1, 37 becomes $2 and so on. Now that we have some data, let’s query users by id:

(def sql "select * from users where id = $1")

(pg/execute conn sql [1])
;; [{:id 1, :name "Ivan", :age 37}]

(pg/execute conn sql [2])
;; [{:id 2, :name "Juan", :age 38}]

This technique allows to you share the same queries for different values.

Prepared Statements

The pg/execute function above does several things under the hood. It prepares a statement, binds the parameters to it, and obtains a portal; then it reads the from the portal and closes it.

There are a couple of functions to do the same in a breakdown. The pg/prepare-statement accepts a connection, a SQL expression and returns a prepared statement:

(def stmt (pg/prepare-statement conn sql))

A prepared statement is just a plain map that carries brief information about its name (auto-generated), columns, and parameters. In the example below, the statement is called statement_10637, there are three columns called “id”, “name” and “age” of type OIDs 23, 25, and 23. There is a single parameter with type 23 (integer):

{:statement "statement_10637"
 :RowDescription
 {:msg :RowDescription
  :column-count 3
  :columns
  [{:index 0
    :name "id"
    :table-oid 18024
    :column-oid 1
    :type-oid 23
    :type-len 4
    :type-mod -1
    :format 0}
   {:index 1
    :name "name"
    :table-oid 18024
    :column-oid 2
    :type-oid 25
    :type-len -1
    :format 0}
   {:index 2
    :name "age"
    :table-oid 18024
    :column-oid 3
    :type-oid 23
    :type-len 4
    :type-mod -1
    :format 0}]}
 :ParameterDescription
 {:msg :ParameterDescription
  :param-count 1
  :param-oids [23]}}

Having a prepared statement, execute it as follows:

(pg/execute-statement conn stmt [1])
;; [{:id 1, :name "Ivan", :age 37}]

(pg/execute-statement conn stmt [2])
;; [{:id 2, :name "Juan", :age 38}]

Pay attention that prepared statements are always bound to a certain connection. You cannot prepare a statement in one connection and execute it with another: it will cause an error response from Postgres.

Once you’ve done with a prepared statement, close it:

(pg/close-statement conn stmt)

Closing statements is important for the server as it releases resources allocated to those statements. To prevent hanging statements, there is a macro called with-statement which closes a statement afterward:

(pg/with-statement [stmt conn "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/execute-statement conn stmt ["Petr" 42])
  (pg/execute-statement conn stmt ["Simon" 33]))

Processing result with :fn-result

Often, you want to process the result somehow. Say, take only the first row of the selection (when you know there is either zero or one record).

All the query, execute, and execute-statement accept optional parameters. The :fn-result function is applied to the whole result; usually, you pass first:

(def sql "select * from users where id = $1")

(pg/with-statement [stmt conn sql]
    (let [user1 (pg/execute-statement conn stmt [1] {:fn-result first})
          user2 (pg/execute-statement conn stmt [2] {:fn-result first})]
      {:user1 user1
       :user2 user2}))

{:user1 {:id 1, :name "Ivan", :age 37},
 :user2 {:id 2, :name "Juan", :age 38}}

Pay attention: when passing the :fn-result function to pg/query with multiple expressions, the function is applied to each expression:

(pg/query conn
          "
           select x from generate_series(0, 3) as s(x);
           select y from generate_series(2, 5) as s(y)
          "
          {:fn-result set})

[#{{:x 1} {:x 3} {:x 2} {:x 0}}
 #{{:y 4} {:y 3} {:y 2} {:y 5}}]

Column names

By default, the client library turns all the column names to keywords. It doesn’t take any kebab-case transformations into account: a column "user_name" becomes :user_name.

(pg/query conn "select 42 as the_answer")

[{:the_answer 42}]

An optional parameter :fn-column changes this behaviour. It’s a function that takes a string column and returns whatever you want. Here is how you can obtain upper-cased string keys:

(require '[clojure.string :as str])

(pg/query conn "select 42 as the_answer" {:fn-column str/upper-case})

[{"THE_ANSWER" 42}]

Of course, you can pass a complex function that transforms the string somehow and then turns it into a keyword or a symbol.

Some Clojure programmers prefer kebab-case keywords (which I honestly consider a bad practice, but still). For this, do one of the two options. Either get such function from the pg.client.func namespace:

(require '[pg.client.func :as func])

(pg/query conn "select 42 as the_answer" {:fn-column func/kebab-keyword})

[{:the-answer 42}]

Or pass it as a “bundle”:

(require '[pg.client.as :as as])

(pg/query conn "select 42 as the_answer" {:as as/kebab-keys})

[{:the-answer 42}]

Bundles are maps of several processing functions which we’re going to describe a bit below.

Column duplicates

In SQL, that’s completely fine when a query returns several columns with the same name, for example:

SELECT 1 as val, true as val, 'dunno' as val;

 val | val |  val
-----+-----+-------
   1 | t   | dunno

But from the client’s perspective, that’s unclear what to do with such a result, especially when you’re dealing with maps.

By default, the client library adds numbers to those columns that have already been in the result. Briefly, for the example above, you’ll get val, val_1 and val_2 columns:

(pg/query conn "SELECT 1 as val, true as val, 'dunno' as val")

[{:val 1, :val_1 true, :val_2 "dunno"}]

This behaviour stacks with the fn-column parameter: the fn-column gets applied after the column names have been transformed.

(pg/query conn "SELECT 1 as val, true as val, 'dunno' as val" {:as as/kebab-keys})

[{:val 1, :val-1 true, :val-2 "dunno"}]

The function which is responsible for column duplicate processing is called :fn-unify. It takes a vector of strings and must return a vector of something (strings, keywords, symbols).

Reducers and bundles

As you’ve seen before, the result of pg/query or pg/execute is a vector of maps. Although it is most likely what you want by default, there are other ways to obtain the result in another shape.

There is a pg.client.as namespace that carries “bundles”: named maps with predefined parameters, mostly functions. Passing these maps into the optional :as field when querying data affects how the rows will be processed.

Java

The as/java bundle builds an ArrayList of mutable HashMaps. Both the top-level set of rows and its children are mutable:

(def res (pg/query conn "SELECT 42 as the_answer" {:as as/java}))

(.add res :someting)
(.put (.get res 0) :some-key "A")

;; [{:the_answer 42, :some-key "A"} :someting]

Kebab-keys

The kebab-keys bundle we have already seen in action: it just transforms the keys from :foo_bar to :foo-bar:

(pg/query conn "SELECT 42 as the_answer" {:as as/kebab-keys})

[{:the-answer 42}]

Matrix

The as/matrix bundle is useful for getting values without names:

(pg/query conn "SELECT 1, false, 'hello'" {:as as/matrix})

[[1 false "hello"]]

The result will be just a vector of vectors which is convenient for writing to CSV or Excel files.

Index by

The following case happens quite often: you select certain entities and then you need to build an index map by id. Namely, transform this:

[{:id 1 :name "Ivan"}
 {:id 2 :name "Juan"}
 ...]

to this:

{1 {:id 1 :name "Ivan"}
 2 {:id 2 :name "Juan"}
 ...}

That would be great of course to do that not once you have read the rows from the database but as you’re reading the rows. That would save the lines of code and resources.

The as/index-by reducer does it for you. It’s a function that takes a row function and returns a bundle:

(pg/query conn "select * from users" {:as (as/index-by :id)})
p
{1 {:id 1, :name "Ivan", :age 37},
 2 {:id 2, :name "Juan", :age 38}}

Of course, a function that is passed to index-by might be something more complex than an ordinary keyword. It can be a call of juxt if you need to group rows by several keys:

(pg/query conn "select * from users" {:as (as/index-by (juxt :id :name))})

{[1 "Ivan"] {:id 1, :name "Ivan", :age 37},
 [2 "Juan"] {:id 2, :name "Juan", :age 38}}

Group by

The as/group-by reducer acts like the standard group-by function. It collects a map where a key is a result of (f row), and the value is a vector of matched rows. The main difference is, that it fills the result on the fly as the data arrives from the server.

Imagine there are more users named Ivan and Juan in our database. Here is how we can select and group them by name:

(pg/query conn "select * from users" {:as (as/group-by :name)})

{"Ivan" [{:id 1, :name "Ivan", :age 37}
         {:id 3, :name "Ivan", :age 37}],
 "Juan" [{:id 2, :name "Juan", :age 38}
         {:id 4, :name "Juan", :age 38}]}

Key-value

There is a kv reducer that allows you to build any map you want. It takes two parameters: a key function (fk) and a value function (fv). The result will be a map like this:

{(fk row) (fv row)}

The as/kv reducer is useful when you want to get a map from rows, for example, a mapping from the id to the name:

(pg/query conn "select * from users" {:as (as/kv :id :name)})

{1 "Ivan", 2 "Juan", 3 "Ivan", 4 "Juan"}

Custom reducers

Making a custom reducer means declaring either a map or a function that returns a map of the following structure:

• :fn-init: a function that returns an empty accumulator value;

• :fn-reduce: a function that takes the accumulator and a row and adds the row to the accumulator;

• :fn-finalize: a function that takes the accumulator and returns the final value.

Here is an example of the kv reducer:

(defn kv [fk fv]
  {:fn-init #(transient {})
   :fn-reduce (fn [acc! row]
                (assoc! acc! (fk row) (fv row)))
   :fn-finalize persistent!})

Transactions

There is a special pg/with-tx macro to wrap several expressions in a single transaction. It takes a connection object and produces BEGIN … COMMIT commands:

(let [sql "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/with-tx [conn]
    (pg/execute conn sql ["Jim" 20])
    (pg/execute conn sql ["Bob" 30])))

Here is what you would see in the logs:

BEGIN
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Jim', $2 = '20'
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Bob', $2 = '30'
COMMIT

Should an exception pop up the middle, the whole transaction ends up with ROLLBACK and the exception is rethrown:

(let [sql "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/with-tx [conn]
    (pg/execute conn sql ["Jim" 20])
    (* 42 nil) ;; <-
    (pg/execute conn sql ["Bob" 30])))

BEGIN
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Jim', $2 = '20'
ROLLBACK

Execution error (NullPointerException) at ...
Cannot invoke "Object.getClass()" because "x" is null

Always Rollback

The pg/with-tx macro takes additional options for precise control over the transaction. Passing the {:rollback? true} would end up a transaction with rolling back the changes even if there was no error.

Here we create a couple of users in a rolling-back transaction. Right after you exit the pg/with-tx macro, all the changes you made get wiped.

(let [sql "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/with-tx [conn {:rollback? true}]
    (pg/execute conn sql ["Jim" 20])
    (pg/execute conn sql ["Bob" 30])))

The logs:

BEGIN
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Jim', $2 = '20'
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Bob', $2 = '30'
ROLLBACK

Always-rollback transactions are great for testing: first, you insert something into the database, perform some checks, roll back, and the database stays untouched. Of course, this is not the case for multi-threaded tests or some tricky logic when multiple DB connections are involved.

Read-only

Passing the {:read-only? true} map will spawn a read-only transaction where only SELECT and SHOW commands are available. Triggering INSERT, DELETE, CREATE and similar commands would make Postgres respond with an error.

Here we try to create a couple of users in read-only mode:

(let [sql "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/with-tx [conn {:read-only? true}]
    (pg/query conn "select * from users")
    (pg/execute conn sql ["Bob" 30])))

Execution error (ExceptionInfo) at pg.client.result/finalize-errors! (result.clj:537).
clojure.lang.ExceptionInfo: ErrorResponse
{:error
  {:msg :ErrorResponse,
   :errors {:severity "ERROR",
            :verbosity "ERROR",
            :code "25006",
            :message "cannot execute INSERT in a read-only transaction",
            :file "utility.c",
            :line "414",
            :function "PreventCommandIfReadOnly"}}}

Usually, the read-only option is mandatory for replicas. Should you try to write something to the replica by mistake, you’ll get an exception.

Isolation level

The {:isolation-level ...} option sets the isolation level for the new transaction. Here is an example of setting a SERIALIZABLE level although there is no need for that, actually.

(let [sql "INSERT INTO users (name, age) VALUES ($1, $2)"]
  (pg/with-tx [conn {:isolation-level :serializable}]
    (pg/execute conn sql ["Jim" 20])
    (pg/execute conn sql ["Bob" 30])))

The logs:

BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Jim', $2 = '20'
INSERT INTO users (name, age) VALUES ($1, $2)
  parameters: $1 = 'Bob', $2 = '30'
COMMIT

The level might be a keyword, a string or a symbol, both in lower or upper case. For example, :SERIALIZABLE, :serializable, "SERIALIZABLE" and so on. Those levels that consist of two words, e.g. REPEATABLE READ, are separated with a hyphen: :repeatable-read, "REPEATABLE-READ", etc.

Just a reminder, there are four isolation levels in Postgres:

• READ UNCOMMITTED
• READ COMMITTED (default)
• REPEATABLE READ
• SERIALIZABLE

Use them wisely: Never set a level explicitly unless you clearly understand what is the default level and why doesn’t it satisfy you.

Manual transactions and status check

On the low level, transactions are driven by the pg/begin, pg/commit and pg/rollback functions. They all take a single argument (a connection) and send a corresponding command to the server.

Briefly, the pg/with-tx macro boils down to the following code:

(pg/begin conn)
(try
  (pg/execute conn ...)
  (pg/execute conn ...)
  (pg/commit conn)
  (catch Throwable e
    (pg/rollback conn)
    (throw e)))

Sometimes, you’d like to know the current state of a connection: whether it’s in a transaction or not, or if the transaction has been aborted. The pg/status returns a single-character keyword that represents the current state:

(pg/status conn)
:I

• :I stands for Idle: there is no transaction;
• :T stands for Transaction: the connection is in the middle of a transaction;
• :E stands for Error: the transaction has failed. Any subsequent query would lead to an error response.

There are also shortcuts to check the state: pg/idle?, pg/in-transaction? and pg/tx-error?. Here is a quick session:

;; no transaction

(pg/status conn)
:I

(pg/idle? conn)
true

;; begin transaction

(pg/begin conn)

(pg/status conn)
:T

(pg/in-transaction? conn)
true

;; spoil the transaction

(pg/query conn "selekt ...")

Execution error (ExceptionInfo) at ...
ErrorResponse syntax error at or near \"selekt\"...

(pg/status conn)
:E

(pg/tx-error? conn)
true

;; rolling back

(pg/rollback conn)

(pg/status conn)
:I

(pg/idle? conn)
true

Configuration

A config map that you pass to pg/connect has various fields. Most of them are not necessary and are derived from the default map. Below, please find a list of parameters and their defaults.

The :socket map has the following sub-options:

Here is an example of the configuration:

{:host "127.0.0.1"
 :port 5432
 :database "project_dev"
 :user "ivan"
 :password "Secret123"
 :fn-notice my-notice-handler
 :fn-notification my-notification-handler
 :protocol-version const/PROTOCOL_VERSION
 :binary-encode? true
 :binary-decode? true
 :socket {:tcp-no-delay? true
          :so-keep-alive? true
          :so-reuse-addr? true
          :so-reuse-port? true
          :so-rcv-buf (int ...)
          :so-snd-buf (int ...)}}

Authorization

The PG client supports several authentication pipelines:

The SASL method includes two algorithms: SCRAM-SHA-256 and SCRAM-SHA-256-PLUS. It’s up to the client which one to use. At the moment, only SCRAM-SHA-256 is implemented.

Cloning a connection

The pg/clone function spawns a new connection from an existing one. It reuses the config from a given connection.

(pg/with-connection [conn config]
  (with-open [conn2 (pg/clone conn)]
    (pg/query conn2 "select 1 as one")))

[{:one 1}]

Cancelling a query

Sometimes, a poorly composed query might hang. If you have a reference to the connection that has spawned such a query, you may cancel it. Cancelling a query requires a new connection to be opened and thus is usually done in a separate thread.

Imagine you’re running a long query in the future:

(def fut
  (future
    (pg/query conn "select pg_sleep(600) as sleep")))

The pg/cancel takes a connection and cancels its current query:

(pg/cancel conn)

Now if you try to deref the future, you’ll get an exception caused by an error response from the server:

@fut

java.util.concurrent.ExecutionException ...
clojure.lang.ExceptionInfo: ErrorResponse ...

Here is what inside the ex-data:

(try
  @fut
  (catch ExecutionException e
    (let [data (-> e ex-case ex-data)]
      ...)))

{:error
 {:msg :ErrorResponse,
  :errors
  {:severity "ERROR",
   :verbosity "ERROR",
   :code "57014",
   :message "canceling statement due to user request",
   :file "postgres.c",
   :line "3092",
   :function "ProcessInterrupts"}}}

Under the hood, cancelling a query means taking some private data from the target connection, opening a new connection and sending a special message. Then, the new connection gets closed immediately.

Notices

In Postgres, notices are short informative messages shown to the user sometimes. For example, if you try to ROLLBACK although there was no BEGIN first, you won’t get an error but a notice instead.

ROLLBACK;
WARNING:  there is no transaction in progress

Don’t mix notices with notifications (see the corresponding chapter).

By default, the client library just prints the NoticeResponse map with all the fields. You’re welcome to pass your own notice handler which logs them, stores them in an atom or whatever else:

(defn my-notice-handler [NoticeResponse]
  (log/infof "Notice response: %s" NoticeResponse))

;; or

(def notices! (atom []))

(defn my-notice-handler [NoticeResponse]
  (swap! notices! conj NoticeResponse))

;; config

{:host "127.0.0.1"
 :port 5432
 ...
 :fn-notice my-notice-handler}

Thread safety

The connection object is not thread-safe. What it means is, that sharing a connection between multiple threads (futures, agents) might lead to weird behaviour when two threads read something from a socket simultaneously. Technically it’s possible to fix that by wrapping each API call with with-locking; yet it’s much better to use the connection pool. The pool provides a special with-connection macro that ships the with-locking wrapper under the hood, so no other threads can interfere.

Debugging

It’s quite easy to debug messages that the client sends and receives from the server. Run REPL with either the PG_DEBUG environment variable or pg.debug system property set:

PG_DEBUG=1 lein with-profile +test repl

;; or, in lein:

:profiles {:dev {:jvm-opts ["-Dpg.debug=1"]}}

Now that you have the debug option set, run some queries in REPL, and you’ll see the dump:

;; startup pipeline

 -> {:msg :StartupMessage, :protocol-version 196608, :user test, :database test, :options nil}
<-  {:msg :AuthenticationSASL, :status 10, :sasl-types #{SCRAM-SHA-256}}
 -> {:msg :SASLInitialResponse, :sasl-type SCRAM-SHA-256, :client-first-message n,,n=test,r=2c0549c8-ef3f-44d4-82e4-4ad99303f7ec}
<-  {:msg :AuthenticationSASLContinue, :status 11, :server-first-message r=2c0549c8-ef3f-44d4-82e4-4ad99303f7echCLyIqVeK1lswQUcIZPZgNB5,s=qkGROo12a/m8jDa9wv90TA==,i=4096}
 -> {:msg :SASLResponse, :client-final-message c=biws,r=2c0549c8-ef3f-44d4-82e4-4ad99303f7echCLyIqVeK1lswQUcIZPZgNB5,p=IbvOGV7fKIj+OIt54dopu/HI35+9Q67R7uPRp1/Ein8=}
<-  {:msg :AuthenticationSASLFinal, :status 12, :server-final-message v=0Mj1tVhYjDUKt7k9ClCkj9h/6zyVtLMAlZ9HIKA6vyc=}
<-  {:msg :AuthenticationOk, :status 0}

;; init pipeline

<-  {:msg :ParameterStatus, :param in_hot_standby, :value off}
<-  {:msg :ParameterStatus, :param integer_datetimes, :value on}
<-  {:msg :ParameterStatus, :param TimeZone, :value Etc/UTC}
<-  {:msg :ParameterStatus, :param IntervalStyle, :value postgres}
<-  {:msg :ParameterStatus, :param is_superuser, :value on}
<-  {:msg :ParameterStatus, :param application_name, :value }
<-  {:msg :ParameterStatus, :param default_transaction_read_only, :value off}
<-  {:msg :ParameterStatus, :param scram_iterations, :value 4096}
<-  {:msg :ParameterStatus, :param DateStyle, :value ISO, MDY}
<-  {:msg :ParameterStatus, :param standard_conforming_strings, :value on}
<-  {:msg :ParameterStatus, :param session_authorization, :value test}
<-  {:msg :ParameterStatus, :param client_encoding, :value UTF8}
<-  {:msg :ParameterStatus, :param server_version, :value 16beta2 (Debian 16~beta2-1.pgdg120+1)}
<-  {:msg :ParameterStatus, :param server_encoding, :value UTF8}
<-  {:msg :BackendKeyData, :pid 5671, :secret-key -1344960525}
<-  {:msg :ReadyForQuery, :tx-status :I}

;; a hanging query has been run

 -> {:msg :Query, :query select pg_sleep(60) as sleep}

;; cancelling it from another connection and terminate

 -> {:msg :CancelRequest, :code 80877102, :pid 5671, :secret-key -1344960525}
 -> {:msg :Terminate}

;; the query fails, emits an error message, then the connection is ready for
;;  further queries

<-  {:msg :RowDescription, :column-count 1, :columns [{:index 0, :name sleep, :table-oid 0, :column-oid 0, :type-oid 2278, :type-len 4, :type-mod -1, :format 0}]}
<-  {:msg :ErrorResponse, :errors {:severity ERROR, :verbosity ERROR, :code 57014, :message canceling statement due to user request, :file postgres.c, :line 3396, :function ProcessInterrupts}}
<-  {:msg :ReadyForQuery, :tx-status :I}

The right arrow ` -> means the message is passed from the client to the server. The left arrow <- ` stands for reading a message from the server.

The debugging facilities come from the pg.client.debug namespace.