Not so happy with psql's new multiline behavior

Has anyone else been finding the recent behavior of CVS-tip psql
to be a disimprovement? I've gotten sufficiently annoyed with it
that I'm ready to propose reverting this patch:

2006-02-11 16:55 momjian

* src/bin/psql/: help.c, input.c, input.h, mainloop.c, prompt.c,
tab-complete.c: o Improve psql's handling of multi-line statements

Currently, while \e saves a single statement as one entry,
interactive
statements are saved one line at a time. Ideally all
statements
would be saved like \e does.

Sergey E. Koposov

Maybe it's just that I'm too used to the old behavior, but I don't like
anything about the way it works now. As an example, the new behavior is
extremely unfriendly to backslash commands. I just got done typing a
long command and then deciding that I would like to have \timing on, so
I hit return (which in prior versions would have entered the line into
the history buffer), typed \timing, hit return again, hit control-P, and
found that I'd lost my long command. In other situations I find that
control-P pulls back weird combinations of SQL and backslash commands,
and there's no way AFAICT to separate the two.

At a minimum this code has to be fixed to understand the difference
between backslash commands and SQL lines, and not combine them in
history entries; otherwise we should revert it. I'm leaning to "revert"
since I haven't actually seen a case where pulling back multiple lines
helped me ... but maybe that just reflects that I don't retype multiline
SQL commands all that much.

Comments?

regards, tom lane

On Sat, Mar 04, 2006 at 12:08:25PM -0500, Tom Lane wrote:
> Comments?

I generally do not use psql in this manner, because I've found it
to be annoying before the change. After the change, from what you
describe, I too would find it annoying still.

For me, I prefer the interactive behaviour of ZSH. Multiline
statements remain as multiline statements, as they were typed.
One can navigate up and down through the multiline statement
to make alterations. The real beauty of this approach comes up
when doing something such as defining a function. Would you
LOVE the ability to edit the function, in the original form,
as originally typed, allowing you to insert text, and even
newlines into the middle? Effectively you have have a full
text editor, for the last complete series of lines.

Too hard to implement? :-)

To check it out, try /bin/zsh (it seems to come with Linux and
Solaris these days), and type out:

$ bindkey -e # Emacs key bindings

$ f()
> {
> a
> }

$ f
f:2: command not found: a

Oh no. We typed the wrong command in. It isn't 'a' we want. We want 'ls'.

Hit up-arrow twice, and we get (_ = cursor)

$ f()
{
a
}_

Let's go change 'a'. up arrow, Control-E (end-of-line):

$ f()
{
a_
}

Then, backspace, ls:

$ f()
{
ls_
}

Then enter:

$ _

Done.

Saved me lots of time. I write very complex functions, directly with
the line editor. If I make a mistake, I go back and change it.

Without this sort of thing, I end up storing my functions to a text
editor window, and cut + pasting back and forth. In fact, that is what
I do with psql today. I have a text editor with a record of all my
statements, because psql line editting sucks.

Just an opinion.

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/

Tom Lane wrote:

> At a minimum this code has to be fixed to understand the difference
> between backslash commands and SQL lines, and not combine them in
> history entries; otherwise we should revert it. I'm leaning to "revert"
> since I haven't actually seen a case where pulling back multiple lines
> helped me ... but maybe that just reflects that I don't retype multiline
> SQL commands all that much.

Reverting or not, this is rather a matter of how annoying it is right now
(for the developers using CVS tip). I think the old behaviour needs
improvement. You could either use \e and have nice editing capabilities, but
have no tab completition, no backslash-commands in between, and your nice
multiple-lines-query fell apart as soon as you exited psql.

I have not tried CVS tip for a while, but what you describe needs fixing.
Backslash-commands should definately work.

Mark <mark(at)mark(dot)mielke(dot)cc> wrote:
> To check it out, try /bin/zsh (it seems to come with Linux and
> Solaris these days), and type out:

Actually I am quite impressed by the way zsh works, I've just tried it. I
think it could even work that way in psql, including the slash commands. For
everyone who has never tried zsh, now is the time. ;-)

When you edit a multiline function in zsh, you can easily press Control-C,
then type "man zsh", return, and press "up" to continue editing the function
as it was left when you pressed Control-C.

This could work the same way in psql. You edit your query, press Control-C,
issue a backslash command, press up, finish your query.

