Обсуждение: Re: [NOVICE] LATIN2->UTF8 conversation with dblink

Ruzsinszky Attila <ruzsinszky.attila@gmail.com> writes:
> The situation:
> We've got two machines. The source database (DB) is running on an RHEL
> 5.x machine
> with PSQL 8.1.11. The destination DB is running on SuSE 11.0 with PSQL 8.3.x.
> The mechines are relative far away each other and there is a 2Mbps WAN
> line between them.

> The DB is the same except the character coding. Source is LATIN2 and
> the target DB is UTF8.
> We wrote a trigger to copy the data from source to target with dblink.
> The problem is the
> different DB character coding! PGSQL complains about wrong byte order.

Hmm.  You can presumably fix this by setting client_encoding in the
dblink connection to match the encoding in use in the database it's
called in.  But I wonder why dblink doesn't just do that for you
automatically.

            regards, tom lane

On Feb 2, 2009, at 1:31 PM, Tom Lane wrote:

> Ruzsinszky Attila <ruzsinszky.attila@gmail.com> writes:
>> The situation:
>> We've got two machines. The source database (DB) is running on an
>> RHEL
>> 5.x machine
>> with PSQL 8.1.11. The destination DB is running on SuSE 11.0 with
>> PSQL 8.3.x.
>> The mechines are relative far away each other and there is a 2Mbps
>> WAN
>> line between them.
>
>> The DB is the same except the character coding. Source is LATIN2 and
>> the target DB is UTF8.
>> We wrote a trigger to copy the data from source to target with
>> dblink.
>> The problem is the
>> different DB character coding! PGSQL complains about wrong byte
>> order.
>
> Hmm.  You can presumably fix this by setting client_encoding in the
> dblink connection to match the encoding in use in the database it's
> called in.  But I wonder why dblink doesn't just do that for you
> automatically.
>
>             regards, tom lane
>


We did it like this:

INSERT INTO a tbl_datafeed
SELECT
nextval('acc_mkt.tbl_ants_to_ace_feed_row_id_seq'),
convert(project_number::bytea, 'WIN1258'::text, 'UTF8'::text),
convert(project_name::bytea, 'WIN1258'::text, 'UTF8'::text),
...
....
...
  FROM dblink('dbname=mydbname host=removehost user=someuser','SELECT
* FROM tbl_datafeed') AS p
(
...
..
...
.)

Ries

Tom Lane wrote:
> Ruzsinszky Attila <ruzsinszky.attila@gmail.com> writes:
>> The DB is the same except the character coding. Source is LATIN2 and
>> the target DB is UTF8.
>> We wrote a trigger to copy the data from source to target with dblink.
>> The problem is the
>> different DB character coding! PGSQL complains about wrong byte order.
>
> Hmm.  You can presumably fix this by setting client_encoding in the
> dblink connection to match the encoding in use in the database it's
> called in.  But I wonder why dblink doesn't just do that for you
> automatically.

Mainly because I never thought about it myself before, and this is the
first time I've seen someone complain ;-)

But if you think automatically setting client encoding is appropriate, I
will make the change. Would it be classified as a bug (and therefore
something to backpatch) or a new feature?

Joe

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Hmm.  You can presumably fix this by setting client_encoding in the
>> dblink connection to match the encoding in use in the database it's
>> called in.  But I wonder why dblink doesn't just do that for you
>> automatically.

> But if you think automatically setting client encoding is appropriate, I
> will make the change. Would it be classified as a bug (and therefore
> something to backpatch) or a new feature?

You could make an argument either way; but given that we're not seeing
vast demand in this thread, I think I'd vote for not changing behavior
in the back branches.  It'd be good to get it into 8.4 though.

            regards, tom lane