While people are working on that, they might want to add some sanity checking
to the multibyte character decoders.  Currently they fail to check for
"illegal" character sequences (i.e. sequences with no valid multibyte mapping),
and fail to do something reasonable (like return an error, silently drop the
offending characters, or anything else besides just returning random garbage
and crashing the backend).

The last time this failed to get on the TODO list because Bruce wanted
more than one person to verify that it was an issue.  If several people 
are going to work on the NATIONAL CHARACTER stuff, maybe they could look
into this issue, too.

	-Michael Robinson

>Added to TODO.
>
>> Since there are several people interested in contributing, we should
>> list:
>> 
>>   Support multiple simultaneous character sets, per SQL92


Wed, 14 Jun 2000 16:24:05 +0800 (+0800)
Michael Robinson <robinson@netrinsics.com>
Re: Big 7.1 open items
> While people are working on that, they might want to add some sanity checking
> to the multibyte character decoders.  Currently they fail to check for
> "illegal" character sequences (i.e. sequences with no valid multibyte mapping),
> and fail to do something reasonable (like return an error, silently drop the
> offending characters, or anything else besides just returning random garbage
> and crashing the backend).
> 
> The last time this failed to get on the TODO list because Bruce wanted
> more than one person to verify that it was an issue.  If several people 
> are going to work on the NATIONAL CHARACTER stuff, maybe they could look
> into this issue, too.

The issue is that some people felt we shouldn't be performing such
checks, and some did.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 14 Jun 2000 10:37:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Big 7.1 open items
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>The issue is that some people felt we shouldn't be performing such
>checks, and some did.

Well, more precisely, the issue was stalemated at "one person felt we 
should perform such checks" and "one person (who, incidentally, wrote the
code) felt we shouldn't".

Obviously, as it stands, the issue is going nowhere.

I was just hoping to encourage more people to examine the problem, so that
we might get a consensus one way or the other.

	-Michael Robinson


Thu, 15 Jun 2000 12:52:20 +0800 (+0800)
Michael Robinson <robinson@netrinsics.com>
Re: Re: Big 7.1 open items
> >The issue is that some people felt we shouldn't be performing such
> >checks, and some did.
> Well, more precisely, the issue was stalemated at "one person felt we
> should perform such checks" and "one person (who, incidentally, wrote 
> the code) felt we shouldn't".
> I was just hoping to encourage more people to examine the problem, so 
> that we might get a consensus one way or the other.

I hope that the issue is clearer once we have a trial implementation to
play with.

                   - Thomas

Thu, 15 Jun 2000 07:06:12 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: Big 7.1 open items
> While people are working on that, they might want to add some sanity checking
> to the multibyte character decoders.  Currently they fail to check for
> "illegal" character sequences (i.e. sequences with no valid multibyte mapping),
> and fail to do something reasonable (like return an error, silently drop the
> offending characters, or anything else besides just returning random garbage
> and crashing the backend).

Hum.. I thought Michael Robinson is the one who is against the idea of
rejecting "illegal" character sequences before they are put in the DB.
I like the idea but I haven't time to do that (However I'm not sure I
would like to do it for EUC-CN, since he dislikes the codes I write).

Bruce, I would like to see followings in the TODO. I also would like
to hear from Thomas and Peter or whoever being interested in
implementing NATIONAL CHARACTER stuffs if they are reasonable.

o Don't accept character sequences those are not valid as their charset
  (signaling ERROR seems appropriate IMHO)

o Make PostgreSQL more multibyte aware (for example, TRIM function and
  NAME data type)

o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
  the number of bytes
--
Tatsuo Ishii

Thu, 15 Jun 2000 18:50:19 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Re: Big 7.1 open items
> Hum.. I thought Michael Robinson is the one who is against the idea of
> rejecting "illegal" character sequences before they are put in the DB.
> I like the idea but I haven't time to do that (However I'm not sure I
> would like to do it for EUC-CN, since he dislikes the codes I write).
> 
> Bruce, I would like to see followings in the TODO. I also would like
> to hear from Thomas and Peter or whoever being interested in
> implementing NATIONAL CHARACTER stuffs if they are reasonable.
> 
> o Don't accept character sequences those are not valid as their charset
>   (signaling ERROR seems appropriate IMHO)
> 
> o Make PostgreSQL more multibyte aware (for example, TRIM function and
>   NAME data type)
> 
> o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
>   the number of bytes

Added to TODO:

* Reject character sequences those are not valid in their charset       
    * Make functions more multi-byte aware, i.e. trim() 
