Re: display hot standby state in psql prompt

On 26.06.25 02:52, Greg Sabino Mullane wrote:
> On Wed, Jun 25, 2025 at 11:50 AM Jim Jones <jim(dot)jones(at)uni-muenster(dot)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(at)%/ %i] '
[jim(at)db read-only] SHOW default_transaction_read_only ;
 default_transaction_read_only
-------------------------------
 on
(1 row)

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

[jim(at)db read-only]

== cluster in hot standby

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

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

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

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

[jim(at)db read/write]

What do you think?

Thanks!

Best regards, Jim