Background
Many, if not most, PostgreSQL client programs access the database using the C client library libpq. libpq provides a set of functions that allow client programs to connect and pass queries to a PostgreSQL backend server, and to receive the results of these queries.
Through libpq, PostgreSQL supports a connection string (or alternatively a connection URI) that may specify multiple host + port possible connections - each candidate will be tried in turn until a connection is successfully established.
The support for specifying multiple hosts was introduced in PostgreSQL 10. At the same time, a new connection parameter target_session_attrs was provided, that allowed specification of the type of backend server to connect to.
Only the following possible parameter values were supported (this remained the case up to and including PostgreSQL 13):
| target_session_attrs value | Meaning |
| any (default) | All connections are acceptable. |
| read-write | Only a connection in which read-write transactions are accepted by default is considered acceptable. |
Starting with PostgreSQL 11, Fujitsu proposed additional parameter values, not only to cater for other connection types but also for internal efficiency improvements to validate the parameter and establish the connection. And so started a long process of review, consultation, and refinement with the Postgres community for this feature improvement. This involved:
- Multiple Fujitsu developers
- Numerous patch versions and patch-set changes - combining, splitting and rebasing patches along the way, and across multiple PostgreSQL versions
- Feedback from many community members, sometimes with differing opinions and ideas
- Requests for additional functionality from some members - for example, better alignment and compatibility with a similar PostgreSQL JDBC driver parameter (this triggered further development and patch versions, but ultimately these additions were rejected)
- Final improvements, tweaks, and simplifications by the committer himself
Yes, it was a long road. But finally, for PostgreSQL 14, this libpq enhancement was committed!
Feature overview
The values of target_session_attrs in PostgreSQL 14 are as follows - new values are highlighted:
| target_session_attrs value | Meaning |
| any (default) | Any successful connection is acceptable |
| read-write | The session must accept read-write transactions by default (that is, the server must not be in hot standby mode and default_transaction_read_only must be off). |
| read-only | The session must not accept read-write transactions by default (the converse of read-write). |
| primary | The server must not be in hot standby mode. |
| standby | The server must be in hot standby mode. |
| prefer-standby | First try to find a standby server, but if none of the listed hosts is a standby server, try again in "any" mode. |
Note that a PostgreSQL server can be read-only yet not in hot standby mode, as a result of the configuration parameter default_transaction_read_only being set to on.
Supported clients
Any PostgreSQL client that links with the libpq library can make use of target_session_attrs and the feature improvements described here. This includes most of the PostgreSQL language-support libraries, such as psycopg2 (the driver for Python) and psqlODBC (the PostgreSQL ODBC driver with C language interface), but it does not include the PostgreSQL JDBC driver or NpgSql (the ADO.NET data provider for PostgreSQL). The JDBC driver supports similar functionality with the targetServerType connection parameter, which accepts values primary/secondary/preferSecondary. NpgSql currently does not have similar functionality.
Connection performance improvements
In addition to the new target_session_attrs parameter values, some connection performance improvements were made, for when connecting to a PostgreSQL 14 or later server. This was achieved through the use of reportable (GUC_REPORT) configuration variables that are related to the session status:
| GUC variable | Description |
| default_transaction_read_only | Made reportable (GUC_REPORT) in PostgreSQL 14. |
| in_hot_standby | New GUC_REPORT variable in PostgreSQL 14. |
These are reported directly to the client by the server on successful connection, saving an extra network round trip in order to determine the session status. So, when connecting to an older version server, a SHOW or SELECT query is issued to detect session read-only-ness or server hot-standby state.
Note that in_hot_standby also gets reported to the client in the event that the server gets promoted to primary during the session.
Examples
psql (the terminal-based front-end to PostgreSQL) uses libpq, so conveniently it can be used for testing target_session_attrs, without the need for any coding.
Some simple examples of using different target_session_attrs values, when attempting to connect to one of two possible local servers, are shown below.
1Create local server instances testdb1 and testdb2 (running on ports 5432 and 5433) - by default, both accept read-write transactions.
$ pg_ctl -D ./testdb2 initdb
$ pg_ctl -D ./testdb1 -o '-p 5432' -l testdb_1.log start
$ pg_ctl -D ./testdb2 -o '-p 5433' -l testdb_2.log start
2Try to connect to a server that only accepts read-only transactions.
psql: error: connection to server at "localhost" (::1), port 5432 failed: session is not read-only
connection to server at "localhost" (::1), port 5433 failed: session is not read-only
3Change server testdb2 to accept only read-only transactions.
Update testdb2/postgresql.conf to add default_transaction_read_only = on and restart.
4Try to connect to a read-only server.
psql (14devel)
Type "help" for help.
postgres=# show port;
port
------
5433
(1 row)
5Try to connect to a server in hot standby mode.
psql: error: connection to server at "localhost" (::1), port 5432 failed: server is not in hot standby mode
connection to server at "localhost" (::1), port 5433 failed: server is not in hot standby mode
6Try to connect to a primary server (i.e., not in standby mode).
psql (14devel)
Type "help" for help.
postgres=# show port;
port
------
5432
(1 row)
7Connect to a server, preferably one in hot standby mode.
psql (14devel)
Type "help" for help.
postgres=# show port;
port
------
5432
(1 row)
Summary
The newly-supported target_session_attrs values allow finer granularity in the choice of desired target server connection by the client, and assist with the multi-host connection functionality. They also provide a rudimentary form of scaling – for example, by allowing read-only requests to be redirected to standby servers, in order to reduce the load on the primary server. Lastly, these new target_session_attrs values may allow multi-host connection strings/URIs to work better with some PostgreSQL failover solutions, in reconnecting to available servers.