* Make n of CHAR(n)/VARCHAR(n) the number of letters, not bytes     


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Jun 2000 09:53:14 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Big 7.1 open items
> o Don't accept character sequences those are not valid as their 
>   charset (signaling ERROR seems appropriate IMHO)
> o Make PostgreSQL more multibyte aware (for example, TRIM function and
>   NAME data type)
> o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
>   the number of bytes

All good, and important features when we are done.

One issue: I can see (or imagine ;) how we can use the Postgres type
system to manage multiple character sets. But allowing arbitrary
character sets in, say, table names forces us to cope with allowing a
mix of character sets in a single column of a system table. afaik this
general capability is not mandated by SQL9x (the SQL_TEXT character set
is used for all system resources??). Would it be acceptable to have a
"default database character set" which is allowed to creep into the
pg_xxx tables? Even that seems to be a difficult thing to accomplish at
the moment (we'd need to get some of the text manipulation functions
from the catalogs, not from hardcoded references as we do now).

We should itemize all of these issues so we can keep track of what is
necessary, possible, and/or "easy".

               - Thomas

Thu, 15 Jun 2000 14:14:47 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: Big 7.1 open items
> > o Don't accept character sequences those are not valid as their 
> >   charset (signaling ERROR seems appropriate IMHO)
> > o Make PostgreSQL more multibyte aware (for example, TRIM function and
> >   NAME data type)
> > o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
> >   the number of bytes
> 
> All good, and important features when we are done.

Glad to hear that.

> One issue: I can see (or imagine ;) how we can use the Postgres type
> system to manage multiple character sets. But allowing arbitrary
> character sets in, say, table names forces us to cope with allowing a
> mix of character sets in a single column of a system table. afaik this
> general capability is not mandated by SQL9x (the SQL_TEXT character set
> is used for all system resources??). Would it be acceptable to have a
> "default database character set" which is allowed to creep into the
> pg_xxx tables? Even that seems to be a difficult thing to accomplish at
> the moment (we'd need to get some of the text manipulation functions
> from the catalogs, not from hardcoded references as we do now).

"default database character set" idea does not seem to be the solution
for cross-db relations such as pg_database. The only solution I can
imagine so far is using SQL_TEXT.

BTW, I've been thinking about SQL_TEXT for a while and it seems
mule_internal_code or Unicode(UTF-8) would be the candidates for
it. Mule_internal_code looks more acceptable for Asian multi-byte
users like me than Unicode. It's clean, simple and does not require
huge conversion tables between Unicode and other encodings. However,
Unicode has a stronger political power in the real world and for most
single-byte users probably it would be enough. My idea is let users
choose one of them. I mean making it a compile time option.

> We should itemize all of these issues so we can keep track of what is
> necessary, possible, and/or "easy".

You are right, probably there would be tons of issues in implementing
multiple charsets support.
--
Tatsuo Ishii

Fri, 16 Jun 2000 23:03:57 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Re: Big 7.1 open items
> "default database character set" idea does not seem to be the solution
> for cross-db relations such as pg_database. The only solution I can
> imagine so far is using SQL_TEXT.
> BTW, I've been thinking about SQL_TEXT for a while and it seems
> mule_internal_code or Unicode(UTF-8) would be the candidates for
> it. Mule_internal_code looks more acceptable for Asian multi-byte
> users like me than Unicode. It's clean, simple and does not require
> huge conversion tables between Unicode and other encodings. However,
> Unicode has a stronger political power in the real world and for most
> single-byte users probably it would be enough. My idea is let users
> choose one of them. I mean making it a compile time option.

Oh. I was recalling SQL_TEXT as being a "subset" character set which
contains only the characters (more or less) that are required for
implementing the SQL92 query language and standard features.

Are you seeing it as being a "superset" character set which can
represent all other character sets??

And, how would you suggest we start tracking this discussion in a design
document? I could put something into the developer's guide, or we could
have a plain-text FAQ, or ??

I'd propose that we start accumulating a feature list, perhaps ordering
it into categories like

o required/suggested by SQL9x
o required/suggested by experience in the real world
o sure would be nice to have
o really bad idea ;)

                  - Thomas

Fri, 16 Jun 2000 14:37:35 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: Big 7.1 open items
Thomas,

A few (hopefully relevant) comments regarding character sets, code pages, 
I18N, and all that:

1) I've seen databases (DB2 if memory serves) that allowed the client 
side to declare itself to the database back-end engine as being in a 
particular code page. For instance, one could have a CP850 Latin-1 client 
and an ISO 8859-1 database. The database engine did appropriate 
translations in both directions.
   
