Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oNyT1-0008K0-Pu for pgsql-bugs@arkaria.postgresql.org; Tue, 16 Aug 2022 15:26:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oNyT0-0000V1-74 for pgsql-bugs@arkaria.postgresql.org; Tue, 16 Aug 2022 15:26:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oNySz-0000Us-Uk for pgsql-bugs@lists.postgresql.org; Tue, 16 Aug 2022 15:26:53 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oNySx-0002TT-ED for pgsql-bugs@lists.postgresql.org; Tue, 16 Aug 2022 15:26:52 +0000 Received: from bruce by momjian.us with local (Exim 4.94.2) (envelope-from ) id 1oNySt-00EL4q-Ee; Tue, 16 Aug 2022 11:26:47 -0400 Date: Tue, 16 Aug 2022 11:26:47 -0400 From: Bruce Momjian To: Tom Lane Cc: Noah Misch , Svante Richter , pgsql-bugs@lists.postgresql.org Subject: Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM CSV Message-ID: References: <20220814070053.GA92855@rfd.leadboat.com> <624693.1660486135@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <624693.1660486135@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sun, Aug 14, 2022 at 10:08:55AM -0400, Tom Lane wrote: > Noah Misch writes: > > The psql documentation says [\copy ... from stdin] looks for "\.". It says > > nothing about doing that for [\copy ... from filename]. I wonder if psql > > should change the filename case to send the whole file to the server, ignoring > > "\." inside. (That is to say, change to match the psql documentation.) > > This seems like a sensible solution. The need to use an in-band EOF > marker when reading from the command source file is clear, and there's > no non-kluge way there. But that doesn't mean we should extend it > to separate files. (I'm surprised to realize we do, actually.) So, look what I found in psql/copy.c: /* check for EOF marker, but not on a partial line */ if (at_line_begin) { /* --> * 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 */ if ((linelen == 3 && memcmp(fgresult, "\\.\n", 3) == 0) || (linelen == 4 && memcmp(fgresult, "\\.\r\n", 4) == 0)) { copydone = true; } } I wondered who would reference an email thread in the source code, so I looked at the whole 2012 thread: https://www.postgresql.org/message-id/flat/E1TdNVQ-0001ju-GO%40wrigleys.postgresql.org and it was, of course, me. :-( Tom's analysis was similar ten years ago: > Ugh. This seems like a rather fundamental oversight in the CSV feature. > The problem is that psql has no idea whether the copy is being done in > CSV mode or not --- and even if it did, it doesn't parse the data fully > enough to realize whether a \. line is inside quotes or not. > > In the case of out-of-line data files, it might be reasonable to just > dispense with the check for \. altogether and always ship the whole file > to the backend; I think there's a \. check on the backend side. (Not > sure this is safe in V2 protocol, but I doubt anyone cares anymore > about that.) > > In the case of in-line data in a script file, CSV mode seems a bit > broken in any case; there's no concept of a terminator in CSV, AFAIK. > So maybe we don't have to worry about that. I think at this point we should either fix this or document it. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson