Обсуждение: psql behavior change on upgrade from version 12.x to 13.1

Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64).

I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the behavior of ordinary copy-and-paste has
changedramatically, and for the worse,  w.r.t. Version 12. 

HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW?

First observation

Now, when I copy a single line SQL command, terminated with semicolon and newline from the Text Edit app (with
Command-Cor the menu item) and then paste it into psql (with Command-V or the menu item), the newline isn't respected.
Ihave to hit the return key by hand to see the effect. Moreover, the pasted line has a highlighted background. 

Second observation

When I copy _several_ lines of SQL commands from the Text Edit app and then paste them into psql, none of the newlines
arerespected. (I still get the strange highlight.) Now when I hit the return key, I get errors like this: 

  \i: extra argument "<the text of the line>" ignored

for every single line.

This makes a standard working practice that my fingers have learned over decades suddenly completely unusable.

NOTE: the \i metacommand still works as it always did.

Hi Bryn,

> On 09. Feb, 2021, at 19:55, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64).
>
> I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the behavior of ordinary copy-and-paste has
changedramatically, and for the worse,  w.r.t. Version 12. 
>
> HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW?
>
> First observation
>
> Now, when I copy a single line SQL command, terminated with semicolon and newline from the Text Edit app (with
Command-Cor the menu item) and then paste it into psql (with Command-V or the menu item), the newline isn't respected.
Ihave to hit the return key by hand to see the effect. Moreover, the pasted line has a highlighted background. 
>
> Second observation
>
> When I copy _several_ lines of SQL commands from the Text Edit app and then paste them into psql, none of the
newlinesare respected. (I still get the strange highlight.) Now when I hit the return key, I get errors like this: 
>
>  \i: extra argument "<the text of the line>" ignored
>
> for every single line.
>
> This makes a standard working practice that my fingers have learned over decades suddenly completely unusable.
>
> NOTE: the \i metacommand still works as it always did.

well, I guess this is a macOS Big Sur issue and not a PostgreSQL problem. Or rather, it's a user (your) issue because
itworks as designed. ;-) 

I'm on Big Sur 11.2 (20D64) too. From what I can tell, copy/paste hasn't changed. But then, I don't use Text Edit. I
useboth vi in a Terminal and TextMate. I don't like Text Edit because this is just as comfortable and capable as is
Windows'Notepad... 

Anyway, copying a line including the newline will require you to mark the whole line. Triple click on a line and you
seethe marking go beyond the last character in the line. 

If you click (and hold!) while moving the mouse you will see the marking move accordingly. Note how it jumps to show
thecomplete line up to the full right including the part beyond the last character if you move the mouse beyond that.
Ifyou want to include the new line in your copying to the clipboard, you'll need to include that in your marking. 

Cheers,
Paul

paul.foerster@gmail.com wrote:

Hi Bryn,

> On 09. Feb, 2021, at 19:55, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> Using a MacBook Pro with the current Big Sur—Version 11.2 (20D64).
>
> I just upgraded to PostgreSQL 13.1. (Earlier, I was on 12.x.) Using psql, the behavior of ordinary copy-and-paste has
changedramatically, and for the worse,  w.r.t. Version 12. 
>
> HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW?
>
> First observation
>
> Now, when I copy a single line SQL command, terminated with semicolon and newline from the Text Edit app (with
Command-Cor the menu item) and then paste it into psql (with Command-V or the menu item), the newline isn't respected.
Ihave to hit the return key by hand to see the effect. Moreover, the pasted line has a highlighted background. 
>
> Second observation
>
> When I copy _several_ lines of SQL commands from the Text Edit app and then paste them into psql, none of the
newlinesare respected. (I still get the strange highlight.) Now when I hit the return key, I get errors like this: 
>
> \i: extra argument "<the text of the line>" ignored
>
> for every single line.
>
> This makes a standard working practice that my fingers have learned over decades suddenly completely unusable.
>
> NOTE: the \i metacommand still works as it always did.

well, I guess this is a macOS Big Sur issue and not a PostgreSQL problem. Or rather, it's a user (your) issue because
itworks as designed. ;-) 

I'm on Big Sur 11.2 (20D64) too. From what I can tell, copy/paste hasn't changed. But then, I don't use Text Edit. I
useboth vi in a Terminal and TextMate. I don't like Text Edit because this is just as comfortable and capable as is
Windows'Notepad... 

