Thread: Separate psql commands from arguments (was: psql command aliases support)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Moving to -hackers ...

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH9RwN5YBsbHkuyV0RAr9ZAKD+XwNYYw3ugsTvowvKImOlKMZzPQCfTHkQ
u9jLkEIAWI/0MbNzzxBt0ok=
=So1n
-----END PGP SIGNATURE-----

On Fri, Apr 4, 2008 at 4:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  Yeah, the fundamental difference between the backslash command situation
>  and aliases in shells and suchlike is that, because we've historically
>  allowed no space between command name and argument, it's not that easy
>  to tell what string ought to be compared against alias names.
>
>  I think that an alias facility would only be acceptably safe if we
>  disallowed that syntax (ie, start to *require* a space between command
>  and args).  Are we ready to do that?
>

As far as i know, this behaviour isn't documented anywhere.  In fact,
the manual denies its existence:
   "The format of a psql command is the backslash, followed
immediately by a command verb, then any arguments. The arguments are
separated from the command verb and each other by any number of
whitespace characters."

(unless you interpret "any number" to include zero, but that's quite a
stretch in this context)

For what it's worth, I've been using Postgres actively for about five
years, and I've not once suspected that it was possible to omit the
space between a psql command and its argument.

The idea of writing the command and its arguments in one word is so
completely bizarre to me that I can't imagine anyone even trying it
casually to see if it works.  Although it is likely that some people
have stumbled upon it accidentally via typos, it's hard to imagine
them wanting to use it in any kind of pratical application.

+1 for dropping this quirk.  And, if there are no objections (or other
takers), I volunteer to write a patch.

Regards,
BJ

--On Freitag, April 04, 2008 05:04:04 +1100 Brendan Jurd
<direvus@gmail.com> wrote:

For everyone else who hasn't read the original discussion on -patches,
here's a link into the archives:

<http://archives.postgresql.org/pgsql-patches/2008-04/msg00005.php>

>
> On Fri, Apr 4, 2008 at 4:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>  Yeah, the fundamental difference between the backslash command situation
>>  and aliases in shells and suchlike is that, because we've historically
>>  allowed no space between command name and argument, it's not that easy
>>  to tell what string ought to be compared against alias names.
>>
>>  I think that an alias facility would only be acceptably safe if we
>>  disallowed that syntax (ie, start to *require* a space between command
>>  and args).  Are we ready to do that?
>>

Okay, the more we talked about that, the more i got that feeling, too.

>
> As far as i know, this behaviour isn't documented anywhere.  In fact,
> the manual denies its existence:
>
>     "The format of a psql command is the backslash, followed
> immediately by a command verb, then any arguments. The arguments are
> separated from the command verb and each other by any number of
> whitespace characters."
>

Not only there, the code itself  doesn't encourage the use of this syntax
(src/bin/psql/command.c), too:


                /*
                 * If the command was not recognized, try to parse it as a
one-letter
                 * command with immediately following argument (a
still-supported, but
                 * no longer encouraged, syntax).
                 */

> (unless you interpret "any number" to include zero, but that's quite a
> stretch in this context)
>
> For what it's worth, I've been using Postgres actively for about five
> years, and I've not once suspected that it was possible to omit the
> space between a psql command and its argument.
>
> The idea of writing the command and its arguments in one word is so
> completely bizarre to me that I can't imagine anyone even trying it
> casually to see if it works.  Although it is likely that some people
> have stumbled upon it accidentally via typos, it's hard to imagine
> them wanting to use it in any kind of pratical application.
>
> +1 for dropping this quirk.  And, if there are no objections (or other
> takers), I volunteer to write a patch.
>

Here's a quick and dirty patch which removes the responsible code from psql
(maybe not enough, but short testing shows it's working). Sorry for the
unified diff....

+1, too. I advised people not to use that syntax for years now, maybe we're
in luck and everyone else was doing the same ;)

--
  Thanks

                    Bernd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Apr 4, 2008 at 6:35 AM, Bernd Helmle  wrote:
