Message-ID: <4AF0E70F.1030201@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 10:29:35 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.21 (X11/20090409)
MIME-Version: 1.0
To: Bryce Nesbitt <bryce2@obviously.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Why don't I get a LATIN1 encoding here with SET ENCODING?
References: <4AF0D531.5080309@obviously.com>
In-Reply-To: <4AF0D531.5080309@obviously.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Bryce Nesbitt wrote:
> I'm tracking another bug, but wanted to verify stuff on the command line.  I 
> can't figure out why this did not work:

> dblack3-deleteme=> insert into bryce1 values(1,2,'test\375');
> ERROR:  invalid byte sequence for encoding "UTF8": 0xfd

I'd say the server is interpreting your query text as latin-1 and
converting it to the server encoding UTF-8 as it should, resulting in
the utf-8 string:

  insert into bryce1 values(1,2,'test\375');

which it *then* interprets escapes in. As test\xfd ('0x74 0x65 0x73 0x74
0xfd') isn't valid UTF-8, the server rejects it.

If my understanding is right then the trouble is that the
client_encoding setting doesn't affect string escapes in SQL queries.
The conversion of the query text from client to server encoding is done
before string escapes are processed.

In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd
in a string, I don't want the server to decide that I must've meant
something else because I have a different client encoding. If I wanted
encoding conversion, I wouldn't have written it in an escape form, I'd
have written 'ı' not '\375'.

--
Craig Ringer