Repost: 'identical' DBs not identical

From: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Repost: 'identical' DBs not identical
Date: 2003-04-17 01:00:30
Message-ID: 3E9DFCAE.4020602@mega-bucks.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I had posted a question I had about why two databases I had that were
supposed to be identical were not. I did some more research into my
problem and my best guess points to a problem with language encoding.

Can anyone suggest what else I could to find out the cause of this
problem and hopefully solve it?

Problem:

I have a production 7.3.2 database that I 'pg_dump -C'. I used the
dumped file in order to re-create the database on another machine also
running 7.3.2. However the 'new' database seems to have identical
content but *not* identical behaviour.

I some more testing on tables containing EUC_JP text and found that the
TEST db does not seem to be able to do 'text'='text' comparison
correctly (the output is in EUC_JP so shows up as ??? in this email):

TAL=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE: Adding missing FROM-clause entry for table "invoices"
ward | ward | id
--------+--------+------
?????| ?????| 1016
?????| ?????| 1023
?????| ?????| 1023
??????| ??????| 1026
?????? | ?????? | 1038
(5 rows)

TEST=# select invoices.ward, wards.ward, wards.id from wards where
ward=invoices.ward;
NOTICE: Adding missing FROM-clause entry for table "invoices"
ward | ward | id
--------+--------+------
?????| ?????| 1016
?????| ?????| 1023
?????| ?????| 1023
??????| ??????| 1026
?????? | ?????? | 1038
?????| ?????? | 2065
(6 rows)

The last row of the TEST query does *not* contain a match yet it is
returned as a matching row ....

But both databases have the same encoding:

Name | Owner | Encoding
-----------+----------+----------
TAL | postgres | EUC_JP

Name | Owner | Encoding
-----------+----------+----------
TEST | postgres | EUC_JP

Why does my TEST db have a problem matching on ECU_JP text? What can I
do to find the root cause of the problem?

Thanks!

Jean-Christian Imbeault

Browse pgsql-general by date

  From Date Subject
Next Message Hadley Willan 2003-04-17 01:10:33 Re: Java and Postgres aren't too happy
Previous Message Drew Wilson 2003-04-17 00:14:39 Re: "ERROR: Argument of WHERE must not be a set function"?