>  Here's a quick and dirty patch which removes the responsible code from psql
> (maybe not enough, but short testing shows it's working). Sorry for the
> unified diff....
>

I didn't realise it would be that straightforward!

You could probably also do away with psql_scan_slash_pushback()
(psqlcan.h, psqlcan.l) as it is only used by the code you removed.

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH9TV85YBsbHkuyV0RAq2aAKC5ay/QJQu9De9Ivq1kcgUFOGUa9gCgyB0c
wt1IjpIZH26O7DiWxf0nXE0=
=7Ybj
-----END PGP SIGNATURE-----

--On Freitag, April 04, 2008 06:52:37 +1100 Brendan Jurd 
<direvus@gmail.com> wrote:

> I didn't realise it would be that straightforward!

Stumbled across it during hacking...

>
> You could probably also do away with psql_scan_slash_pushback()
> (psqlcan.h, psqlcan.l) as it is only used by the code you removed.

It's not clean yet, but i thought we need something quick so people can try 
and comment on it.

--  Thanks
                   Bernd

"Brendan Jurd" <direvus@gmail.com> writes:

> +1 for dropping this quirk.  And, if there are no objections (or other
> takers), I volunteer to write a patch.

Regardless of whether we go ahead with this (and I'm not fond of it primarily
because I want \c& to "work"), I think we would still be better off keeping
the aliases in a separate namespace from psql commands and having an explicit
command for calling them.

I also don't see any point in allowing aliases which call other psql commands.
psql is not a particularly nice and well defined interface and it would just
make it that much more complex and confusing.

I still see it much cleaner and much clearer for people reading the script to
have something like

\query dpkg perl-base*

Than to have something like

\dpkg perl-base*

which looks like it might be related to \dp in some way the way (like how \dFp
is related to \dF).

It would also mean that if you try to run a query which doesn't exist you
won't accidentally type a real command or get a "command not found" error.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning

Gregory Stark escribió:

> I still see it much cleaner and much clearer for people reading the script to
> have something like
> 
> \query dpkg perl-base*

This also helps to separate the namespaces for tab completion if you
want to use this in interactive mode.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Gregory Stark <stark@enterprisedb.com> writes:
> "Brendan Jurd" <direvus@gmail.com> writes:
>> +1 for dropping this quirk.  And, if there are no objections (or other
>> takers), I volunteer to write a patch.

> Regardless of whether we go ahead with this (and I'm not fond of it primarily
> because I want \c& to "work"),

Perhaps the useful part of the old behavior could be kept by allowing
the command name to be terminated by punctuation as well as whitespace.
("Punctuation" being all nonletters except "+", since we already accept
"+" in command names --- or alternatively we could consider the "+" as
the first argument?)  Would that cover all the cases you are used to
using?

> I think we would still be better off keeping
> the aliases in a separate namespace from psql commands and having an explicit
> command for calling them.

+1.  As I already said, the psql command namespace is so dense that it's
going to be real hard to actually use aliases unless we separate them
somehow.

However, the invoking command has to be pretty dang short or typing it
will negate the point of having aliases.  Perhaps something like this:
if your alias is "foo" you invoke it as "\-foo ...".
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 5, 2008 at 10:00 AM, Gregory Stark  wrote:
>  Regardless of whether we go ahead with this (and I'm not fond of it primarily
>  because I want \c& to "work"),

Okay, but what on earth is "\c&" and what would you expect it to do
when it "works"?  I suppose you're connecting to a database, but
somehow I don't think you're talking about a database with the name
"&".

If & has some special meaning as a psql argument (and I see no mention
of it in the manual; another undocumented quirk?) then you'd still be
able to do "\c &" ...

Regards,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH9szU5YBsbHkuyV0RAvQkAKCdEM5auZYGDTgoy2h/gRZlaazowgCfePcg
G45rv1TBopMYNl011fuo/XU=
=AZ04
-----END PGP SIGNATURE-----

"Brendan Jurd" <direvus@gmail.com> writes:

> On Sat, Apr 5, 2008 at 10:00 AM, Gregory Stark  wrote:
>>  Regardless of whether we go ahead with this (and I'm not fond of it primarily
>>  because I want \c& to "work"),
>
> Okay, but what on earth is "\c&" and what would you expect it to do
> when it "works"?  I suppose you're connecting to a database, but
> somehow I don't think you're talking about a database with the name
> "&".

Sorry, it was in a patch I submitted a while ago to do concurrent connections.
It's supposed to be like & in the shell -- which doesn't require a space
before it. I was just explaining in a parenthetical comment the only reason I
was personally fond of that feature. It's not an important factor.

I think the main point is just that backslash-commands in psql are quirky
short strings without a systematic rigorous parser attached. If they were all
full words with a simple set of lexer rules and a regular grammer then
allowing users to create new commands might be a good idea. But if it's an
ad-hoc hand-rolled command loop with short one and two-letter commands that
are handled inconsistently then it just seems risky.

I tend to think a real cleanup would go something like how GNU --long-options
cleaned up traditional unix options. Now most options come in long form by
default and short form as a short-cut for frequently used options.

Renaming existing commands would be a bit traumatic but we should add new
commands with whole-word commands instead of one-character abbreviations. And
perhaps add optional abbreviations as short-cuts.

In any case the reason the aliases seem like a good idea to me is not to save
typing five characters but to save remembering how to put together the magic
SQL query I need. That could involve checking the schema of few tables,
remembering which functions I need to call and what their calling convention
is, etc.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 5, 2008 at 12:14 PM, Gregory Stark  wrote:
> "Brendan Jurd"  writes:
>  > Okay, but what on earth is "\c&" and what would you expect it to do
>  > when it "works"?  I suppose you're connecting to a database, but
>  > somehow I don't think you're talking about a database with the name
>  > "&".
>
>  Sorry, it was in a patch I submitted a while ago to do concurrent connections.
>  It's supposed to be like & in the shell -- which doesn't require a space
>  before it. I was just explaining in a parenthetical comment the only reason I
>  was personally fond of that feature. It's not an important factor.
>

Ah, thanks for clearing that up.  I wasn't tuned into the concurrent
connections thread.

I agree that having shell-like behaviours is a useful goal.  The good
news is, if we had a psql where the command and the arguments are
always separated, we could just make \c& a separate command from \c.

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH9tbk5YBsbHkuyV0RAuohAKDYa6/hTEqVQr5nYlsZLLu14NuwzwCeOmVF
854P3drq/8+SrMwcbhUFNw4=
=PjSp
-----END PGP SIGNATURE-----

Am Samstag, 5. April 2008 schrieb Gregory Stark:
> Regardless of whether we go ahead with this (and I'm not fond of it
> primarily because I want \c& to "work"), I think we would still be better
> off keeping the aliases in a separate namespace from psql commands and
> having an explicit command for calling them.

The very point of this feature is to *not* have them in a separate name space.  
Shell aliases are commonly used for defining one- or two-letter abbreviations 
for other commands.  No one would be using shell commands if they required 
you to prefix the call by "mycommand " or something like that.

If you want to have a separate namespace, you could just write a function and 
call it, which uses about as many keystrokes as your proposed \query syntax.

> I also don't see any point in allowing aliases which call other psql
> commands. psql is not a particularly nice and well defined interface and it
> would just make it that much more complex and confusing.

But other people do want to use it.  If it is too confusing for you, don't use 
it.  That's what's nice about this feature: If you don't use it, it doesn't 
affect you at all.

> I still see it much cleaner and much clearer for people reading the script

Aliases are not primarily intended for scripts but for interactive use.  No 
one wants to optimize away a few letters from a script.

"Peter Eisentraut" <peter_e@gmx.net> writes:

> But other people do want to use it.  If it is too confusing for you, don't use 
> it.  That's what's nice about this feature: If you don't use it, it doesn't 
> affect you at all.

Ah but I would use it. In particular the query I found myself writing *all*
the time over and over again in Oracle was:

select count(*),n from (select count(*) as n from <tab> group by <col>) group by n

I can type it out now from finger-memory without even thinking about it. I
would have killed for a macro facility like this where I could just do

\query dist users city

and get the frequency distribution of cities in the users table. 

I don't think

\dist users city

is really much of a savings and I think it would be a huge source of confusion
that it's unrelated to the \di \ds and \dt commands. And I might well not know
about those commands and define a \di alias myself, only to try using \di
later. Or worse, define a \dx command and have it fail mysteriously in Pg 8.4.

Also, people do share stuff, even (or especially!) cute short cuts like this.
In the worst case witness Redhat's insistence on putting those damn aliases in
the standard dotfiles for example.

And plenty of sites have aliases in their root dotfiles which are part of
their site operating procedures. Picture having to explain how to use psql to
new hires including the site-specific aliases which you've built up over time
when some of those aliases conflict or have similar names to built-in
commands. A new user has no way to figure out which ones will do what type of
action.

Sure in the majority of cases it doesn't really matter how awkwardly
intermingled with the \commands the interface is. But it doesn't make much
sense to design around the cases where the design doesn't matter -- that way
lies, uhm, other databases. Let's keep in mind when designing the feature the
most long-term use where the design matters most rather than the case where it
matters least.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning

Gregory Stark escribió:

> Ah but I would use it. In particular the query I found myself writing *all*
> the time over and over again in Oracle was:
> 
> select count(*),n from (select count(*) as n from <tab> group by <col>) group by n
> 
> I can type it out now from finger-memory without even thinking about it. I
> would have killed for a macro facility like this where I could just do
> 
> \query dist users city

If we separated the namespace with something that involved a bit less
typing, would you use it?  Say

\-dist users city

(Or some other char instead of hyphen)

The point is that you don't mix it with other \ commands, and as soon as
you put \- you can already press TAB to get a list of "aliases".  So it
_is_ useful both for interactive use and script use.

"\query dist" is good for scripts but bad for interactive: too much
extra typing.  Whereas "\dist" is only relatively good for interactive
(no good support for tab completion), and not any better for scripting.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Am Samstag, 5. April 2008 schrieb Gregory Stark:
> On Apr 10, 2008, at 7:50 AM, Peter Eisentraut wrote:
>> I also don't see any point in allowing aliases which call other psql
>> commands.


Why disallow it? I think it could be very useful. One thing I  
sometimes find myself doing is wanting to run a command on a list of  
tables. That can be accomplished via:

SELECT 'ALTER TABLE ' || oid::regclass || 'blah;' FROM pg_class  
WHERE ...
\g | psql blah blah blah

I'd love to be able to alias something like that.

Another real example is being able to force certain output functions  
for certain queries. In particular, it would be handy if I had an  
alias that was SELECT now()-query_start,* FROM pg_stat_activity WHERE  
current_query != '<IDLE>' ORDER BY 1 and force that to be brought  
into less (which I could probably do via \g | less).
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

"Decibel!" <decibel@decibel.org> writes:

> Am Samstag, 5. April 2008 schrieb Gregory Stark:
>> On Apr 10, 2008, at 7:50 AM, Peter Eisentraut wrote:
>>> I also don't see any point in allowing aliases which call other psql
>>> commands.
>
> Why disallow it? I think it could be very useful. 

Well I feel like storing a query and resending it later is something
predictable which will work reliably. Storing a psql input line and
reinterpreting it later is surely going to cause weird things to happen.

Just for some examples off the top of my head, what happens if I define an
alias "\foo" which consists of "\foo" and call it? What happens if I have
mutually recursive aliases? What happens if I define "\foo" to run \ followed
by its first argument, and I pass it "foo"? What happens if I pass it "unalias
foo"?

What happens if you press C-c during an alias, does it keep running subsequent
commands? What if the editor returns an error after a \e command? What about
if a \i command doesn't find the file?

What happens to aliases when you change the encoding in the middle of running
one? Actually, come to think of it, what *does* happen to aliases when the
encoding changes??


Basically it sounds like you're treating psql as if it was a well defined
language with well defined syntax and semantics. And I don't think it is. It's
just one big if-else-if block with lots of strcmps. There's no infrastructure
to parse or manage a stack of calls to functions.


--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
 

--On Donnerstag, April 10, 2008 22:48:24 +0100 Gregory Stark 
<stark@enterprisedb.com> wrote:

> Well I feel like storing a query and resending it later is something
> predictable which will work reliably. Storing a psql input line and
> reinterpreting it later is surely going to cause weird things to happen.

Als already mentioned in the original thread, excluding backslash commands 
from aliasing
isn't a big deal, but this would negate the usefulness of this feature.

>
> Just for some examples off the top of my head, what happens if I define an
> alias "\foo" which consists of "\foo" and call it? What happens if I have
> mutually recursive aliases? What happens if I define "\foo" to run \
> followed by its first argument, and I pass it "foo"? What happens if I
> pass it "unalias foo"?
>

aliases are substituted only once within a call. They are acting exactly 
the same way you'll find on your favorite shell. If the current patch 
doesn't, it needs to be adressed, of course. Have you tried your examples 
on the current patch? Retesting shows it works as expected...

> What happens if you press C-c during an alias, does it keep running
> subsequent commands? What if the editor returns an error after a \e
> command? What about if a \i command doesn't find the file?

Since aliases are simple substitutions nothing different. Multiple commands 
are placed directly into the query buffer, so nothing different than the 
current behavior should happen. Again, do you have any concrete examples?

>
> What happens to aliases when you change the encoding in the middle of
> running one? Actually, come to think of it, what *does* happen to aliases
> when the encoding changes??

Hmm? What happens if you are going to change your encoding within multiple 
backslash commands now?

>
>
> Basically it sounds like you're treating psql as if it was a well defined
> language with well defined syntax and semantics. And I don't think it is.

So i do.

> It's just one big if-else-if block with lots of strcmps. There's no
> infrastructure to parse or manage a stack of calls to functions.

Again, it's text _substitution_, replacing the very first token with 
another token. Wether this token can be complex or not is another question, 
but of course, i'm not so foolish to describe psql as turing complete 
language.

--  Thanks
                   Bernd

"Bernd Helmle" <mailings@oopsware.de> writes:

>> What happens to aliases when you change the encoding in the middle of
>> running one? Actually, come to think of it, what *does* happen to aliases
>> when the encoding changes??
>
> Hmm? What happens if you are going to change your encoding within multiple
> backslash commands now?

I mean. What happens if I do:

\encoding 'UTF8'
\alias foo select 'あ' from tab;
\encoding euc-jp
\foo

What encoding is the alias itself stored in? Do we have to worry about
converting it when you switch client-encodings?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!

On Apr 10, 2008, at 4:48 PM, Gregory Stark wrote:
> Well I feel like storing a query and resending it later is something
> predictable which will work reliably. Storing a psql input line and
> reinterpreting it later is surely going to cause weird things to  
> happen.
>
> Just for some examples off the top of my head, what happens if I  
> define an
> alias "\foo" which consists of "\foo" and call it? What happens if  
> I have
> mutually recursive aliases? What happens if I define "\foo" to run  
> \ followed
> by its first argument, and I pass it "foo"? What happens if I pass  
> it "unalias
> foo"?

As Bernd said, I see this as simple search and replace, and then  
stick it in the command buffer. If you define an alias that calls  
itself, you could end up with a stack overflow, same as with server  
functions.

> What happens if you press C-c during an alias, does it keep running  
> subsequent
> commands? What if the editor returns an error after a \e command?  
> What about
> if a \i command doesn't find the file?

You throw an error. In shell, you can use && to control if you keep  
going or not after that; perhaps we should have that.

> Basically it sounds like you're treating psql as if it was a well  
> defined
> language with well defined syntax and semantics. And I don't think  
> it is. It's
> just one big if-else-if block with lots of strcmps. There's no  
> infrastructure
> to parse or manage a stack of calls to functions.

So perhaps we should change that. Don't get me wrong, psql is the  
most powerful command-line database I've ever seen, but it still  
irritates me that it's not more shell-like in nature. In particular,  
the inability to do things like condition processing, or save the  
results of an SQL query into a psql variable are very annoying. If  
that (or aliases) means psql needs to be more than a big IF-THEN-ELSE  
then I think that's part of what we should do.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

--On Freitag, April 11, 2008 13:53:11 -0500 Decibel! <decibel@decibel.org> 
wrote:

> As Bernd said, I see this as simple search and replace, and then stick it
> in the command buffer. If you define an alias that calls itself, you
> could end up with a stack overflow, same as with server functions.

The current implementation behaves the same as any other shell alias 
implementation i've seen: aliases are substituted exactly one time.

--  Thanks
                   Bernd