Re: backslash-dot quoting in COPY CSV

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Michael Paquier" <michael(at)paquier(dot)xyz>
Cc: "Bruce Momjian" <bruce(at)momjian(dot)us>,pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: backslash-dot quoting in COPY CSV
Date: 2019-01-28 15:06:17
Message-ID: bca0a11c-ca0d-413e-bf7e-304f31bba1a8@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Michael Paquier wrote:

> In src/bin/psql/copy.c, handleCopyIn():
>
> /*
> * 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;
> }

Indeed, it's exactly that problem.
And there's the related problem that it derails the input stream
in a way that lines of data become commands, but that one is
not specific to that particular error.

For the backslash-dot in a quoted string, the root cause is
that psql is not aware that the contents are CSV so it can't
parse them properly.
I can think of several ways of working around that, more or less
inelegant:

- the end of data could be expressed as a length (in number of lines
for instance) instead of an in-data marker.

- the end of data could be configurable, as in the MIME structure of
multipart mail messages, where a part is ended by a "boundary",
line, generally a long randomly generated string. This boundary
would have to be known to psql through setting a dedicated
variable or command.

- COPY as the SQL command could have the boundary option
for data fed through its STDIN. This could neutralize the
special role of backslash-dot in general, not just in quoted fields,
since the necessity to quote backslash-dot is a wart anyway.

- psql could be told somehow that the next piece of inline data is in
the CSV format, and then pass it through a CSV parser.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jesper Pedersen 2019-01-28 15:15:34 Re: pg_upgrade: Pass -j down to vacuumdb
Previous Message Dmitry Dolgov 2019-01-28 14:55:39 Re: Pluggable Storage - Andres's take