display hot standby state in psql prompt

On Wed, Jun 25, 2025 at 4:02 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

if (pset.db && PQparameterStatus(pset.db, "in_hot_standby"))

Seems transaction_read_only might be a more useful thing to examine? That's
the side-effect, if you will, that people really care about when in hot
standby mode (and of course, we can get into TRO other ways).

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Hi Greg

On 25.06.25 17:17, Greg Sabino Mullane wrote:

Seems transaction_read_only might be a more useful thing to examine?
That's the side-effect, if you will, that people really care about
when in hot standby mode (and of course, we can get into TRO other ways).

Good point. But wouldn't it mean that I would need to execute a query
every time the prompt is refreshed? Since I cannot get the value of
transaction_read_only via PQparameterStatus. I like the idea, but I'm
concerned about the overhead.

Best, Jim

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.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

On Thu, Jun 26, 2025 at 3:22 AM Jim Jones <jim.jones@uni-muenster.de> wrote:

What do you think?

Seems good enough for me. I think as long as we document it well, it's only
going to be a net positive, even with some edge cases.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Hi

On 21.07.25 21:12, Greg Sabino Mullane wrote:

Seems good enough for me. I think as long as we document it well, it's
only going to be a net positive, even with some edge cases.

I just moved the patch from PG19-Drafts to PG19-2 commitfest.[1]

Thanks a lot for the feedback!

Best regards, Jim

1 - https://commitfest.postgresql.org/patch/5872/

Hi Jim,

On Tue, Jul 22, 2025 at 4:40 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

Hi

On 21.07.25 21:12, Greg Sabino Mullane wrote:

Seems good enough for me. I think as long as we document it well, it's
only going to be a net positive, even with some edge cases.

I just moved the patch from PG19-Drafts to PG19-2 commitfest.[1]

Thanks a lot for the feedback!

Best regards, Jim

1 - https://commitfest.postgresql.org/patch/5872/

+1 for the patch,i have reviewed and tested this patch, except these below
cosmetic changes it LGTM.

cosmetic changes:
1) add comment about %i in get_prompt api.
2) maybe we can use read-write instead of read/write to be consistent with
the
naming such as options of target_session_attrs uses read-write.

testing:

=> in primary node:

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

postgres=# \set PROMPT1 '[%i] # '
[read/write] # set default_transaction_read_only=on;
SET
[read-only] # set default_transaction_read_only=off;
SET
[read/write] # show in_hot_standby ;
in_hot_standby
----------------
off
(1 row)

[read/write] # set default_transaction_read_only=on;
SET
[read-only] # show in_hot_standby ;
in_hot_standby
----------------
off
(1 row)

[read-only] # \q

=> in replica node

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

postgres=# \set PROMPT1 '[%i] # '
[read-only] # show in_hot_standby ;
in_hot_standby
----------------
on
(1 row)

[read-only] # show default_transaction_read_only;
default_transaction_read_only
-------------------------------
off
(1 row)

[read-only] # set default_transaction_read_only=on;
SET
[read-only] # set transaction_read_only=on;
SET
[read-only] # set transaction_read_only=off;
ERROR: cannot set transaction read-write mode during recovery
[read-only] # select pg_promote();
pg_promote
------------
t
(1 row)

[read-only] # show in_hot_standby ;
in_hot_standby
----------------
off
(1 row)

[read-only] # show default_transaction_read_only;
default_transaction_read_only
-------------------------------
on
(1 row)

[read-only] # set default_transaction_read_only=off;
SET
[read/write] #

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

Hi Srinath

On 23.07.25 09:03, Srinath Reddy Sadipiralla wrote:

+1 for the patch,i have reviewed and tested this patch, except these
below cosmetic changes it LGTM.

cosmetic changes:
1) add comment about %i in get_prompt api.

Done.

2) maybe we can use read-write instead of read/write to be consistent
with the
    naming such as options of target_session_attrs uses read-write.

I believe that 'read/write' is more idiomatic than 'read-write' in this
context. 'Read-only' works as a hyphenated adjective, and 'read/write'
is typically treated as a paired label that indicates two distinct
capabilities --- read and write. What do you think?

v3 attached.

Thanks for the thorough testing and review!

Best, Jim

On Wed, Jul 23, 2025 at 1:33 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

I believe that 'read/write' is more idiomatic than 'read-write' in this
context. 'Read-only' works as a hyphenated adjective, and 'read/write'
is typically treated as a paired label that indicates two distinct
capabilities --- read and write. What do you think?

Makes sense.

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

On Wed, Jul 23, 2025 at 10:03:54AM +0200, Jim Jones wrote:

I believe that 'read/write' is more idiomatic than 'read-write' in this
context. 'Read-only' works as a hyphenated adjective, and 'read/write'
is typically treated as a paired label that indicates two distinct
capabilities --- read and write. What do you think?