Anyway, copying a line including the newline will require you to mark the whole line. Triple click on a line and you
seethe marking go beyond the last character in the line. 

If you click (and hold!) while moving the mouse you will see the marking move accordingly. Note how it jumps to show
thecomplete line up to the full right including the part beyond the last character if you move the mouse beyond that.
Ifyou want to include the new line in your copying to the clipboard, you'll need to include that in your marking. 

Cheers,
Paul

————————————————————————————————————————

We can cut Text Edit out of the picture entirely. If I enter, say, “select version();” in psql it of course works fine
whenI hit “return”. If I then copy-and-paste this from the psql screen (including the newline), then I see the same
effectthat I described above. 

I should have said that I also use YugaByteDB in the same env. This has its own app like psql called ysqlsh. It's built
onpsql version 11.2. The behavior that I describe does NOT show up with ysqlsh. 

I could have said that copy-and-paste from the Text Edit app to the terminal window O/S prompt works as it always has.

The effect is specific to psql.

I can presently try PostgreSQL Version 12 (latest).

Bryn Llewellyn <bryn@yugabyte.com> writes:
> HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW?

> First observation

> Now, when I copy a single line SQL command, terminated with semicolon and newline from the Text Edit app (with
Command-Cor the menu item) and then paste it into psql (with Command-V or the menu item), the newline isn't respected.
Ihave to hit the return key by hand to see the effect. Moreover, the pasted line has a highlighted background. 

> Second observation

> When I copy _several_ lines of SQL commands from the Text Edit app and then paste them into psql, none of the
newlinesare respected. (I still get the strange highlight.) Now when I hit the return key, I get errors like this: 
>   \i: extra argument "<the text of the line>" ignored

FWIW, I'm not seeing that here, with Big Sur 11.2 and up-to-date Postgres.

In a typical Postgres build, most of psql's input behavior is not
determined by psql itself, but by libreadline (or possibly libedit,
if PG was configured to use that instead).  I speculate that your
build switched to a newer version of readline or libedit, and it's
behaving differently than you're used to.  You could get some info
about this by applying "otool -L" to the psql executable.  On my
laptop I see

$ otool -L /Users/tgl/testversion/bin/psql
/Users/tgl/testversion/bin/psql:
        /Users/tgl/testversion/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.14.0)
        /usr/lib/libedit.3.dylib (compatibility version 2.0.0, current version 3.0.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.60.1)

of which the relevant bit for this purpose is "/usr/lib/libedit.3.dylib",
pointing to the Apple-supplied version of libedit.  Maybe you see
something else?

            regards, tom lane

On 09-Feb-2021, at 11:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bryn Llewellyn <bryn@yugabyte.com> writes:
> HAS ANYBODY ELSE SEEN WHAT I REPORT BELOW?

> First observation

> Now, when I copy a single line SQL command, terminated with semicolon and newline from the Text Edit app (with
Command-Cor the menu item) and then paste it into psql (with Command-V or the menu item), the newline isn't respected.
Ihave to hit the return key by hand to see the effect. Moreover, the pasted line has a highlighted background. 

> Second observation

> When I copy _several_ lines of SQL commands from the Text Edit app and then paste them into psql, none of the
newlinesare respected. (I still get the strange highlight.) Now when I hit the return key, I get errors like this: 
>  \i: extra argument "<the text of the line>" ignored

FWIW, I'm not seeing that here, with Big Sur 11.2 and up-to-date Postgres.

In a typical Postgres build, most of psql's input behavior is not
determined by psql itself, but by libreadline (or possibly libedit,
if PG was configured to use that instead).  I speculate that your
build switched to a newer version of readline or libedit, and it's
behaving differently than you're used to.  You could get some info
about this by applying "otool -L" to the psql executable.  On my
laptop I see

$ otool -L /Users/tgl/testversion/bin/psql
/Users/tgl/testversion/bin/psql:
       /Users/tgl/testversion/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.14.0)
       /usr/lib/libedit.3.dylib (compatibility version 2.0.0, current version 3.0.0)
       /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.60.1)

of which the relevant bit for this purpose is "/usr/lib/libedit.3.dylib",
pointing to the Apple-supplied version of libedit.  Maybe you see
something else?

            regards, tom lane

—————

Here’s what I get when I do "otool -L /usr/local/bin/psql";

/usr/local/bin/psql:
    /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
    /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)

