'create conversion' problem

From: Roman <Roman_Khlystik(at)ukr(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: 'create conversion' problem
Date: 2009-03-23 13:48:26
Message-ID: 49C7932A.3080502@ukr.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

There is a database in KOI8-R encoding. And we have a client who is
querying the database:
set client_encoding TO 'ALT'
and then he write some data into the database.
I have a problem with some symbols which exists in ALT encoding and
which are absent in KOI8-R encoding. As result, during inserting strings
with such symbols postgresql returns an error, for example:
ERROR: character 0xfc of encoding "ALT" has no equivalent in "MULE_INTERNAL"
I've decided to write my own converter, so here is my code:

[code]

#include <postgres.h>
#include <fmgr.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(fix_alt_to_koi8r);
PG_FUNCTION_INFO_V1(fix_koi8r_to_alt);

Datum
fix_alt_to_koi8r(PG_FUNCTION_ARGS)
{
elog(NOTICE,"ALT_TO_KOI8");
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);

Assert(PG_GETARG_INT32(*0*) == PG_ALT);
Assert(PG_GETARG_INT32(*1*) == PG_KOI8R);
Assert(len >= *0*);

static const unsigned char convert866toKOI8[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*127*,
*225*,*226*,*247*,*231*,*228*,*229*,*246*,*250*,*233*,*234*,*235*,*236*,*237*,*238*,*239*,*240*,
*242*,*243*,*244*,*245*,*230*,*232*,*227*,*254*,*251*,*253*,*255*,*249*,*248*,*252*,*224*,*241*,
*193*,*194*,*215*,*199*,*196*,*197*,*214*,*218*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,*208*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*180*,*164*,*183*,*167*,*196*,*197*,*198*,*199*,*200*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,
*208*,*209*,*210*,*211*,*212*,*213*,*214*,*215*,*216*,*217*,*218*,*219*,*220*,*221*,*222*,*223*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*179*,*241*,*242*,*243*,*244*,*245*,*246*,*247*,*248*,*249*,*250*,*251*,*252*,*253*,*254*,*255*
};

while(len){
*dest = convert866toKOI8[(unsigned char)(*src)];
++src;
++dest;
--len;
}

PG_RETURN_VOID();
}

Datum
fix_koi8r_to_alt(PG_FUNCTION_ARGS)
{
elog(NOTICE,"KOI8_TO_ALT");
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);

Assert(PG_GETARG_INT32(*0*) == PG_KOI8R);
Assert(PG_GETARG_INT32(*1*) == PG_ALT);
Assert(len >= *0*);

static const unsigned char KOI8to866[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*255*,
*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*32*,*32*,*32*,*32*,*32*,*249*,*32*,*32*,*32*,*32*,*32*,*32*,*248*,*32*,*32*,*32*,
*32*,*32*,*32*,*241*,*193*,*32*,*105*,*195*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*244*,
*32*,*32*,*32*,*240*,*192*,*32*,*73*,*194*,*252*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*238*,*160*,*161*,*230*,*164*,*165*,*228*,*163*,*229*,*168*,*169*,*170*,*171*,*172*,*173*,*174*,
*175*,*239*,*224*,*225*,*226*,*227*,*166*,*162*,*236*,*235*,*167*,*232*,*237*,*233*,*231*,*234*,
*158*,*128*,*129*,*150*,*132*,*133*,*148*,*131*,*149*,*136*,*137*,*138*,*139*,*140*,*141*,*142*,
*143*,*159*,*144*,*145*,*146*,*147*,*134*,*130*,*156*,*155*,*135*,*152*,*157*,*153*,*151*,*154*
};

while(len){
*dest = KOI8to866[(unsigned char)(*src)];
++src;
++dest;
--len;
}

PG_RETURN_VOID();

[/code]

I has copied this library into $libdir/funcs and I was trying to install
the new converter instead of the old one, in two directions alt->koi8
and koi8->alt:

[code]

db_server=# CREATE FUNCTION fix_koi8r_to_alt(integer,integer,cstring,internal,integer) RETURNS integer AS '$libdir/funcs/fix_conv.so','fix_koi8r_to_alt' LANGUAGE C STRICT;
CREATE FUNCTION
db_server=# CREATE FUNCTION fix_alt_to_koi8r(integer,integer,cstring,internal,integer) RETURNS integer AS '$libdir/funcs/fix_conv.so','fix_alt_to_koi8r' LANGUAGE C STRICT;
CREATE FUNCTION
db_server=# CREATE DEFAULT CONVERSION koi2alt FOR 'KOI8' TO 'ALT' FROM fix_koi8r_to_alt;
CREATE CONVERSION
db_server=# CREATE DEFAULT CONVERSION alt2koi FOR 'ALT' TO 'KOI8' FROM fix_alt_to_koi8r;
CREATE CONVERSION
db_server=# set client_encoding to 'alt';

[/code]

I thought, that was all, but it was not. During the insert of 'bad' symbol in the test table i had the same error:
ERROR: character 0xfc of encoding "ALT" has no equivalent in "MULE_INTERNAL"

I think that my conversion functions are not used. I saw in table pg_conversion:
conname | connamespace | conowner | conforencoding | contoencoding | conproc | condefault
koi8_r_to_windows_866 | *11* | *1* | *21* | *23* | koi8r_to_alt | t
koi2alt | *2200* | *1* | *21* | *23* | fix_koi8r_to_alt | t
windows_866_to_koi8_r *11* | *1* | *23* | *21* | alt_to_koi8r | t
alt2koi | *2200* | *1* | *23* | *21* | fix_alt_to_koi8r | t

and deleted two standard conversion functions:
[code]
db_server=# DROP CONVERSION koi8_r_to_windows_866;
DROP CONVERSION
db_server=# DROP CONVERSION windows_866_to_koi8_r;
DROP CONVERSION
[/code]

leaving only my own conversion functions.After this, when I'm trying to set the client encoding to ALT I got an error:

db_server=# set client_encoding to 'alt';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Is anyone can tell me what I'm doing wrong?
Thanks.

Browse pgsql-general by date

  From Date Subject
Next Message josep porres 2009-03-23 13:52:51 Re: debugging in pgadmin
Previous Message Dave Page 2009-03-23 13:48:23 Re: debugging in pgadmin