Horton

Thread

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

  1. display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-06-25T08:01:40Z

    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

  2. Re: display hot standby state in psql prompt

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-06-25T15:17:54Z

    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

  3. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-06-25T15:50:39Z

    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

  4. Re: display hot standby state in psql prompt

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-06-26T00:52:06Z

    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

  5. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-06-26T07:22:11Z

    
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

  6. Re: display hot standby state in psql prompt

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-07-21T19:12:24Z

    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

  7. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-07-22T09:51:49Z

    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/

  8. Re: display hot standby state in psql prompt

    Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2025-07-23T07:03:02Z

    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/

  9. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-07-23T08:03:54Z

    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

  10. Re: display hot standby state in psql prompt

    Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2025-07-23T08:45:28Z

    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/

  11. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-23T21:02:46Z

    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

  12. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-23T23:07:41Z

    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

  13. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-24T14:12:47Z

    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

  14. Re: display hot standby state in psql prompt

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-10-24T14:54:57Z

    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

  15. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-24T15:03:01Z

    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

  16. Re: display hot standby state in psql prompt

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-10-24T15:05:56Z

    *shrug* I still like the earlier versions:

read-only
read/write

  17. Re: display hot standby state in psql prompt

    Srinath Reddy Sadipiralla <srinath2133@gmail.com> — 2025-10-24T15:13:20Z

    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/

  18. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-10-24T15:21:28Z

    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

  19. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-24T16:13:09Z

    
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

  20. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-24T20:31:41Z

    
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

  21. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-10-27T03:32:10Z

    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

  22. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-27T08:08:17Z

    
On 27/10/2025 04:32, Fujii Masao wrote:
> I think the "hs &&" and "ro &&" checks are no longer needed,
> since we've already confirmed they're not NULL at that point.

Right. Checks removed.

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

Here I am not sure if it's applicable. In this file there are other
strings that are not marked for translation, "abort", "on", "off". I
changed the string to _("unknown") for now, but we can remove it in
further iterations if we agree it's not desirable :)

v5 attached.

Thanks for the review.

Best, Jim

  23. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-27T16:11:19Z

    On Fri, Oct 24, 2025 at 08:43:20PM +0530, Srinath Reddy Sadipiralla wrote:
> On Fri, Oct 24, 2025 at 8:36 PM Greg Sabino Mullane <htamfids@gmail.com>
> wrote:
>> read-only
>> read/write
>
> +1

Alright, I seem to have been outvoted, then.

-- 
nathan

  24. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-27T16:23:12Z

    On Mon, Oct 27, 2025 at 09:08:17AM +0100, Jim Jones wrote:
> +            Displays the session's read-only status as <literal>read-only</literal>
> +            if the server is in hot standby (<literal>in_hot_standby</literal> is
> +            <literal>on</literal>) or the default transaction mode is read-only
> +            (<literal>default_transaction_read_only</literal> is <literal>on</literal>),
> +            or <literal>read-write</literal> otherwise. Useful for identifying
> +            sessions that cannot perform writes, such as in replication setups.

This was briefly mentioned upthread, but I'm a little concerned that this
doesn't respond to commands like SET TRANSACTION READ ONLY.  I wonder if we
should mark transaction_read_only as GUC_REPORT and use that instead.  FWIW
I see that we marked search_path as GUC_REPORT somewhat recently (see
commit 28a1121).

-- 
nathan

  25. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-27T20:17:03Z

    
On 27/10/2025 17:23, Nathan Bossart wrote:
> This was briefly mentioned upthread, but I'm a little concerned that this
> doesn't respond to commands like SET TRANSACTION READ ONLY.  I wonder if we
> should mark transaction_read_only as GUC_REPORT and use that instead.  FWIW
> I see that we marked search_path as GUC_REPORT somewhat recently (see
> commit 28a1121).

You're right, it doesn't. I like the idea, but I'm not sure how to
integrate a transaction-scoped variable into this feature. Would that
mean we also need to change the reset mechanism for GUC_REPORT variables
when the transaction ends?

Best, Jim

  26. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-27T21:00:01Z

    On Mon, Oct 27, 2025 at 09:17:03PM +0100, Jim Jones wrote:
