Обсуждение: Deprecate custom encoding conversions

Hi,

PostgreSQL allows writing custom encoding conversion functions between 
any character encodings, using the CREATE CONVERSION command. It's 
pretty flexible, you can define default and non-default conversions, and 
the conversions live in schemas so you can have multiple conversions 
installed in a system and you can switch between them by changing 
search_path.

However:

We never use non-default conversions for anything. All code that 
performs encoding conversions only cares about the default ones.

I think this flexibility is kind of ridiculous anyway. If a built-in 
conversion routine doesn't handle some characters correctly, surely we 
should fix the built-in function, rather than provide a mechanism for 
having your own conversion functions. If you truly need to perform the 
conversions differently than the built-in routines do, you can always 
perform the conversion in the client instead.

Note that we don't support adding completely new custom encodings, all 
this is just for conversions between the built-in encodings that we have.

I propose that we add a notice to the CREATE CONVERSION docs to say that 
it is deprecated, and remove it in a few years.

Any objections? Anyone using custom encoding conversions in production?

- Heikki

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> I propose that we add a notice to the CREATE CONVERSION docs to say that 
> it is deprecated, and remove it in a few years.

While I agree that it's probably not that useful, what would we gain
by removing it?  If you intend to remove those catalogs, what lookup
mechanism would replace them?  We can't exactly drop the encoding
conversion functionality.

            regards, tom lane

On 02/12/2020 18:18, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka@iki.fi> writes:
>> I propose that we add a notice to the CREATE CONVERSION docs to say that
>> it is deprecated, and remove it in a few years.
> 
> While I agree that it's probably not that useful, what would we gain
> by removing it?  If you intend to remove those catalogs, what lookup
> mechanism would replace them?  We can't exactly drop the encoding
> conversion functionality.

I'm thinking of replacing the catalog with a hard-coded 2D array of 
function pointers. Or something along those lines.

I had this idea while looking at the encoding conversions performed in 
COPY. The current conversion functions return a null-terminated, 
palloc'd string, which is a bit awkward for the callers. The caller 
needs to call strlen() on the result, and it would be nice to reuse the 
same buffer for all the conversions. And I've got a hunch that it'd be 
faster to convert the whole 64 kB raw input buffer in one go, rather 
than convert each line separately, but the current function signature 
doesn't make that easy either.

So I'm looking for refactoring the conversion routines to be more 
convenient for the callers. But the current function signature is 
exposed at the SQL level, thanks to CREATE CONVERSION.

- Heikki

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 02/12/2020 18:18, Tom Lane wrote:
>> Heikki Linnakangas <hlinnaka@iki.fi> writes:
>>> I propose that we add a notice to the CREATE CONVERSION docs to say that
>>> it is deprecated, and remove it in a few years.

>> While I agree that it's probably not that useful, what would we gain
>> by removing it?  If you intend to remove those catalogs, what lookup
>> mechanism would replace them?  We can't exactly drop the encoding
>> conversion functionality.

> I'm thinking of replacing the catalog with a hard-coded 2D array of 
> function pointers. Or something along those lines.

I like the current structure in which the encoding functions are in
separate .so's, so that you don't load the ones you don't need.
It's not real clear how to preserve that if we hard-code things.

> So I'm looking for refactoring the conversion routines to be more 
> convenient for the callers. But the current function signature is 
> exposed at the SQL level, thanks to CREATE CONVERSION.

I'd be the first to agree that the current API for conversion functions is
not terribly well-designed.  But what if we just change it?  That can't be
any worse of a compatibility hit than removing CREATE CONVERSION
altogether.

            regards, tom lane

From: Heikki Linnakangas <hlinnaka@iki.fi>
> I propose that we add a notice to the CREATE CONVERSION docs to say that
> it is deprecated, and remove it in a few years.
> 
> Any objections? Anyone using custom encoding conversions in production?

I can't answer deeper questions because I'm not familiar with character sets, but I saw some Japanese web articles that
useCREATE CONVERSION to handle external characters.  So, I think we may as well retain it.  (OTOH, I'm wondering how
otherDBMSs support external characters and what Postgres should implement it.)
 

Also, the SQL standard has CREATE CHARACTER SET and CREATE TRANSLATION.  I don't know how these are useful, but the
mechanismof CREATE CONVERSION can be used to support them.
 


CREATE CHARACTER SET <character set name> [ AS ]

<character set source> [ <collate clause> ]


CREATE TRANSLATION <transliteration name> FOR <source character set specification>

TO <target character set specification> FROM <transliteration source>


Regards
Takayuki Tsunakawa

"tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com> writes:
> From: Heikki Linnakangas <hlinnaka@iki.fi>
>> Any objections? Anyone using custom encoding conversions in production?

> I can't answer deeper questions because I'm not familiar with character sets, but I saw some Japanese web articles
thatuse CREATE CONVERSION to handle external characters.  So, I think we may as well retain it.  (OTOH, I'm wondering
howother DBMSs support external characters and what Postgres should implement it.) 

I recall a discussion in which someone explained that things are messy for
Japanese because there's not really just one version of SJIS; there are
several, because various groups extended the original standard in not-
quite-compatible ways.  In turn this means that there's not really one
well-defined conversion to/from Unicode --- which is the reason why
allowing custom conversions seemed like a good idea.  I don't know
whether anyone over there is actually using custom conversions, but
I'd be hesitant to just nuke the feature altogether.

Having said that, it doesn't seem like the conversion API is necessarily
any more set in stone than any of our other C-level APIs.  We break things
at the C API level whenever there's sufficient reason.

            regards, tom lane

On Thu, Dec 03, 2020 at 12:54:56AM +0000, tsunakawa.takay@fujitsu.com wrote:
> I can't answer deeper questions because I'm not familiar with
> character sets, but I saw some Japanese web articles that use CREATE
> CONVERSION to handle external characters.  So, I think we may as
> well retain it.  (OTOH, I'm wondering how other DBMSs support
> external characters and what Postgres should implement it.)

Tsunakawa-san, could you post a link to this article, if possible?  I
am curious about their problem and why they used CREATE CONVERSION as
a way to solve it.  That's fine even if it is in Japanese.
--
Michael

> I recall a discussion in which someone explained that things are messy for
> Japanese because there's not really just one version of SJIS; there are
> several, because various groups extended the original standard in not-
> quite-compatible ways.  In turn this means that there's not really one
> well-defined conversion to/from Unicode

That's true.

> --- which is the reason why
> allowing custom conversions seemed like a good idea.  I don't know
> whether anyone over there is actually using custom conversions, but
> I'd be hesitant to just nuke the feature altogether.

By Googling I found an instance which is using CREATE CONVERSION
(Japanese article).

http://grep.blog49.fc2.com/blog-entry-87.html
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: Michael Paquier <michael@paquier.xyz>
> Tsunakawa-san, could you post a link to this article, if possible?  I am curious
> about their problem and why they used CREATE CONVERSION as a way to
> solve it.  That's fine even if it is in Japanese.

I just pulled info from my old memory in my previous mail.  Now the following links seem to be relevant.  (They should
bedifferent from what I saw in the past.) 

https://ml.postgresql.jp/pipermail/pgsql-jp/2007-January/013103.html

https://teratail.com/questions/295988

IIRC, the open source Postgres extension EUDC also uses CREATE CONVERSION.



Regards
Takayuki Tsunakawa

On 2020/12/03 11:48, tsunakawa.takay@fujitsu.com wrote:
> From: Michael Paquier <michael@paquier.xyz>
>> Tsunakawa-san, could you post a link to this article, if possible?  I am curious
>> about their problem and why they used CREATE CONVERSION as a way to
>> solve it.  That's fine even if it is in Japanese.
> 
> I just pulled info from my old memory in my previous mail.  Now the following links seem to be relevant.  (They
shouldbe different from what I saw in the past.)
 
> 
> https://ml.postgresql.jp/pipermail/pgsql-jp/2007-January/013103.html
> 
> https://teratail.com/questions/295988
> 
> IIRC, the open source Postgres extension EUDC also uses CREATE CONVERSION.

FWIW, about four years before, for the real project, I wrote
the extension [1] that provides yet another encoding conversion
from UTF-8 to EUC_JP, to handle some external characters.
This extension uses CREATE CONVERSION.

[1]
https://github.com/MasaoFujii/pg_fallback_utf8_to_euc_jp

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

On 2020/12/03 1:04, Heikki Linnakangas wrote:
> Hi,
> 
> PostgreSQL allows writing custom encoding conversion functions between any character encodings, using the CREATE
CONVERSIONcommand. It's pretty flexible, you can define default and non-default conversions, and the conversions live
inschemas so you can have multiple conversions installed in a system and you can switch between them by changing
search_path.
> 
> However:
> 
> We never use non-default conversions for anything. All code that performs encoding conversions only cares about the
defaultones.
 

Yes. I had to update pg_conversion.condefault directly so that we can
use custom encoding when I registered it. The direct update of
pg_conversion is of course not good thing. So I was wondering
if we should have something like ALTER CONVERSION SET DEFAULT.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Fujii Masao <masao.fujii@oss.nttdata.com> writes:
> On 2020/12/03 1:04, Heikki Linnakangas wrote:
>> We never use non-default conversions for anything. All code that performs encoding conversions only cares about the
defaultones. 

> Yes. I had to update pg_conversion.condefault directly so that we can
> use custom encoding when I registered it. The direct update of
> pg_conversion is of course not good thing. So I was wondering
> if we should have something like ALTER CONVERSION SET DEFAULT.

Perhaps, but what I'd think is more urgent is having some way for
a session to select a non-default conversion for client I/O.

            regards, tom lane

On 2020/12/03 13:07, Tom Lane wrote:
> Fujii Masao <masao.fujii@oss.nttdata.com> writes:
>> On 2020/12/03 1:04, Heikki Linnakangas wrote:
>>> We never use non-default conversions for anything. All code that performs encoding conversions only cares about the
defaultones.
 
> 
>> Yes. I had to update pg_conversion.condefault directly so that we can
>> use custom encoding when I registered it. The direct update of
>> pg_conversion is of course not good thing. So I was wondering
>> if we should have something like ALTER CONVERSION SET DEFAULT.
> 
> Perhaps, but what I'd think is more urgent is having some way for
> a session to select a non-default conversion for client I/O.

+1

What about adding new libpq parameter like encoding_conversion
(like client_encoding) so that a client can specify what conversion to use?
That setting is sent to the server while establishing the connection.
Probably we would need to verify that the setting of this new parameter
is consistent with that of client_encoding.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION