Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Jim Nasby]

On 8/18/16 10:05 AM, Alvaro Herrera wrote:

Bash has PROMPT_COMMAND, which is executed prior to issuing each prompt.
What about introducing such a feature into psql?  Then the \gset command
you had in your first post could be used to set the variable correctly
just before each prompt.


As someone that has the backend PID in his prompt... +1. Or 
alternatively, a command that is just run when the connection changes 
instead of every command...

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Alvaro Herrera]

Aleksander Alekseev wrote:

> Peter has a good point that in general case it's more complicated than
> just master or replica. I also agree with David that what actually
> would be nice to have is a some syntax that allows user to execute
> arbitrary commands before displaying a prompt. This way user could
> display any information he or she wants, in any format. As we can see
> it's quite doable now but probably too complicated and ugly.
> 
> Still I don't think that many users have a problem discussed in this
> thread, i.e. updating a prompt on the fly. Probably introducing a new
> syntax (I personally don't even have a good idea how exactly it should
> look like), not mentioning implementing and supporting it, doesn't
> worth an effort. Maybe there is a simpler way.
> 
> Let's say we introduce a new variable EXPORT_CONNECTION_STRING.

Bash has PROMPT_COMMAND, which is executed prior to issuing each prompt.
What about introducing such a feature into psql?  Then the \gset command
you had in your first post could be used to set the variable correctly
just before each prompt.

One issue is what to do if psql is in an aborted transaction state
(since the query would fail to run at all); in that case I suppose it
should simply not run the command at all: the feature would be
documented to run the query only when the connection is in a known good
state.

There might be other corner cases to think about, but I think it's
enough to limit the feature to work when the query is reasonable -- I
mean, if the user enters something stupid (say, something that causes
errors to be raised or creates savepoints), it's not on psql to behave
sanely.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Aleksander Alekseev]

Hello, Alvaro.

> ... this is terrible.  Not only because it's a lot of code dependent
> on an external tool which will be rather heavyhanded for something
> that should probably be very lightweight, but also because the code
> itself is (no offense) an unreadable mess.
> 
> I think your general idea for the feature is a good one, even if the
> patch you submitted isn't a good implementation.  Not sure a real one
> is worth the effort, though.  I don't think Peter's comment that more
> roles than master/replica are possible is a showstopper: that's
> already going to be an issue for things like ALTER TRIGGER ENABLE
> REPLICA and the like, I suppose.

Thank you for sharing your thoughts on this!

Peter has a good point that in general case it's more complicated than
just master or replica. I also agree with David that what actually
would be nice to have is a some syntax that allows user to execute
arbitrary commands before displaying a prompt. This way user could
display any information he or she wants, in any format. As we can see
it's quite doable now but probably too complicated and ugly.

Still I don't think that many users have a problem discussed in this
thread, i.e. updating a prompt on the fly. Probably introducing a new
syntax (I personally don't even have a good idea how exactly it should
look like), not mentioning implementing and supporting it, doesn't
worth an effort. Maybe there is a simpler way.

Let's say we introduce a new variable EXPORT_CONNECTION_STRING. When
this variable is true, psql sets PSQL_CONNECTION_STRING environment
variable for every child process it creates. Naturally psql itself
checks this environment variable and if it's specified uses
corresponding connection string.

In this case user could extend prompt string quite easily like this:

```
\set EXPORT_CONNECTION_STRING true
\set PROMPT1 '%`psql ~/.psql_prompt_script`> =#'
```

Basically it does the same I did using Python and Bash aliases but in
much simpler and clean way.

What do you think about such approach?

-- 
Best regards,
Aleksander Alekseev


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Alvaro Herrera]

Aleksander Alekseev wrote:

> $ cat ~/.psqlrc 
> select (case when pg_is_in_recovery() then 'r' else 'm' end) as mor
> \gset
> 
> \set PROMPT1 '%p (%:mor:) =# '

Okay, this seems moderately reasonable, but ...

> Besides I figured out that replica promotion case could also be properly
> handled without any patches. In case anyone is interested here is a
> brief description of a solution.
> 
> ~/.bash_profile:

... this is terrible.  Not only because it's a lot of code dependent on
an external tool which will be rather heavyhanded for something that
should probably be very lightweight, but also because the code itself is
(no offense) an unreadable mess.

I think your general idea for the feature is a good one, even if the
patch you submitted isn't a good implementation.  Not sure a real one
is worth the effort, though.  I don't think Peter's comment that more
roles than master/replica are possible is a showstopper: that's already
going to be an issue for things like ALTER TRIGGER ENABLE REPLICA and
the like, I suppose.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Aleksander Alekseev]

