From bryce2@obviously.com Wed Nov  4 01:24:08 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E1AD16334EB
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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 <bryce2@obviously.com>
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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
I'm tracking another bug, but wanted to verify stuff on the command
line.&nbsp; I can't figure out why this did not work:<br>
<br>
# psql dblack3-deleteme<br>
Welcome to psql 8.3.8 (server 8.3.4), the PostgreSQL interactive
terminal.<br>
dblack3-deleteme=&gt; create table bryce1 (key int,val1 text,val2
bytea);<br>
dblack3-deleteme=&gt; \encoding<br>
UTF8<br>
dblack3-deleteme=&gt; SET CLIENT_ENCODING TO 'LATIN1';<br>
SET<br>
dblack3-deleteme=&gt; \encoding<br>
LATIN1<br>
dblack3-deleteme=&gt; insert into bryce1 values(1,2,'test\177');<br>
INSERT 0 1<br>
<br>
dblack3-deleteme=&gt; insert into bryce1 values(1,2,'test\375');<br>
ERROR:&nbsp; invalid byte sequence for encoding "UTF8": 0xfd<br>
HINT:&nbsp; This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".<br>
<br>
dblack3-deleteme=&gt; insert into bryce1 values(1,2,'test\200');<br>
ERROR:&nbsp; invalid byte sequence for encoding "UTF8": 0x80<br>
HINT:&nbsp; This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".<br>
<br>
<br>
------------------------------------------------------<br>
<br>
The underlying problem is a version upgrade of DBI drivers has broken
some binary objects we used.<br>
For the binary data:<br>
<span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span
 class="Apple-style-span"
 style="border-collapse: collapse; font-family: arial,sans-serif,'Arial Unicode MS'; font-size: 13px;">
<div><font face="'courier new', monospace">&lt;12&gt;&lt;1&gt;5&lt;0&gt;&lt;0&gt;&lt;0&gt;&lt;4&gt;FOO1</font></div>
<div><font face="'courier new', monospace">&lt;10&gt;&lt;205&gt;&lt;0&gt;&lt;0&gt;&lt;0&gt;&lt;4&gt;FOO2</font></div>
<div><font face="'courier new', monospace"><br>
</font></div>
<font face="'courier new', monospace">Going in like so:</font><br>
<div class="im" style="color: rgb(80, 0, 80);">
<div><font face="'courier new', monospace">$sth-&gt;bind_param($index++,
$data, {pg_type =&gt; $SQLDb::PG_BYTEA});</font></div>
<div><font face="'courier new', monospace"><br>
</font></div>
</div>
<div><font face="'courier new', monospace">It now comes out munged:</font></div>
<div><span style="font-family: 'courier new',monospace;">\012 \001 5
\000 \000 \000 \004 FOO1</span></div>
<div><font face="'courier new', monospace">\010 \377 \000 \000 \000
\004 FOO</font></div>
</span></span>
</body>
</html>



