Re: [HACKERS] utf8 COPY DELIMITER?
Jim C. Nasby wrote: On Tue, Apr 17, 2007 at 02:28:18PM -0400, Tom Lane wrote: I doubt that supporting a single multibyte character would be an interesting extension --- if we wanted to do anything at all there, we'd just generalize the delimiter to be an arbitrary string. But it would certainly slow down COPY by some amount, which is an area where you'll get push-back for performance losses, so you'd need to make a convincing use-case for it. Couldn't we use a fast code path (what we have now) for the case when the delimiter is a single byte? That would allow for multi-character delimiters without penalizing those that don't use them. As for use case, I worked on migrating some stuff out of a MySQL database a while ago, and having arbitrary string delimiters would have made life easier. The first thing to note is that the COPY code is quite complex and fragile. Personally, I'd want a heck of a lot of convincing to see it changed, and your use case looks to me like it would be better handled by preprocessing using a perl script. Also, if we accept string delimiters on input, we should also allow them on output. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] utf8 COPY DELIMITER?
On Tue, Apr 17, 2007 at 02:28:18PM -0400, Tom Lane wrote: > I doubt that supporting a single multibyte character would be an > interesting extension --- if we wanted to do anything at all there, we'd > just generalize the delimiter to be an arbitrary string. But it would > certainly slow down COPY by some amount, which is an area where you'll > get push-back for performance losses, so you'd need to make a convincing > use-case for it. Couldn't we use a fast code path (what we have now) for the case when the delimiter is a single byte? That would allow for multi-character delimiters without penalizing those that don't use them. As for use case, I worked on migrating some stuff out of a MySQL database a while ago, and having arbitrary string delimiters would have made life easier. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] utf8 COPY DELIMITER?
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > The message in question should be something like: > > "COPY delimiter must be a single ASCII character" > > If we phrase it like that we should enforce it like that --- ie, reject > high-bit-set characters. > > But I'm a bit hesitant to do so, because it actually does work fine to > use a high-bit-set character as a delimiter as long as client and server > encodings are the same LATINx set. We'd be taking away functionality > for European users for no very good reason. > > Is it worth going to the trouble of distinguish same-encoding and > different-encoding cases and applying a looser check for the former > case? I think yes. Seems a good idea. Even better, however, is fixing the CVS escaping and quoting I think. Clearly it's a bug. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] utf8 COPY DELIMITER?
Tatsuo Ishii wrote: > > On looking at the code, there's another issue: the CSV escape and quote > > characters are assumed to be the same in client and server encodings, > > because they're checked for before we do transcoding. This pretty much > > restricts them to be ASCII. > > > > regards, tom lane > > +1. > > The message in question should be something like: > > "COPY delimiter must be a single ASCII character" New text is: The single ASCII character that separates columns within each row Backpatched to 8.2.X. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] utf8 COPY DELIMITER?
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > The message in question should be something like: > "COPY delimiter must be a single ASCII character" If we phrase it like that we should enforce it like that --- ie, reject high-bit-set characters. But I'm a bit hesitant to do so, because it actually does work fine to use a high-bit-set character as a delimiter as long as client and server encodings are the same LATINx set. We'd be taking away functionality for European users for no very good reason. Is it worth going to the trouble of distinguish same-encoding and different-encoding cases and applying a looser check for the former case? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] utf8 COPY DELIMITER?
> On looking at the code, there's another issue: the CSV escape and quote > characters are assumed to be the same in client and server encodings, > because they're checked for before we do transcoding. This pretty much > restricts them to be ASCII. > > regards, tom lane +1. The message in question should be something like: "COPY delimiter must be a single ASCII character" -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] utf8 COPY DELIMITER?
On looking at the code, there's another issue: the CSV escape and quote characters are assumed to be the same in client and server encodings, because they're checked for before we do transcoding. This pretty much restricts them to be ASCII. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] utf8 COPY DELIMITER?
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Mark Dilger wrote: >> I'm working on fixing bugs relating to multibyte character encodings. >> I wasn't sure whether this was a bug or not. I don't think we should >> use the phrasing "COPY delimiter must be a single character" when, in >> utf8 land, I did in fact use a single character. We might say "a >> single byte", or we might extend the functionality to handle multibyte >> characters. > Doing the latter would be a feature, and so is of course right off the > table for this release. Changing the error messages to be clearer should > be fine. +1 on changing the message: "character" is clearly less correct than "byte" here. I doubt that supporting a single multibyte character would be an interesting extension --- if we wanted to do anything at all there, we'd just generalize the delimiter to be an arbitrary string. But it would certainly slow down COPY by some amount, which is an area where you'll get push-back for performance losses, so you'd need to make a convincing use-case for it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] utf8 COPY DELIMITER?
Mark Dilger wrote: Andrew Dunstan wrote: Mark Dilger wrote: The \COPY command rejects multibyte delimiters. Is this intentional behavior? It is certainly a known limitation, and I suspect removing it could add non-trivial overhead to the input processing. What is the use case for using such a delimiter? I'm working on fixing bugs relating to multibyte character encodings. I wasn't sure whether this was a bug or not. I don't think we should use the phrasing "COPY delimiter must be a single character" when, in utf8 land, I did in fact use a single character. We might say "a single byte", or we might extend the functionality to handle multibyte characters. Doing the latter would be a feature, and so is of course right off the table for this release. Changing the error messages to be clearer should be fine. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] utf8 COPY DELIMITER?
Andrew Dunstan wrote: Mark Dilger wrote: The \COPY command rejects multibyte delimiters. Is this intentional behavior? It is certainly a known limitation, and I suspect removing it could add non-trivial overhead to the input processing. What is the use case for using such a delimiter? I'm working on fixing bugs relating to multibyte character encodings. I wasn't sure whether this was a bug or not. I don't think we should use the phrasing "COPY delimiter must be a single character" when, in utf8 land, I did in fact use a single character. We might say "a single byte", or we might extend the functionality to handle multibyte characters. mark cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] utf8 COPY DELIMITER?
Mark Dilger wrote: The \COPY command rejects multibyte delimiters. Is this intentional behavior? It is certainly a known limitation, and I suspect removing it could add non-trivial overhead to the input processing. What is the use case for using such a delimiter? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] utf8 COPY DELIMITER?
The \COPY command rejects multibyte delimiters. Is this intentional behavior? Here is an example of the behavior: [EMAIL PROTECTED] ~ $ touch foo [EMAIL PROTECTED] ~ $ psql -p Welcome to psql 8.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit pgsql=# create table foo (a integer); CREATE TABLE pgsql=# \copy foo from foo delimiter '標' ERROR: COPY delimiter must be a single character \copy: ERROR: COPY delimiter must be a single character If your email/news reader doesn't render that properly, I'm using a pictogram character for the delimiter. I checked out a new copy of the sources from cvs this morning. It behaves the same way on 8.2.3. mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
