Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VNrdS-0005k6-1H for pgsql-hackers@arkaria.postgresql.org; Sun, 22 Sep 2013 21:56:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VNrdQ-0002Db-Hc for pgsql-hackers@arkaria.postgresql.org; Sun, 22 Sep 2013 21:56:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VNrdN-0002DQ-Qw for pgsql-hackers@postgresql.org; Sun, 22 Sep 2013 21:56:06 +0000 Received: from mail-ie0-x22f.google.com ([2607:f8b0:4001:c03::22f]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VNrdK-0007vF-Fz; Sun, 22 Sep 2013 21:56:05 +0000 Received: by mail-ie0-f175.google.com with SMTP id e14so5165743iej.34 for ; Sun, 22 Sep 2013 14:56:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=My3+ki5rB3umkeaUF6tD7DeTWHNKhj27pwxWyCLhxgg=; b=XAmlF7vfxAjcXh+oXp+odW5OzS3/dfEyYlcFZayF+FzMBxyu6+Wg4QAkMe4qQfA8wG ndnyaOYO72QoKwf6h1isTujGj3XU/60vx9gx8ahniguOWscLZmMn7IoWwmCfC3Q18UQG rJZkg7FieMv6wjIb6SqnKwV3ksLAtHaarQWuBCCJui2rP2QXVwlT75JikpG04KHiIOQQ smow6Jc8PkPzhixryqRtmZ9XWIMe/W7YL3daTnKorHeHEj5C4Bt8hLgTc1rTYhxhobF7 8nMIjvpDGjZLvpyR8yoUrSBahTU6kgmNcblfSiT55JWOpOQcV2b7CdPCdi7vQbH5rJyr tBCA== X-Received: by 10.50.67.107 with SMTP id m11mr10277414igt.11.1379886960241; Sun, 22 Sep 2013 14:56:00 -0700 (PDT) MIME-Version: 1.0 Received: by 10.64.102.161 with HTTP; Sun, 22 Sep 2013 14:55:40 -0700 (PDT) In-Reply-To: References: <522594E8.2050106@vmware.com> <904.1378304922@sss.pgh.pa.us> <37B76474BB3149FD841373E12E355851@maumau> From: Valentine Gogichashvili Date: Sun, 22 Sep 2013 23:55:40 +0200 Message-ID: Subject: Re: UTF8 national character data type support WIP patch and list of open issues. To: MauMau Cc: ishii@postgresql.org, Robert Haas , Tom Lane , "Boguk, Maksym" , Heikki Linnakangas , PostgreSQL Hackers Content-Type: multipart/alternative; boundary=047d7bd7526e18760204e6fff8f4 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --047d7bd7526e18760204e6fff8f4 Content-Type: text/plain; charset=UTF-8 > > > PostgreSQL has a very powerful possibilities for storing any kind of >> encoding. So maybe it makes sense to add the ENCODING as another column >> property, the same way a COLLATION was added? >> > > Some other people in this community suggested that. ANd the SQL standard > suggests the same -- specifying a character encoding for each column: > CHAR(n) CHARASET SET ch. > > > Text operations should work automatically, as in memory all strings will >> be >> converted to the database encoding. >> >> This approach will also open a possibility to implement custom ENCODINGs >> for the column data storage, like snappy compression or even BSON, gobs or >> protbufs for much more compact type storage. >> > > Thanks for your idea that sounds interesting, although I don't understand > that well. > > The idea is very simple: CREATE DATABASE utf8_database ENCODING 'utf8'; \c utf8_database CREATE TABLE a( id serial, ascii_data text ENCODING 'ascii', -- will use ascii_to_utf8 to read and utf8_to_ascii to write koi8_data text ENCODING 'koi8_r', -- will use koi8_r_to_utf8 to read and utf8_to_koi8_r to write json_data json ENCODING 'bson' -- will use bson_to_json to read and json_to_bson to write ); The problem with bson_to_json here is that probably it will not be possible to write JSON in koi8_r for example. But now it is also even not considered in these discussions. If the ENCODING machinery would get not only the encoding name, but also the type OID, it should be possible to write encoders for TYPEs and array of TYPEs (I had to do it using the casts to bytea and protobuff to minimize the size of storage for an array of types when writing a lot of data, that could be unpacked afterwords directly in the DB as normal database types). I hope I made my point a little bit clearer. Regards, Valentine Gogichashvili --047d7bd7526e18760204e6fff8f4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

PostgreSQL has a very powerful possibilities for storing any kind of
encoding. So maybe it makes sense to add the ENCODING as another column
property, the same way a COLLATION was added?

Some other people in this community suggested that. =C2=A0ANd the SQL stand= ard suggests the same -- specifying a character encoding for each column: C= HAR(n) CHARASET SET ch.


Text operations should work automatically, as in memory all strings will be=
converted to the database encoding.

This approach will also open a possibility to implement custom ENCODINGs for the column data storage, like snappy compression or even BSON, gobs or<= br> protbufs for much more compact type storage.

Thanks for your idea that sounds interesting, although I don't understa= nd that well.


The idea is very simple:=C2=A0

CREATE DATABASE utf8_database ENCODING 'utf8';

\c utf8_database

CREATE TAB= LE a(
=C2=A0 id serial,
=C2=A0 ascii_data text ENCODING 'ascii= ', -- will use ascii_to_utf8 to read and utf8_to_ascii to write
=C2=A0 koi8_data text ENCODING 'koi8_r',=C2=A0-- will use koi8_r= _to_utf8 to read and utf8_to_koi8_r to write
=C2=A0 json_data json ENCODING 'bson' -- will use bson_to_json to r= ead and json_to_bson to write
);

The problem with bson_to_= json here is that probably it will not be possible to write JSON in koi8_r = for example. But now it is also even not considered in these discussions.
If the ENCODING machinery would get not only the encoding name, but als= o the type OID, it should be possible to write encoders for TYPEs and array= of TYPEs (I had to do it using the casts to bytea and protobuff to minimiz= e the size of storage for an array of types when writing a lot of data, tha= t could be unpacked afterwords directly in the DB as normal database types)= .

I hope I made my point a little bit clearer.
=
Regards,=C2=A0

Valentine Gogichashv= ili
--047d7bd7526e18760204e6fff8f4--