Horton

Re: display hot standby state in psql prompt

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-06-26T07:22:11Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: Add %i prompt escape to indicate hot standby status.

  2. Mark search_path as GUC_REPORT

Attachments


On 26.06.25 02:52, Greg Sabino Mullane wrote:
> On Wed, Jun 25, 2025 at 11:50 AM Jim Jones <jim.jones@uni-muenster.de>
> wrote:
>
>     Since I cannot get the value of transaction_read_only via
>     PQparameterStatus.
>
>
> Hmmm... we can at least get default_transaction_read_only. As
> fe-connect.c points out:
>                     /*
>                      * "transaction_read_only = on" proves that at
> least one
>                      * of default_transaction_read_only and
> in_hot_standby is
>                      * on, but we don't actually know which.  We don't
> care
>                      * though for the purpose of identifying a read-only
>                      * session, so satisfy the CONNECTION_CHECK_TARGET
> code by
>                      * claiming they are both on.  On the other hand,
> if it's
>                      * a read-write session, they are certainly both off.
>                      */
> Maybe that's good enough? It won't detect people starting a new
> transaction and declaring it read-only, but it should be sufficient to
> warn people when a connection is starting out in a read-only state.
> And it will still toggle auto-magically on promotion.
>

Combining in_hot_standby and default_transaction_read_only might provide
better coverage for this feature, e.g.

case 'i':
    if (pset.db)
    {
        const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
        const char *ro = PQparameterStatus(pset.db,
"default_transaction_read_only");

        if ((hs && strcmp(hs, "on") == 0) ||
            (ro && strcmp(ro, "on") == 0))
            strlcpy(buf, "read-only", sizeof(buf));
        else
            strlcpy(buf, "read/write", sizeof(buf));
    }
    else
        buf[0] = '\0';
    break;


Relying solely on default_transaction_read_onlycould be misleading, as a
database in hot standby mode might be wrongly shown as "read/write".
(draft patch attached)


Some tests:

== default_transaction_read_only set to 'on'

psql (18beta1)
Type "help" for help.

db=# \set PROMPT1 '[%n@%/ %i] '
[jim@db read-only] SHOW default_transaction_read_only ;
 default_transaction_read_only
-------------------------------
 on
(1 row)

[jim@db read-only] SHOW in_hot_standby ;
 in_hot_standby
----------------
 off
(1 row)

[jim@db read-only]


== cluster in hot standby

psql (18beta1)
Type "help" for help.

db=# \set PROMPT1 '[%n@%/ %i] '
[jim@db read-only] SHOW in_hot_standby ;
 in_hot_standby
----------------
 on
(1 row)

[jim@db read-only] SHOW default_transaction_read_only ;
 default_transaction_read_only
-------------------------------
 off
(1 row)

[jim@db read-only] SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

[jim@db read/write]


What do you think?

Thanks!

Best regards, Jim