Re: display hot standby state in psql prompt

[Jim Jones]

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/
From d428f71ab5c7f8dea5d5446223f29c203d41dfec Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Tue, 22 Jul 2025 11:30:22 +0200
Subject: [PATCH v2] 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 | 14 ++++++++++++++
 src/bin/psql/prompt.c          | 14 ++++++++++++++
 2 files changed, 28 insertions(+)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 4f7b11175c..c8e1449766 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -5008,6 +5008,20 @@ testdb=&gt; <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
         </listitem>
       </varlistentry>
 
+      <varlistentry id="app-psql-prompting-i">
+        <term><literal>%i</literal></term>
+        <listitem>
+          <para>
+            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.
+          </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 b08d7328fb..7ac085060e 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -247,7 +247,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