Thread: Feature request: make cluster_name GUC useful for psql prompts

Feature request: make cluster_name GUC useful for psql prompts

From
Steve Crawford
Date:
It's great that 9.5 has the new cluster_name variable as an available GUC.

It would be even better to make that GUC available for use in psql prompting escape sequences.

Prompting via sequences utilizing %M, %m and %> means the same cluster could be identified numerous ways (local, 127.0.0.1, 10.1.2.3, localhost, myserver.example.com, myserver, etc.) which is further exacerbated when pooling or port-forwarding is in play.

In the inverse case, when logging into a multiple servers and running psql, all the prompts might just say "local" despite all being different clusters.

Adding an escape sequence that references cluster_name would enable prompts to identify the cluster in a manner that is both consistent and distinct regardless of access path.

Potential issues/improvements:

What should the escape-sequence display if cluster_name is not set or the cluster is a pre-9.5 version. %M? %m?

In future server versions should there be a default for cluster_name if it is not set? If so, what should it be? Would the server canonical hostname + listen-port be reasonable?

Cheers,
Steve

Re: Feature request: make cluster_name GUC useful for psql prompts

From
Peter Eisentraut
Date:
On 5/5/16 9:21 PM, Steve Crawford wrote:
> Adding an escape sequence that references cluster_name would enable
> prompts to identify the cluster in a manner that is both consistent and
> distinct regardless of access path.

I think that would be a good idea.  You could probably design it so that 
any server parameter reported to the client can be put in a psql prompt.

> Potential issues/improvements:
>
> What should the escape-sequence display if cluster_name is not set or
> the cluster is a pre-9.5 version. %M? %m?
>
> In future server versions should there be a default for cluster_name if
> it is not set? If so, what should it be? Would the server canonical
> hostname + listen-port be reasonable?

Those are good questions.  I don't really like the proposed answers, 
because that could cause confusion in practical use.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Feature request: make cluster_name GUC useful for psql prompts

From
Jerry Sievers
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

> On 5/5/16 9:21 PM, Steve Crawford wrote:
>
>> Adding an escape sequence that references cluster_name would enable
>> prompts to identify the cluster in a manner that is both consistent and
>> distinct regardless of access path.
>
> I think that would be a good idea.  You could probably design it so
> that any server parameter reported to the client can be put in a psql
> prompt.

The OP can easily work around that lack of support with something such as follow...

Add this to ~/.psqlrc[-optional version stuff]

select setting as cluster_name from pg_settings where name = 'cluster_name'  -- do not simicolon terminate this line
\gset

\set PROMPT1 :cluster_name ': how cool is this:'

>
>> Potential issues/improvements:
>>
>> What should the escape-sequence display if cluster_name is not set or
>> the cluster is a pre-9.5 version. %M? %m?
>>
>> In future server versions should there be a default for cluster_name if
>> it is not set? If so, what should it be? Would the server canonical
>> hostname + listen-port be reasonable?
>
> Those are good questions.  I don't really like the proposed answers,
> because that could cause confusion in practical use.
>
> -- 
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800



Re: Feature request: make cluster_name GUC useful for psql prompts

From
Steve Crawford
Date:
That is almost identical to the solution I suggested a week or two ago to someone tackling the issue and the hack works on initial connection.

Connect to a different cluster with "\c", however, and it will leave the prompt showing you connected to the original database which is not good.

Cheers,
Steve

On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

> On 5/5/16 9:21 PM, Steve Crawford wrote:
>
>> Adding an escape sequence that references cluster_name would enable
>> prompts to identify the cluster in a manner that is both consistent and
>> distinct regardless of access path.
>
> I think that would be a good idea.  You could probably design it so
> that any server parameter reported to the client can be put in a psql
> prompt.

The OP can easily work around that lack of support with something such as follow...

Add this to ~/.psqlrc[-optional version stuff]

select setting as cluster_name from pg_settings where name = 'cluster_name'  -- do not simicolon terminate this line
\gset

\set PROMPT1 :cluster_name ': how cool is this:'

>
>> Potential issues/improvements:
>>
>> What should the escape-sequence display if cluster_name is not set or
>> the cluster is a pre-9.5 version. %M? %m?
>>
>> In future server versions should there be a default for cluster_name if
>> it is not set? If so, what should it be? Would the server canonical
>> hostname + listen-port be reasonable?
>
> Those are good questions.  I don't really like the proposed answers,
> because that could cause confusion in practical use.
>
> --
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Re: Feature request: make cluster_name GUC useful for psql prompts

From
Jerry Sievers
Date:
Steve Crawford <scrawford@pinpointresearch.com> writes:

> That is almost identical to the solution I suggested a week or two ago to someone tackling the issue and the hack
workson initial connection.
 
>
> Connect to a different cluster with "\c", however, and it will leave the prompt showing you connected to the original
databasewhich is not good.
 

True and I've always thought of it as a possible misfeature of psql that
it scans .psqlrc only once.