In other words, different from what you see. I'm an ordinary end user. I don't even think expllictly about “building"
anythingin the PostgreSQL system. I got into this mess (as I believe) because I did this: 

brew update
brew upgrade

A colleague advised me to do this periodically as a hygiene measure. It had the surprising, and for me undesired,
side-effectof upgrading my PostgreSQL installation from 12 to Version 13.1. I suppose that this triggered a build of
somekind. 

Bryn Llewellyn <bryn@yugabyte.com> writes:
> Here’s what I get when I do "otool -L /usr/local/bin/psql";

> /usr/local/bin/psql:
>     /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
>     /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
>     /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)

Right, so that's using a version of libreadline that's supplied by
Homebrew (the /usr/local/opt path is the giveaway on that).

I don't know whether these things represent an intentional change
of libreadline's behavior in Homebrew's build, or a bug, but in
either case you should take the issue to the Homebrew support forums.
If it's intentional, I imagine there's a way to get the old behavior
back.

Also, libreadline is fairly configurable, so maybe this boils down
to some unintentional change in your ~/.inputrc ?

            regards, tom lane

On 2/9/21 1:57 PM, Bryn Llewellyn wrote:
[snip]
> In other words, different from what you see. I'm an ordinary end user. I don't even think expllictly about “building"
anythingin the PostgreSQL system. I got into this mess (as I believe) because I did this:
 
>
> brew update
> brew upgrade
>
> A colleague advised me to do this periodically as a hygiene measure. It had the surprising, and for me undesired,
side-effectof upgrading my PostgreSQL installation from 12 to Version 13.1. I suppose that this triggered a build of
somekind.
 

Don't run commands (especially system-related commands) unless you know what 
they do.

In this case, they mean:
Get the latest version numbers of homebrew-installed software.
Upgrade all homebrew-installed software to the latest version.

-- 
Angular momentum makes the world go 'round.

On 09-Feb-2021, at 12:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bryn Llewellyn <bryn@yugabyte.com> writes:
> Here’s what I get when I do "otool -L /usr/local/bin/psql";

> /usr/local/bin/psql:
>     /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
>     /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
>     /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)

Right, so that's using a version of libreadline that's supplied by
Homebrew (the /usr/local/opt path is the giveaway on that).

I don't know whether these things represent an intentional change
of libreadline's behavior in Homebrew's build, or a bug, but in
either case you should take the issue to the Homebrew support forums.
If it's intentional, I imagine there's a way to get the old behavior
back.

Also, libreadline is fairly configurable, so maybe this boils down
to some unintentional change in your ~/.inputrc ?

            regards, tom lane

—————

Thank you very much, Tom. It seems, then, that we have the “microscopic” explanation. I’ll have to to a fair bit of
researchto find out what to do to fix this problem. 

On 2/9/21 12:19 PM, Bryn Llewellyn wrote:
> 
> 
> On 09-Feb-2021, at 12:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Bryn Llewellyn <bryn@yugabyte.com> writes:
>> Here’s what I get when I do "otool -L /usr/local/bin/psql";
> 
>> /usr/local/bin/psql:
>>     /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
>>     /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
>>     /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)
> 
> Right, so that's using a version of libreadline that's supplied by
> Homebrew (the /usr/local/opt path is the giveaway on that).
> 
> I don't know whether these things represent an intentional change
> of libreadline's behavior in Homebrew's build, or a bug, but in
> either case you should take the issue to the Homebrew support forums.
> If it's intentional, I imagine there's a way to get the old behavior
> back.
> 
> Also, libreadline is fairly configurable, so maybe this boils down
> to some unintentional change in your ~/.inputrc ?
> 
>             regards, tom lane
> 
> —————
> > Thank you very much, Tom. It seems, then, that we have the 
“microscopic” explanation. I’ll have to to a fair bit of research to 
find out what to do to fix this problem.

I would start here:

https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/postgresql.rb

and contact the maintainer:

https://github.com/MikeMcQuaid

FYI, the formula points at another formula for readline:

def install
     ENV.prepend "LDFLAGS", "-L#{Formula["openssl@1.1"].opt_lib} 
-L#{Formula["readline"].opt_lib}"
     ENV.prepend "CPPFLAGS", "-I#{Formula["openssl@1.1"].opt_include} 
-I#{Formula["readline"].opt_include}"

which can be found here:

https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/readline.rb
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Feb 09, 2021 at 12:19:21PM -0800, Bryn Llewellyn <bryn@yugabyte.com> wrote:

