Re: Very strange Error in Updates

Tom Lane wrote:

>>2) the server converts from client_encoding UNICODE to database encoding
>>SQL_ASCII; for characters that are invalid in SQL_ASCII (>127) it does
>>some arbitary conversion, probably just copying the illegal values
>>unchanged.
>
>
> Not really.  SQL_ASCII encoding basically means "we don't know what this
> data is, just store it verbatim".  So the UTF-8 string sent by the
> driver is stored verbatim.

Hmm, so SQL_ASCII is not really a first-class encoding -- it doesn't do
encoding conversions at all? It's going to break horribly in the face of
clients using different client_encoding values, and somewhat less
horribly even when everything uses a client_encoding of UNICODE (i.e.
string lengths are wrong)?

I wonder if the server behaviour could be somehow changed so that people
don't shoot themselves in the foot so often (variants on this problem
come up again and again..). The problem is that it works most of the
time, only breaking on certain data, so it's not instantly apparent that
you have a problem.

What about refusing to change client_encoding to something other than
SQL_ASCII on SQL_ASCII databases? (This would make the JDBC driver
unusable against those database even for data that currently appears to
work, though)

Or perhaps the JDBC driver could issue a warning whenever it notices the
underlying encoding is SQL_ASCII (this means another round-trip on
connection setup even when using V3 though). Or refuse to even try to
encode strings with characters >127 when the database encoding is SQL_ASCII.

>>The solution is to use a database encoding that matches your data.
>
> Actually, if you intend to access the database primarily through JDBC,
> it'd be best to use server encoding UNICODE.  The JDBC driver will
> always want UNICODE on the wire, and I see no reason to force extra
> character set conversions.  Non-UNICODE-aware clients can be handled by
> setting client_encoding properly.

Sure -- it just depends on what other clients use the db. By the sounds
of it in this case the other client is an ODBC client that isn't aware
of encodings at all.. I suppose this can be handled by the default
client_encoding setting in postgresql.conf?

-O