pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
8+ messages / 4 participants
[nested] [flat]

* Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-12 12:25  Meetesh Karia <meetesh.karia@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Meetesh Karia @ 2008-03-12 12:25 UTC (permalink / raw)
  To: pgsql-admin

Hi all,

I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to 
8.3.0 and I've been trying to solve what appears to be an encoding 
problem.  My old db was in the UNICODE encoding.  I know that this isn't 
supported on 8.0.x, but it was a restore of a db from a Linux 
environment and postgres didn't appear to have any problems with it.

My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I 
tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried 
to restore the db, I got an error during index creation which wouldn't 
let me create a unique index on a column that had all unique values (it 
had the index in 8.0 and a group by having query with no indexes on the 
table confirms uniqueness).  The thing that this column does have 
however is values like:

'Bruehl'
'Brühl'

I created a blank table with the unique index on it and inserted rows 
one at a time until I confirmed that it was the above values that were 
causing a problem.  Running the following query shows the difference in 
the hex encoded values (I changed my client encoding to WIN1250 to get 
the below to show up correctly):

select name, encode(decode(name, 'escape'), 'hex') from ...

     name      |           encode
---------------+----------------------------
 Daniel Brühl  | 44616e69656c204272c3bc686c
 Daniel Bruehl | 44616e69656c2042727565686c
(2 rows)

I've also tried exporting using an encoding of WIN1250 but I get errors 
like this:

pg_dump: Error message from server: ERROR:  character 0xc383 of encoding 
"UNICODE" has no equivalent in "WIN1250"

Anyone have any thoughts or suggestions?  Why would the index creation 
fail?  Is there a workaround?

Thanks,
Meetesh

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-12 13:37  Meetesh Karia <meetesh.karia@gmail.com>
  parent: Meetesh Karia <meetesh.karia@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Meetesh Karia @ 2008-03-12 13:37 UTC (permalink / raw)
  To: meetesh.karia@alumni.duke.edu; +Cc: pgsql-admin

One quick addition to this:

The column I'm creating this unique index on is a varchar(255) and the 
command I was running was:

create unique index foo_name on foo (name);

If I use the following, it now works:

create unique index foo_name on foo (cast(name as bytea));

Thoughts?

Meetesh

Meetesh Karia wrote:
> Hi all,
>
> I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to 
> 8.3.0 and I've been trying to solve what appears to be an encoding 
> problem.  My old db was in the UNICODE encoding.  I know that this 
> isn't supported on 8.0.x, but it was a restore of a db from a Linux 
> environment and postgres didn't appear to have any problems with it.
>
> My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I 
> tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried 
> to restore the db, I got an error during index creation which wouldn't 
> let me create a unique index on a column that had all unique values 
> (it had the index in 8.0 and a group by having query with no indexes 
> on the table confirms uniqueness).  The thing that this column does 
> have however is values like:
>
> 'Bruehl'
> 'Brühl'
>
> I created a blank table with the unique index on it and inserted rows 
> one at a time until I confirmed that it was the above values that were 
> causing a problem.  Running the following query shows the difference 
> in the hex encoded values (I changed my client encoding to WIN1250 to 
> get the below to show up correctly):
>
> select name, encode(decode(name, 'escape'), 'hex') from ...
>
>      name      |           encode
> ---------------+----------------------------
>  Daniel Brühl  | 44616e69656c204272c3bc686c
>  Daniel Bruehl | 44616e69656c2042727565686c
> (2 rows)
>
> I've also tried exporting using an encoding of WIN1250 but I get 
> errors like this:
>
> pg_dump: Error message from server: ERROR:  character 0xc383 of 
> encoding "UNICODE" has no equivalent in "WIN1250"
>
> Anyone have any thoughts or suggestions?  Why would the index creation 
> fail?  Is there a workaround?
>
> Thanks,
> Meetesh

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 03:28  Robert Treat <xzilla@users.sourceforge.net>
  parent: Meetesh Karia <meetesh.karia@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Robert Treat @ 2008-03-13 03:28 UTC (permalink / raw)
  To: pgsql-admin; meetesh.karia@alumni.duke.edu

On Wednesday 12 March 2008 09:37, Meetesh Karia wrote:
> One quick addition to this:
>
> The column I'm creating this unique index on is a varchar(255) and the
> command I was running was:
>
> create unique index foo_name on foo (name);
>
> If I use the following, it now works:
>
> create unique index foo_name on foo (cast(name as bytea));
>
> Thoughts?
>
> Meetesh
>
> Meetesh Karia wrote:
> > Hi all,
> >
> > I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to
> > 8.3.0 and I've been trying to solve what appears to be an encoding
> > problem.  My old db was in the UNICODE encoding.  I know that this
> > isn't supported on 8.0.x, but it was a restore of a db from a Linux
> > environment and postgres didn't appear to have any problems with it.
> >
> > My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I
> > tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried
> > to restore the db, I got an error during index creation which wouldn't
> > let me create a unique index on a column that had all unique values
> > (it had the index in 8.0 and a group by having query with no indexes
> > on the table confirms uniqueness).  The thing that this column does
> > have however is values like:
> >
> > 'Bruehl'
> > 'Brühl'
> >
> > I created a blank table with the unique index on it and inserted rows
> > one at a time until I confirmed that it was the above values that were
> > causing a problem.  Running the following query shows the difference
> > in the hex encoded values (I changed my client encoding to WIN1250 to
> > get the below to show up correctly):
> >
> > select name, encode(decode(name, 'escape'), 'hex') from ...
> >
> >      name      |           encode
> > ---------------+----------------------------
> >  Daniel Brühl  | 44616e69656c204272c3bc686c
> >  Daniel Bruehl | 44616e69656c2042727565686c
> > (2 rows)
> >
> > I've also tried exporting using an encoding of WIN1250 but I get
> > errors like this:
> >
> > pg_dump: Error message from server: ERROR:  character 0xc383 of
> > encoding "UNICODE" has no equivalent in "WIN1250"
> >
> > Anyone have any thoughts or suggestions?  Why would the index creation
> > fail?  Is there a workaround?
> >

I'm not convinced your problem isn't solved by proper setting of 
client_encoding for both input and output:

pagila=# create table x (r varchar(255) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
table "x "
CREATE TABLE
pagila=# set client_encoding=WIN1250;
SET
pagila=# insert into x (r) values ('Daniel Brühl');
INSERT 0 1
pagila=# insert into x (r) values ('Daniel Bruehl');
INSERT 0 1
pagila=# select * from x;
       r
---------------
 Daniel Brühl
 Daniel Bruehl
(2 rows)

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 10:00  Meetesh Karia <meetesh.karia@gmail.com>
  parent: Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 2 replies; 8+ messages in thread

From: Meetesh Karia @ 2008-03-13 10:00 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-admin; meetesh.karia@alumni.duke.edu

Thanks for your response Robert.

Unfortunately I don't think that will work (unless I'm misunderstanding 
something).

I can't export using a client encoding of WIN1250 because I have true 
UNICODE chars which can't be represented with it (I've tried and I get 
the error below).  Therefore, I have a sql dump with UNICODE chars and 
setting the client encoding to WIN1250 when I run the sql dump script 
would toast the characters.

Additionally, here's what I get when I run your test below (my server 
encoding is UTF-8):

ltefull=# create table x (r varchar(255) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
table "x"
CREATE TABLE
ltefull=#
ltefull=# set client_encoding=WIN1250;
SET
ltefull=# insert into x (r) values ('Daniel Brühl');
INSERT 0 1
ltefull=#
ltefull=# insert into x (r) values ('Daniel Bruehl');
ERROR:  duplicate key value violates unique constraint "x_r_key"

Meetesh

Robert Treat wrote:
> On Wednesday 12 March 2008 09:37, Meetesh Karia wrote:
>   
>> One quick addition to this:
>>
>> The column I'm creating this unique index on is a varchar(255) and the
>> command I was running was:
>>
>> create unique index foo_name on foo (name);
>>
>> If I use the following, it now works:
>>
>> create unique index foo_name on foo (cast(name as bytea));
>>
>> Thoughts?
>>
>> Meetesh
>>
>> Meetesh Karia wrote:
>>     
>>> Hi all,
>>>
>>> I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to
>>> 8.3.0 and I've been trying to solve what appears to be an encoding
>>> problem.  My old db was in the UNICODE encoding.  I know that this
>>> isn't supported on 8.0.x, but it was a restore of a db from a Linux
>>> environment and postgres didn't appear to have any problems with it.
>>>
>>> My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I
>>> tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried
>>> to restore the db, I got an error during index creation which wouldn't
>>> let me create a unique index on a column that had all unique values
>>> (it had the index in 8.0 and a group by having query with no indexes
>>> on the table confirms uniqueness).  The thing that this column does
>>> have however is values like:
>>>
>>> 'Bruehl'
>>> 'Brühl'
>>>
>>> I created a blank table with the unique index on it and inserted rows
>>> one at a time until I confirmed that it was the above values that were
>>> causing a problem.  Running the following query shows the difference
>>> in the hex encoded values (I changed my client encoding to WIN1250 to
>>> get the below to show up correctly):
>>>
>>> select name, encode(decode(name, 'escape'), 'hex') from ...
>>>
>>>      name      |           encode
>>> ---------------+----------------------------
>>>  Daniel Brühl  | 44616e69656c204272c3bc686c
>>>  Daniel Bruehl | 44616e69656c2042727565686c
>>> (2 rows)
>>>
>>> I've also tried exporting using an encoding of WIN1250 but I get
>>> errors like this:
>>>
>>> pg_dump: Error message from server: ERROR:  character 0xc383 of
>>> encoding "UNICODE" has no equivalent in "WIN1250"
>>>
>>> Anyone have any thoughts or suggestions?  Why would the index creation
>>> fail?  Is there a workaround?
>>>
>>>       
>
> I'm not convinced your problem isn't solved by proper setting of 
> client_encoding for both input and output:
>
> pagila=# create table x (r varchar(255) unique);
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
> table "x "
> CREATE TABLE
> pagila=# set client_encoding=WIN1250;
> SET
> pagila=# insert into x (r) values ('Daniel Brühl');
> INSERT 0 1
> pagila=# insert into x (r) values ('Daniel Bruehl');
> INSERT 0 1
> pagila=# select * from x;
>        r
> ---------------
>  Daniel Brühl
>  Daniel Bruehl
> (2 rows)
>
>   

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 12:01  Meetesh Karia <meetesh.karia@gmail.com>
  parent: Meetesh Karia <meetesh.karia@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Meetesh Karia @ 2008-03-13 12:01 UTC (permalink / raw)
  To: meetesh.karia@alumni.duke.edu; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-admin

