On 07/21/2012 04:59 PM, Herouth Maoz
wrote:
I am using Postgresql 8.3.14 on our reporting system. There
are scripts that collect data from many databases across the
firm into this database. Recently I added tables from a
particular database which has encoding UTF-8.
First, I know there have been encoding and UTF-8 handling fixes
since 8.3 . It'd be interesting to see if this still happens on a
more recent version.
You're also missing five bug-fix point-releases in the 8.3 series,
as the latest is 8.3.19 . See:
for fixes you're missing.
Explanation for what I think is going on below:
But this puzzles me, because I then took the file
... which was created with a \copy with client encoding set to
utf-8, right?
ran psql and \copy <table> from file
With which client encoding set? UTF-8 or ISO_8859_8? I bet you
copied it in with ISO_8859_1.
And it worked. I tried it again now, and I can see the row
with its Arabic content, even though it is not in the database
encoding.
It shows up correctly?
If you \copy a dump in utf-8, then \copy it back in with ISO_8859_8,
it should be mangled.
If you set your client_encoding to utf_8 ("\encoding utf-8") does it
still show up correctly? I suspect it's wrong in the database and
you're just unmangling it on display.
It would help if you would actually show the bytes of:
- The chars in the \copy dump, using `xxd' or similar
- The chars in the database before the copy out and copy in, using a
CAST to `bytea`
- The chars in the database AFTER the copy out and copy in, again
with a CAST to `bytea`
... as well as the database encoding, NOT just the client encoding
(see below):
I checked \encoding. It replies
ISO_8859_8
That is the client encoding.
Try:
\l+
to list databases. You'll see the database encoding there. The same
info is available from:
SELECT datname, encoding from pg_database WHERE datname =
'mydatabase';
Maybe this demo will help enlighten you.
regress=# select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc
(GCC) 4.7.0 20120507 (Red Hat 4.7.0-5), 64-bit
(1 row)
regress=# \l+
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges | Size | Tablespace
| Description
--------------------+------------+----------+-------------+-------------+---------------------------+---------+------------+--------------------------------------------
....
regress | craig | UTF8 | en_US.UTF-8 |
en_US.UTF-8 | | 41 MB | pg_default |
regress=# CREATE TABLE enctest (a serial, x text);
CREATE TABLE
regress=# -- Some text randomly pulled off Google News taiwan,
since it was convenient and the exact text doesn't matter
regress=# insert into enctest (x) values ('心情已平復很多」。 中國網絡電視台報導');
INSERT 0 1
regress=# \encoding
UTF8
regress=# \copy ( select x from enctest ) to enctest.csv
Set a 1-byte non-utf encoding, doesn't really matter which one. Then
import the data we dumped as utf-8.
regress=# \encoding latin-1
regress=# \copy enctest(x) from enctest.csv
enctest now contains two rows. One is the correctly encoded
original, one is the dumped and reloaded one.
We can't view the whole table while we're in latin-1 encoding
because the correct row won't translate right.
regress=# select * from enctest;
ERROR: character 0xe5bf83 of encoding "UTF8" has no equivalent in
"LATIN1"
but we *CAN* view the second row we dumped as utf-8 then imported as
latin-1:
regress=# regress=# select * from enctest where a = 2;
a | x
---+---------------------------------------------------------
2 | 心情已平復很多」。 中國網絡電視台報導
(1 row)
regres
At this point you're probably thinking "WTF!?!".
It shows up correctly in my terminal because my terminal is utf-8,
irrespective of the encoding set in psql. Setting a non-utf-8
encoding in psql via "\encoding" just lies to psql about the
encoding of the bytes I paste in on my terminal. It receives a byte
sequence that could be valid latin-1- though it's actually nonsense
garbage, it can't tell the difference. It trusts me and translates
the "latin-1" I sent into utf-8 for storage. When I ask for it back
again and I'm in a latin-1 client encoding, it converts that utf-8
back into latin-1 - or that's what it thinks it's doing. It's
actually demangling mangled utf-8 so my console can display it.
We can undestand this better if we examine what's actually in the
database.
regress=# \encoding utf-8
regress=# select * from enctest;
a
|
x
---+------------------------------------------------------------------------------------------------------------------------------------
1 | 心情已平復很多」。 中國網絡電視台報導
2 |
å¿\u0083æ\u0083\u0085已平復å¾\u0088å¤\u009Aã\u0080\u008Dã\u0080\u0082
ä¸å\u009C\u008B網絡é\u009B»è¦\u0096å\u008F°å ±å°\u008E
(2 rows)
regress=# select a, x::bytea from enctest;
a
|
x
---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
1 |
\xe5bf83e68385e5b7b2e5b9b3e5bea9e5be88e5a49ae3808de3808220e4b8ade59c8be7b6b2e7b5a1e99bbbe8a696e58fb0e5a0b1e5b08e
2 |
\xc3a5c2bfc283c3a6c283c285c3a5c2b7c2b2c3a5c2b9c2b3c3a5c2bec2a9c3a5c2bec288c3a5c2a4c29ac3a3c280c28dc3a3c280c28220c3a4c2b8c2adc3a5c29cc28bc3a7c2b6c2b2c3a7c2b5c2a1c3a9c29bc2b
bc3a8c2a6c296c3a5c28fc2b0c3a5c2a0c2b1c3a5c2b0c28e
(2 rows)
This should help. See how the first row shows up correctly when
we're in the right client encoding, but the second one is gibberish?
That's what UTF-8 that's been interpreted as latin-1 and "converted"
into utf-8 looks like.
It's even more informative if I start a terminal in latin-1 and then
set client_encoding to latin-1, so I'm not lying to psql about my
client encoding.
$ LANG=en_AU.iso55891 LC_ALL=en_AU.iso88591 xterm
$ psql regress
regress=# \encoding
LATIN-1
regress=# select x from enctest where a = 2;
x
---------------------------------------------------------
å¿æ ã ä¸å網絡é¦å°å ±å°
(1 row)¾©å¾å¤ã
Note that latin-1 is a synonym for iso-8859-1.
Now you can see that the data in the DB is actually mangled. It's
just that when you accidentally lie to Pg the same way in the input
and output phases as I've shown, it *looks* ok though the byte
sequence in the database is garbage.
Here's yet another way to illustrate it. Let's take the first char
of our input and see what data is produced when we convert its utf-8
byte using a conversion function from latin-1 into utf-8.
regress=# select '心'::bytea, convert( '心'::bytea, 'latin-1',
'utf-8') from enctest;
bytea | convert
----------+----------------
\xe5bf83 | \xc3a5c2bfc283
(1 row)
Look familiar from the example above? See how the valid utf-8
sequence in the first col gets converted into garbage in the second
col? Yet we can reverse the incorrect conversion to get valid utf-8
again:
regress=# select convert( BYTEA '\xc3a5c2bfc283', 'utf-8',
'latin-1');
convert
----------
\xe5bf83
(1 row)
... which is probably what you've been doing.
What's happening here? Why does the database accept input in the
wrong encoding and doesn't shout when I then try to select that
input?
Correct, because legacy 1-byte encodings cannot be verified. There's
no way to say "Yup, this is latin-1" or "Yup, this is ISO-8859-8".
Don't set client_encoding unless you REALLY know encodings. If you
do set it for \copy, make sure you always \copy in with the same
encoding you used for the \copy out. PostgreSQL cannot protect you
from this.
--
Craig Ringer
