pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed\copy produces CSV output that cannot be read by \copy
5+ messages / 4 participants
[nested] [flat]
* \copy produces CSV output that cannot be read by \copy
@ 2017-08-04 12:38 Nicolas Barbier <nicolas.barbier@gmail.com>
2017-08-05 16:39 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nicolas Barbier @ 2017-08-04 12:38 UTC (permalink / raw)
To: pgsql-bugs
Description: If the end-of-data marker \. occurs on a line of its own in a
multiline string value, \copy outputs it to CSV without any quoting.
Subsequently reading that CSV file using \copy fails.
Version:
$ psql
psql (9.5.7)
Type "help" for help.
test=# select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-16)
6.3.0 20170425, 64-bit
(1 row)
How to reproduce:
CREATE TABLE test (t varchar);
INSERT INTO test (t) VALUES ('
\.
');
\copy (SELECT t FROM test) TO 'test.csv' WITH CSV;
\copy test FROM 'test.csv' WITH (FORMAT CSV);
This results in:
ERROR: unterminated CSV quoted field
CONTEXT: COPY test, line 1: ""
"
Expected result: \copy can read the files it produced itself.
Analysis: I think that neither the writing nor the reading is incompatible
with the documentation. Rather, there doesn’t seem to be a way to write
such values to CSV that will be read back correctly (or at least I couldn‘t
find such a way), as the reader always stops when encountering \. on a line
by itself, and because there doesn’t seem to be a way to escape such a
value in a way that wouldn’t produce a \. on a line by itself.
Nicolas
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: \copy produces CSV output that cannot be read by \copy
2017-08-04 12:38 \copy produces CSV output that cannot be read by \copy Nicolas Barbier <nicolas.barbier@gmail.com>
@ 2017-08-05 16:39 ` Michael Paquier <michael.paquier@gmail.com>
2017-08-05 16:52 ` Re: \copy produces CSV output that cannot be read by \copy Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Paquier @ 2017-08-05 16:39 UTC (permalink / raw)
To: Nicolas Barbier <nicolas.barbier@gmail.com>; +Cc: pgsql-bugs
On Fri, Aug 4, 2017 at 2:38 PM, Nicolas Barbier
<nicolas.barbier@gmail.com> wrote:
> Analysis: I think that neither the writing nor the reading is incompatible
> with the documentation. Rather, there doesn’t seem to be a way to write such
> values to CSV that will be read back correctly (or at least I couldn‘t find
> such a way), as the reader always stops when encountering \. on a line by
> itself, and because there doesn’t seem to be a way to escape such a value in
> a way that wouldn’t produce a \. on a line by itself.
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
*/
if (strcmp(buf, "\\.\n") == 0 ||
strcmp(buf, "\\.\r\n") == 0)
{
copydone = true;
break;
}
postgresql.org is offline now, and I don't have this thread at hand,
but I guess that the answer is there...
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: \copy produces CSV output that cannot be read by \copy
2017-08-04 12:38 \copy produces CSV output that cannot be read by \copy Nicolas Barbier <nicolas.barbier@gmail.com>
2017-08-05 16:39 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
@ 2017-08-05 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-08-17 04:53 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2017-08-05 16:52 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Nicolas Barbier <nicolas.barbier@gmail.com>; pgsql-bugs
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.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: \copy produces CSV output that cannot be read by \copy
2017-08-04 12:38 \copy produces CSV output that cannot be read by \copy Nicolas Barbier <nicolas.barbier@gmail.com>
2017-08-05 16:39 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
2017-08-05 16:52 ` Re: \copy produces CSV output that cannot be read by \copy Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-08-17 04:53 ` Michael Paquier <michael.paquier@gmail.com>
2023-11-22 01:14 ` Re: [BUGS] \copy produces CSV output that cannot be read by \copy Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Paquier @ 2017-08-17 04:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Nicolas Barbier <nicolas.barbier@gmail.com>; pgsql-bugs
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.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [BUGS] \copy produces CSV output that cannot be read by \copy
2017-08-04 12:38 \copy produces CSV output that cannot be read by \copy Nicolas Barbier <nicolas.barbier@gmail.com>
2017-08-05 16:39 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
2017-08-05 16:52 ` Re: \copy produces CSV output that cannot be read by \copy Tom Lane <tgl@sss.pgh.pa.us>
2017-08-17 04:53 ` Re: \copy produces CSV output that cannot be read by \copy Michael Paquier <michael.paquier@gmail.com>
@ 2023-11-22 01:14 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2023-11-22 01:14 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Nicolas Barbier <nicolas.barbier@gmail.com>; pgsql-bugs
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.patch
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))
end of thread, other threads:[~2023-11-22 01:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-04 12:38 \copy produces CSV output that cannot be read by \copy Nicolas Barbier <nicolas.barbier@gmail.com>
2017-08-05 16:39 ` Michael Paquier <michael.paquier@gmail.com>
2017-08-05 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-08-17 04:53 ` Michael Paquier <michael.paquier@gmail.com>
2023-11-22 01:14 ` Bruce Momjian <bruce@momjian.us>
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