Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5bpF-00EBvh-TO for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Nov 2023 01:14:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r5bpD-003bbB-8Q for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Nov 2023 01:14:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5bpC-003baG-Vc for pgsql-bugs@lists.postgresql.org; Wed, 22 Nov 2023 01:14:42 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5bpA-007hOy-2c for pgsql-bugs@postgresql.org; Wed, 22 Nov 2023 01:14:42 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us; s=2023062407; h=In-Reply-To:Content-Type:MIME-Version:References:Message-ID: Subject:Cc:To:From:Date:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description; bh=ZG8g+nhm6gwcdf9nb70NPYOFv0BZ0nml03OWmdfLjv0=; b=4gQor Kd+k9h/0oRRJ2Xm2Kz/9emQbXNGyvPGxmK9zRttFEKU+8/JUHOvoHsQVEvySbYOrJ42pvUt1PoaUX 2PWD+OBgC7m9KLvM67FZrWw7EB4bwCpVnuR8DuXfahrqOHMKtaSqdBC/yaFr/bWKz0NLVrYDyqVHo xNQt1/+6x7dJ5Tph1/wphtjYbTYlmLtBwnySo/QKU+G6v4tijX9wrLr2SWSP41VYj0DKuHzji5Dlo LEtPE4wTYwMGHhrEtyCx4yzgX05jrA7vrNA2NIwT4Y6xD90kL8z2GU6edRhVjgRRIChng00z2W9XP 47K2NgYOreGU7PTji6RuEWSoc6mTQ==; Received: from bruce by momjian.us with local (Exim 4.96) (envelope-from ) id 1r5bp8-007twn-1f; Tue, 21 Nov 2023 20:14:38 -0500 Date: Tue, 21 Nov 2023 20:14:38 -0500 From: Bruce Momjian To: Michael Paquier Cc: Tom Lane , Nicolas Barbier , PostgreSQL Bugs Subject: Re: [BUGS] \copy produces CSV output that cannot be read by \copy Message-ID: References: <1473.1501951948@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, Aug 17, 2017 at 01:53:05PM +0900, Michael Paquier wrote: > On Sat, Aug 5, 2017 at 6:52 PM, Tom Lane wrote: > > Michael Paquier 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 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 SQL command might be preferable. + Also, because of this pass-through method, \copy + ... from in CSV mode will erroneously + treat a \. data value alone on a line as an + end-of-input marker. 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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.