Repost: 'identical' DBs not identical

Started by Jean-Christian Imbeaultalmost 23 years ago1 messagesgeneral
Jump to latest
#1Jean-Christian Imbeault
jc@mega-bucks.co.jp

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