The zsh that comes with my linux distribution is BSD licensed, so we could
even borrow code. :-)

On the other hand, I don't know if everybody will like it this way. Perhaps
this should be implemented as a "plugin". (Worst case scenario, but I wonder
wether we can make all people happy ever.)

Best Regards,
Michael Paesold

Michael Paesold wrote:

> When you edit a multiline function in zsh, you can easily press Control-C,
> then type "man zsh", return, and press "up" to continue editing the
> function as it was left when you pressed Control-C.

Not sure about zsh's Ctrl-C, but in bash I press Esc-# and a # is
prepended to the current line and entered into the history. This is
what I use when I want to review some manpage or something.

It also "works" in psql, but unsurprisingly it also prepends #. We
could fix it by having it prepend -- instead, or maybe enclose the
current editing buffer in /* */.

(This only works in a single line fashion in bash, but I don't see why
we couldn't make it work multiline in psql.)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

> Michael Paesold wrote:
>
>> When you edit a multiline function in zsh, you can easily press
>> Control-C,
>> then type "man zsh", return, and press "up" to continue editing the
>> function as it was left when you pressed Control-C.
>
> Not sure about zsh's Ctrl-C, but in bash I press Esc-# and a # is
> prepended to the current line and entered into the history. This is
> what I use when I want to review some manpage or something.

Nice, didn't know about that.

> It also "works" in psql, but unsurprisingly it also prepends #. We
> could fix it by having it prepend -- instead, or maybe enclose the
> current editing buffer in /* */.
>
> (This only works in a single line fashion in bash, but I don't see why
> we couldn't make it work multiline in psql.)

The main big difference between zsh and bash is that zsh allows real
in-place multiline editing. You can use your arrow keys to navigate through
the "buffer".

I don't know how the new psql mode works. Does it do multi-line editing even
including returns? I probably should try it out myself...

Best Regards,
Michael Paesold

Tom Lane wrote:
> Has anyone else been finding the recent behavior of CVS-tip psql
> to be a disimprovement?

Another minor issue is that \s doesn't show copy-pastable things.

For example:

alvherre=# select 1
alvherre-# union all
alvherre-# select 2;
?column?
----------
1
2
(2 rows)

alvherre=# \s
select 1union allselect 2;

I imagine this is just a matter of translating the line separator
character into true newlines.

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Michael Paesold wrote:
>> When you edit a multiline function in zsh, you can easily press Control-C,
>> then type "man zsh", return, and press "up" to continue editing the
>> function as it was left when you pressed Control-C.

> Not sure about zsh's Ctrl-C, but in bash I press Esc-# and a # is
> prepended to the current line and entered into the history. This is
> what I use when I want to review some manpage or something.

Seems like this discussion has drifted off into things that could only
be changed by altering libreadline, which is not within the purview
of our project ...

(I believe btw that the '#' referred to above is actually a variable
and can be set in the readline config file, so it's not really our
problem anyway.)

regards, tom lane

On Sat, 4 Mar 2006, Tom Lane wrote:

> Has anyone else been finding the recent behavior of CVS-tip psql
> to be a disimprovement? I've gotten sufficiently annoyed with it
> that I'm ready to propose reverting this patch:
>
> 2006-02-11 16:55 momjian
>
> * src/bin/psql/: help.c, input.c, input.h, mainloop.c, prompt.c,
> tab-complete.c: o Improve psql's handling of multi-line statements
>
> Currently, while \e saves a single statement as one entry,
> interactive
> statements are saved one line at a time. Ideally all
> statements
> would be saved like \e does.
>
> Sergey E. Koposov
>
>
> Maybe it's just that I'm too used to the old behavior, but I don't like
> anything about the way it works now. As an example, the new behavior is
> extremely unfriendly to backslash commands. I just got done typing a
> long command and then deciding that I would like to have \timing on, so
> I hit return (which in prior versions would have entered the line into
> the history buffer), typed \timing, hit return again, hit control-P, and
> found that I'd lost my long command.

I don't understand really this point. For example (in 8.1.3):

wsdb=# select 'your long query here ' ||
wsdb-# \timing
Timing is on.
wsdb-# 'plus something additional (also long)';
?column?
------------------------------------------------------------
your long query here plus something additional (also long)
(1 row)

Time: 3,644 ms

(all the time in that example I hit only return button)

Example in 8.2devel:

wsdb=# select 'your long query here ' ||
wsdb-# \timing
Timing is on.
wsdb-# 'something additional (also long)';
?column?
-------------------------------------------------------
your long query here something additional (also long)
(1 row)

Time: 0,760 ms

(again I hit only return button)

Yes, in the second example \timing WILL go to the history. And I think
that IS ok(even for example in 8.1.3 case the backslash commands are
also NOT stripped away from the SQL commands) Like this:

wsdb=# select 'your long query here ' \timing
Timing is on.
wsdb-# 'plus something additional (also long)';
?column?
------------------------------------------------------------
your long query here plus something additional (also long)
(1 row)

Time: 0,287 ms

The history will contain "select 'your long query here ' \timing"

So, do you think, that stripping away the \backslash commands from
multiline history entry is logical and is needed often? I certainly don't.

---------
Second issue:

In your example you probably forgot to say about pressing Ctrl+C. In that
case
in 8.1.3

wsdb=# select 'your long query here '
wsdb-# \timing
Timing is on.
wsdb-#

(after pressing Ctrl+C in last line, the "select ..." will be in the history),
and in 8.2devel it will NOT.

I fixed that issue. I've send the patch to -patches.

So, except that, I do not really see any problems with multiline queries.

Yes, I agree that for longterm PG hackers, the behaviour can seem a bit
unusual, but it is better ... (my opinion).

Regards,
Sergey

*****************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math(at)sai(dot)msu(dot)ru

Just to clarify, is this the problem?

test=> SELECT
test-> \d
No relations found.
test-> 1;
?column?
----------
1
(1 row)

test=> SELECT
\d
1;
Did not find any relation named "1".

I do like the new behavior and rarely do backslashes while I am typing a
multi-line statements. I suppose we could supress backslashes from
being added to history when you are in the middle of a multi-line query.

---------------------------------------------------------------------------

Tom Lane wrote:
> Has anyone else been finding the recent behavior of CVS-tip psql
> to be a disimprovement? I've gotten sufficiently annoyed with it
> that I'm ready to propose reverting this patch:
>
> 2006-02-11 16:55 momjian
>
> * src/bin/psql/: help.c, input.c, input.h, mainloop.c, prompt.c,
> tab-complete.c: o Improve psql's handling of multi-line statements
>
> Currently, while \e saves a single statement as one entry,
> interactive
> statements are saved one line at a time. Ideally all
> statements
> would be saved like \e does.
>
> Sergey E. Koposov
>
>
> Maybe it's just that I'm too used to the old behavior, but I don't like
> anything about the way it works now. As an example, the new behavior is
> extremely unfriendly to backslash commands. I just got done typing a
> long command and then deciding that I would like to have \timing on, so
> I hit return (which in prior versions would have entered the line into
> the history buffer), typed \timing, hit return again, hit control-P, and
> found that I'd lost my long command. In other situations I find that
> control-P pulls back weird combinations of SQL and backslash commands,
> and there's no way AFAICT to separate the two.
>
> At a minimum this code has to be fixed to understand the difference
> between backslash commands and SQL lines, and not combine them in
> history entries; otherwise we should revert it. I'm leaning to "revert"
> since I haven't actually seen a case where pulling back multiple lines
> helped me ... but maybe that just reflects that I don't retype multiline
> SQL commands all that much.
>
> Comments?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, Mar 04, 2006 at 01:07:15PM -0500, mark(at)mark(dot)mielke(dot)cc wrote:
> For me, I prefer the interactive behaviour of ZSH. Multiline
> statements remain as multiline statements, as they were typed.
> One can navigate up and down through the multiline statement
> to make alterations. The real beauty of this approach comes up
> when doing something such as defining a function. Would you
> LOVE the ability to edit the function, in the original form,
> as originally typed, allowing you to insert text, and even
> newlines into the middle? Effectively you have have a full
> text editor, for the last complete series of lines.
<snip>
> Oh no. We typed the wrong command in. It isn't 'a' we want. We want 'ls'.
>
> Hit up-arrow twice, and we get (_ = cursor)
>
> $ f()
> {
> a
> }_

ISTM this is much more useful than the way psql used to work. Don't know
about what's happening in -tip...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461