> On 27/10/2025 17:23, Nathan Bossart wrote:
>> This was briefly mentioned upthread, but I'm a little concerned that this
>> doesn't respond to commands like SET TRANSACTION READ ONLY.  I wonder if we
>> should mark transaction_read_only as GUC_REPORT and use that instead.  FWIW
>> I see that we marked search_path as GUC_REPORT somewhat recently (see
>> commit 28a1121).
> 
> You're right, it doesn't. I like the idea, but I'm not sure how to
> integrate a transaction-scoped variable into this feature. Would that
> mean we also need to change the reset mechanism for GUC_REPORT variables
> when the transaction ends?

Hm.  You're right, that seems to have problems (I'm curious about the use
of stmt->is_local in SetPGVariable() for SET TRANSACTION statements).  I
also see some past discussions in this area [0] [1] [2].

[0] https://postgr.es/m/flat/3a40f835-116d-0f95-aede-d5236337bbf0%402ndquadrant.com
[1] https://postgr.es/m/flat/CA%2BTgmoZsHrHeqh5dYpoH%2BWW5EmT-egMGuyrLTsjKz80WajT4tg%40mail.gmail.com
[2] https://postgr.es/m/flat/CAFj8pRBFU-WzzQhNrwRHn67N0Ug8a9-0-9BOo69PPtcHiBDQMA%40mail.gmail.com

-- 
nathan

  27. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-10-27T23:55:43Z

    On Tue, Oct 28, 2025 at 6:00 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Mon, Oct 27, 2025 at 09:17:03PM +0100, Jim Jones wrote:
> > On 27/10/2025 17:23, Nathan Bossart wrote:
> >> This was briefly mentioned upthread, but I'm a little concerned that this
> >> doesn't respond to commands like SET TRANSACTION READ ONLY.  I wonder if we
> >> should mark transaction_read_only as GUC_REPORT and use that instead.  FWIW
> >> I see that we marked search_path as GUC_REPORT somewhat recently (see
> >> commit 28a1121).
> >
> > You're right, it doesn't. I like the idea, but I'm not sure how to
> > integrate a transaction-scoped variable into this feature. Would that
> > mean we also need to change the reset mechanism for GUC_REPORT variables
> > when the transaction ends?

If we mark transaction_read_only as GUC_REPORT, wouldn't the reset value
be sent automatically at the end of the transaction? It seems like we wouldn't
need any new mechanism for that. However, the downside might be that
more ParameterStatus messages would be sent, potentially adding overhead.

Regards,

-- 
Fujii Masao

  28. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-28T11:03:48Z

    
On 28/10/2025 00:55, Fujii Masao wrote:
> If we mark transaction_read_only as GUC_REPORT, wouldn't the reset value
> be sent automatically at the end of the transaction? It seems like we wouldn't
> need any new mechanism for that. However, the downside might be that
> more ParameterStatus messages would be sent, potentially adding overhead.


I tried that, but simply marking it as GUC_REPORT does not reset the
variable when the transaction ends.

== guc_parameters.dat ==

{ name => 'transaction_read_only', type => 'bool', context =>
'PGC_USERSET', group => 'CLIENT_CONN_STATEMENT',
  short_desc => 'Sets the current transaction\'s read-only status.',
  flags => 'GUC_NO_RESET | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE |
GUC_DISALLOW_IN_FILE | GUC_REPORT',
  variable => 'XactReadOnly',
  boot_val => 'false',
  check_hook => 'check_transaction_read_only',
},

== prompt.c ==

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

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

...

== test ==

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

postgres=# \set PROMPT1 '[%i] # '
[read/write] # BEGIN;
BEGIN
[read/write] # SET transaction_read_only TO on;
SET
[read-only] # END;
COMMIT
[read-only] # SHOW transaction_read_only;
 transaction_read_only
-----------------------
 off
(1 row)

[read-only] #


So I assumed that the reset happens during transaction cleanup, which
might not send parameter status updates.

Am I missing something?

Thanks!

Best, Jim

  29. Re: display hot standby state in psql prompt

    Nathan Bossart <nathandbossart@gmail.com> — 2025-10-28T16:42:52Z

    On Tue, Oct 28, 2025 at 12:03:48PM +0100, Jim Jones wrote:
> On 28/10/2025 00:55, Fujii Masao wrote:
>> If we mark transaction_read_only as GUC_REPORT, wouldn't the reset value
>> be sent automatically at the end of the transaction? It seems like we wouldn't
>> need any new mechanism for that. However, the downside might be that
>> more ParameterStatus messages would be sent, potentially adding overhead.
> 
> I tried that, but simply marking it as GUC_REPORT does not reset the
> variable when the transaction ends.

IIUC the problem is that we use GUC_ACTION_SET for those even though they
are reset at transaction end by the routines in xact.c.  Something like the
following seems to be enough to get it working as expected in some basic
tests, but there are probably other things to consider.  Keep in mind that
previous proposals to mark transaction_read_only as GUC_REPORT have been
rejected, too.

diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a82286cc98a..d0bbb5aff19 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3349,6 +3349,12 @@ set_config_with_handle(const char *name, config_handle *handle,
     bool        prohibitValueChange = false;
     bool        makeDefault;

+    if (action == GUC_ACTION_SET &&
+        (strcmp(name, "transaction_isolation") == 0 ||
+         strcmp(name, "transaction_read_only") == 0 ||
+         strcmp(name, "transaction_deferrable") == 0))
+        action = GUC_ACTION_LOCAL;
+
     if (elevel == 0)
     {
         if (source == PGC_S_DEFAULT || source == PGC_S_FILE)

-- 
nathan

  30. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-29T01:16:32Z

    
On 28/10/2025 17:42, Nathan Bossart wrote:
> IIUC the problem is that we use GUC_ACTION_SET for those even though they
> are reset at transaction end by the routines in xact.c.  Something like the
> following seems to be enough to get it working as expected in some basic
> tests, but there are probably other things to consider.  Keep in mind that
> previous proposals to mark transaction_read_only as GUC_REPORT have been
> rejected, too.
> 
> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> index a82286cc98a..d0bbb5aff19 100644
> --- a/src/backend/utils/misc/guc.c
> +++ b/src/backend/utils/misc/guc.c
> @@ -3349,6 +3349,12 @@ set_config_with_handle(const char *name, config_handle *handle,
>      bool        prohibitValueChange = false;
>      bool        makeDefault;
> 
> +    if (action == GUC_ACTION_SET &&
> +        (strcmp(name, "transaction_isolation") == 0 ||
> +         strcmp(name, "transaction_read_only") == 0 ||
> +         strcmp(name, "transaction_deferrable") == 0))
> +        action = GUC_ACTION_LOCAL;
> +
>      if (elevel == 0)
>      {
>          if (source == PGC_S_DEFAULT || source == PGC_S_FILE)

Thanks! It does solve the current problem (don't mind the debug messages):

postgres=# \set PROMPT1 '[%i] # '
DEBUG: hs='off' ro='off' tr='off'

[read/write] # BEGIN;
BEGIN
DEBUG: hs='off' ro='off' tr='off'

[read/write] # SET transaction_read_only TO on;
SET
DEBUG: hs='off' ro='off' tr='on'

[read-only] # END;
COMMIT
DEBUG: hs='off' ro='off' tr='off'

But in other cases it now fails, because the parameter status of
transaction_read_only isn't being updated:

postgres=# \set PROMPT1 '[%i] # '
DEBUG: hs='on' ro='off' tr='on'

[read-only] # SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

DEBUG: hs='off' ro='off' tr='on'

[read-only] # SHOW transaction_read_only;
 transaction_read_only
-----------------------
 off
(1 row)

DEBUG: hs='off' ro='off' tr='on'


I played a bit with the following hack, which solves the
transaction_read_only issue ...


diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a82286cc98..5c7748fbba 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2551,9 +2551,19 @@ ReportChangedGUCOptions(void)
         * transition from false to true.
         */
        if (in_hot_standby_guc && !RecoveryInProgress())
+       {
                SetConfigOption("in_hot_standby", "false",
                                                PGC_INTERNAL,
PGC_S_OVERRIDE);

+               /*
+                * Similarly, when exiting hot standby,
transaction_read_only may need
+                * to be reported. During hot standby, writes were
blocked regardless
+                * of the transaction_read_only setting, so report the
actual state now.
+                */
+               SetConfigOption("transaction_read_only","off",
+                                               PGC_INTERNAL,
PGC_S_OVERRIDE);
+       }
+
        /* Transmit new values of interesting variables */
        slist_foreach_modify(iter, &guc_report_list)
        {
@@ -3349,6 +3359,12 @@ set_config_with_handle(const char *name,
config_handle *handle,
        bool            prohibitValueChange = false;
        bool            makeDefault;

+       if (action == GUC_ACTION_SET &&
+               (strcmp(name, "transaction_isolation") == 0 ||
+                strcmp(name, "transaction_read_only") == 0 ||
+                strcmp(name, "transaction_deferrable") == 0))
+               action = GUC_ACTION_LOCAL;
+
        if (elevel == 0)
        {
                if (source == PGC_S_DEFAULT || source == PGC_S_FILE)


== tests ==

postgres=# \set PROMPT1 '[%i] # '
DEBUG: hs='on' ro='off' tr='on'

[read-only] # SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

DEBUG: hs='off' ro='off' tr='off'

[read/write] # BEGIN;
BEGIN
DEBUG: hs='off' ro='off' tr='off'

[read/write] # SET TRANSACTION READ ONLY;
SET
DEBUG: hs='off' ro='off' tr='on'

[read-only] # END;
COMMIT
DEBUG: hs='off' ro='off' tr='off'

[read/write] # SET default_transaction_read_only TO on;
SET
DEBUG: hs='off' ro='on' tr='off'

[read-only] # SET default_transaction_read_only TO off;
SET
DEBUG: hs='off' ro='off' tr='off'
[read/write] #

... but I'm still not sure of its impact. I guess I need to go back to
the drawing board on this one.

Thanks!

Best, Jim

  31. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-10-30T03:42:27Z

    On Tue, Oct 28, 2025 at 8:03 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
>
>
> On 28/10/2025 00:55, Fujii Masao wrote:
> > If we mark transaction_read_only as GUC_REPORT, wouldn't the reset value
> > be sent automatically at the end of the transaction? It seems like we wouldn't
> > need any new mechanism for that. However, the downside might be that
> > more ParameterStatus messages would be sent, potentially adding overhead.
>
>
> I tried that, but simply marking it as GUC_REPORT does not reset the
> variable when the transaction ends.
>
> == guc_parameters.dat ==
>
> { name => 'transaction_read_only', type => 'bool', context =>
> 'PGC_USERSET', group => 'CLIENT_CONN_STATEMENT',
>   short_desc => 'Sets the current transaction\'s read-only status.',
>   flags => 'GUC_NO_RESET | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE |
> GUC_DISALLOW_IN_FILE | GUC_REPORT',
>   variable => 'XactReadOnly',
>   boot_val => 'false',
>   check_hook => 'check_transaction_read_only',
> },
>
> == prompt.c ==
>
> ...
> const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
> const char *ro = PQparameterStatus(pset.db,
> "default_transaction_read_only");
> const char *tr = PQparameterStatus(pset.db, "transaction_read_only");
>
> if (!hs || !ro || !tr)
>         strlcpy(buf, _("unknown"), sizeof(buf));
> else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0 || strcmp(tr,
> "on") == 0)
>         strlcpy(buf, "read-only", sizeof(buf));
> else
>         strlcpy(buf, "read/write", sizeof(buf));
>
> ...
>
> == test ==
>
> psql (19devel)
> Type "help" for help.
>
> postgres=# \set PROMPT1 '[%i] # '
> [read/write] # BEGIN;
> BEGIN
> [read/write] # SET transaction_read_only TO on;
> SET
> [read-only] # END;
> COMMIT
> [read-only] # SHOW transaction_read_only;
>  transaction_read_only
> -----------------------
>  off
> (1 row)
>
> [read-only] #
>
>
> So I assumed that the reset happens during transaction cleanup, which
> might not send parameter status updates.
>
> Am I missing something?

No, you're right. I mistakenly thought marking it as GUC_REPORT was enough
since BEGIN READ ONLY + COMMIT/ROLLBACK worked as expected,
but I overlooked the SET transaction_read_only + COMMIT case where
it doesn't. Sorry for the noise.

Regards,

-- 
Fujii Masao

  32. Re: display hot standby state in psql prompt

    Chao Li <li.evan.chao@gmail.com> — 2025-10-30T07:51:53Z

    
> On Oct 30, 2025, at 11:42, Fujii Masao <masao.fujii@gmail.com> wrote:
> 
> On Tue, Oct 28, 2025 at 8:03 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>> 

I did a quick test, and found a problem. I shutdown the server, and “\c” reconnecting failed, but psql still show “read/write”, which seems wrong:

```
"read/write"\c
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
Previous connection kept
hs=off ro=off
"read/write"
hs=off ro=off
"read/write"
hs=off ro=off
"read/write"
hs=off ro=off
"read/write”
```

Looks like I am too late to vote. Actually, if I had the chance, I would vote “rw/ro”.

And a question:
```
+						if (!hs || !ro)
+							strlcpy(buf, _("unknown"), sizeof(buf));
+						else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
+							strlcpy(buf, "read-only", sizeof(buf));
+						else
+							strlcpy(buf, "read/write", sizeof(buf));
```

Why wrap “unknown” in "_()” but not “read-only” and “read/write”?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  33. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-30T09:20:50Z

    Hi Chao

On 30/10/2025 08:51, Chao Li wrote:
> I did a quick test, and found a problem. I shutdown the server, and “\c” reconnecting failed, but psql still show “read/write”, which seems wrong:
> 
> "read/write"\c
> connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
> 	Is the server running locally and accepting connections on that socket?
> Previous connection kept

I can see that a "read/write" prompt in this case sounds misleading, but
I am not entirely sure it is a problem. The message says "Previous
connection kept", which might suggest that the previous parameter values
are cached? See %p:

postgres=# \set PROMPT1 '[%p] # '
[1268754] # \c
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
or directory
        Is the server running locally and accepting connections on that
socket?
Previous connection kept
[1268754] #

The backend pid is still displayed, although the server is no longer
running.
 > Why wrap “unknown” in "_()” but not “read-only” and “read/write”?
It didn't occur to me that read-only and read/write needed translation,
but I guess you're right. I'll add to the next version.

Thanks for the review. Much appreciated!

Best, Jim

  34. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-30T10:16:53Z

    
On 28/10/2025 17:42, Nathan Bossart wrote:
> On Tue, Oct 28, 2025 at 12:03:48PM +0100, Jim Jones wrote:
>> On 28/10/2025 00:55, Fujii Masao wrote:
>>> If we mark transaction_read_only as GUC_REPORT, wouldn't the reset value
>>> be sent automatically at the end of the transaction? It seems like we wouldn't
>>> need any new mechanism for that. However, the downside might be that
>>> more ParameterStatus messages would be sent, potentially adding overhead.
>>
>> I tried that, but simply marking it as GUC_REPORT does not reset the
>> variable when the transaction ends.
> 
> IIUC the problem is that we use GUC_ACTION_SET for those even though they
> are reset at transaction end by the routines in xact.c.  Something like the
> following seems to be enough to get it working as expected in some basic
> tests, but there are probably other things to consider.  Keep in mind that
> previous proposals to mark transaction_read_only as GUC_REPORT have been
> rejected, too.
> 
> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> index a82286cc98a..d0bbb5aff19 100644
> --- a/src/backend/utils/misc/guc.c
> +++ b/src/backend/utils/misc/guc.c
> @@ -3349,6 +3349,12 @@ set_config_with_handle(const char *name, config_handle *handle,
>      bool        prohibitValueChange = false;
>      bool        makeDefault;
> 
> +    if (action == GUC_ACTION_SET &&
> +        (strcmp(name, "transaction_isolation") == 0 ||
> +         strcmp(name, "transaction_read_only") == 0 ||
> +         strcmp(name, "transaction_deferrable") == 0))
> +        action = GUC_ACTION_LOCAL;
> +
>      if (elevel == 0)
>      {
>          if (source == PGC_S_DEFAULT || source == PGC_S_FILE)
> 

Considering the potential overhead of marking transaction_read_only as
GUC_REPORT, and the fact that this change has been rejected in the past,
I think simply calling SHOW transaction_read_only when needed would be a
more palatable approach -- at least a less invasive one.

To minimise overhead, the current implementation first checks the
session-level parameters (default_transaction_read_only and
in_hot_standby) via PQparameterStatus(). If both indicate "read/write"
mode, it then queries the server for the transaction-level
transaction_read_only setting. This means no extra queries are issued on
hot standby systems or sessions with default_transaction_read_only = on:

if (!hs || !ro)
  strlcpy(buf, _("unknown"), sizeof(buf));
else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
  strlcpy(buf, _("read-only"), sizeof(buf));
else
{
  const char *tr = NULL;
  PGresult   *res;

  res = PQexec(pset.db, "SHOW transaction_read_only");
  if (PQresultStatus(res) == PGRES_TUPLES_OK &&
  PQntuples(res) == 1)
  tr = PQgetvalue(res, 0, 0);

  if (!tr)
    strlcpy(buf, _("unknown"), sizeof(buf));
  else if (strcmp(tr, "on") == 0)
    strlcpy(buf, _("read-only"), sizeof(buf));
  else
    strlcpy(buf, _("read/write"), sizeof(buf));

  PQclear(res);
}

As pointed out by Chao Li, I marked "read/write" and "read-only" for
translation.

== test ==

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

postgres=# \set PROMPT1 '[%i] # '

[read-only] # SHOW in_hot_standby;
 in_hot_standby
----------------
 on
(1 row)

[read-only] # SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

[read/write] # SET default_transaction_read_only TO on;
SET
[read-only] # SET default_transaction_read_only TO off;
SET
[read/write] # BEGIN;
BEGIN
[read/write] # SET transaction_read_only TO on;
SET
[read-only] # END;
COMMIT
[read/write] #

Any thoughts on this approach?

v6 attached.

Best, Jim

  35. Re: display hot standby state in psql prompt

    Chao Li <li.evan.chao@gmail.com> — 2025-10-30T10:28:53Z

    
> On Oct 30, 2025, at 17:20, Jim Jones <jim.jones@uni-muenster.de> wrote:
> 
> Hi Chao
> 
> On 30/10/2025 08:51, Chao Li wrote:
>> I did a quick test, and found a problem. I shutdown the server, and “\c” reconnecting failed, but psql still show “read/write”, which seems wrong:
>> 
>> "read/write"\c
>> connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
>> Is the server running locally and accepting connections on that socket?
>> Previous connection kept
> 
> I can see that a "read/write" prompt in this case sounds misleading, but
> I am not entirely sure it is a problem. The message says "Previous
> connection kept", which might suggest that the previous parameter values
> are cached? See %p:
> 
> postgres=# \set PROMPT1 '[%p] # '
> [1268754] # \c
> connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
> or directory
>        Is the server running locally and accepting connections on that
> socket?
> Previous connection kept
> [1268754] #
> 

Okay, if you consider that is not a problem. How about this?

```
[read/write]select * from test_multi order by category, name;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
[]
[]
```

After shutting down the server, I ran a random statement in psql, then the prompt became empty, does it suppose to show “_unknown”?

Without setting PROMIT1, the prompt will become “!?>”.
```
evantest=# select * from test_multi order by category, name;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  36. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-30T10:50:50Z

    
On 30/10/2025 11:28, Chao Li wrote:
> How about this?
> 
> ```
> [read/write]select * from test_multi order by category, name;
> FATAL:  terminating connection due to administrator command
> server closed the connection unexpectedly
> 	This probably means the server terminated abnormally
> 	before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> The connection to the server was lost. Attempting reset: Failed.
> []
> []
> ```
> 
> After shutting down the server, I ran a random statement in psql, then the prompt became empty, does it suppose to show “_unknown”?

This behaviour is normal -- at least it is how other options behave. See
%p and the newly committed %S:

postgres=# \set PROMPT1 '[%p] # '
[1376487] # \c
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
or directory
        Is the server running locally and accepting connections on that
socket?
Previous connection kept
[1376487] # SELECT 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
[] # SELECT 1;
You are currently not connected to a database.
[] #

====

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

postgres=# \set PROMPT1 '[%S] # '
["$user", public] # \c
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
or directory
        Is the server running locally and accepting connections on that
socket?
Previous connection kept
["$user", public] # SELECT 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
[] # SELECT 1;
You are currently not connected to a database.
[] #


Best, Jim

  37. Re: display hot standby state in psql prompt

    Chao Li <li.evan.chao@gmail.com> — 2025-10-30T12:09:52Z

    
> On Oct 30, 2025, at 18:50, Jim Jones <jim.jones@uni-muenster.de> wrote:
> 
> 
> 
> On 30/10/2025 11:28, Chao Li wrote:
>> How about this?
>> 
>> ```
>> [read/write]select * from test_multi order by category, name;
>> FATAL:  terminating connection due to administrator command
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>> The connection to the server was lost. Attempting reset: Failed.
>> []
>> []
>> ```
>> 
>> After shutting down the server, I ran a random statement in psql, then the prompt became empty, does it suppose to show “_unknown”?
> 
> This behaviour is normal -- at least it is how other options behave. See
> %p and the newly committed %S:
> 
> postgres=# \set PROMPT1 '[%p] # '
> [1376487] # \c
> connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
> or directory
>        Is the server running locally and accepting connections on that
> socket?
> Previous connection kept
> [1376487] # SELECT 1;
> FATAL:  terminating connection due to administrator command
> server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> The connection to the server was lost. Attempting reset: Failed.
> [] # SELECT 1;
> You are currently not connected to a database.
> [] #
> 
> ====
> 
> psql (19devel)
> Type "help" for help.
> 
> postgres=# \set PROMPT1 '[%S] # '
> ["$user", public] # \c
> connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file
> or directory
>        Is the server running locally and accepting connections on that
> socket?
> Previous connection kept
> ["$user", public] # SELECT 1;
> FATAL:  terminating connection due to administrator command
> server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> The connection to the server was lost. Attempting reset: Failed.
> [] # SELECT 1;
> You are currently not connected to a database.
> [] #
> 

Thanks for the clarification. Though I still think that might be a bug, but I agree that doesn’t belong to the current patch.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  38. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-10-31T21:00:51Z

    
On 30/10/2025 11:16, Jim Jones wrote:
> if (!hs || !ro)
>   strlcpy(buf, _("unknown"), sizeof(buf));
> else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
>   strlcpy(buf, _("read-only"), sizeof(buf));
> else
> {
>   const char *tr = NULL;
>   PGresult   *res;
> 
>   res = PQexec(pset.db, "SHOW transaction_read_only");
>   if (PQresultStatus(res) == PGRES_TUPLES_OK &&
>   PQntuples(res) == 1)
>   tr = PQgetvalue(res, 0, 0);
> 
>   if (!tr)
>     strlcpy(buf, _("unknown"), sizeof(buf));
>   else if (strcmp(tr, "on") == 0)
>     strlcpy(buf, _("read-only"), sizeof(buf));
>   else
>     strlcpy(buf, _("read/write"), sizeof(buf));
> 
>   PQclear(res);
> }

While reviewing another patch, I had another idea to further minimise
the overhead of checking transaction_read_only, namely, to check it only
when within a transaction block:

if (!hs || !ro)
  strlcpy(buf, _("unknown"), sizeof(buf));
else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
  strlcpy(buf, _("read-only"), sizeof(buf));
else
{
  PGTransactionStatusType tstatus = PQtransactionStatus(pset.db);

  /*
   * Check transaction_read_only only when in a transaction
   * block.  When idle (not in a transaction), the value of
   * transaction_read_only is the same as
   * default_transaction_read_only, which we already checked
   * above.  Avoiding the query improves performance,
   * especially for prompt redisplays.
   */

  if (tstatus == PQTRANS_IDLE)
    strlcpy(buf, _("read/write"), sizeof(buf));
  else
  {
    /* In a transaction block, need to check transaction_read_only */
    const char *tr = NULL;
    PGresult *res;

    res = PQexec(pset.db, "SHOW transaction_read_only");
    if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1)
      tr = PQgetvalue(res, 0, 0);

    if (!tr)
      strlcpy(buf, _("unknown"), sizeof(buf));
    else if (strcmp(tr, "on") == 0)
      strlcpy(buf, _("read-only"), sizeof(buf));
    else
      strlcpy(buf, _("read/write"), sizeof(buf));

    PQclear(res);
  }
}

The idea is to skip the test if tstatus == PQTRANS_IDLE, which indicates
that it is not in a transaction block, making the check for
transaction_read_only unnecessary.

Thoughts on this approach?

v7 attached.

Best, Jim

  39. Re: display hot standby state in psql prompt

    Andreas Karlsson <andreas@proxel.se> — 2025-11-08T08:57:13Z

    On 10/31/25 10:00 PM, Jim Jones wrote:
> Thoughts on this approach?

I am not a fan at all of introducing running a query when calculating 
the prompt. Imagine if there are network issues or otherwise and in 
general I imagine that the prompt can send a query would be highly 
surprising to most users.

I personally lean towards that maybe we should keep it simple and go 
back to the origins of the patch which just tried to show if it is a 
standby or not given how complex it is to tell that we are in read-only 
or not. The alternative would be to update the protocol so the client if 
informed if a transaction is read-only or not but I do not think it 
would be possible to convince people to update the protocol just for 
psql. Maybe if some connection pooler would also be interest but I am 
not sure what they would need this for.

Andreas

  40. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-11-08T10:21:32Z

    Hi Andreas

On 08/11/2025 09:57, Andreas Karlsson wrote:
> I am not a fan at all of introducing running a query when calculating
> the prompt. Imagine if there are network issues or otherwise and in
> general I imagine that the prompt can send a query would be highly
> surprising to most users.


I was unable to find a less invasive way to make it work. Initially we
thought about setting transaction_read_only as GUC_REPORT, but we got
concerned about the implicit overhead.


> I personally lean towards that maybe we should keep it simple and go
> back to the origins of the patch which just tried to show if it is a
> standby or not given how complex it is to tell that we are in read-only
> or not. The alternative would be to update the protocol so the client if
> informed if a transaction is read-only or not but I do not think it
> would be possible to convince people to update the protocol just for
> psql. Maybe if some connection pooler would also be interest but I am
> not sure what they would need this for.


If we decide to not consider transaction_read_only for this feature, I
guess we should prevent the prompt to show anything if inside of a
transaction block. Simply ignoring it and showing a potentially wrong
status wouldn't be a good idea.

What do you guys think?

Thanks!

Best, Jim

  41. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-11-10T07:30:38Z

    On Sat, Nov 8, 2025 at 7:21 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
> > I personally lean towards that maybe we should keep it simple and go
> > back to the origins of the patch which just tried to show if it is a
> > standby or not given how complex it is to tell that we are in read-only
> > or not. The alternative would be to update the protocol so the client if
> > informed if a transaction is read-only or not but I do not think it
> > would be possible to convince people to update the protocol just for
> > psql. Maybe if some connection pooler would also be interest but I am
> > not sure what they would need this for.
>
>
> If we decide to not consider transaction_read_only for this feature, I
> guess we should prevent the prompt to show anything if inside of a
> transaction block. Simply ignoring it and showing a potentially wrong
> status wouldn't be a good idea.
>
> What do you guys think?

If the main goal of this feature is to help users easily determine
whether they're connected to a primary or a standby,
seems simply showing whether the server is in hot standby
should be sufficient. I'm not sure how useful it would be in practice
to show information based on default_transaction_read_only or
transaction_read_only.

Regards,

-- 
Fujii Masao

  42. Re: display hot standby state in psql prompt

    Jim Jones <jim.jones@uni-muenster.de> — 2025-11-10T08:14:49Z

    
On 10/11/2025 08:30, Fujii Masao wrote:
> If the main goal of this feature is to help users easily determine
> whether they're connected to a primary or a standby,
> seems simply showing whether the server is in hot standby
> should be sufficient. I'm not sure how useful it would be in practice
> to show information based on default_transaction_read_only or
> transaction_read_only.

This "primary" or "standby" approach was actually the initial proposal,
but it evolved after a few reviews. Extending it to use
transaction_read_only and default_transaction_read_only adds real value
to the feature, but doing so requires either:

1. Marking transaction_read_only as GUC_REPORT (controversial and
rejected in the past), or
2. Querying transaction_read_only with SHOW every time the prompt is
displayed within a transaction block (IMO minimal overhead -- but
unconventional?).

Although I lean toward the initial proposal, I can totally live with
either approach, since both would be able to distinguish if a server is
in hot standby or not.

Should we switch back to the initial proposal?

Thanks!

Best, Jim

  43. Re: display hot standby state in psql prompt

    Fujii Masao <masao.fujii@gmail.com> — 2025-11-12T03:48:43Z

    On Mon, Nov 10, 2025 at 5:14 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
>
>
>
> On 10/11/2025 08:30, Fujii Masao wrote:
> > If the main goal of this feature is to help users easily determine
> > whether they're connected to a primary or a standby,
> > seems simply showing whether the server is in hot standby
> > should be sufficient. I'm not sure how useful it would be in practice
> > to show information based on default_transaction_read_only or
> > transaction_read_only.
>
> This "primary" or "standby" approach was actually the initial proposal,
> but it evolved after a few reviews. Extending it to use
> transaction_read_only and default_transaction_read_only adds real value
> to the feature, but doing so requires either:
>
> 1. Marking transaction_read_only as GUC_REPORT (controversial and
> rejected in the past), or
> 2. Querying transaction_read_only with SHOW every time the prompt is
> displayed within a transaction block (IMO minimal overhead -- but
> unconventional?).
>
> Although I lean toward the initial proposal, I can totally live with
> either approach, since both would be able to distinguish if a server is
> in hot standby or not.
>
> Should we switch back to the initial proposal?

I'm fine with the initial proposal. I think showing whether the connected
server is a primary or standby in the prompt would be helpful when managing
multiple servers. OTOH, I'm not sure how useful it would be to display
whether the current transaction is read-only.

That said, this is just my view, so I'd like to hear what others think.

Regards,

-- 
Fujii Masao