> 
> 
> On 09-Feb-2021, at 12:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Bryn Llewellyn <bryn@yugabyte.com> writes:
> > Here’s what I get when I do "otool -L /usr/local/bin/psql";
> 
> > /usr/local/bin/psql:
> >     /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
> >     /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
> >     /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)
> 
> Right, so that's using a version of libreadline that's supplied by
> Homebrew (the /usr/local/opt path is the giveaway on that).
> 
> I don't know whether these things represent an intentional change
> of libreadline's behavior in Homebrew's build, or a bug, but in
> either case you should take the issue to the Homebrew support forums.
> If it's intentional, I imagine there's a way to get the old behavior
> back.
> 
> Also, libreadline is fairly configurable, so maybe this boils down
> to some unintentional change in your ~/.inputrc ?
> 
>             regards, tom lane
> 
> —————
> 
> Thank you very much, Tom. It seems, then, that we have the
> “microscopic” explanation. I’ll have to to a fair bit of research to
> find out what to do to fix this problem.

This sounds exactly like changes that happened in
debian a while ago. I was taken by surprise as well,
but it's actually much better behaviour than previous
behaviour. It's nice to know tht you have to confirm
the execution of a pasted shell command (especially
when pasting commands as root). It feels safer. You
might come to like it. But of course, the readline
library is probably configurable enough to change the
behaviour.

According to https://tiswww.case.edu/php/chet/readline/rluserman.html,
this could be what you're looking for:

  enable-bracketed-paste
  When set to `On', Readline will configure the
  terminal in a way that will enable it to insert each
  paste into the editing buffer as a single string of
  characters, instead of treating each character as if
  it had been read from the keyboard. This can prevent
  pasted characters from being interpreted as editing
  commands. The default is `On'.

So try putting this in your ~/.inputrc file:

  set enable-bracketed-paste off

cheers,
raf

On 09-Feb-2021, at 14:16, raf <raf@raf.org> wrote:

On Tue, Feb 09, 2021 at 12:19:21PM -0800, Bryn Llewellyn <bryn@yugabyte.com> wrote:

>
>
> On 09-Feb-2021, at 12:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bryn Llewellyn <bryn@yugabyte.com> writes:
>> Here’s what I get when I do "otool -L /usr/local/bin/psql";
>
>> /usr/local/bin/psql:
>>     /usr/local/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.13.0)
>>     /usr/local/opt/readline/lib/libreadline.8.dylib (compatibility version 8.0.0, current version 8.0.0)
>>     /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.0.0)
>
> Right, so that's using a version of libreadline that's supplied by
> Homebrew (the /usr/local/opt path is the giveaway on that).
>
> I don't know whether these things represent an intentional change
> of libreadline's behavior in Homebrew's build, or a bug, but in
> either case you should take the issue to the Homebrew support forums.
> If it's intentional, I imagine there's a way to get the old behavior
> back.
>
> Also, libreadline is fairly configurable, so maybe this boils down
> to some unintentional change in your ~/.inputrc ?
>
>             regards, tom lane
>
> —————
>
> Thank you very much, Tom. It seems, then, that we have the
> “microscopic” explanation. I’ll have to to a fair bit of research to
> find out what to do to fix this problem.

This sounds exactly like changes that happened in
debian a while ago. I was taken by surprise as well,
but it's actually much better behaviour than previous
behaviour. It's nice to know tht you have to confirm
the execution of a pasted shell command (especially
when pasting commands as root). It feels safer. You
might come to like it. But of course, the readline
library is probably configurable enough to change the
behaviour.

According to
https://www.google.com/url?q=https://tiswww.case.edu/php/chet/readline/rluserman.html&source=gmail-imap&ust=1613513793000000&usg=AOvVaw2WcGHc2rFgrzBRyyoHH-Vk,
this could be what you're looking for:

 enable-bracketed-paste
 When set to `On', Readline will configure the
 terminal in a way that will enable it to insert each
 paste into the editing buffer as a single string of
 characters, instead of treating each character as if
 it had been read from the keyboard. This can prevent
 pasted characters from being interpreted as editing
 commands. The default is `On'.

So try putting this in your ~/.inputrc file:

 set enable-bracketed-paste off

cheers,
raf

——————————————————————————————————————————————————

Thanks, raf. I didn’t have a ~/.inputrc file. So I created one with the single line that you mentioned. It worked like
acharm. Now life is back to normal.