Обсуждение: display hot standby state in psql prompt

Hi,

Some weeks ago we briefly discussed in the discord channel the
possibility of introducing a psql prompt display option to identify if
the connected database is in hot standby mode, which can be useful when
using multiple hosts in the connection string. Right now, it's using the
in_hot_standby value in prompt.c to determine the database state:

case 'i':
    if (pset.db && PQparameterStatus(pset.db, "in_hot_standby"))
    {
        const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
        if (hs && strcmp(hs, "on") == 0)
            strlcpy(buf, "standby", sizeof(buf));
        else
            strlcpy(buf, "primary", sizeof(buf));

.. which could be used like this:

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

postgres=# \set PROMPT1 '[%i] # '
[standby] # SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

[primary] #


The hardcoded "standby" and "primary" strings are not very flexible, but
I am not sure how to make these strings customisable just yet.

Any thoughts on this feature?

Best regards, Jim

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

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 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 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: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

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

On Sat, Oct 25, 2025 at 1:13 AM Jim Jones <jim.jones@uni-muenster.de> 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?

Yes, thanks for working on this!


> if (!hs || !ro)
>     strlcpy(buf, "unknown", sizeof(buf));
> else if ((hs && strcmp(hs, "on") == 0) ||
>     (ro && strcmp(ro, "on") == 0))

I think the "hs &&" and "ro &&" checks are no longer needed,
since we've already confirmed they're not NULL at that point.

Also, should "unknown" be marked for translation, as in the \conninfo code?
I'm not sure whether showing a translated string in the psql prompt is
desirable, though.

Regards,

--
Fujii Masao