Thanks to help from Sergey Benner, I was able to narrow the problem 
down.  When I first initialized the PG cluster, I used the following 
command:

initdb -D d:/postgres/8.3/data -E UTF8 -U postgres

And that set my LC_COLLATE and LC_CTYPE values to 'English_United 
States.1252'.  'en_US-UTF-8' would be ideal for me but that doesn't seem 
to exist on Windows.

Anyhow, when I recreated the PG cluster using this command:

initdb -D ./data -E UTF8 --locale=C -U postgres

It now correctly handles the values and allows me to create the unique 
index.  I can use the C locale, but I'm a bit surprised that the default 
windows locale wouldn't work.

Is that to be expected?

Thanks,
Meetesh

Meetesh Karia wrote:
> Thanks for your response Robert.
>
> Unfortunately I don't think that will work (unless I'm 
> misunderstanding something).
>
> I can't export using a client encoding of WIN1250 because I have true 
> UNICODE chars which can't be represented with it (I've tried and I get 
> the error below).  Therefore, I have a sql dump with UNICODE chars and 
> setting the client encoding to WIN1250 when I run the sql dump script 
> would toast the characters.
>
> Additionally, here's what I get when I run your test below (my server 
> encoding is UTF-8):
>
> ltefull=# create table x (r varchar(255) unique);
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" 
> for table "x"
> CREATE TABLE
> ltefull=#
> ltefull=# set client_encoding=WIN1250;
> SET
> ltefull=# insert into x (r) values ('Daniel Brühl');
> INSERT 0 1
> ltefull=#
> ltefull=# insert into x (r) values ('Daniel Bruehl');
> ERROR:  duplicate key value violates unique constraint "x_r_key"
>
> Meetesh
>
> Robert Treat wrote:
>> On Wednesday 12 March 2008 09:37, Meetesh Karia wrote:
>>   
>>> One quick addition to this:
>>>
>>> The column I'm creating this unique index on is a varchar(255) and the
>>> command I was running was:
>>>
>>> create unique index foo_name on foo (name);
>>>
>>> If I use the following, it now works:
>>>
>>> create unique index foo_name on foo (cast(name as bytea));
>>>
>>> Thoughts?
>>>
>>> Meetesh
>>>
>>> Meetesh Karia wrote:
>>>     
>>>> Hi all,
>>>>
>>>> I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to
>>>> 8.3.0 and I've been trying to solve what appears to be an encoding
>>>> problem.  My old db was in the UNICODE encoding.  I know that this
>>>> isn't supported on 8.0.x, but it was a restore of a db from a Linux
>>>> environment and postgres didn't appear to have any problems with it.
>>>>
>>>> My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I
>>>> tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried
>>>> to restore the db, I got an error during index creation which wouldn't
>>>> let me create a unique index on a column that had all unique values
>>>> (it had the index in 8.0 and a group by having query with no indexes
>>>> on the table confirms uniqueness).  The thing that this column does
>>>> have however is values like:
>>>>
>>>> 'Bruehl'
>>>> 'Brühl'
>>>>
>>>> I created a blank table with the unique index on it and inserted rows
>>>> one at a time until I confirmed that it was the above values that were
>>>> causing a problem.  Running the following query shows the difference
>>>> in the hex encoded values (I changed my client encoding to WIN1250 to
>>>> get the below to show up correctly):
>>>>
>>>> select name, encode(decode(name, 'escape'), 'hex') from ...
>>>>
>>>>      name      |           encode
>>>> ---------------+----------------------------
>>>>  Daniel Brühl  | 44616e69656c204272c3bc686c
>>>>  Daniel Bruehl | 44616e69656c2042727565686c
>>>> (2 rows)
>>>>
>>>> I've also tried exporting using an encoding of WIN1250 but I get
>>>> errors like this:
>>>>
>>>> pg_dump: Error message from server: ERROR:  character 0xc383 of
>>>> encoding "UNICODE" has no equivalent in "WIN1250"
>>>>
>>>> Anyone have any thoughts or suggestions?  Why would the index creation
>>>> fail?  Is there a workaround?
>>>>
>>>>       
>>
>> I'm not convinced your problem isn't solved by proper setting of 
>> client_encoding for both input and output:
>>
>> pagila=# create table x (r varchar(255) unique);
>> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
>> table "x "
>> CREATE TABLE
>> pagila=# set client_encoding=WIN1250;
>> SET
>> pagila=# insert into x (r) values ('Daniel Brühl');
>> INSERT 0 1
>> pagila=# insert into x (r) values ('Daniel Bruehl');
>> INSERT 0 1
>> pagila=# select * from x;
>>        r
>> ---------------
>>  Daniel Brühl
>>  Daniel Bruehl
>> (2 rows)
>>
>>   

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 15:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Meetesh Karia <meetesh.karia@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2008-03-13 15:43 UTC (permalink / raw)
  To: meetesh.karia@alumni.duke.edu; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-admin; pgsql-hackers@postgresql.org

