psql possible TODO
Hello,
A reasonable simple feature we were thinking about sponsoring was an
addition to psql. Currently if you perform a \s within psql it gives
you the query buffer. My suggestion is that \s give us a execution
number, similar to bash. Thus at a psql prompt we could do:
foo=> !76
And it would execute whatever query was number 76. Secondly:
foo=> !!76
Would execute an explain on whatever query was 76. Lastly:
foo=> !@76
Would execute an explain analyze within a transaction and a rollback (so
we could do updates/deletes).
I am of course open to other thoughts but it certainly seems like a nice
shortcut.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
"Joshua D. Drake" <jd@commandprompt.com> writes:
Hello,
A reasonable simple feature we were thinking about sponsoring was an
addition to psql. Currently if you perform a \s within psql it gives
you the query buffer. My suggestion is that \s give us a execution
number, similar to bash. Thus at a psql prompt we could do:
foo=> !76
And it would execute whatever query was number 76. Secondly:
foo=> !!76
Would execute an explain on whatever query was 76. Lastly:
foo=> !@76
Would execute an explain analyze within a transaction and a rollback (so
we could do updates/deletes).
Surely you must use backslash commands for this. Or had you forgotten
that ! and @ are legal operator characters in Postgres?
Also, you can't take away the existing functionality of \s. Invent some
other command instead.
But lastly, do we need this at all? It seems like a relatively awkward,
highly error-prone way to do what you can do today with control-P and
re-execute.
regards, tom lane
Would execute an explain analyze within a transaction and a rollback (so
we could do updates/deletes).Surely you must use backslash commands for this. Or had you forgotten
that ! and @ are legal operator characters in Postgres?
Well I didn't forget, but I assumed we could use the psql parser to
handle the \^\*\![0-9] (that is likely not the correct regex).
Also, you can't take away the existing functionality of \s. Invent some
other command instead.
I wouldn't assume that we would be taking it away. The only difference
is that at \^ there would be a number. I am not sure how that effects
anyone using \s anyway since \s picks up all errors etc...
But lastly, do we need this at all? It seems like a relatively awkward,
highly error-prone way to do what you can do today with control-P and
re-execute.
If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:
=> \s
76 SELECT * FROM Foo;
=> !76
Just like bash. I certainly don't have a problem with uses different
characters or the like and am open to any suggestion thereof. The reason
I picked ! was because it is what bash uses. The reason I picked !@ is
because they are right next to each other.
Sincerely,
Joshua D. Drake
regards, tom lane
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
"Joshua D. Drake" <jd@commandprompt.com> writes:
But lastly, do we need this at all? It seems like a relatively awkward,
highly error-prone way to do what you can do today with control-P and
re-execute.
If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:
No, I mean control-P as in "recall the previous entry". What you showed
takes *more* work to recall a recent entry than hitting control-P a few
times. It has vastly greater chance of error, too, ie, executing the
wrong command for lack of any feedback about what it is you're really
about to execute. I will grant that control-P isn't a good way to
recall an entry from dozens or hundreds of commands ago, but that's what
control-R is for; and I don't see that a command number would help any
for that anyway.
Just like bash.
What we have is just like bash --- at least the parts of it that I use.
regards, tom lane
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
But lastly, do we need this at all? It seems like a relatively awkward,
highly error-prone way to do what you can do today with control-P and
re-execute.If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:No, I mean control-P as in "recall the previous entry". What you showed
takes *more* work to recall a recent entry than hitting control-P a few
times. It has vastly greater chance of error, too, ie, executing the
wrong command for lack of any feedback about what it is you're really
about to execute. I will grant that control-P isn't a good way to
recall an entry from dozens or hundreds of commands ago, but that's what
control-R is for; and I don't see that a command number would help any
for that anyway.Just like bash.
What we have is just like bash --- at least the parts of it that I use.
This reminds me of the old csh function where you could recall things by
numbers and do search/replace on them. The capability was so hard to
use I could imagine anyone _normal_ using it.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Tue, Dec 05, 2006 at 01:13:04PM -0800, Joshua D. Drake wrote:
If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:=> \s
76 SELECT * FROM Foo;
=> !76
+1. I often find myself having to edit a pasted command just to add in
either explain or explain analyze when I'm doing performance tuning.
Granted, this should be easier in 8.2 with the improved buffer/up-arrow
behavior, but it's still not as handy as doing something like !42.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim C. Nasby wrote:
On Tue, Dec 05, 2006 at 01:13:04PM -0800, Joshua D. Drake wrote:
If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:=> \s
76 SELECT * FROM Foo;
=> !76+1. I often find myself having to edit a pasted command just to add in
either explain or explain analyze when I'm doing performance tuning.
Granted, this should be easier in 8.2 with the improved buffer/up-arrow
behavior, but it's still not as handy as doing something like !42.
Hmm, what is NOT the answer to the ultimate question of Life, the
Universe and Everything? Doesn't make much sense to me ...
+1 on the !<num>, but I agree with Tom that the ! had better be a \
command (and \! is already taken).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
No, I mean control-P as in "recall the previous entry". What you showed
takes *more* work to recall a recent entry than hitting control-P a few
times. It has vastly greater chance of error, too, ie, executing the
wrong command for lack of any feedback about what it is you're really
about to execute. I will grant that control-P isn't a good way to
recall an entry from dozens or hundreds of commands ago, but that's what
control-R is for; and I don't see that a command number would help any
for that anyway.
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.
Just like bash.
What we have is just like bash --- at least the parts of it that I use.
Yes, and I am trying to add more functionality that is like bash :)
Sincerely,
Joshua D. Drake
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Tue, Dec 05, 2006 at 06:55:04PM -0300, Alvaro Herrera wrote:
+1 on the !<num>, but I agree with Tom that the ! had better be a \
command (and \! is already taken).
Extend \r maybe, so \r on it's own clears the buffer whereas \r ###
will load it with history entry ###. Mnemonic; r=retrieve command
Silly: You could even do \r xyz and load the buffer with the last line
beginning xyz
(this would parallel the "r" command in Korn Shell which does
history in this way; "r 10" would rerun history entry 10; "r ls" would
redo the last ls command).
--
rgds
Stephen
Just like bash.
What we have is just like bash --- at least the parts of it that I use.
This reminds me of the old csh function where you could recall things by
numbers and do search/replace on them. The capability was so hard to
use I could imagine anyone _normal_ using it.
Except that this has nothing to do with that. This is *standard* feature
set for the unix shell:
794 scp admin@compile.commandprompt.com:pgdump* .
795 scp admin@compile.commandprompt.com:*bench* .
796 joe pgbench.results
797 ps a-x
798 ps -ax
799 bin/psql -U jd -p5500 bench
800 bin/psql -U jd -p5501 bench
801 bin/pg_ctl -D slave0 stop
802 bin/pg_ctl -D slave1 stop
803 joe bin/starttest.sh
804 history
805 joe bin/starttest.sh
806 history
Oh... that's right, its call starttest.sh:
!805
Except for PostgreSQL it would be:
postgres=# \s
1 SELECT * from cmd_pg_stat_all_tables ;
2 SELECT count(*) from cmd_pg_stat_all_tables ;
3 SELECT count(*) from cmd_pg_stat_database;
postgres=# !1
Otherwise I either have to copy and paste what is 1 or I have to do a
ctrl-r. So now i have done a \s to see what queries are in the buffer
and then I am doing a ctrl-r to get the info out of the buffer. That's
silly. When if there was a number associated, I could just execute
whatever I need.
I am regularly in 100 different catalogs a week, I can't always remember
what is going on from one to the other. I use \s all the time to help
with this. Eliminating the need for a mouse or to cycle through ctrl-r
would make this alot easier.
Don't get me wrong, ctrl-r is great, but it is not an end all :)
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Tue, 2006-12-05 at 18:55 -0300, Alvaro Herrera wrote:
Jim C. Nasby wrote:
On Tue, Dec 05, 2006 at 01:13:04PM -0800, Joshua D. Drake wrote:
If you mean control-P as in "paste" then I would say... get your hands
off the mouse. The mouse is counter productive and it is faster to do
this:=> \s
76 SELECT * FROM Foo;
=> !76+1. I often find myself having to edit a pasted command just to add in
either explain or explain analyze when I'm doing performance tuning.
Granted, this should be easier in 8.2 with the improved buffer/up-arrow
behavior, but it's still not as handy as doing something like !42.Hmm, what is NOT the answer to the ultimate question of Life, the
Universe and Everything? Doesn't make much sense to me ...+1 on the !<num>, but I agree with Tom that the ! had better be a \
command (and \! is already taken).
Yeah I am good with changing the !.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.
... but, somehow, you know exactly what command number it has?
The above seems completely unimpressive as arguments for adding
command-number-based recall.
What we have is just like bash --- at least the parts of it that I use.
Yes, and I am trying to add more functionality that is like bash :)
It's like a part of bash that's been obsolete since readline was
written, and probably would never have existed at all if readline had
appeared first. I can't really picture a situation in which this is an
improvement over the readline facilities.
regards, tom lane
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.... but, somehow, you know exactly what command number it has?
The above seems completely unimpressive as arguments for adding
command-number-based recall.What we have is just like bash --- at least the parts of it that I use.
Yes, and I am trying to add more functionality that is like bash :)
It's like a part of bash that's been obsolete since readline was
written, and probably would never have existed at all if readline had
appeared first. I can't really picture a situation in which this is an
improvement over the readline facilities.
Yea, that was my impression. It seemed so awkward, but so is 'vi' to
me. (Me ducks.)
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Stephen Harris <lists@spuddy.org> writes:
Silly: You could even do \r xyz and load the buffer with the last line
beginning xyz
We've got that: control-R xyz.
The thing I'm having a hard time with here is the notion that command
number is a convenient way of identifying prior commands. It seems like
an idea that should have gone out with punched cards.
regards, tom lane
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.... but, somehow, you know exactly what command number it has?
Well, presumably \s would give you the numbers. "history" does on bash anyway.
I use it on bash all the time: I do "history | grep something" and then
!<number of command I want>.
I don't think we can do the "| grep" part, but it's useful anyway.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, 2006-12-05 at 17:30 -0500, Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.... but, somehow, you know exactly what command number it has?
I would after I did a \s yes.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
It's like a part of bash that's been obsolete since readline was
written, and probably would never have existed at all if readline had
appeared first. I can't really picture a situation in which this is an
improvement over the readline facilities.Yea, that was my impression. It seemed so awkward, but so is 'vi' to
me. (Me ducks.)
Well you won't get any argument from me about vi ;) but as others have
already mentioned, it is useful to some.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Alvaro Herrera wrote:
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.... but, somehow, you know exactly what command number it has?
Well, presumably \s would give you the numbers. "history" does on bash anyway.
I use it on bash all the time: I do "history | grep something" and then
!<number of command I want>.I don't think we can do the "| grep" part, but it's useful anyway.
OK, now at least I understand how it would be used, and could be
explained easily in the documentation --- do \s, then \! 99, or maybe \#
99. I don't like making \! do shells and pull SQL commands from history.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Tue, 2006-12-05 at 17:34 -0500, Tom Lane wrote:
Stephen Harris <lists@spuddy.org> writes:
Silly: You could even do \r xyz and load the buffer with the last line
beginning xyzWe've got that: control-R xyz.
The thing I'm having a hard time with here is the notion that command
number is a convenient way of identifying prior commands. It seems like
an idea that should have gone out with punched cards.
Imagine that you are profiling 50 queries, the queries are all the same
except for varying where clauses or joins.
Perhaps you are building out a query that contains 50 joins, and you are
building one step at a time (which I do ALL the time). You get to join
47 and realize that your data set isn't right.
Now, there is a chance that query 46 is the query that I want, but
ooops, I have 3 syntax errors so now it is actually query 43. How do I
get back to 43?
I ctrl-r... which really doesn't seem useful in this sense.
I \s and copy and paste? Even worse and what I do now.
Or... I \s and hit !46 (or whatever the ! becomes).
!46 seems awful easy to me.
Worse yet... what if it is actually query 27 because I got distracted by
a support question over jabber from the customer I am actually working
on and I need to help him profile a query for a couple of minutes?
Sincerely,
Joshua D. Drake
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
On Tue, 2006-12-05 at 17:45 -0500, Bruce Momjian wrote:
Alvaro Herrera wrote:
Tom Lane wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
control-R isn't real useful for 17 queries that are exactly the same
except for 3 different join clauses. It also isn't useful when you don't
know exactly what query you are looking for.... but, somehow, you know exactly what command number it has?
Well, presumably \s would give you the numbers. "history" does on bash anyway.
I use it on bash all the time: I do "history | grep something" and then
!<number of command I want>.I don't think we can do the "| grep" part, but it's useful anyway.
OK, now at least I understand how it would be used, and could be
explained easily in the documentation --- do \s, then \! 99, or maybe \#
99. I don't like making \! do shells and pull SQL commands from history.
Yeah the # was the next logical thing. Would we have to escape it?
\#12... hmmm
#12
I prefer #12
Sincerely,
Joshua D. Drkae
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
