Re: [BUGS] \copy produces CSV output that cannot be read by \copy - Mailing list 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

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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.