Meetesh Karia <meetesh.karia@gmail.com> writes:
> Additionally, here's what I get when I run your test below (my server 
> encoding is UTF-8):

> ltefull=# create table x (r varchar(255) unique);
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
> table "x"
> CREATE TABLE
> ltefull=#
> ltefull=# set client_encoding=WIN1250;
> SET
> ltefull=# insert into x (r) values ('Daniel Brühl');
> INSERT 0 1
> ltefull=#
> ltefull=# insert into x (r) values ('Daniel Bruehl');
> ERROR:  duplicate key value violates unique constraint "x_r_key"

You said this was on Windows, right?

I was about to say "that should be impossible", until I looked at
varstr_cmp() and realized that whoever put in the WIN32/UTF8
special case omitted this part:

        /*
         * In some locales strcoll() can claim that nonidentical strings are
         * equal.  Believing that would be bad news for a number of reasons,
         * so we follow Perl's lead and sort "equal" strings according to
         * strcmp().
         */
        if (result == 0)
            result = strcmp(a1p, a2p);

So we behave differently on Windows (with UTF8) than anywhere else.
This is pretty nasty, not least because it means that texteq is
inconsistent with other text comparison operators.

I think this is a "must fix" bug for 8.3.1, anyone disagree?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 16:33  Gregory Stark <stark@enterprisedb.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Gregory Stark @ 2008-03-13 16:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: meetesh.karia@alumni.duke.edu; Robert Treat <xzilla@users.sourceforge.net>; pgsql-admin; pgsql-hackers@postgresql.org

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> I think this is a "must fix" bug for 8.3.1, anyone disagree?

Agreed.

It seems we should collect cases like this for the regression tests. The only
one I was aware of previously was the Turkish one.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
@ 2008-03-13 18:06  Meetesh Karia <meetesh.karia@gmail.com>
  parent: Gregory Stark <stark@enterprisedb.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Meetesh Karia @ 2008-03-13 18:06 UTC (permalink / raw)
  To: Gregory Stark <stark@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; meetesh.karia@alumni.duke.edu, Robert Treat <xzilla@users.sourceforge.net>; pgsql-admin; pgsql-hackers@postgresql.org

Thanks for the confirmation of this.  For now I'll continue to use the C 
locale and I'll switch with 8.3.1.

Meetesh

Gregory Stark wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>
>   
>> I think this is a "must fix" bug for 8.3.1, anyone disagree?
>>     
>
> Agreed.
>
> It seems we should collect cases like this for the regression tests. The only
> one I was aware of previously was the Turkish one.
>
>   

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2008-03-13 18:06 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-12 12:25 Encoding problems with migration from 8.0.14 to 8.3.0 on Windows Meetesh Karia <meetesh.karia@gmail.com>
2008-03-12 13:37 ` Meetesh Karia <meetesh.karia@gmail.com>
2008-03-13 03:28   ` Robert Treat <xzilla@users.sourceforge.net>
2008-03-13 10:00     ` Meetesh Karia <meetesh.karia@gmail.com>
2008-03-13 12:01       ` Meetesh Karia <meetesh.karia@gmail.com>
2008-03-13 15:43       ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-13 16:33         ` Gregory Stark <stark@enterprisedb.com>
2008-03-13 18:06           ` Meetesh Karia <meetesh.karia@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox