Thread: [Patch] New psql prompt substitution %r (m = master, r = replica)
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. Usage example: ``` $ cat ~/.psqlrc \set PROMPT1 '%p (%r) =# ' $ psql -p 5432 psql (9.6beta4) Type "help" for help. 20638 (m) =# \q $ psql -p 5433 psql (9.6beta4) Type "help" for help. 20647 (r) =# \q ``` Currently I'm working on some functionality involving master-slave replication. Sometimes I accidentally connect to a master instead of replica and vice versa. Presence of functionality described above would make my work much more convenient. Hopefully there are other people in PostgreSQL community who feel that way. -- Best regards, Aleksander Alekseev
Attachment
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
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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
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 <david(at)fetter(dot)org> 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
On Fri, Aug 12, 2016 at 12:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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
+ },
+ &session_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
Re: [Patch] New psql prompt substitution %r (m = master, r = replica)
From
Aleksander Alekseev
Date:
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
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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: [Patch] New psql prompt substitution %r (m = master, r = replica)
From
Aleksander Alekseev
Date:
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
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 Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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