Fujitsu Logo
ENQUIRE

    In this post, I will discuss the enhancements that will be added to the database connection setting target_session_attrs in PostgreSQL 14, explain the history behind this feature, how it can be used, and the various benefits it provides.

    photo-greg-nancarrow-in-red-circleBackground

    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.

    img-man-looking-at-laptopSupported 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 ./testdb1 initdb
    $ 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 "host=localhost,localhost port=5432,5433 dbname=postgres target_session_attrs=read-only"

    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
    Connection failed, because both instances only accept read-write transactions

    3Change server testdb2 to accept only read-only transactions.

    Update testdb2/postgresql.conf to add default_transaction_read_only = on and restart.

    $ pg_ctl -D ./testdb2 -o '-p 5433' -l testdb_2.log restart

    4Try to connect to a read-only server.

    $ psql "host=localhost,localhost port=5432,5433 dbname=postgres target_session_attrs=read-only"
    psql (14devel)
    Type "help" for help.

    postgres=# show port;
    port
    ------
    5433
    (1 row)
    Connected to testdb2, which we changed to only accept read-only transactions above

    5Try to connect to a server in hot standby mode.

    $ psql "host=localhost,localhost port=5432,5433 dbname=postgres target_session_attrs=standby"

    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
    Connection failed, because no instance is in hot standby mode

    6Try to connect to a primary server (i.e., not in standby mode).

    $ psql "host=localhost,localhost port=5432,5433 dbname=postgres target_session_attrs=primary"

    psql (14devel)
    Type "help" for help.

    postgres=# show port;
    port
    ------
    5432
    (1 row)
    Connected to testdb1

    7Connect to a server, preferably one in hot standby mode.

    $ psql "host=localhost,localhost port=5432,5433 dbname=postgres target_session_attrs=prefer-standby"

    psql (14devel)
    Type "help" for help.

    postgres=# show port;
    port
    ------
    5432
    (1 row)
    Connected to testdb1

    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.

    Subscribe to be notified of future blog posts
    If you would like to be notified of my next blog posts and other PostgreSQL-related articles, fill the form here.
    We also have a series of technical articles for PostgreSQL enthusiasts of all stripes, with tips and how-to's.

     

    Explore PostgreSQL Insider

    Topics: PostgreSQL, PostgreSQL Community

    Receive our blog

    Receive notification of PostgreSQL-based articles for business and technical audiences.

    SEARCH BY TOPIC

    see all

    Read our latest blogs

    Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.