Re: [HACKERS] UTF8 with BOM support in psql
On mån, 2009-11-16 at 22:37 +0200, Peter Eisentraut wrote: On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: Sure. Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. I have committed a change that implements the above. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On ons, 2009-11-18 at 12:52 +0900, Itagaki Takahiro wrote: Peter Eisentraut pete...@gmx.net wrote: Together, that should cover a lot of cases. Not perfect, but far from useless. For Japanese users on Windows, the client encoding are always set to SJIS because of the restriction of cmd.exe. But the script file can be written in UTF8 with BOM. I don't think we should depend on client encoding. Set by whom, how, and because of what restriction? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On tis, 2009-11-17 at 23:22 -0500, Andrew Dunstan wrote: Itagaki Takahiro wrote: I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. That's what we require in all other cases. Why should UTF8 be special? But now we're back to the original problem. Certain editors insert BOMs at the beginning of the file. And that is by any definition before the embedded client encoding declaration. I think the only ways to solve this are: 1) Ignore the BOM if a client encoding declaration of UTF8 appears in a narrowly defined location near the beginning of the file (XML and PEP-0263 style). For *example*, we could ignore the BOM if the file starts with exactly BOM\encoding UTF8\n. Would probably not work well in practice. 2) Parse two alternative versions of the file, one with the BOM ignored and one with the BOM not ignored, until you need to make a decision. Hilariously complicated, but would perhaps solve the problem. 3) Give up, do nothing. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut wrote: But now we're back to the original problem. Certain editors insert BOMs at the beginning of the file. And that is by any definition before the embedded client encoding declaration. I think the only ways to solve this are: 1) Ignore the BOM if a client encoding declaration of UTF8 appears in a narrowly defined location near the beginning of the file (XML and PEP-0263 style). For *example*, we could ignore the BOM if the file starts with exactly BOM\encoding UTF8\n. Would probably not work well in practice. 2) Parse two alternative versions of the file, one with the BOM ignored and one with the BOM not ignored, until you need to make a decision. Hilariously complicated, but would perhaps solve the problem. 3) Give up, do nothing. 4) set the client encoding before the file is read in any of the ways that have already been discussed and then allow psql to eat the BOM. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On ons, 2009-11-18 at 08:52 -0500, Andrew Dunstan wrote: 4) set the client encoding before the file is read in any of the ways that have already been discussed and then allow psql to eat the BOM. This is certainly a workaround, just like piping the file through a suitable sed expression would be, but conceptually, the client encoding is a property of the file and should therefore be marked in the file. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net writes: This is certainly a workaround, just like piping the file through a suitable sed expression would be, but conceptually, the client encoding is a property of the file and should therefore be marked in the file. In a perfect world things would be like that, but the world is imperfect. When only one of the available encodings even pretends to have a marking convention, and even that one convention is broken, imagining that you can fix it is just a recipe for making things worse. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
I don't know what the best solution is here. The BOM encoded as UTF-8 is valid data in other encodings. Of course, there is your point that such data cannot be at the start of an SQL command. Is the UTF-8 BOM ( EF BB BF ) actually valid data in any other multi-byte encoding (other than it's intended use in UTF-8)? I realize that for single-byte encoding, such as latin-1, it would be legal as data, since any bytes other that 00 are legal, although never legal outside a quoted string in a SQL command or psql command. Certainly, no psql command input file can start with those bytes, or you would get an error (unless it is changed so the BOM is ignored). As to zero-width non-breaking space: the BOM is supposed to be treated as such if in the middle of a string, but if it is the start, it is just the BOM, and isn't considered part of the data, if I'm reading the spec right. Perhaps the lexers should allow for it as white space (along with other Unicode space characters, such as U+2060). It's not really important, since allowing the BOM sequence in the middle of a file is deprecated according to the Unicode standard. And what if you see a real BOM, FF FE or FE FF or FF FE 00 00 or 00 00 FE FF? Give an error saying UTF-16 and UTF-32 are not supported? Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). If we accept UTF-8 BOM, we should at least detect the other BOM sequences and give an error or warning. Overall, from my user point of view, having psql deal with the BOM (at least the utf-8 one) would be more friendly than current behavior, as some editors (notepad for example) automatically add the BOM to the beginning of Unicode files, and it's not obvious without dumping the file in hex. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro wrote: Multi-byte scripts without encoding are always dangerous whether BOM is present or not. I'd say we can always throw an error when we find queries that contain multi-byte characters if no prior encoding declaration. You will break a gazillion scripts that today work quite happily if you do. I think you have really not thought out these proposals well. Maybe there is a case for a extra command line switch to set the initial client encoding for psql, which would make that a little easier and less obscure to do. Would that make things simpler for you? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net writes: I think I could support using the presence of the BOM as a fall-back indicator of encoding in absence of any other declaration. It seems to me, however, that the description above ignores the existence of encodings other than SQL_ASCII and UTF8. Yeah. This entire proposal rests on the assumption that UTF8 is the only encoding that really matters, and introducing a possibility of breaking things for users of other encodings is acceptable damage. I do not think that supporting a deprecated-by-standards behavior is worth that. Even assuming that we had consensus on a behavior that involved silently changing client_encoding, I do not believe that it's practical to implement it in an acceptable fashion. Just issuing a SET behind the user's back will not work in a number of scenarios: * We are inside a transaction when \i is called, and the file contains a ROLLBACK. * We are inside a failed transaction when \i is called --- the SET won't even work at all. * Same two cases inside a savepoint. * The file contains a \c command. If you expect that the previous client_encoding should be restored at the end of the \i inclusion (as I certainly would) then you have the first three hazards at file end as well, except that now the odds of being inside a failed transaction are significantly higher. Also, what if the file contained a SET CLIENT_ENCODING command itself? How should that interact with this? Lastly, a silent change of client_encoding would also affect the encoding of notice and error messages that come out while the \i file is running. I fail to find that non-astonishing, either. I think that the only way this sort of behavior could be implemented without a bunch of broken corner cases would be if we put the responsibility of encoding conversion inside psql, so that switching its idea of the encoding was just a local change rather than something it had to ask the backend to do, and it could be careful to apply the encoding only to the data coming from the \i file. Which is possible, perhaps, but it hardly seems that slightly-more-convenient BOM handling is worth it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On tis, 2009-11-17 at 09:31 +0900, Itagaki Takahiro wrote: Peter Eisentraut pete...@gmx.net wrote: OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that Are they AND condition? If so, this patch will be useless. Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. Presumably, if you have editors throwing in BOM marks without asking, you have an environment where either a) You can set the client encoding to UTF8 in the environment, so it applies by default, or b) The server encoding is UTF8, so the client encoding will default to that. Together, that should cover a lot of cases. Not perfect, but far from useless. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
-Original Message- From: Peter Eisentraut [mailto:pete...@gmx.net] Sent: Tuesday, November 17, 2009 9:05 AM To: Chuck McDevitt Cc: Itagaki Takahiro; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] UTF8 with BOM support in psql On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Certainly that would be nice, given that UTF-16 is the native encoding for Java, C#, Visual Basic.net, JDBC, ODBC drivers = ver 3.5, Microsoft Windows (all system calls use UTF-16, with a compatibility layer for old apps), and apps that Postgres users might switch from, such as MS SQLServer. But for the short term, a warning or error saying we don't support it is better than a confusing lexer error or syntax error. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut wrote: On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
-Original Message- From: Andrew Dunstan [mailto:and...@dunslane.net] Sent: Tuesday, November 17, 2009 9:15 AM To: Peter Eisentraut Cc: Chuck McDevitt; Itagaki Takahiro; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] UTF8 with BOM support in psql Peter Eisentraut wrote: On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? Not really... You can't treat UTF-16 the same way you do UTF-8, but we are talking about it being a client_encoding, not a server_encoding. So, it's only the routines that look at the strings pre-conversion, and the conversion routines themselves, that need to understand UTF-16 strings are 16-bits at a time, and end with a 16 bit 0x. Obviously, it's more work than handing another 8-bit client_encoding, but doesn't seem insurmountable. And given the 1:1 mapping from UTF-16 to UTF-8, you don't have any new issues due to characters that can't be converted. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net writes: Peter Eisentraut wrote: Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? Just about impossible. It would require a protocol bump, and removal of C-style string usage *everywhere* on the client side. Again, this is something that might be more feasible with encoding conversion inside psql --- translating UTF16 to UTF8 immediately upon reading it from any external file would confine the problem to possibly manageable bounds. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Peter Eisentraut wrote: Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? Just about impossible. It would require a protocol bump, and removal of C-style string usage *everywhere* on the client side. Again, this is something that might be more feasible with encoding conversion inside psql --- translating UTF16 to UTF8 immediately upon reading it from any external file would confine the problem to possibly manageable bounds. Well, it might be a good idea to provide at least some support in libpq. Making each client do it from scratch seems a bit inefficient. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net writes: Well, it might be a good idea to provide at least some support in libpq. Making each client do it from scratch seems a bit inefficient. Encoding conversion seems far outside libpq's charter, and as for from scratch there are other libraries for that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net wrote: Together, that should cover a lot of cases. Not perfect, but far from useless. For Japanese users on Windows, the client encoding are always set to SJIS because of the restriction of cmd.exe. But the script file can be written in UTF8 with BOM. I don't think we should depend on client encoding. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net wrote: Itagaki Takahiro wrote: Multi-byte scripts without encoding are always dangerous whether BOM is present or not. I'd say we can always throw an error when we find queries that contain multi-byte characters if no prior encoding declaration. You will break a gazillion scripts that today work quite happily if you do. Sure. That's why I didn't send a patch for it :) If by any chance we do so, we'll have a boolean option to disable the check. Maybe there is a case for a extra command line switch to set the initial client encoding for psql, which would make that a little easier and less obscure to do. Would that make things simpler for you? No. There are complex reasons on Windows in Japan. The client encoding is always SJIS because of Windows restriction, but the database is initialized with UTF8. Simple interactive works with psql are done under SJIS encoding, but some scripts are written in UTF8 because it matches the server encoding. (Of course the script is executed as psql -f utf8.sql out.txt) I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro wrote: I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. That's what we require in all other cases. Why should UTF8 be special? If I have a script in Latin1 and Postgres thinks it's UTF8 it will probably explode. Same for the reverse situation. Second-guessing the user strikes me as being quite as dangerous as what you're trying to cure, for all the reasons Tom outline earlier today. What is more, you will teach Windows users to rely on the client encoding being set in UTF8 scripts without their doing anything, and then when they get on another platform they will not understand why it doesn't work because the BOMs will be missing. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net wrote: Itagaki Takahiro wrote: I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. That's what we require in all other cases. Why should UTF8 be special? No. I didn't think about UTF-8 nor BOM in that point. I assumed we are discussing the following line: I'd say we can always throw an error when we find queries that contain multi-byte characters if no prior encoding declaration. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: Sure. Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net writes: I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. So wouldn't it be better to remove the three bytes, rather than replace with spaces? The latter will certainly confuse clients that think that column 1 means what they think is the first character. A syntax error in the first line of the file should be sufficient to demonstrate the issue. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net wrote: OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that Are they AND condition? If so, this patch will be useless. Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, like: Defining Python Source Code Encodings: http://www.python.org/dev/peps/pep-0263/ I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. I assumed psql discards whitespaces automatically, but I see it is more robust to remove BOM bytes explitly. I'll fix it. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, As has been stated multiple times, that will not get accepted, because it will *break* files in other encodings that chance to match the BOM pattern. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro wrote: Peter Eisentraut pete...@gmx.net wrote: OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that Are they AND condition? If so, this patch will be useless. Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, like: Defining Python Source Code Encodings: http://www.python.org/dev/peps/pep-0263/ As previously discussed we should not be automagically setting the client encoding, nor inferring it from the presence of a BOM. As for when it can be set, unless I'm mistaken you should be able to set it before any file is opened, if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. Of course, if the server encoding is utf8 then, in the absence of it being set using those methods, the client encoding will start as utf8 also. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net writes: As for when it can be set, unless I'm mistaken you should be able to set it before any file is opened, if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. Of course, if the server encoding is utf8 then, in the absence of it being set using those methods, the client encoding will start as utf8 also. It could also be set in ~/.psqlrc, which would probably be the most convenient method for regular users of UTF8 files who need to talk to non-UTF8 databases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. It could also be set in ~/.psqlrc, which would probably be the most convenient method for regular users of UTF8 files who need to talk to non-UTF8 databases. It's nonsense. Users often use scripts written in difference encodings at once. Encoding information should be packed in script file itself. We should not force users to open script files and check its encoding before they execute the files. BTW, I have an idea to improve handling of per-file encoding. We continue to use the encoding settings specified in included file at \i command. But should the setting be reverted at the end of file? ie. =# \encoding SJIS =# \i script-in-utf8.sql =# -- encoding should be SJIS here. If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. This isn't going to happen, so please stop wasting our time arguing about it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Tom Lane t...@sss.pgh.pa.us wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. This isn't going to happen, so please stop wasting our time arguing about it. Ok, sorry. But I still cannot accept this restriction. - Only when client encoding is UTF-8 -- please fix that The attachd patch is a new proposal of the feature. When we found BOM at beginning of file, set expected_encoding to UTF8. Before every execusion of query, if pset.encoding is not UTF8, we check the query string not to contain any non-ASCII characters and throw an error if found. Encoding declarations are typically written only in ascii characters, so we can postpone encoding checking until non-ascii characters appear. Since the default value of expected_encoding is SQL_ASCII, that pass through all characters, so the patch does nothing to scripts without BOM. (There are no codes to set expected_encoding except BOM.) If client encoding is UTF8, it skips BOM and no effect to the script body. BOMs are skipped even if client encoding is not set to UTF8, but can throw an error if there are no explicit encoding declaration. AFAIC, the patch can solve the almost problems in the discussions developmentally. Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center psql-utf8bom_20091117.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On tis, 2009-11-17 at 14:19 +0900, Itagaki Takahiro wrote: The attachd patch is a new proposal of the feature. When we found BOM at beginning of file, set expected_encoding to UTF8. Before every execusion of query, if pset.encoding is not UTF8, we check the query string not to contain any non-ASCII characters and throw an error if found. Encoding declarations are typically written only in ascii characters, so we can postpone encoding checking until non-ascii characters appear. Since the default value of expected_encoding is SQL_ASCII, that pass through all characters, so the patch does nothing to scripts without BOM. (There are no codes to set expected_encoding except BOM.) If client encoding is UTF8, it skips BOM and no effect to the script body. BOMs are skipped even if client encoding is not set to UTF8, but can throw an error if there are no explicit encoding declaration. I think I could support using the presence of the BOM as a fall-back indicator of encoding in absence of any other declaration. It seems to me, however, that the description above ignores the existence of encodings other than SQL_ASCII and UTF8. Also, when the proposed patch to set the encoding from the locale appears, we need to make this logic more precise. Something like: 1. set client_encoding or \encoding, otherwise 2. if BOM found, then UTF8, otherwise 3. by locale environment, otherwise 4. SQL_ASCII (= server encoding, effectively) Also, I'm not sure if we need this logic only when we send a query. It might be better to do this in the lexer when we find a non-ASCII character and we don't have a client encoding != SQL_ASCII set yet. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net wrote: I think I could support using the presence of the BOM as a fall-back indicator of encoding in absence of any other declaration. What is the difference the fall-back and set client encoding to UTF-8 if BOM found ? I read this discussion that we cannot accept any automatic encoding detections (properly speaking, detection is ok, but automatic assignment is not). We should not have any fall-back mechanism, no? Also, when the proposed patch to set the encoding from the locale appears, we need to make this logic more precise. Encoding-from-locale feature will be useful, but the patch does *not* set any encodings. The reason is same as above. Also, I'm not sure if we need this logic only when we send a query. It might be better to do this in the lexer when we find a non-ASCII character and we don't have a client encoding != SQL_ASCII set yet. Absolutely, but is it an indepedent issue from BOM? Multi-byte scripts without encoding are always dangerous whether BOM is present or not. I'd say we can always throw an error when we find queries that contain multi-byte characters if no prior encoding declaration. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. I don't know what the best solution is here. The BOM encoded as UTF-8 is valid data in other encodings. Of course, there is your point that such data cannot be at the start of an SQL command. There is also the notion of how files are handled on Unix. Normally, you'd assume that all of psql -f file.sql psql file.sql cat file.sql | psql cat file1.sql file2.sql | psql behave consistently. That would require that the BOM is ignored in the middle of the data stream (which is legal and required per Unicode standard) and that this only happens if the character set is actually Unicode. Any ideas? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut wrote: On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. I don't know what the best solution is here. The BOM encoded as UTF-8 is valid data in other encodings. Of course, there is your point that such data cannot be at the start of an SQL command. There is also the notion of how files are handled on Unix. Normally, you'd assume that all of psql -f file.sql psql file.sql cat file.sql | psql cat file1.sql file2.sql | psql behave consistently. That would require that the BOM is ignored in the middle of the data stream (which is legal and required per Unicode standard) and that this only happens if the character set is actually Unicode. Cases 2 and 3 should be indistinguishable from psql's POV, although case 3 wins a Useless Use of cat award. If we are only eating a BOM at the start of a file, which was the consensus IIRC, and we treat STDIN as a file for this purpose, then we would eat the leading BOM on file.sql and file1.sql in all the cases above but not on file2.sql since we would not have any idea where the file boundary was. That last case strikes me as a not very likely usage (I'm pretty sure I've never used it, at least). A file containing: \i file1.sql \i file2.sql would be the workaround if needed. As for handling the fact that client encoding can't be set in a script until after the leading BOM, there is always PGOPTIONS=-c client_encoding=UTF8 or similar. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: So, we should always ignore BOM sequence at the file head no matter what client encoding is used. I think we can't do that. That byte sequence might be valid user data in other encodings. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On Wed, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. I feel that psql is the wrong place to fix this. BOMs in UTF-8 should be ignored everywhere, all the time. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut wrote: On Wed, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. I feel that psql is the wrong place to fix this. BOMs in UTF-8 should be ignored everywhere, all the time. I suggest you re-read the Unicode FAQ on the subject. That is not the conclusion I came to after I read it. Quite the reverse in fact. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Bruce Momjian br...@momjian.us wrote: Itagaki Takahiro wrote: When psql opens a file with -f or \i, it checks first 3 bytes of the file. If they are BOM, discard the 3 bytes and change client encoding to UTF8 automatically. Seems there is community support for accepting BOM: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php Thank yor for information. I read the thread that we discussed about BOM handling in *data types*. I agree the decision in the thead that we should not skip BOM characters, but we can handle BOM in a different way in the head of *files* for psql and COPY input. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On Tue, 2009-10-20 at 14:41 +0900, Itagaki Takahiro wrote: UTF8 encoding text files with BOM (Byte Order Mark) are commonly used in Windows, though BOM was designed for UTF16 text originally. However, psql cannot read such format even if we set client encoding to UTF8. Is it worth supporting those format in psql? psql doesn't have a problem, but the backend's lexer doesn't parse the BOM as whitespace. Since the lexer is byte-based, it will presumably have problems with anything outside of ASCII that Unicode considers whitespace. When psql opens a file with -f or \i, it checks first 3 bytes of the file. If they are BOM, discard the 3 bytes and change client encoding to UTF8 automatically. While I see that the Unicode standard supports using a UTF-8 encoded BOM as UTF-8 signature, I wonder if those bytes can usefully appear in a leading position in other encodings. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Bruce Momjian br...@momjian.us writes: Seems there is community support for accepting BOM: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php That discussion has approximately nothing to do with the much-more-invasive change that Itagaki-san is suggesting. In particular I think an automatic change of client_encoding isn't particularly a good idea --- wouldn't you have to change it back later, and is there any possibility of creating a security issue from such behavior? Remember that client_encoding *IS* tied to security issues such as backslash escape handling. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Seems there is community support for accepting BOM: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php That discussion has approximately nothing to do with the much-more-invasive change that Itagaki-san is suggesting. In particular I think an automatic change of client_encoding isn't particularly a good idea --- wouldn't you have to change it back later, and is there any possibility of creating a security issue from such behavior? Remember that client_encoding *IS* tied to security issues such as backslash escape handling. Yeah, I don't think we should be second-guessing the user on the encoding. What I think we might sensibly do is to eat the leading BOM of an SQL file iff the client encoding is UTF8, and otherwise treat it as just bytes in whatever the encoding is. Should we also do the same for files passed via \copy? What about streams on stdin? What about files read from the backend via COPY? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net writes: What I think we might sensibly do is to eat the leading BOM of an SQL file iff the client encoding is UTF8, and otherwise treat it as just bytes in whatever the encoding is. That seems relatively non-risky. Should we also do the same for files passed via \copy? What about streams on stdin? What about files read from the backend via COPY? Not thrilled about doing this on stdin --- you have no good justification for assuming that start of stdin corresponds to a file boundary somewhere. COPY files, maybe. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net wrote: What I think we might sensibly do is to eat the leading BOM of an SQL file iff the client encoding is UTF8, and otherwise treat it as just bytes in whatever the encoding is. Only at the beginning of the file or stream? What happens when people concatenate files? Would it make sense to treat BOM as whitespace in UTF-8, or maybe ignore it entirely? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
2009/10/20 Tom Lane t...@sss.pgh.pa.us: Andrew Dunstan and...@dunslane.net writes: What I think we might sensibly do is to eat the leading BOM of an SQL file iff the client encoding is UTF8, and otherwise treat it as just bytes in whatever the encoding is. That seems relatively non-risky. +1. Should we also do the same for files passed via \copy? What about streams on stdin? What about files read from the backend via COPY? Not thrilled about doing this on stdin --- you have no good justification for assuming that start of stdin corresponds to a file boundary somewhere. COPY files, maybe. Yeah, that seems a lot more error-prone. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
On Oct 20, 2009, at 10:51 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: What I think we might sensibly do is to eat the leading BOM of an SQL file iff the client encoding is UTF8, and otherwise treat it as just bytes in whatever the encoding is. That seems relatively non-risky. Is that only when the default client encoding is set to UTF8 (PGCLIENTENCODING, whatever), or will it be coded to work with the following: $ PGCLIENTENCODING=...nonutf8... $ psql -f file Where file is: BOM ... SET CLIENT ENCODING 'utf8'; ... EOF Regards, David -- David Christensen End Point Corporation da...@endpoint.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
David Christensen da...@endpoint.com wrote: Is that only when the default client encoding is set to UTF8 (PGCLIENTENCODING, whatever), or will it be coded to work with the following: $ psql -f file Where file is: BOM SET CLIENT ENCODING 'utf8'; Sure. Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. This patch does nothing about COPY and \copy commands. It might be possible to add BOM handling code around AllocateFile() in CopyFrom() to support COPY FROM 'utf8file-with-bom.tsv', but we need another approach for COPY FROM STDIN. For example, $ echo utf8bom-1.tsv utf8bom-2.tsv | psql -c COPY FROM STDIN might contain BOM sequence in the middle of input stream. Anyway, those changes would come from another patch in the future. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center psql-utf8bom_20091021.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] UTF8 with BOM support in psql
UTF8 encoding text files with BOM (Byte Order Mark) are commonly used in Windows, though BOM was designed for UTF16 text originally. However, psql cannot read such format even if we set client encoding to UTF8. Is it worth supporting those format in psql? When psql opens a file with -f or \i, it checks first 3 bytes of the file. If they are BOM, discard the 3 bytes and change client encoding to UTF8 automatically. Is this change reasonable? Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Itagaki Takahiro wrote: UTF8 encoding text files with BOM (Byte Order Mark) are commonly used in Windows, though BOM was designed for UTF16 text originally. However, psql cannot read such format even if we set client encoding to UTF8. Is it worth supporting those format in psql? When psql opens a file with -f or \i, it checks first 3 bytes of the file. If they are BOM, discard the 3 bytes and change client encoding to UTF8 automatically. Is this change reasonable? Comments welcome. Seems there is community support for accepting BOM: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php Should I add this as a TODO item? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
