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
From 35f2a0c8971d31ec63b7752292b011b90b1ca004 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Thu, 30 Oct 2025 09:27:24 +0100
Subject: [PATCH v6] 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 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.
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 | 34 ++++++++++++++++++++++++++++++++++
2 files changed, 50 insertions(+)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1a339600bc..3b45514f34 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -5044,6 +5044,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 b08d7328fb..5d43d5bbcb 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -43,6 +43,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)
@@ -247,7 +249,39 @@ 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
+ {
+ 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
