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
From 641db33a494cd9c9c1b484e0393f0c86a9a79ffb Mon Sep 17 00:00:00 2001 From: Jim Jones <[email protected]> Date: Fri, 24 Oct 2025 22:09:34 +0200 Subject: [PATCH v4] 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 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 | 20 +++++++++++++++++++- 2 files changed, 33 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 1a339600bc..662dfb2ed5 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -5044,6 +5044,20 @@ 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 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..06774aa53f 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 default_transaction_read_only + * is 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,23 @@ 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 ((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