2) Mixing code pages in a single column and then having the database 
engine support it is not trivial. Either each CHAR/VARCHAR would have to 
have some code page settable per row (eg either as a separate column or 
as something like mycolumnname.encoding). 
   Even if you could handle all that you'd still be faced with the issue 
is collating sequence. Each individual code page will have a collating 
sequence. But how do you collate across code pages? There'd be letters 
that were only in a single code page. Plus, it gets messy because with, 
for instance, a simple umlauted a that occurs in CP850, CP1252, and ISO 
8859-1 (and likely in other code pages as well). That letter is really 
the same letter in all those code pages and should treated as such when 
sorting. 

3) I think it is more important for a database to support lots of 
languages in the stored data than in the field names and table names. If 
a programmer has to deal with A-Za-z for naming identifiers and that 
perseon is Korean or Japanese then that is certain is an imposition on 
them. But its a far far bigger imposition if that programmer can't build 
a database that will store the letters of his national language and sort 
and index and search them in convenient ways. 

4) The real solution to the multiple code page dilemma is Unicode. 
   Yes, its more space. But the can of worms of dealing with multiple 
code pages in a column is really no fun and the result is not great. 
BTDTHTTS.

5) The problem with enforcing 
   I've built a database in DB2 where particular columns in it contained 
data from many different code pages (each row had a code page field as 
well as a text field). For some applications that is okay if that field 
is not going to be part of an index. 
   However, if a database is going to be defined as being in a particular 
code page, and if the database engine is going to reject characters that 
are not recognized as part of that code page then you can't play the sort 
of game I just described _unless_ there is a different datatype that is 
similar to CHAR/VARCHAR but for which the RDBMS does not enforce code 
page legality on each character. Otherwise you choose some code page for 
a column, you go merrily stuffing in all sorts of rows in all sorts of 
code pages, and then along come some character that is of a value that is 
not a value for some other character in the code page that the RDBMS 
thinks it is. 

Anyway, I've done lots of I18N database stuff and hopefully a few of my 
comments will be useful to the assembled brethren <g>.

In news:<3948E4D7.A3B722E9@alumni.caltech.edu>, 
lockhart@alumni.caltech.edu says...
> One issue: I can see (or imagine ;) how we can use the Postgres type
> system to manage multiple character sets. But allowing arbitrary
> character sets in, say, table names forces us to cope with allowing a
> mix of character sets in a single column of a system table. afaik this
> general capability is not mandated by SQL9x (the SQL_TEXT character set
> is used for all system resources??). Would it be acceptable to have a
> "default database character set" which is allowed to creep into the
> pg_xxx tables? Even that seems to be a difficult thing to accomplish at
> the moment (we'd need to get some of the text manipulation functions
> from the catalogs, not from hardcoded references as we do now).
> 

Fri, 16 Jun 2000 16:18:23 -0700
Randall Parker <rgparker@west.net>
Re: Re: Big 7.1 open items
> Oh. I was recalling SQL_TEXT as being a "subset" character set which
> contains only the characters (more or less) that are required for
> implementing the SQL92 query language and standard features.
> 
> Are you seeing it as being a "superset" character set which can
> represent all other character sets??

Yes, it's my understanding from the section 19.3.1 of Date's book
(fourth edition). Please correct me if I am wrong.

> And, how would you suggest we start tracking this discussion in a design
> document? I could put something into the developer's guide, or we could
> have a plain-text FAQ, or ??
> 
> I'd propose that we start accumulating a feature list, perhaps ordering
> it into categories like
> 
> o required/suggested by SQL9x
> o required/suggested by experience in the real world
> o sure would be nice to have
> o really bad idea ;)

Sounds good. Could I put "CREATE CHARACTER SET" as the first item of
the list and start a discussion for that?

I have a feeling that you have an idea to treat user defined charset
as a PostgreSQL new data type. So probably "CREATE CHARACTER SET"
could be traslated to our "CREATE TYPE" by the parer, right?
--
Tatsuo Ishii

Sun, 18 Jun 2000 18:18:09 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Re: Big 7.1 open items
> > Oh. I was recalling SQL_TEXT as being a "subset" character set which
> > contains only the characters (more or less) that are required for
> > implementing the SQL92 query language and standard features.
> > Are you seeing it as being a "superset" character set which can
> > represent all other character sets??
> Yes, it's my understanding from the section 19.3.1 of Date's book
> (fourth edition). Please correct me if I am wrong.

