On 26.06.25 02:52, Greg Sabino Mullane wrote:
> On Wed, Jun 25, 2025 at 11:50 AM Jim Jones <jim.jo...@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, JimFrom 4e90176efda343c8ca8ced7ddb0b0b98ce4e016b Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Thu, 26 Jun 2025 09:07:27 +0200
Subject: [PATCH v1] Add %i prompt escape to indicate server read-only status
This patch introduces a new prompt escape `%i` for psql, which shows
whether the connected server is operating in read-only mode. It
expands to `read-only` if either the server is in hot standby mode
(`in_hot_standby = on`) or the session's default transaction mode is
read-only (`default_transaction_read_only = on`). Otherwise, it
displays `read/write`.
This is useful for distinguishing read-only sessions (e.g. connected
to a standby, or using a default read-only transaction mode) from
read/write ones at a glance, especially when working with multiple
connections in replicated or restricted environments.
---
doc/src/sgml/ref/psql-ref.sgml | 15 +++++++++++++++
src/bin/psql/prompt.c | 14 ++++++++++++++
2 files changed, 29 insertions(+)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 95f4cac246..ff853bb18f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4999,6 +4999,21 @@ testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-prompting-i">
+ <term><literal>%i</literal></term>
+ <listitem>
+ <para>
+ Indicates whether the current session is read-only. The value is
+ shown as <literal>read-only</literal> if the server reports either
+ <literal>in_hot_standby</literal> or
+ <literal>default_transaction_read_only</literal> as <literal>on</literal>,
+ and <literal>read/write</literal> otherwise.
+ This prompt escape can be used to quickly identify sessions that
+ cannot perform write operations.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-prompting-x">
<term><literal>%x</literal></term>
<listitem>
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 3aa7d2d06c..61e02f63e7 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -243,7 +243,21 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
break;
}
break;
+ 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;
case 'x':
if (!pset.db)
buf[0] = '?';
--
2.43.0