From craig@postnewspapers.com.au Wed Nov  4 02:29:52 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 742B06329DF
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 22:29:52 -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 50780-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 02:29:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id AC35A63222F
	for <pgsql-sql@postgresql.org>; Tue,  3 Nov 2009 22:29:41 -0400 (AST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id A81E11E0440;
	Wed,  4 Nov 2009 10:29:35 +0800 (WST)
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>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.279 tagged_above=-10 required=5 tests=AWL=0.320,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/5
X-Sequence-Number: 33356

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



From bryce2@obviously.com Wed Nov  4 03:36:38 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id AC89163353A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:36:38 -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 64294-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:36:18 +0000 (UTC)
X-Greylist: from auto-whitelisted 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 9E02C634036
	for <pgsql-sql@postgresql.org>; Tue,  3 Nov 2009 23:36:11 -0400 (AST)
Received: from idiom.com (mx0.idiom.com [216.240.32.160])
	by out.internet-mail-service.net (Postfix) with ESMTP id 9C0C4E4AE3;
	Tue,  3 Nov 2009 19:36:11 -0800 (PST)
X-Client-Authorized: MaGic Cook1e
X-Client-Authorized: MaGic Cook1e
Received: from [192.168.2.105] (adsl-71-135-168-64.dsl.pltn13.pacbell.net
	[71.135.168.64]) by idiom.com (Postfix) with ESMTP id D3B5C2D6014;
	Tue,  3 Nov 2009 19:36:09 -0800 (PST)
Message-ID: <4AF0F6A7.7070505@obviously.com>
Date: Tue, 03 Nov 2009 19:36:07 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: Craig Ringer <craig@postnewspapers.com.au>
Subject: Re: Why don't I get a LATIN1 encoding here with SET ENCODING?
References: <4AF0D531.5080309@obviously.com>
	<4AF0E70F.1030201@postnewspapers.com.au>
In-Reply-To: <4AF0E70F.1030201@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.439 tagged_above=-10 required=5 tests=AWL=1.160,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/6
X-Sequence-Number: 33357



Craig Ringer wrote:
> 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'.
I've got a client encoding of LATIN1... so I'd expect to be able to 
present any valid LATIN1 character, not care how the backend stored it, 
then get the same character back from the database.



From craig@postnewspapers.com.au Wed Nov  4 03:47:32 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CCF5D633980
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:47:32 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 76296-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:47:24 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id 71EB3633847
	for <pgsql-sql@postgresql.org>; Tue,  3 Nov 2009 23:47:24 -0400 (AST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 476201E044D;
	Wed,  4 Nov 2009 11:47:21 +0800 (WST)
Message-ID: <4AF0F949.3060807@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 11:47:21 +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>
	<4AF0E70F.1030201@postnewspapers.com.au>
	<4AF0F6A7.7070505@obviously.com>
In-Reply-To: <4AF0F6A7.7070505@obviously.com>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/7
X-Sequence-Number: 33358

Bryce Nesbitt wrote:
> 
> 
> Craig Ringer wrote:
>> 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'.

> I've got a client encoding of LATIN1... so I'd expect to be able to
> present any valid LATIN1 character, not care how the backend stored it,
> then get the same character back from the database.

Yes - but you are *not* presenting a Latin-1 character. You're
presenting four Latin-1 characters:

  '\', '3', '7', '5'

The server *cannot* process those as an escape sequence before first
converting the SQL string from client to server encoding. It doesn't
know what the bytes you sent it mean until it converts the data sent by
the client to the server encoding. Not all encodings preserve the lower
128 characters - in shift-jis, for example, the bytes usually used for
the '\' and '~' characters mean '¥' and '‾' respectively. If the server
didn't do client-to-server encoding before escape processing, a user
with a shift-jis client encoding who sent:

   test¥041

would be very surprised when the server saw that as:

   test!

instead of literally test¥041 like it should.


Perhaps when processing escapes after doing the encoding conversion the
server could apply any client->server encoding transformation on escape
sequences too. That would achieve the result you wanted here, but it
would leave you very, very, very confused and frustrated the first time
you tried to insert an image into a `bytea' field or manipulate a BLOB,
because the server would 'helpfully' translate the byte escapes for you.

To come closer to what you want, the server would have to detect whether
the escape was in a string that was going to land up in a
character-typed field instead of a byte-typed field. But what about
casts, functions, etc? And how would you specify it if you really did
want exactly those bytes in a text field? It'd be a nightmare.

The server does the only sensible, consistent thing - when you give it a
byte sequence, it assumes you mean literally those bytes.

--
Craig Ringer



From bryce2@obviously.com Wed Nov  4 03:54:47 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 541E0634036
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Nov 2009 23:54:47 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 78406-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 03:54:39 +0000 (UTC)
X-Greylist: from auto-whitelisted 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 B4363633DD5
	for <pgsql-sql@postgresql.org>; Tue,  3 Nov 2009 23:54:39 -0400 (AST)
Received: from idiom.com (mx0.idiom.com [216.240.32.160])
	by out.internet-mail-service.net (Postfix) with ESMTP id DCE5FDC167;
	Tue,  3 Nov 2009 19:54:36 -0800 (PST)
X-Client-Authorized: MaGic Cook1e
X-Client-Authorized: MaGic Cook1e
Received: from [192.168.2.105] (adsl-71-135-168-64.dsl.pltn13.pacbell.net
	[71.135.168.64]) by idiom.com (Postfix) with ESMTP id 767A22D601C;
	Tue,  3 Nov 2009 19:54:36 -0800 (PST)
Message-ID: <4AF0FAF9.8080306@obviously.com>
Date: Tue, 03 Nov 2009 19:54:33 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: Craig Ringer <craig@postnewspapers.com.au>
Subject: Re: Why don't I get a LATIN1 encoding here with SET ENCODING?
References: <4AF0D531.5080309@obviously.com>
	<4AF0E70F.1030201@postnewspapers.com.au>
	<4AF0F6A7.7070505@obviously.com>
	<4AF0F949.3060807@postnewspapers.com.au>
In-Reply-To: <4AF0F949.3060807@postnewspapers.com.au>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/8
X-Sequence-Number: 33359

Craig Ringer wrote:
> Yes - but you are *not* presenting a Latin-1 character. You're
> presenting four Latin-1 characters:
>
>   '\', '3', '7', '5'
Well, then I have a different question.  If I can view a bytea column as so:

 > select object from context_objects where context_key=100041;
           object
-----------------------------
 \005\007\021\007Article\003
(1 row)

How can I modify the data and put it back into the column?

I'm trying to track down while a ten year old system no longer works 
after a Perl DBI upgrade.  Something is munging high LATIN1.  The DB 
itself is UTF-8, client_encoding is set to LATIN1.  We expect LATIN1 in, 
same stuff out.  But right now that is what I'm debugging.




From craig@postnewspapers.com.au Wed Nov  4 04:25:56 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A6CA263292A
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 00:25:56 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 82986-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 04:25:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id 7CC5B63278C
	for <pgsql-sql@postgresql.org>; Wed,  4 Nov 2009 00:25:47 -0400 (AST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 53CD51E0449;
	Wed,  4 Nov 2009 12:25:41 +0800 (WST)
Message-ID: <4AF10245.3050707@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 12:25:41 +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>
	<4AF0E70F.1030201@postnewspapers.com.au>
	<4AF0F6A7.7070505@obviously.com>
	<4AF0F949.3060807@postnewspapers.com.au>
	<4AF0FAF9.8080306@obviously.com>
In-Reply-To: <4AF0FAF9.8080306@obviously.com>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/9
X-Sequence-Number: 33360

Bryce Nesbitt wrote:
> Craig Ringer wrote:
>> Yes - but you are *not* presenting a Latin-1 character. You're
>> presenting four Latin-1 characters:
>>
>>   '\', '3', '7', '5'
> Well, then I have a different question.  If I can view a bytea column as
> so:
> 
>> select object from context_objects where context_key=100041;
>           object
> -----------------------------
> \005\007\021\007Article\003
> (1 row)
> 
> How can I modify the data and put it back into the column?

Presuming the working environment:



CREATE TABLE context_objects(
  id serial primary key,
  object bytea not null
);

-- to avoid E'' and double-escaping
SET standard_conforming_strings = on;

INSERT INTO context_objects (object)
VALUES ( '\005\007\021\007Article\003' ), ( 'test\375' );




I'd just SELECT the `object' value, receiving it in escape form from the
PostgreSQL protocol:

SELECT object FROM context_objects
           object
-----------------------------
\005\007\021\007Article\003

... which is a string of 27 characters in the local client_encoding. So
in Perl (or whatever) I'd have a string of length 27:

  \005\007\021\007Article\003

I'd then convert that by parsing the escape sequences. (As Perl uses \0
as the octal escape prefix instead of just \, it's necessary to write a
simple loop for that. I don't do Perl voluntarily and haven't used it
for a while so I won't write one inline here, but it should be easy enough.)

Once you have the byte string (length 12 bytes) you manipulate it
however you want, then convert it back to octal escape form for sending
to the Pg server.

client_encoding has no effect on any of this so long as you're using a
client encoding that preserves the lower 128 characters, ie basically
anything except shift-jis.



Now, if your 'object' is actually a string in a single known text
encoding, not just a sequence of bytes, then another approach is
possible. First, stop using `bytea' for text. If the byte sequences are
all known to be latin-1 encoded text, for example, use:

ALTER TABLE context_objects ALTER COLUMN object TYPE text
USING ( convert_from(object, 'latin-1'));

--
-- Now retrieve the rows, which will be returned as text in
-- the client_encoding. Note that \375 is Ă½ in latin-1.
--
testdb=> SELECT object FROM context_objects;
            object
-----------------------------
 \x05\x07\x11\x07Article\x03
 testĂ½
(2 rows)

(Note: If they're all of different encodings, but you know the
encodings, you can make the encoding param of convert_from a column
reference instead).

Now you have 'object' as server-side utf-8 encoded text that'll be
automatically converted to and from the specified client_encoding . If
you want to get the raw server-side-encoded byte sequence you can always
cast to bytea in your query to get the utf-8 byte sequence in octal
escape form:

testdb=> SELECT object::bytea FROM context_objects;
           object
-----------------------------
 \005\007\021\007Article\003
 test\303\275


... but usually you'd just let the DB




> I'm trying to track down while a ten year old system no longer works
> after a Perl DBI upgrade.

Is it just Perl DBI? Or is it also Pg?

Could it be related to the standards_conforming_strings change that
affects string escaping ?

--
Craig Ringer



From craig@postnewspapers.com.au Wed Nov  4 04:34:47 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 41CA56334EB
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 00:34:47 -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 72980-07-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed,  4 Nov 2009 04:34:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id 2324063292A
	for <pgsql-sql@postgresql.org>; Wed,  4 Nov 2009 00:34:36 -0400 (AST)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id E81C51E0440;
	Wed,  4 Nov 2009 12:34:30 +0800 (WST)
Message-ID: <4AF10456.8070806@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 12:34:30 +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>
	<4AF0E70F.1030201@postnewspapers.com.au>
	<4AF0F6A7.7070505@obviously.com>
	<4AF0F949.3060807@postnewspapers.com.au>
	<4AF0FAF9.8080306@obviously.com>
In-Reply-To: <4AF0FAF9.8080306@obviously.com>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.296 tagged_above=-10 required=5 tests=AWL=0.303,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/10
X-Sequence-Number: 33361

Bryce Nesbitt wrote:
> Craig Ringer wrote:
>> Yes - but you are *not* presenting a Latin-1 character. You're
>> presenting four Latin-1 characters:
>>
>>   '\', '3', '7', '5'
> Well, then I have a different question.  If I can view a bytea column as
> so:
> 
>> select object from context_objects where context_key=100041;
>           object
> -----------------------------
> \005\007\021\007Article\003
> (1 row)
> 
> How can I modify the data and put it back into the column?

Oh: you might also find the encode(...) function useful. From :

http://www.postgresql.org/docs/current/static/functions-string.html

encode(data bytea, type text) returns text
Encode binary data to different representation. Supported types are:
base64, hex, escape. Escape merely outputs null bytes as \000 and
doubles backslashes.

so rather than struggling through Pg's octal-escaped strings you can ask
for them in hex or base64 form, eg:



craig=> \d context_objects
                         Table "public.context_objects"
 Column |  Type   |                          Modifiers

--------+---------+--------------------------------------------------------------
 id     | integer | not null default
nextval('context_objects_id_seq'::regclass)
 object | bytea   | not null
Indexes:
    "context_objects_pkey" PRIMARY KEY, btree (id)

craig=> select * from context_objects;
 id |           object
----+-----------------------------
  1 | \005\007\021\007Article\003
  2 | test\375
(2 rows)

craig=> SELECT encode( object, 'hex' ) FROM context_objects;
          encode
--------------------------
 0507110741727469636c6503
 74657374fd
(2 rows)

craig=> SELECT encode( object, 'base64' ) FROM context_objects;
      encode
------------------
 BQcRB0FydGljbGUD
 dGVzdP0=
(2 rows)


Both the hex-string and base64 forms are trivial to convert to and from
a byte string in Perl. You can use the matching 'decode' function when
updating / inserting data, eg:

  UPDATE context_objects
  SET object = decode('BQcRB0FydGljbGUD', 'base64')
  WHERE id = 2;

--
Craig Ringer