Add %r substitution for psql prompts to show recovery status
Hi
Attached patch adds an "%r" substitution for psql prompts to show
recovery status. Specifically it displays an "&" (ampersand) if
the server is in recovery, otherwise nothing, e.g.:
postgres=&# SELECT foo;
Why is this useful? Because I find myself messing about with replication
clusters a lot, and it would be nice to have an at-a-glance confirmation
whether I'm connected to a standby or not.
Why an ampersand? Because it's not used for any other prompts, and
it can be used as a mnemonic: "and" -> "st'and'by" (clutching at
straws a bit there I admit, but best I could come up with).
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.
A possible alternative would be only to check the status each time a new
database connection is made, but that wouldn't catch the case where the
server has been promoted.
Will submit to next commitfest.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
psql-recovery-status-prompt.patchtext/x-patch; name=psql-recovery-status-prompt.patchDownload
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index fce7e3a..dfef268 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4104,6 +4104,17 @@ testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput>
</varlistentry>
<varlistentry>
+ <term><literal>%r</literal></term>
+ <listitem>
+ <para>
+ Recovery status: & ("st<emphasis>and</emphasis>by") if in recovery, otherwise empty.
+ Note this prompt substitution sends a <literal><function>pg_is_in_recovery()</function></literal>
+ query to the server each time it is encountered.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
<listitem>
<para>
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 913b23e..90586eb 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -22,6 +22,7 @@
#include "prompt.h"
#include "settings.h"
+static bool is_in_recovery(void);
/*--------------------------
* get_prompt
@@ -46,6 +47,7 @@
* in prompt3 nothing
* %x - transaction status: empty, *, !, ? (unknown or no connection)
* %l - The line number inside the current statement, starting from 1.
+ * %r - recovery status: & ("st'and'by") if in recovery, otherwise empty
* %? - the error code of the last query (not yet implemented)
* %% - a percent sign
*
@@ -313,6 +315,11 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
#endif /* USE_READLINE */
break;
+ case 'r':
+ if (is_in_recovery())
+ buf[0] = '&';
+ break;
+
default:
buf[0] = *p;
buf[1] = '\0';
@@ -336,3 +343,26 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
return destination;
}
+
+
+bool
+is_in_recovery(void)
+{
+ PGresult *res;
+ bool server_is_in_recovery = false;
+
+ if (!pset.db)
+ return false;
+
+ res = PSQLexec("SELECT pg_catalog.pg_is_in_recovery()");
+
+ if (!res)
+ return false;
+
+ if (strcmp(PQgetvalue(res, 0, 0), "t") == 0)
+ server_is_in_recovery = true;
+
+ PQclear(res);
+
+ return server_is_in_recovery;
+}
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.
That seems kinda painful.
And what happens in an aborted transaction?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Dec 6, 2017 at 7:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com>
wrote:
A possible alternative would be only to check the status each time a new
database connection is made, but that wouldn't catch the case where the
server has been promoted.
Can we cache a false pg_is_in_recovery response and return that instead of
querying the server?
David J.
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.That seems kinda painful.
And what happens in an aborted transaction?
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
On Fri, Dec 8, 2017 at 3:54 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com>
wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.That seems kinda painful.
And what happens in an aborted transaction?
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.
If we would use parameter status messages, then can we handle compatibility
correctly? So that new psql can work with old backend without errors (and
without recovery status display, for sure), and old psql can work with new
backend without errors.
Because seeing recovery status in psql prompt is very neat. But execution
of extra query each time doesn't seem like reasonable price for it...
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.If we would use parameter status messages, then can we handle compatibility
correctly? So that new psql can work with old backend without errors (and
without recovery status display, for sure),
I suppose so.
and old psql can work with new
backend without errors.
It should work because any frontend program should ignore parameter
status messages they do not know, according to the docs.
Because seeing recovery status in psql prompt is very neat. But execution
of extra query each time doesn't seem like reasonable price for it...
I think so.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
On 12/7/17 19:54, Tatsuo Ishii wrote:
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.That seems kinda painful.
And what happens in an aborted transaction?
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.
I agree a backend status message is the right way to do this.
We could perhaps report transaction_read_only, if we don't want to add a
new one.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 1/9/18 15:36, Peter Eisentraut wrote:
On 12/7/17 19:54, Tatsuo Ishii wrote:
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.That seems kinda painful.
And what happens in an aborted transaction?
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.I agree a backend status message is the right way to do this.
We could perhaps report transaction_read_only, if we don't want to add a
new one.
I'm setting this CF item to returned with feedback, since it would
apparently be a much bigger change than then initial patch.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 01/23/2018 03:19 AM, Peter Eisentraut wrote:
On 1/9/18 15:36, Peter Eisentraut wrote:
On 12/7/17 19:54, Tatsuo Ishii wrote:
On Wed, Dec 6, 2017 at 9:19 PM, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
Note this substitution sends a "pg_is_in_recovery()" query to the server
each time it's encountered; unless there's something I'm overlooking I
think that's the only reliable way to determine current recovery status.That seems kinda painful.
And what happens in an aborted transaction?
Yeah. I think we need some from help backend for this. For example, a
parameter status message can be used here. If PostgreSQL moves to the
recovery state or vice versa, PostgreSQL sends the parameter status
message to frontend.I agree a backend status message is the right way to do this.
We could perhaps report transaction_read_only, if we don't want to add a
new one.I'm setting this CF item to returned with feedback, since it would
apparently be a much bigger change than then initial patch.
Yup, agree :)
Thanks for the feedback!
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services