Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM CSV

On Tue, Aug 16, 2022 at 11:26:47AM -0400, Bruce Momjian wrote:
> On Sun, Aug 14, 2022 at 10:08:55AM -0400, Tom Lane wrote:
> > Noah Misch <noah(at)leadboat(dot)com> 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.)

> 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.)

> I think at this point we should either fix this or document it.

Things have gotten simpler in the last ten years, specifically commit 3174d69
removing protocol v2. Before that, the server would have mishandled "\." even
if the client didn't. These days, one can fix all but [\copy ... from stdin].