Thread: 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

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 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

>> 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