Thread: pasting a lot of commands to psql

Dmitry Shalashov <skaurus@gmail.com> writes:
> Let say that I have some sql file with like hundred of simple statements in
> it. I `cat` it, copy it to buffer, go to my beloved psql and insert it
> there.
> But somewhere after first few lines it screws over:

Yeah, I've noticed there's a limit on the amount you can paste into psql,
at least on some platforms.  AFAICT this must be a readline
bug/limitation, or maybe something about the X cutbuffer protocol;
there's nothing in psql itself that would even know that a paste is
happening.

You might have better luck with "psql -n", or maybe not.

            regards, tom lane

On 7/7/2016 10:30 AM, Dmitry Shalashov wrote:
>
> There is another ways to do it, which do not involve copy-paste, but I
> am wondering why is copy-paste won't work. What exactly is happening
> there...

probably your OS's console terminal emulation is overrunning on the
large paste.   many moving parts between the OS desktop clipboard, and psql.

how about using psql -f file_of_commands.sql    ?



--
john r pierce, recycling bits in santa cruz

Oh sweet! Thanks!

Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:39 GMT+03:00 Scott Mead <scottm@openscg.com>:

On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:

Hi Sameer,

I am trying to copy-paste (and execute) random snippets of SQL to psql console.

There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...

Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:

On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:

Hi everyone.

Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.

But somewhere after first few lines it screws over: 

b2b=> BEGIN;

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);

INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);

'

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);

b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'

b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)

Unclosed quotes, unclosed parenthesis - anyway it wont work.

How to safely insert big number of statements to psql at once?

I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)

My personal favorite for this exact thing is to use '\e'

  When you are in psql, if you \e (on *nix) it will open a temp file in whatever your $EDITOR variable is set ( I use vim).  Paste your data, then save-close the file.  It will put you back into psql and execute the command for you.

--Scott

 

What are you exactly aiming to do?

Have you tried -

psql  < myfile

Dmitry Shalashov, surfingbird.ru & relap.io

--

--

Best Regards

Sameer Kumar | DB Solution Architect 

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

--

--
Scott Mead
Sr. Architect
OpenSCG

http://openscg.com

On 07/07/2016 10:41 AM, Dmitry Shalashov wrote:
>> Have you also tried simplifying the sql for multiple values in one
> statement?
> this is another possible solution, but it's not answering my general
> "why is this happening" question.

When I have seen this I put it down to encoding issues. It happens most
often when I cut and paste from places like this list, where sometimes
strange/hidden characters are swept up in the cut and then do not
translate into the paste.

>
> This is happening with PostgreSQL 9.5.3 and psql 9.5.3, but actually I
> am seeing this for a few years... Since I started to use PostgreSQL
> actively I believe.
>
>
> Dmitry Shalashov, surfingbird.ru <http://surfingbird.ru> & relap.io
> <http://relap.io>
>
> 2016-07-07 20:36 GMT+03:00 Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>>:
>
>
>
>     On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov <skaurus@gmail.com
>     <mailto:skaurus@gmail.com>> wrote:
>
>         > *\i your_file_name*
>         *
>         *
>         I actually mentioned this way in my original posting. In my case
>         it would involve copy-paste anyway - to create a file on a
>         system where I have psql opened.
>         I may do it that way. But why copy-paste directly to psql
>         results in this behaviour?
>
>
>         Dmitry Shalashov, surfingbird.ru
>         <http://surfingbird.ru> & relap.io <http://relap.io>
>
>         2016-07-07 20:30 GMT+03:00 Melvin Davidson <melvin6925@gmail.com
>         <mailto:melvin6925@gmail.com>>:
>
>             This might seem a bit basic, but as long as you have a psql
>             session, why not just use
>             \i your_file_name
>
>             On Thu, Jul 7, 2016 at 1:26 PM, Sameer Kumar
>             <sameer.kumar@ashnik.com <mailto:sameer.kumar@ashnik.com>>
>             wrote:
>
>
>
>                 On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,
>                 <skaurus@gmail.com <mailto:skaurus@gmail.com>> wrote:
>
>                     Hi everyone.
>
>                     Let say that I have some sql file with like hundred
>                     of simple statements in it. I `cat` it, copy it to
>                     buffer, go to my beloved psql and insert it there.
>                     But somewhere after first few lines it screws over:
>
>                     b2b=> BEGIN;
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','ati.su
>                     <http://ati.su>',0,NULL,5);
>                     INSERT INTO oko_topsites
>                     VALUES('russian_federation'b2b=> INSERT INTO
>                     oko_topsites VALUES('russian_federation','atn.ua
>                     <http://atn.ua>',0,NULL,5);
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','audit-it.ru
>                     <http://audit-it.ru>',0,NULL,5);
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','aup.ru
>                     <http://aup.ru>',0,NULL,5);
>                     '
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','autocentre.ua
>                     <http://autocentre.ua>',0,NULL,5);
>
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','autodoc.ru
>                     <http://autodoc.ru>',0,NULL,5);
>
>                     b2b=> INSERT INTO oko_topsites
>                     VALUES('russian_federation','calend.ru
>                     <http://calend.ru>'
>
>                     b2b(> INSERT INTO oko_topsites
>                     VALUES('russian_federation','calorizator.ru
>                     <http://calorizator.ru>',0,NULL,5)
>
>                     Unclosed quotes, unclosed parenthesis - anyway it
>                     wont work.
>
>                     How to safely insert big number of statements to
>                     psql at once?
>                     I am aware about "execute this file" \i option of
>                     psql, that is not the answer I am looking for,
>                     thanks :-)
>
>
>                 What are you exactly aiming to do?
>
>                 Have you tried -
>                 psql  < myfile
>
>
>
>                     Dmitry Shalashov, surfingbird.ru
>                     <http://surfingbird.ru> & relap.io <http://relap.io>
>
>                 --
>                 --
>                 Best Regards
>                 Sameer Kumar | DB Solution Architect
>                 *ASHNIK PTE. LTD.*
>
>                 101 Cecil Street, #11-11 Tong Eng Building, Singapore
>                 069 533
>
>                 T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
>
>             >...I `cat` it, copy it to buffer, go to my beloved psql and
>             insert it there.
>             This might seem a bit basic, but as long as you have a psql
>             session, why not just use
>             *\i your_file_name  *
>
>             No need to cat, copy & paste!
>             --
>             *Melvin Davidson*
>             I reserve the right to fantasize.  Whether or not you
>             wish to share my fantasy is entirely up to you.
>
>
>
>     It would help if you provided the version of PostgreSQL and the O/S.
>
>     Have you also tried simplifying the sql for multiple values in one
>     statement?
>     eg:
>      INSERT INTO oko_topsites
>      VALUES
>      ('russian_federation','ati.su <http://ati.su>',0,NULL,5),
>      ('russian_federation','audit-it.ru <http://audit-it.ru>',0,NULL,5),
>      ...
>      ...
>      ('russian_federation','calorizator.ru
>     <http://calorizator.ru>',0,NULL,5);
>
>     --
>     *Melvin Davidson*
>     I reserve the right to fantasize.  Whether or not you
>     wish to share my fantasy is entirely up to you.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane wrote:

> You might have better luck with "psql -n", or maybe not.

I've wished sometimes for a "\set READLINE off" psql metacommand for
this kind of thing.  It's pretty annoying when the text being pasted
contains tabs and readline uses to do completion.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Tom Lane wrote:
>
>> You might have better luck with "psql -n", or maybe not.
>
> I've wished sometimes for a "\set READLINE off" psql metacommand for
> this kind of thing.  It's pretty annoying when the text being pasted
> contains tabs and readline uses to do completion.

Agreed.  I've looked at this problem extensively and concur that
readline is the culprit; I don't think there's any solution on our end
besides filing a bug with the readline.  I also agree with the
upthread suggestion that the best workaround today is to \e into a
non-readline based editor (vim qualifies).  Having said that, at least
on linux/gnome, very long pastes can cause severe performance issues
as well.  So for large pastes I go with psql -f.

merlin

Merlin Moncure wrote:
> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > Tom Lane wrote:
> >
> >> You might have better luck with "psql -n", or maybe not.
> >
> > I've wished sometimes for a "\set READLINE off" psql metacommand for
> > this kind of thing.  It's pretty annoying when the text being pasted
> > contains tabs and readline uses to do completion.
>
> Agreed.  I've looked at this problem extensively and concur that
> readline is the culprit; I don't think there's any solution on our end
> besides filing a bug with the readline.  I also agree with the
> upthread suggestion that the best workaround today is to \e into a
> non-readline based editor (vim qualifies).  Having said that, at least
> on linux/gnome, very long pastes can cause severe performance issues
> as well.  So for large pastes I go with psql -f.

Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few
weeks ago and was pleasantly surprised to discover that they worked just
fine with no noticeable performance problem.  I was pasting skype logs
directly from the Linux skype client window into an xterm running cat,
with obviously no readline involved.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Agreed.  I've looked at this problem extensively and concur that
> readline is the culprit; I don't think there's any solution on our end
> besides filing a bug with the readline.  I also agree with the
> upthread suggestion that the best workaround today is to \e into a
> non-readline based editor (vim qualifies).  Having said that, at least
> on linux/gnome, very long pastes can cause severe performance issues
> as well.  So for large pastes I go with psql -f.

Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few
weeks ago and was pleasantly surprised to discover that they worked just
fine with no noticeable performance problem.  I was pasting skype logs
directly from the Linux skype client window into an xterm running cat,
with obviously no readline involved.

-- 
john r pierce, recycling bits in santa cruz

On Thu, Jul 7, 2016 at 2:48 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Merlin Moncure wrote:
>> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> > Tom Lane wrote:
>> >
>> >> You might have better luck with "psql -n", or maybe not.
>> >
>> > I've wished sometimes for a "\set READLINE off" psql metacommand for
>> > this kind of thing.  It's pretty annoying when the text being pasted
>> > contains tabs and readline uses to do completion.
>>
>> Agreed.  I've looked at this problem extensively and concur that
>> readline is the culprit; I don't think there's any solution on our end
>> besides filing a bug with the readline.  I also agree with the
>> upthread suggestion that the best workaround today is to \e into a
>> non-readline based editor (vim qualifies).  Having said that, at least
>> on linux/gnome, very long pastes can cause severe performance issues
>> as well.  So for large pastes I go with psql -f.
>
> Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few
> weeks ago and was pleasantly surprised to discover that they worked just
> fine with no noticeable performance problem.  I was pasting skype logs
> directly from the Linux skype client window into an xterm running cat,
> with obviously no readline involved.

Might be a 'xterm vs Mate Terminal' problem.  Using raw xterm
performance is great.  I like some of the creature comforts of the
mate terminal though.

merlin

On 7/7/2016 10:36 AM, Melvin Davidson wrote:
> It would help if you provided the version of PostgreSQL and the O/S.

we still don't know what OS you're using here, and if its something like
linux, what desktop environment you're using (gnome, kde, mate, etc) ?



--
john r pierce, recycling bits in santa cruz

Merlin Moncure wrote:

> Might be a 'xterm vs Mate Terminal' problem.  Using raw xterm
> performance is great.  I like some of the creature comforts of the
> mate terminal though.

Heh.  I've been using lxterminal for a couple of weeks now and I find
some of these comfort features rather uncomfortable.  Haven't tried
megabyte pastes.  But yeah, it might be the terminal.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> You might have better luck with "psql -n", or maybe not.

> I've wished sometimes for a "\set READLINE off" psql metacommand for
> this kind of thing.  It's pretty annoying when the text being pasted
> contains tabs and readline uses to do completion.

Seems like the readline aspect of that wouldn't be too hard.  The fun
part would be agreeing on how we want history to behave if you flip
this on/off during a session.  Which lines get added to history?
Does ~/.psql_history get written at all if READLINE is off at the
end of the session?

A spec that seems reasonable and probably not too hard to implement is:

1. ~/.psql_history is read the first time you turn on READLINE during
a session.

2. Input lines are added to history only when READLINE is on.

3. ~/.psql_history is written at session exit if any lines have been
added to history during the session (regardless of whether READLINE
is currently on).

People might complain about point 2, but I think it would be rather
difficult to do otherwise, seeing that the active history storage
is mostly under libreadline's control.  Also, other definitions might
lead to retaining a history buffer even in totally non-interactive
sessions (where READLINE never gets turned on).  That doesn't sound
like what we'd want.

Another definitional issue is whether turning on READLINE does anything
if stdin is not a tty.  I'd vote no.

            regards, tom lane

Hi:

On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> You might have better luck with "psql -n", or maybe not.
> I've wished sometimes for a "\set READLINE off" psql metacommand for
> this kind of thing.  It's pretty annoying when the text being pasted
> contains tabs and readline uses to do completion.

Doesn't 'cat | psql ' disable it? I use it with other programs for
these purpose ( as well as things like ls | cat to avoid
colors/wordwrapping, just makes the program see a non-tty on
stidn/stdout ).

Francisco Olarte.

Francisco Olarte <folarte@peoplecall.com> writes:
> On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> I've wished sometimes for a "\set READLINE off" psql metacommand for
>> this kind of thing.  It's pretty annoying when the text being pasted
>> contains tabs and readline uses to do completion.

> Doesn't 'cat | psql ' disable it?

Sure, but you could as well use 'psql -n'.  I think the point is to be
able to turn it on and off without starting a fresh session.  (Admittedly,
maybe there's not a lot of usability gain there.)

            regards, tom lane

Tom Lane wrote:
> Francisco Olarte <folarte@peoplecall.com> writes:
> > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >> I've wished sometimes for a "\set READLINE off" psql metacommand for
> >> this kind of thing.  It's pretty annoying when the text being pasted
> >> contains tabs and readline uses to do completion.
>
> > Doesn't 'cat | psql ' disable it?
>
> Sure, but you could as well use 'psql -n'.  I think the point is to be
> able to turn it on and off without starting a fresh session.  (Admittedly,
> maybe there's not a lot of usability gain there.)

If your command line already connected to the correct server/database,
with the correct login role, then yeah you can do that.  If you have to
switch role (say the role that runs the commands is not a login role),
it's not so convenient to disconnect and launch a new psql.

Now of course this not a huge new feature, but a usability improvement
only -- but psql is full of small usability features and they make it a
pleasure to use.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services