Thank you everyone for your replies!

I did some research and apparently the is no need in any patch. As
David pointed out what I did could be easily done using \gset:

```
$ cat ~/.psqlrc 
select (case when pg_is_in_recovery() then 'r' else 'm' end) as mor
\gset

\set PROMPT1 '%p (%:mor:) =# '
```

Besides I figured out that replica promotion case could also be properly
handled without any patches. In case anyone is interested here is a
brief description of a solution.

~/.bash_profile:

```
export PATH="/home/eax/bin:$PATH"
alias psql='psql_wrapper'
```

~/bin/psql_wrapper:

```
#!/usr/bin/env python3

import subprocess
import sys

arg_string = ""
idx = 1
maxidx = len(sys.argv) - 1

while idx <= maxidx:
  arg_string += "'" + sys.argv[idx] + "' "
  idx += 1

cmd = """USER_ARGS=$'{}' psql {}""".format(
  arg_string.replace("'","\\'"), arg_string)

subprocess.call(cmd, shell = True)
```

~/.psqlrc:

```
\set PROMPT1 '%p <%`sh -c "psql $USER_ARGS -A -t -c $\'select case when 
pg_is_in_recovery() then \\\'replica\\\' else \\\'master\\\' end\'"`> =# '
```

-- 
Best regards,
Aleksander Alekseev


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Michael Paquier]

On Fri, Aug 12, 2016 at 12:51 AM, Tom Lane  wrote:
> Peter Eisentraut  writes:
>> On 8/11/16 6:04 AM, Aleksander Alekseev wrote:
>>> Suggested patch introduces an %r substitution in psql's prompt. This
>>> substitution allows to display whether user is connected to master or
>>> replica right in a prompt.
>
>> In the near future, there will (probably) be a lot more variants about
>> what it means to be a master or a replica.  There will be logical
>> replication, where you could be a publisher of something and a consumer
>> of something else.  You could even be a logical consumer but a physical
>> master.  So a global binary facility is probably not very forward
>> looking and will lead to confusion.
>
> Also, the patch as given is broken since it fails to account for the
> server being promoted while a psql session is open.

 {
+{"is_master", PGC_INTERNAL, UNGROUPED,
+gettext_noop("Shows whether the current instance is
master or replica."),
+NULL,
+GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+},
+_is_master,
+false,
+NULL, NULL, NULL
+},
Having a GUC for that purpose is not a fruitful approach. And it seems
to me that this patch is dead-in-the water because this makes prompt
parsing dependent on a status only known by the server, which would
require in the worst case to issue an SQL based on for example
pg_is_in_recovery() in get_prompt(): all the other fields using libpq
routines fetch values defined when the connection is established, like
the session PID or the database.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[David Fetter]

On Thu, Aug 11, 2016 at 01:04:19PM +0300, Aleksander Alekseev wrote:
> Hello.
> 
> Suggested patch introduces an %r substitution in psql's prompt. This
> substitution allows to display whether user is connected to master or
> replica right in a prompt.

This is a neat idea, but there are some issues.

- There's a new GUC.  This is probably not compelling enough a feature for that.

- The check, if I understand correctly, is only done on connect, even though it
  could change during a session.

How about something that:

- Allowed setting a query to be executed after each command.

- Put the results of that query into a psql variable.

This differs from \gset in that it would be executed silently at the
end of each command.

What say?

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Tom Lane]

Peter Eisentraut  writes:
> On 8/11/16 6:04 AM, Aleksander Alekseev wrote:
>> Suggested patch introduces an %r substitution in psql's prompt. This
>> substitution allows to display whether user is connected to master or
>> replica right in a prompt.

> In the near future, there will (probably) be a lot more variants about
> what it means to be a master or a replica.  There will be logical
> replication, where you could be a publisher of something and a consumer
> of something else.  You could even be a logical consumer but a physical
> master.  So a global binary facility is probably not very forward
> looking and will lead to confusion.

Also, the patch as given is broken since it fails to account for the
server being promoted while a psql session is open.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

[Peter Eisentraut]

On 8/11/16 6:04 AM, Aleksander Alekseev wrote:
> Suggested patch introduces an %r substitution in psql's prompt. This
> substitution allows to display whether user is connected to master or
> replica right in a prompt.

In the near future, there will (probably) be a lot more variants about
what it means to be a master or a replica.  There will be logical
replication, where you could be a publisher of something and a consumer
of something else.  You could even be a logical consumer but a physical
master.  So a global binary facility is probably not very forward
looking and will lead to confusion.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers