Switching from OSX to Linux, multi-line queries in \copy don't work anymore

On 07/27/2012 09:28 PM, Ryan Kelly wrote:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

Am I missing something here?

A wild guess: I'd say this is a consequence of the fact that psql on OS
X uses libedit, on Linux it uses readline.

Personally I had no idea that multiline \copy was possible at all. I've
always though the way backslash commands are EOL-terminated while
everything else is semicolon terminated is a bit of a wart, though.

I don't have an answer for you. Using the --no-readline argument makes
no difference on my 9.1.4 here. This may just be an area where libedit
is smarter than readline - or it might be that I'm totally wrong and the
real issue is something else entirely.

Thanks for making the effort to produce a good post with all the
detailed version info, exact error text, etc.

--
Craig Ringer

Craig Ringer wrote:

On 07/27/2012 09:28 PM, Ryan Kelly wrote:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

Am I missing something here?

A wild guess: I'd say this is a consequence of the fact that psql on OS
X uses libedit, on Linux it uses readline.

Personally I had no idea that multiline \copy was possible at all. I've
always though the way backslash commands are EOL-terminated while
everything else is semicolon terminated is a bit of a wart, though.

I don't have an answer for you. Using the --no-readline argument makes
no difference on my 9.1.4 here. This may just be an area where libedit
is smarter than readline - or it might be that I'm totally wrong and the
real issue is something else entirely.

Thanks for making the effort to produce a good post with all the
detailed version info, exact error text, etc.

Also appear to get it here on single-line queries:

markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv' with
csv header;
\copy: parse error at "select"
markMLl=>

However my psql and server are rather old which could be an issue
(8.1.19 to server 8.4 if I recall correctly).

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

On Fri, Jul 27, 2012 at 09:49:06PM +0800, Craig Ringer wrote:

On 07/27/2012 09:28 PM, Ryan Kelly wrote:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

Am I missing something here?

A wild guess: I'd say this is a consequence of the fact that psql on
OS X uses libedit, on Linux it uses readline.

My psql on OSX is built with readline:
ryan@huxley: otool -L psql
/opt/local/lib/postgresql90/bin/psql:
/opt/local/lib/postgresql90/libpq.5.dylib (compatibility version 5.0.0, current version 5.3.0)
/opt/local/lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
/opt/local/lib/libreadline.6.2.dylib (compatibility version 6.0.0, current version 6.2.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 125.2.11)

Also note that I don't usually type these in directly, rather I use \e
to write them in vim. Doesn't make any difference, though.

Personally I had no idea that multiline \copy was possible at all.
I've always though the way backslash commands are EOL-terminated
while everything else is semicolon terminated is a bit of a wart,
though.

I don't have an answer for you. Using the --no-readline argument
makes no difference on my 9.1.4 here. This may just be an area where
libedit is smarter than readline - or it might be that I'm totally
wrong and the real issue is something else entirely.

Thanks for making the effort to produce a good post with all the
detailed version info, exact error text, etc.

--
Craig Ringer

-Ryan

On Fri, Jul 27, 2012 at 02:06:01PM +0000, Mark Morgan Lloyd wrote:

Craig Ringer wrote:

On 07/27/2012 09:28 PM, Ryan Kelly wrote:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

Am I missing something here?

A wild guess: I'd say this is a consequence of the fact that psql
on OS X uses libedit, on Linux it uses readline.

Personally I had no idea that multiline \copy was possible at all.
I've always though the way backslash commands are EOL-terminated
while everything else is semicolon terminated is a bit of a wart,
though.

I don't have an answer for you. Using the --no-readline argument
makes no difference on my 9.1.4 here. This may just be an area
where libedit is smarter than readline - or it might be that I'm
totally wrong and the real issue is something else entirely.

Thanks for making the effort to produce a good post with all the
detailed version info, exact error text, etc.

Also appear to get it here on single-line queries:

markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv'
with csv header;
\copy: parse error at "select"
markMLl=>

However my psql and server are rather old which could be an issue
(8.1.19 to server 8.4 if I recall correctly).

This works for me, it might be the case that your psql is too old.
Perhaps \copy didn't support queries until later?

-Ryan

Ryan Kelly <rpkelly22@gmail.com> writes:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

FWIW, I get that error on either OS X or Linux, and I'm a bit astonished
by your report that there are any versions of psql that allow it.
psql doesn't do multi-line backslash commands, in any context. Are you
sure you weren't doing a plain SQL "copy" command, without a backslash?

regards, tom lane

On Fri, Jul 27, 2012 at 12:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ryan Kelly <rpkelly22@gmail.com> writes:

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
select
*
from
pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

FWIW, I get that error on either OS X or Linux, and I'm a bit astonished
by your report that there are any versions of psql that allow it.
psql doesn't do multi-line backslash commands, in any context. Are you
sure you weren't doing a plain SQL "copy" command, without a backslash?

it can be coerced:

postgres=# \copy (
select 0
) to stdout
0

now -- to do that, I had to use the \e command to do it in vi, then
recall the command with readline :-). \copy can work arbitrarily work
or fail in all kinds of ways.

merlin