Yuck. That is what is says, all right :(

Date says that SQL_TEXT is required to have two things:
1) all characters used in the SQL language itself (which is what I
recalled)

2) Every other character from every character set in the installation.

afaict (2) pretty much kills extensibility if we interpret that
literally. I'd like to research it a bit more before we accept it as a
requirement.

> > I'd propose that we start accumulating a feature list, perhaps ordering
> > it into categories like
> >
> > o required/suggested by SQL9x
> > o required/suggested by experience in the real world
> > o sure would be nice to have
> > o really bad idea ;)
> 
> Sounds good. Could I put "CREATE CHARACTER SET" as the first item of
> the list and start a discussion for that?
> 
> I have a feeling that you have an idea to treat user defined charset
> as a PostgreSQL new data type. So probably "CREATE CHARACTER SET"
> could be traslated to our "CREATE TYPE" by the parer, right?

Yes. Though the SQL_TEXT issue may completely kill this. And lead to a
requirement that we have a full-unicode backend :((

I'm hoping that there is a less-intrusive way to do this. What do other
database systems have for this? I assume most do not have much...

                  - Thomas

Tue, 20 Jun 2000 15:47:26 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: Big 7.1 open items
Thomas Lockhart writes:

> One issue: I can see (or imagine ;) how we can use the Postgres type
> system to manage multiple character sets.

But how are you going to tell a genuine "type" from a character set? And
you might have to have three types for each charset. There'd be a lot of
redundancy and confusion regarding the input and output functions and
other pg_type attributes. No doubt there's something to be learned from
the type system, but character sets have different properties -- like
characters(!), collation rules, encoding "translations" and what not.
There is no doubt also need for different error handling. So I think that
just dumping every character set into pg_type is not a good idea. That's
almost equivalent to having separate types for char(6), char(7), etc.

Instead, I'd suggest that character sets become separate objects. A
character entity would carry around its character set in its header
somehow. Consider a string concatenation function, being invoked with two
arguments of the same exotic character set. Using the type system only
you'd have to either provide a function signature for all combinations of
characters sets or you'd have to cast them up to SQL_TEXT, concatenate
them and cast them back to the original charset. A smarter concatentation
function instead might notice that both arguments are of the same
character set and simply paste them together right there.


> But allowing arbitrary character sets in, say, table names forces us
> to cope with allowing a mix of character sets in a single column of a
> system table.

The priority is probably the data people store, not the way they get to
name their tables.

> Would it be acceptable to have a "default database character set"
> which is allowed to creep into the pg_xxx tables?

I think we could go with making all system table char columns Unicode, but
of course they are really of the "name" type, which is another issue
completely.


> We should itemize all of these issues so we can keep track of what is
> necessary, possible, and/or "easy".

Here are a couple of "items" I keep wondering about:

* To what extend would we be able to use the operating systems locale
facilities? Besides the fact that some systems are deficient or broken one
way or another, POSIX really doesn't provide much besides "given two
strings, which one is greater", and then only on a per-process basis.
We'd really need more that, see also LIKE indexing issues, and indexing in
general.

* Client support: A lot of language environments provide pretty smooth
Unicode support these days, e.g., Java, Perl 5.6, and I think that C99 has
also made some strides. So while "we can store stuff in any character set
you want" is great, it's really no good if it doesn't work transparently
with the client interfaces. At least something to keep in mind.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 20 Jun 2000 18:43:44 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Character sets (Re: Re: Big 7.1 open items)
> But how are you going to tell a genuine "type" from a character set? And
> you might have to have three types for each charset. There'd be a lot of
> redundancy and confusion regarding the input and output functions and
> other pg_type attributes. No doubt there's something to be learned from
> the type system, but character sets have different properties -- like
> characters(!), collation rules, encoding "translations" and what not.
> There is no doubt also need for different error handling. So I think that
> just dumping every character set into pg_type is not a good idea. That's
> almost equivalent to having separate types for char(6), char(7), etc.
> 
> Instead, I'd suggest that character sets become separate objects. A
> character entity would carry around its character set in its header
> somehow. Consider a string concatenation function, being invoked with two
> arguments of the same exotic character set. Using the type system only
> you'd have to either provide a function signature for all combinations of
> characters sets or you'd have to cast them up to SQL_TEXT, concatenate
> them and cast them back to the original charset. A smarter concatentation
> function instead might notice that both arguments are of the same
> character set and simply paste them together right there.

Intersting idea. But what about collations? SQL allows to assign a
collation different from the default one to a character set on the
fly. Should we make collations as separate obejcts as well?

> Here are a couple of "items" I keep wondering about:
> 
> * To what extend would we be able to use the operating systems locale
> facilities? Besides the fact that some systems are deficient or broken one
> way or another, POSIX really doesn't provide much besides "given two
> strings, which one is greater", and then only on a per-process basis.
> We'd really need more that, see also LIKE indexing issues, and indexing in
> general.

Correct. I'd suggest completely getting ride of OS's locale.

> * Client support: A lot of language environments provide pretty smooth
> Unicode support these days, e.g., Java, Perl 5.6, and I think that C99 has
> also made some strides. So while "we can store stuff in any character set
> you want" is great, it's really no good if it doesn't work transparently
> with the client interfaces. At least something to keep in mind.

Do you suggest that we should convert everyting into Unicode and store
them into DB?
--
Tatsuo Ishii

Wed, 21 Jun 2000 15:19:17 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Character sets (Re: Re: Big 7.1 open items)
> Yuck. That is what is says, all right :(
> 
> Date says that SQL_TEXT is required to have two things:
> 1) all characters used in the SQL language itself (which is what I
> recalled)
> 
> 2) Every other character from every character set in the installation.

Doesn't it say "charcter repertory", rather than character set? I
think it would be possible to let our SQL_TEXT support every character
repertories in the world, if we use Unicode or Mule internal code for
that.
--
Tatsuo Ishii

Wed, 21 Jun 2000 15:20:00 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
SQL_TEXT (Re: Re: Big 7.1 open items)
> > Date says that SQL_TEXT is required to have two things:
> > 1) all characters used in the SQL language itself (which is what I
> > recalled)
> > 2) Every other character from every character set in the 
> > installation.
> Doesn't it say "charcter repertory", rather than character set? I
> think it would be possible to let our SQL_TEXT support every character
> repertories in the world, if we use Unicode or Mule internal code for
> that.

