Re: [BUGS] \copy produces CSV output that cannot be read by \copy

On Thu, Aug 17, 2017 at 01:53:05PM +0900, Michael Paquier wrote:
> On Sat, Aug 5, 2017 at 6:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> >> The format produced by COPY OUT looks fine to me, and can be reloaded
> >> with a plain COPY (not \copy). And you may be interested in this bit
> >> from src/bin/psql/copy.c:
> >> /*
> >> * This code erroneously assumes '\.' on a line alone
> >> * inside a quoted CSV string terminates the \copy.
> >> *
> >> http://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
> >> */
> >
> > I wonder if it would improve matters to check for "\." only when
> > copystream == pset.cur_cmd_source, that is, only when the copy data
> > is inlined into the SQL stream. That would create an inconsistency
> > between inline and out-of-line data, but it might be a reasonable
> > thing to do anyway.
>
> A complete solution would be to look for the quote option provided by
> the user and track if the string being passed to the backend is within
> a quoted area or not, no? If that's a quoted area, the check for "\."
> could be bypassed. Now, as parse_slash_copy() has its own way to parse
> the command options given by the user, perhaps all this extra
> engineering is not worth fixing an edge case.
>
> In short, I agree that what you propose here has value to fix the case
> proposed here, as even COPY FROM stdin (not only \copy) fails now.

This is a six-year-old thread, but I have applied the following doc
patch to at least document this behavior.

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

commit 42d3125ada
Author: Bruce Momjian <bruce(at)momjian(dot)us>
Date: Fri Nov 3 13:57:59 2023 -0400

doc: \copy can get data values \. and end-of-input confused

Reported-by: Svante Richter

Discussion: https://postgr.es/m/fcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com

Backpatch-through: 11

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d94e3cacfc..cc7d797159 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1119,6 +1119,10 @@ INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
destination, because all data must pass through the client/server
connection. For large amounts of data the <acronym>SQL</acronym>
command might be preferable.
+ Also, because of this pass-through method, <literal>\copy
+ ... from</literal> in <acronym>CSV</acronym> mode will erroneously
+ treat a <literal>\.</literal> data value alone on a line as an
+ end-of-input marker.
</para>
</tip>

diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index b3cc3d9a29..dbbbdb8898 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -627,6 +627,8 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
* This code erroneously assumes '\.' on a line alone
* inside a quoted CSV string terminates the \copy.
* https://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
+ *
+ * https://www.postgresql.org/message-id/bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com
*/
if ((linelen == 3 && memcmp(fgresult, "\\.\n", 3) == 0) ||
(linelen == 4 && memcmp(fgresult, "\\.\r\n", 4) == 0))

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.