Thread: Support UTF-8 files with BOM in COPY FROM
Hi, I'd like to support UTF-8 text or csv files that has BOM (byte order mark) in COPY FROM command. BOM will be automatically detected and ignored if the file encoding is UTF-8. WIP patch attached. I'm thinking about only COPY FROM for reads, but if someone wants to add BOM in COPY TO, we might also support COPY TO WITH BOM for writes. Comments welcome. -- Itagaki Takahiro
Attachment
On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote: > I'd like to support UTF-8 text or csv files that has BOM (byte order mark) > in COPY FROM command. BOM will be automatically detected and ignored > if the file encoding is UTF-8. WIP patch attached. By my reading of http://unicode.org/faq/utf_bom.html#bom5, I'd say +1 So I think what you propose makes sense. > I'm thinking about only COPY FROM for reads, but if someone wants to add > BOM in COPY TO, we might also support COPY TO WITH BOM for writes. I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM." Best, David
On Mon, Sep 26, 2011 at 06:58, Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote: > Hi, > > I'd like to support UTF-8 text or csv files that has BOM (byte order mark) > in COPY FROM command. BOM will be automatically detected and ignored > if the file encoding is UTF-8. WIP patch attached. > > I'm thinking about only COPY FROM for reads, but if someone wants to add > BOM in COPY TO, we might also support COPY TO WITH BOM for writes. > > Comments welcome. I like it in general. But if we're looking at the BOM, shouldn't we also look and *reject* the file if it's a BOM for a non-UTF8 file? Say if the BOM claims it's UTF16? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Mon, Sep 26, 2011 at 20:12, Magnus Hagander <magnus@hagander.net> wrote: > I like it in general. But if we're looking at the BOM, shouldn't we > also look and *reject* the file if it's a BOM for a non-UTF8 file? Say > if the BOM claims it's UTF16? -1 because we're depending on manual configuration for now. It would be reasonable if we had used automatic detection of character encoding, but we don't. In addition, some crazy encoding might use BOM codes as a valid character. -- Itagaki Takahiro
On Mon, Sep 26, 2011 at 13:36, Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote: > On Mon, Sep 26, 2011 at 20:12, Magnus Hagander <magnus@hagander.net> wrote: >> I like it in general. But if we're looking at the BOM, shouldn't we >> also look and *reject* the file if it's a BOM for a non-UTF8 file? Say >> if the BOM claims it's UTF16? > > -1 because we're depending on manual configuration for now. > It would be reasonable if we had used automatic detection of > character encoding, but we don't. In addition, some crazy > encoding might use BOM codes as a valid character. Does such an encoding really exist? And the code only executes when the user thinks he's in UTF8, right? So it would still only happen if the incorrect encoding was specified.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On 09/26/2011 07:12 AM, Magnus Hagander wrote: > On Mon, Sep 26, 2011 at 06:58, Itagaki Takahiro > <itagaki.takahiro@gmail.com> wrote: >> Hi, >> >> I'd like to support UTF-8 text or csv files that has BOM (byte order mark) >> in COPY FROM command. BOM will be automatically detected and ignored >> if the file encoding is UTF-8. WIP patch attached. >> >> I'm thinking about only COPY FROM for reads, but if someone wants to add >> BOM in COPY TO, we might also support COPY TO WITH BOM for writes. >> >> Comments welcome. > I like it in general. But if we're looking at the BOM, shouldn't we > also look and *reject* the file if it's a BOM for a non-UTF8 file? Say > if the BOM claims it's UTF16? > It should be rejected as invalidly encoded anyway, as a non-utf8 BOM is not valid utf-8. We shouldn't check in non-unicode cases where the sequence might be valid in those encodings (e.g. ISO-8859-1). cheers andrew
"David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com> writes:
> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote:
>> I'm thinking about only COPY FROM for reads, but if someone wants to add
>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes.
> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM."
Putting a BOM into UTF8 data is flat out invalid per spec --- the fact
that Microsloth does it does not make it standards-conformant.
I think that accepting it on input can be sensible, on the principle of
"be liberal in what you accept", but the other side of that is "be
conservative in what you send". No BOMs in output, please.
regards, tom lane
> I'd like to support UTF-8 text or csv files that has BOM (byte order mark) > in COPY FROM command. BOM will be automatically detected and ignored > if the file encoding is UTF-8. WIP patch attached. From RFC3629(http://tools.ietf.org/html/rfc3629#section-6): o A protocol SHOULD forbid use of U+FEFF as a signature for those textual protocol elements that the protocol mandates tobe always UTF-8, the signature function being totally useless in those cases. COPY explicitly specifies the encoding (to be UTF-8 in this case). So I think we should not regard U+FEFF as "BOM" in COPY, rather we should regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
> "David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com> writes: >> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote: >>> I'm thinking about only COPY FROM for reads, but if someone wants to add >>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes. > >> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM." > > Putting a BOM into UTF8 data is flat out invalid per spec --- the fact > that Microsloth does it does not make it standards-conformant. > > I think that accepting it on input can be sensible, on the principle of > "be liberal in what you accept", but the other side of that is "be > conservative in what you send". No BOMs in output, please. Suppose a user uses brain-dead editor, which does not accept UTF-8 without BOM. He decides to save his editor data into PostgreSQL using COPY FROM. He extracts the data using COPY TO. Now he finds that his stupid editor does not accept his data any more. So I think if we decide to accept UTF-8 with BOM, we should keep BOM when importing the data and output the data with BOM. If we don't want to output UTF-8 with BOM, we should not accept UTF-8 with BOM. It seems we don't have much choice... -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> "David E. Wheeler" <david@kineticode.com> <CAJW2+qdYg1+xLaHDqnJs3AcKmCSVCDkv_LCAPWUtwmxL9dzVhQ@mail.gmail.com> writes: >>> On Sep 25, 2011, at 9:58 PM, Itagaki Takahiro wrote: >>>> I'm thinking about only COPY FROM for reads, but if someone wants to add >>>> BOM in COPY TO, we might also support COPY TO WITH BOM for writes. >> >>> I think it would have to be optional, since "some recipients of UTF-8 encoded data do not expect a BOM." >> >> Putting a BOM into UTF8 data is flat out invalid per spec --- the fact >> that Microsloth does it does not make it standards-conformant. >> >> I think that accepting it on input can be sensible, on the principle of >> "be liberal in what you accept", but the other side of that is "be >> conservative in what you send". No BOMs in output, please. > > Suppose a user uses brain-dead editor, which does not accept UTF-8 > without BOM. He decides to save his editor data into PostgreSQL using > COPY FROM. He extracts the data using COPY TO. Now he finds that his > stupid editor does not accept his data any more. > > So I think if we decide to accept UTF-8 with BOM, we should keep BOM > when importing the data and output the data with BOM. If we don't want > to output UTF-8 with BOM, we should not accept UTF-8 with BOM. It > seems we don't have much choice... Maybe this needs to be an optional behavior, controlled by some COPY option. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Sep 26, 2011 at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >>> Suppose a user uses brain-dead editor, which does not accept UTF-8 >>> without BOM. > >> Maybe this needs to be an optional behavior, controlled by some COPY option. > > I'm not excited about emitting non-standards-conformant output on the > strength of a hypothetical argument about users and editors that may or > may not exist. I believe that there's a use-case for reading BOMs, but > I have seen no field complaints demonstrating that we need to write > them. Even if we had a couple, "use a less brain dead editor" might be > the best response. We cannot promise to be compatible with arbitrarily > broken software. The thing that makes me doubt that is this comment from Tatsuo Ishii: TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). So TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". If a BOM is confusable with valid data, then I think recognizing it and discarding it unconditionally is no good - you could end up where COPY OUT, TRUNCATE, COPY IN changes the table contents. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Sep 26, 2011 at 11:09 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> Suppose a user uses brain-dead editor, which does not accept UTF-8
>> without BOM.
> Maybe this needs to be an optional behavior, controlled by some COPY option.
I'm not excited about emitting non-standards-conformant output on the
strength of a hypothetical argument about users and editors that may or
may not exist. I believe that there's a use-case for reading BOMs, but
I have seen no field complaints demonstrating that we need to write
them. Even if we had a couple, "use a less brain dead editor" might be
the best response. We cannot promise to be compatible with arbitrarily
broken software.
regards, tom lane
Robert Haas <robertmhaas@gmail.com> writes: > The thing that makes me doubt that is this comment from Tatsuo Ishii: > TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). So > TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should > TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". Yeah, that's a reasonable argument for rejecting the patch altogether. I'm not qualified to decide whether it outweighs the "we need to be able to read Notepad output" argument. I do observe that http://en.wikipedia.org/wiki/Byte_order_mark says Unicode 3.2 has deprecated the no-break-space interpretation, but on the other hand you're right that we can't really assume that the character is not present in people's data. regards, tom lane
On Mon, Sep 26, 2011 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> The thing that makes me doubt that is this comment from Tatsuo Ishii: >> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). So >> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should >> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". > > Yeah, that's a reasonable argument for rejecting the patch altogether. Yeah, or for making the behavior optional. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Sep 26, 2011 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> The thing that makes me doubt that is this comment from Tatsuo Ishii:
>>> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). �So
>>> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should
>>> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE".
>> Yeah, that's a reasonable argument for rejecting the patch altogether.
> Yeah, or for making the behavior optional.
Sorry, I should have been clearer: it's an argument for rejecting *this*
patch. A patch that introduced a "BOM" option for COPY (which logically
could apply just as well to input or output) would be a different patch.
BTW, another issue with the patch-as-proposed is that it assumes,
without even checking, that fseek() will work (for that matter, it would
also fail pretty miserably on a file shorter than 3 bytes). We could
dodge that problem with an option since it would be reasonable to define
the option as meaning that there MUST be a BOM there. I would envision
it as acting much like the CSV HEADER option.
regards, tom lane
On tis, 2011-09-27 at 00:09 +0900, Tatsuo Ishii wrote: > Suppose a user uses brain-dead editor, which does not accept UTF-8 > without BOM. I would first like to see evidence that such an editor exists.
On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote: > The thing that makes me doubt that is this comment from Tatsuo Ishii: > > TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). > So > TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we > should > TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". > > If a BOM is confusable with valid data, then I think recognizing it > and discarding it unconditionally is no good - you could end up where > COPY OUT, TRUNCATE, COPY IN changes the table contents. We did recently accept a patch for psql -f to skip over a UTF-8 byte-order mark. We had a lot of this same discussion there.
On Mon, Sep 26, 2011 at 2:38 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote: >> The thing that makes me doubt that is this comment from Tatsuo Ishii: >> >> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). >> So >> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we >> should >> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". >> >> If a BOM is confusable with valid data, then I think recognizing it >> and discarding it unconditionally is no good - you could end up where >> COPY OUT, TRUNCATE, COPY IN changes the table contents. > > We did recently accept a patch for psql -f to skip over a UTF-8 > byte-order mark. We had a lot of this same discussion there. But that case is different, because zero-width, non-breaking space has no particular meaning in an SQL script - it's either going to be ignored as a BOM, ignored as whitespace, or an error. But inside a file being subjected to COPY it might be confusable with data that the user wanted to end up in some table. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 09/26/2011 02:38 PM, Peter Eisentraut wrote: > On mån, 2011-09-26 at 13:19 -0400, Robert Haas wrote: >> The thing that makes me doubt that is this comment from Tatsuo Ishii: >> >> TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). >> So >> TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we >> should >> TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". >> >> If a BOM is confusable with valid data, then I think recognizing it >> and discarding it unconditionally is no good - you could end up where >> COPY OUT, TRUNCATE, COPY IN changes the table contents. > We did recently accept a patch for psql -f to skip over a UTF-8 > byte-order mark. We had a lot of this same discussion there. > > Yes, but wasn't part of the rationale that this was safe because a leading BOM could not possibly be mistaken for anything else legitimate in an SQL source file? That's quite different from a data file. ISTM. cheers andrew
On mån, 2011-09-26 at 14:44 -0400, Robert Haas wrote: > > We did recently accept a patch for psql -f to skip over a UTF-8 > > byte-order mark. We had a lot of this same discussion there. > > But that case is different, because zero-width, non-breaking space has > no particular meaning in an SQL script - it's either going to be > ignored as a BOM, ignored as whitespace, or an error. But inside a > file being subjected to COPY it might be confusable with data that the > user wanted to end up in some table. Yes, my point was more directed toward the discussion about whether BOM in UTF-8 are valid at all. But your point pretty much kills this altogether. If I store a BOM in row 1, column 1 of my table, because, well, maybe it's an XML document or something, then it needs to be able to survive a copy out and in. The only way we could proceed with this would be if we prohibited BOMs in all user-data.
Tom Lane wrote: > Putting a BOM into UTF8 data is flat out invalid per spec --- the fact > that Microsloth does it does not make it standards-conformant. Could you share a pointer to the spec? All I've ever heard is that a BOM is optional for UTF-8 but not forbidden. The Unicode FAQ (http://unicode.org/faq/utf_bom.html#BOM) states "that some recipients of UTF-8 encoded data do not expect a BOM". Postgres obviously belongs to those recipients. That's why all my psql-scripts transferring data from MSSQL to Postgres need a '\! perl -CD -pi.orig -e "tr/\x{feff}//d" "C:/datafile.txt"' before feeding data into COPY TO. Reading it tolerantly and writing it on user request is probably the way that would help most users. Regards, Brar
Tom Lane wrote:
> Yeah, that's a reasonable argument for rejecting the patch altogether.
> I'm not qualified to decide whether it outweighs the "we need to be able
> to read Notepad output" argument.
Actually it's not only notepad.
I quite often find myself doing something like the following when moving
data from MSSQL to PostgreSQL.
\echo Fetching data for table "patient"
\! sqlcmd -S DBSERVER -d DATABASE -E -f 65001 -o "C:/datafile.txt" -h -1
-W -s "|" -Q "SET NOCOUNT ON; SELECT * FROM my_table;"
\! perl -CD -pi.orig -e "tr/\x{feff}//d" "C:/datafile.txt"
\echo Importing data into table "patient"
\copy my_table FROM 'C:/datafile.txt' WITH DELIMITER '|' NULL 'NULL'
Regards,
Brar
Robert Haas wrote: > The thing that makes me doubt that is this comment from Tatsuo Ishii: > > TI> COPY explicitly specifies the encoding (to be UTF-8 in this case). So > TI> I think we should not regard U+FEFF as "BOM" in COPY, rather we should > TI> regard U+FEFF as "ZERO WIDTH NO-BREAK SPACE". > > If a BOM is confusable with valid data, then I think recognizing it > and discarding it unconditionally is no good - you could end up where > COPY OUT, TRUNCATE, COPY IN changes the table contents. Citing from the Unicode FAQ again: Q: Where is a BOM useful? A: A BOM is useful at the beginning of files that are typed as text, but for which it is not known whether they are in big or little endian format—it can also serve as a hint indicating that the file is in Unicode, as opposed to in a legacy encoding and furthermore, it act as a signature for the specific encoding form used. I think that the major hint in the answer is "beginning of files". To correctly handle a BOM you need to be sure to be in the context of files that have defined bounds (especially a *beginning*) you can't properly handle a BOM in arbitrary streams. Regards, Brar
Brar Piening <brar@gmx.de> writes:
> Citing from the Unicode FAQ again:
> Q: Where is a BOM useful?
> A: A BOM is useful at the beginning of files that are typed as text, but
> for which it is not known whether they are in big or little endian
> format�it can also serve as a hint indicating that the file is in
> Unicode, as opposed to in a legacy encoding and furthermore, it act as a
> signature for the specific encoding form used.
Note that the reference to byte order betrays the implicit context
assumption: that we're talking about UTF16 or UTF32 representation.
A BOM in UTF8 data is useless for its intended purpose of disambiguating
byte order. It could possibly be useful for telling UTF8 data apart
from non-UTF8 data, except for the inconvenient fact that that byte
sequence is not invalid data in non-UTF8 encodings.
BOM is useless in UTF8, no matter what Microsoft thinks. Any tool that
relies on it to detect UTF8 data has to have a workaround for overriding
that detection, or it's broken to the point of uselessness.
regards, tom lane
<span id="IDstID">Tom Lane wrote:</span><blockquote cite="mid:29877.1317066533@sss.pgh.pa.us" type="cite"><pre wrap=""> Note that the reference to byte order betrays the implicit context assumption: that we're talking about UTF16 or UTF32 representation.</pre></blockquote> Note that there is no implicit contextassumption in the Unicode FAQ. It's equally covering UTF-8, UTF-16 and UTF-32.<br /> Another quote:<br /> Q: Can aUTF-8 data stream contain the BOM character (in UTF-8 form)? If yes, then can I still assume the remaining UTF-8 bytes arein big-endian order?<br /> A: Yes, UTF-8 can contain a BOM. However, it makes <i>no</i> difference as to the endiannessof the byte stream. UTF-8 always has the same byte order. An initial BOM is <i>only</i> used as a signature — anindication that an otherwise unmarked text file is in UTF-8. Note that some recipients of UTF-8 encoded data do not expecta BOM. Where UTF-8 is used<i> transparently</i> in 8-bit environments, the use of a BOM will interfere with any protocolor file format that expects specific ASCII characters at the beginning, such as the use of "#!" of at the beginningof Unix shell scripts.<br /><blockquote cite="mid:29877.1317066533@sss.pgh.pa.us" type="cite"><pre wrap=""> BOM is useless in UTF8, no matter what Microsoft thinks. Any tool that relies on it to detect UTF8 data has to have a workaround for overriding that detection, or it's broken to the point of uselessness.</pre></blockquote> This kind of brokenness is currently existingthe other way around (see my reference to the perl script I' using to work aound it).<br /><br /> Note also thatI'm not citing a Microsoft FAQ but the Unicode FAQ.<br /> I'm also not trying to convert Postgres into a Microsoft tool(I'm pretty happy it isn't) but I'm pointing to existing compatibility issues on a Platform that others have decidedto support.<br /> Belonging to the huge group of users who have little or no choice in what OS they are using andbeing from a country where plain ASCII isn't enough to cover all existing characters this is probably fair.<br /><br />It's a pity that the Unicode standard actually allows something that can cause problems but blaming the non-platform againdoesn't solve the existing issues.<br /><br /> Regards,<br /><br /> Brar<br />
Brar Piening wrote: > > It's a pity that the Unicode standard actually allows something that > can cause problems but blaming the non-platform again doesn't solve > the existing issues. To put in a more humoruos but actually correct way: M$ has found a standard conforming way of preventing users to migrate data from MSSQL to PostgreSQL. Do you want to work arond it? Regards, Brar
On mån, 2011-09-26 at 21:49 +0300, Peter Eisentraut wrote: > If I store a BOM in row 1, column 1 of my table, because, > well, maybe it's an XML document or something, then it needs to be > able to survive a copy out and in. The only way we could proceed with > this would be if we prohibited BOMs in all user-data. Alternative consideration: We could allow this in CSV format if we made users quote the first value if it starts with a BOM. This might be a reasonable way to get MS compatibility.
Peter Eisentraut <peter_e@gmx.net> writes:
> Alternative consideration: We could allow this in CSV format if we made
> users quote the first value if it starts with a BOM. This might be a
> reasonable way to get MS compatibility.
I don't think we can get away with a retroactive restriction on the
contents of data files.
If we're going to do this at all, I still think an explicit BOM option
for COPY, to either eat (and require) a BOM on input or emit a BOM on
output, would be the sanest way. None of the "automatic" approaches
seem safe to me.
regards, tom lane