I think that "character set" and "character repertoire" are synonymous
(at least I am interpreting them that way). SQL99 makes a slight
distinction, in that "repertoire" is a "set" in a specific context of
application.

I'm starting to look at the SQL99 doc. I am going to try to read the doc
as if SQL_TEXT is a placeholder for "any allowed character set", not
"all character sets simultaneously" and see if that works. 

Since there are a wide range of encodings to choose from, and since most
character sets can not be translated to another random character set,
having SQL_TEXT usefully require all sets present simultaneously seems a
bit of a stretch.

I'm also not going to try to understand the complete doc before having a
trial solution; we can extend/modify/redefine/throw away the trial
solution as we understand the spec better.

While I'm thinking about it: afaict, if we have the ability to load
multiple character sets simultaneously, we will want to have *one* of
those mapped in as the "default character set" for an installation or
database. So we might want to statically link that one in, while the
others get loaded dynamically.

                    - Thomas

Sun, 25 Jun 2000 04:55:08 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: SQL_TEXT (Re: Re: Big 7.1 open items)
> I think that "character set" and "character repertoire" are synonymous
> (at least I am interpreting them that way). 

Is it? I think that a "character set" consists of a "character
repertoire" and a "form of use".

> SQL99 makes a slight
> distinction, in that "repertoire" is a "set" in a specific context of
> application.

I don't understand this probably due to my English ability. Can you
tell me where I can get SQL99 on line doc so that I could study it
more?

> While I'm thinking about it: afaict, if we have the ability to load
> multiple character sets simultaneously, we will want to have *one* of
> those mapped in as the "default character set" for an installation or
> database. So we might want to statically link that one in, while the
> others get loaded dynamically.

Right. While I am not sure we could statically link it, there could be
"default character set" in a installation or database. Also we could
have another "default character set" for NATIONAL CHARACTER. It seems
that those "default character set" are actually same acording to the
standard?
--
Tatsuo Ishii

Sun, 25 Jun 2000 21:21:27 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: SQL_TEXT (Re: Re: Big 7.1 open items)
> I think that a "character set" consists of a "character
> repertoire" and a "form of use".
> > SQL99 makes a slight distinction, in that "repertoire" is a "set" in 
> > a specific context of application.
> I don't understand this probably due to my English ability.

I'm pretty sure that it is due to convoluted standards ;)

> Can you tell me where I can get SQL99 on line doc so that I could 
> study it more?

 From Peter E:

ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ansi-iso-9075-[12345]-1999.txt

(a set of 5 files) which are also available in PDF at the same site.

                - Thomas

Sun, 25 Jun 2000 16:40:27 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: SQL_TEXT (Re: Re: Big 7.1 open items)