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
From 172d3c208fd37411a21768fdeefafc7f763baa22 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Fri, 31 Oct 2025 21:38:42 +0100
Subject: [PATCH v7] 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 or read/write
mode. It expands to "read-only" if any of the following are true:
- The server is in hot standby mode (in_hot_standby = on)
- The session's default transaction mode is read-only
(default_transaction_read_only = on)
- The current transaction is explicitly set to read-only via
SET TRANSACTION READ ONLY (transaction_read_only = on)
Otherwise, it displays "read/write".
To minimize overhead, the implementation checks session-level parameters
(in_hot_standby and default_transaction_read_only) first via
PQparameterStatus(). If both indicate read/write mode, it only queries
the server for transaction_read_only when inside a transaction block.
When idle (not in a transaction), transaction_read_only has the same
value as default_transaction_read_only, so the query can be skipped.
This optimization avoids extra server round-trips for the common case
of displaying the prompt between commands.
This is useful for distinguishing read-only sessions (e.g. connected
to a standby, using a default read-only transaction mode, or in an
explicit read-only transaction) 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 | 16 +++++++++++
src/bin/psql/prompt.c | 50 ++++++++++++++++++++++++++++++++++
2 files changed, 66 insertions(+)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 84683f62b1..93d4c11aa6 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -5055,6 +5055,22 @@ testdb=> <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 current transaction is read-only (<literal>transaction_read_only</literal>
+ is <literal>on</literal>), 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>); otherwise displays <literal>read-write</literal>.
+ Useful for identifying sessions that cannot perform writes, such as in
+ replication setups or when explicit read-only transactions are in use.
+ </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 59a2ceee07..a34c6baead 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -44,6 +44,8 @@
* or a ! if session is not connected to a database;
* in prompt2 -, *, ', or ";
* in prompt3 nothing
+ * %i - displays "read-only" if in hot standby, or if default_transaction_read_only
+ * or transaction_read_only are on, "read/write" otherwise.
* %x - transaction status: empty, *, !, ? (unknown or no connection)
* %l - The line number inside the current statement, starting from 1.
* %? - the error code of the last query (not yet implemented)
@@ -258,7 +260,55 @@ 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 || !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);
+ }
+ }
+ }
+ else
+ buf[0] = '\0';
+ break;
case 'x':
if (!pset.db)
buf[0] = '?';
--
2.43.0
