Re: Alternative to \copy in psql modelled after \g

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Fabien COELHO" <coelho(at)cri(dot)ensmp(dot)fr>
Cc: "PostgreSQL Hackers" <pgsql-hackers(at)lists(dot)postgresql(dot)org>,"David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Subject: Re: Alternative to \copy in psql modelled after \g
Date: 2018-12-26 12:28:06
Message-ID: 78364083-1d62-4abb-adb9-ae1827f9be90@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Fabien COELHO wrote:

> Is this just kind of a bug fix? Beforehand the documentation says "\g fn"
> sends to file, but that was not happening with COPY, and now it does as it
> says?

Yes. The doc says about \g:

Sends the current query buffer to the server for execution. If an
argument is given, the query's output is written to the named file
or piped to the given shell command, instead of displaying it as
usual.

It does not add "unless the query is a COPY", so it seems right
to make that just work, and call it a bug fix.

> A question: if opening the output file fails, should not the query
> be cancelled and an error be reported? Maybe it is too late for
> that. It seems that "SELECT pg_sleep(10) \g /bad/file" fails but
> executes the query nevertheless.

Yes. This part works as documented:

"The file or command is written to only if the query successfully
returns zero or more tuples, not if the query fails or is a
non-data-returning SQL command."

> However, it does not contain any tests (bad:-)

Testing this requires at least some interaction with the OS which I'm
uncomfortable to add. There is a precedent in
regress/sql/hs_standby_allowed.sql doing:

COPY hs1 TO '/tmp/copy_test'
\! cat /tmp/copy_test

We could add something like that in psql.sql, but I'm not fond of it
because it assumes a Unix-ish environment, and that it's OK to clobber
the hardcoded /tmp/copy_test should it preexist. I'd rather work with
a dedicated temporary directory. On Linux mktemp -d could be used, but
I don't know about a portable solution, plus POSIX has deprecated
mktemp.
I'm open to ideas on a portable way for psql.sql to test \g writing to a
file or a program, but ATM my inclination is to not add that test.

> nor documentation (hmmm... maybe none needed, though).

\copy has this paragraph:

"The syntax of this command is similar to that of the SQL COPY
command. All options other than the data source/destination are as
specified for COPY. Because of this, special parsing rules apply to
the \copy meta-command. Unlike most other meta-commands, the entire
remainder of the line is always taken to be the arguments of \copy,
and neither variable interpolation nor backquote expansion are
performed in the arguments".

We could add that COPY TO STDOUT with a redirection might be used as an
alternative. The downside is that with four paragraphs plus one tip,
the explanations on \copy give already a lot to chew on, so is it
worth to add more?

> ISTM that overriding copystream on open failures is not necessary, because
> its value is already NULL in this case.
>
> I'd suggest that is_pipe should be initialized to false, otherwise it is
> unclear from the code when it is set before use, and some compilers may
> complain.

OK, will consider these in the next revision.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2018-12-26 12:32:56 Re: chained transactions
Previous Message Magnus Hagander 2018-12-26 11:54:37 Re: CF app feature request