My first thought when looking at this thread/patch was that something like
"read-write" might be a bit long for a psql prompt. I probably would've
chosen something like "r" or "ro" for read-only and "rw" for read-write. I
suppose you could argue that those are more difficult to see, and this
seems particularly useful when you want to be sure that you won't
inadvertently write any data.

--
nathan

Hi Nathan

On 23/10/2025 23:02, Nathan Bossart wrote:

My first thought when looking at this thread/patch was that something like
"read-write" might be a bit long for a psql prompt. I probably would've
chosen something like "r" or "ro" for read-only and "rw" for read-write. I
suppose you could argue that those are more difficult to see, and this
seems particularly useful when you want to be sure that you won't
inadvertently write any data.

I can see the appeal of keeping the indicator as short as possible in a
prompt, but that can also make it a bit harder to quickly distinguish
between the read/write and read-only states. In this case, clarity might
be more important than compactness. Then again, the traditional shell
prompt symbols ($, #) aren't exactly self-explanatory either, and people
seem fine with those :)

Although I prefer it being explicit in this case, I can live with either
approach. Do you think we should go for "ro" or "rw"?

Thanks for the review!

Best, Jim

On Fri, Oct 24, 2025 at 01:07:41AM +0200, Jim Jones wrote:

Although I prefer it being explicit in this case, I can live with either
approach. Do you think we should go for "ro" or "rw"?

I'm currently leaning towards ro/rw, but I'm also hoping that others chime
in with opinions here.

--
nathan

On Fri, Oct 24, 2025 at 10:12 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I'm currently leaning towards ro/rw, but I'm also hoping that others chime
in with opinions here.

-1. Too short, too cryptic, too similar. I know our existing symbols aren't
great either, but we can do better.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

On Fri, Oct 24, 2025 at 10:54:57AM -0400, Greg Sabino Mullane wrote:

On Fri, Oct 24, 2025 at 10:12 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I'm currently leaning towards ro/rw, but I'm also hoping that others chime
in with opinions here.

-1. Too short, too cryptic, too similar. I know our existing symbols aren't
great either, but we can do better.

What would you prefer instead?

--
nathan

*shrug* I still like the earlier versions:

read-only
read/write

On Fri, Oct 24, 2025 at 8:36 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

read-only
read/write

+1

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

On Wed, Jul 23, 2025 at 5:04 PM Jim Jones <jim.jones@uni-muenster.de> wrote:

v3 attached.

+ const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
+ const char *ro = PQparameterStatus(pset.db, "default_transaction_read_only");

When either hs or ro is NULL, the displayed status can be incorrect.
For example, connecting to a standby server running PostgreSQL 10
incorrectly shows "read/write". In such cases, wouldn't it be clearer
to display something like "unknown", similar to how the "Hot Standby"
column in \conninfo reports "unknown"?

Regards,

--
Fujii Masao

On 24/10/2025 17:21, Fujii Masao wrote:

+ const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
+ const char *ro = PQparameterStatus(pset.db, "default_transaction_read_only");

When either hs or ro is NULL, the displayed status can be incorrect.
For example, connecting to a standby server running PostgreSQL 10
incorrectly shows "read/write". In such cases, wouldn't it be clearer
to display something like "unknown", similar to how the "Hot Standby"
column in \conninfo reports "unknown"?

Oh, it didn't occur to me to test this edge case. Thanks for the hint!

Would this be what you have in mind?

if (!hs || !ro)
strlcpy(buf, "unknown", sizeof(buf));
else if ((hs && strcmp(hs, "on") == 0) ||
(ro && strcmp(ro, "on") == 0))
strlcpy(buf, "read-only", sizeof(buf));
else
strlcpy(buf, "read/write", sizeof(buf));

Best, Jim

On 24/10/2025 18:13, Jim Jones wrote:

On 24/10/2025 17:21, Fujii Masao wrote:

+ const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
+ const char *ro = PQparameterStatus(pset.db, "default_transaction_read_only");

When either hs or ro is NULL, the displayed status can be incorrect.
For example, connecting to a standby server running PostgreSQL 10
incorrectly shows "read/write". In such cases, wouldn't it be clearer
to display something like "unknown", similar to how the "Hot Standby"
column in \conninfo reports "unknown"?

Oh, it didn't occur to me to test this edge case. Thanks for the hint!

Would this be what you have in mind?

if (!hs || !ro)
strlcpy(buf, "unknown", sizeof(buf));
else if ((hs && strcmp(hs, "on") == 0) ||
(ro && strcmp(ro, "on") == 0))
strlcpy(buf, "read-only", sizeof(buf));
else
strlcpy(buf, "read/write", sizeof(buf));

I just realised I forgot to attach the patch. Sorry about that!
PFA v4.

Best, Jim