> Cheers,
> Steve
>
> On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
>
>     Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>    
>     > On 5/5/16 9:21 PM, Steve Crawford wrote:
>     >
>     >> Adding an escape sequence that references cluster_name would enable
>     >> prompts to identify the cluster in a manner that is both consistent and
>     >> distinct regardless of access path.
>     >
>     > I think that would be a good idea.  You could probably design it so
>     > that any server parameter reported to the client can be put in a psql
>     > prompt.
>    
>     The OP can easily work around that lack of support with something such as follow...
>    
>     Add this to ~/.psqlrc[-optional version stuff]
>    
>     select setting as cluster_name from pg_settings where name = 'cluster_name'  -- do not simicolon terminate this
line
>     \gset
>    
>     \set PROMPT1 :cluster_name ': how cool is this:'
>    
>     >
>     >> Potential issues/improvements:
>     >>
>     >> What should the escape-sequence display if cluster_name is not set or
>     >> the cluster is a pre-9.5 version. %M? %m?
>     >>
>     >> In future server versions should there be a default for cluster_name if
>     >> it is not set? If so, what should it be? Would the server canonical
>     >> hostname + listen-port be reasonable?
>     >
>     > Those are good questions.  I don't really like the proposed answers,
>     > because that could cause confusion in practical use.
>     >
>     > --
>     > Peter Eisentraut              http://www.2ndQuadrant.com/
>     > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>    
>     --
>     Jerry Sievers
>     Postgres DBA/Development Consulting
>     e: postgres.consulting@comcast.net
>     p: 312.241.7800
>

-- 
Jerry Sievers
e: jerry.sievers@comcast.net
p: 312.241.7800



Re: Feature request: make cluster_name GUC useful for psql prompts

From
Alvaro Herrera
Date:
Jerry Sievers wrote:
> Steve Crawford <scrawford@pinpointresearch.com> writes:
> 
> > That is almost identical to the solution I suggested a week or two ago to someone tackling the issue and the hack
workson initial connection.
 
> >
> > Connect to a different cluster with "\c", however, and it will leave the prompt showing you connected to the
originaldatabase which is not good.
 
> 
> True and I've always thought of it as a possible misfeature of psql that
> it scans .psqlrc only once.

Interesting point.  I think what you're saying boils down to there being
two init files, one that is read once at program start (sets up the
general environment) and another one that's executed each time a
connection is established.

I wonder where does this leave Greg Stark's concurrent psql sessions
feature.

Now, for the current case I think we should definitely have a specifier
for the prompt.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Feature request: make cluster_name GUC useful for psql prompts

From
Steve Crawford
Date:
Although this is getting slightly off the original topic, rereading .psqlrc is a potential can of worms. What triggers a reread? What portions of .psqlrc are re-read?

For example, say I have just set tuples-only, extended-display, or output file. Would they all get reset just because I changed connections?

You can use variables to approximate the behavior of aliases so you can hack an alias that includes the reconnect and re-read. Or just \i ~/.psqlrc as you deem necessary.

Cheers,
Steve




On Fri, May 6, 2016 at 12:50 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
Steve Crawford <scrawford@pinpointresearch.com> writes:

> That is almost identical to the solution I suggested a week or two ago to someone tackling the issue and the hack works on initial connection.
>
> Connect to a different cluster with "\c", however, and it will leave the prompt showing you connected to the original database which is not good.

True and I've always thought of it as a possible misfeature of psql that
it scans .psqlrc only once.

> Cheers,
> Steve
>
> On Fri, May 6, 2016 at 11:42 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
>
>     Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>
>     > On 5/5/16 9:21 PM, Steve Crawford wrote:
>     >
>     >> Adding an escape sequence that references cluster_name would enable
>     >> prompts to identify the cluster in a manner that is both consistent and
>     >> distinct regardless of access path.
>     >
>     > I think that would be a good idea.  You could probably design it so
>     > that any server parameter reported to the client can be put in a psql
>     > prompt.
>
>     The OP can easily work around that lack of support with something such as follow...
>
>     Add this to ~/.psqlrc[-optional version stuff]
>
>     select setting as cluster_name from pg_settings where name = 'cluster_name'  -- do not simicolon terminate this line
>     \gset
>
>     \set PROMPT1 :cluster_name ': how cool is this:'
>
>     >
>     >> Potential issues/improvements:
>     >>
>     >> What should the escape-sequence display if cluster_name is not set or
>     >> the cluster is a pre-9.5 version. %M? %m?
>     >>
>     >> In future server versions should there be a default for cluster_name if
>     >> it is not set? If so, what should it be? Would the server canonical
>     >> hostname + listen-port be reasonable?
>     >
>     > Those are good questions.  I don't really like the proposed answers,
>     > because that could cause confusion in practical use.
>     >
>     > --
>     > Peter Eisentraut              http://www.2ndQuadrant.com/
>     > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>     --
>     Jerry Sievers
>     Postgres DBA/Development Consulting
>     e: postgres.consulting@comcast.net
>     p: 312.241.7800
>

--
Jerry Sievers
e: jerry.sievers@comcast.net
p: 312.241.7800