Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E1AD16334EB for ; Tue, 3 Nov 2009 21:24:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 40406-09 for ; Wed, 4 Nov 2009 01:23:49 +0000 (UTC) X-Greylist: delayed 00:10:26.71962 by SQLgrey-1.7.6 Received: from outA.internet-mail-service.net (outa.internet-mail-service.net [216.240.47.224]) by mail.postgresql.org (Postfix) with ESMTP id 8C12F63278C for ; Tue, 3 Nov 2009 21:23:56 -0400 (AST) Received: from idiom.com (mx0.idiom.com [216.240.32.160]) by out.internet-mail-service.net (Postfix) with ESMTP id 99BA8B987B for ; Tue, 3 Nov 2009 17:13:24 -0800 (PST) X-Client-Authorized: MaGic Cook1e Received: from [10.0.101.52] (dsl081-252-019.sfo1.dsl.speakeasy.net [64.81.252.19]) by idiom.com (Postfix) with ESMTP id 1767A2D6013 for ; Tue, 3 Nov 2009 17:13:24 -0800 (PST) Message-ID: <4AF0D531.5080309@obviously.com> Date: Tue, 03 Nov 2009 17:13:21 -0800 From: Bryce Nesbitt User-Agent: Thunderbird 2.0.0.23 (Windows/20090812) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Why don't I get a LATIN1 encoding here with SET ENCODING? Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.257 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_FONT_FACE_BAD=0.884, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457 X-Spam-Level: X-Archive-Number: 200911/4 X-Sequence-Number: 33355 I'm tracking another bug, but wanted to verify stuff on the command line. I can't figure out why this did not work:

# psql dblack3-deleteme
Welcome to psql 8.3.8 (server 8.3.4), the PostgreSQL interactive terminal.
dblack3-deleteme=> create table bryce1 (key int,val1 text,val2 bytea);
dblack3-deleteme=> \encoding
UTF8
dblack3-deleteme=> SET CLIENT_ENCODING TO 'LATIN1';
SET
dblack3-deleteme=> \encoding
LATIN1
dblack3-deleteme=> insert into bryce1 values(1,2,'test\177');
INSERT 0 1

dblack3-deleteme=> insert into bryce1 values(1,2,'test\375');
ERROR: invalid byte sequence for encoding "UTF8": 0xfd
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

dblack3-deleteme=> insert into bryce1 values(1,2,'test\200');
ERROR: invalid byte sequence for encoding "UTF8": 0x80
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

------------------------------------------------------

The underlying problem is a version upgrade of DBI drivers has broken some binary objects we used.
For the binary data:

<12><1>5<0><0><0><4>FOO1

<10><205><0><0><0><4>FOO2

Going in like so:

$sth->bind_param($index++, $data, {pg_type => $SQLDb::PG_BYTEA});

It now comes out munged:

\012 \001 5 \000 \000 \000 \004 FOO1

\010 \377 \000 \000 \000 \004 FOO