[SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

[Bryce Nesbitt]

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