From: Bruce Momjian <bruce@momjian.us>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nicolas Barbier <nicolas.barbier@gmail.com>
Cc: PostgreSQL Bugs <pgsql-bugs@postgresql.org>
Subject: Re: [BUGS] \copy produces CSV output that cannot be read by \copy
Date: Tue, 21 Nov 2023 20:14:38 -0500
Message-ID: <ZV1V_ibI-fxuKpfp@momjian.us> (raw)
In-Reply-To: <CAB7nPqStSFuX8pev3F=saMy8iG3U9WgB3a+jG0oDWWiERO0xMA@mail.gmail.com>
References: <CAP-rdTbO35=U0+2Dn-i9FAYvau7Lm6Athd29KZ4sS+kDs21tLg@mail.gmail.com>
<CAB7nPqQvFQz=C2q8aTs5YGRe==796EwnjcmjXmW4J4McZUF8Ow@mail.gmail.com>
<1473.1501951948@sss.pgh.pa.us>
<CAB7nPqStSFuX8pev3F=saMy8iG3U9WgB3a+jG0oDWWiERO0xMA@mail.gmail.com>
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@sss.pgh.pa.us> wrote:
> > Michael Paquier <michael.paquier@gmail.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@momjian.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
inline patch 1: doc/src/sgml/ref/psql-ref.sgml, src/bin/psql/copy.c (+6/-0 lines) download inline-1.patchdiff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgmlindex 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.cindex 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@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, michael.paquier@gmail.com, tgl@sss.pgh.pa.us, nicolas.barbier@gmail.com
Subject: Re: [BUGS] \copy produces CSV output that cannot be read by \copy
In-Reply-To: <ZV1V_ibI-fxuKpfp@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox