plperlu problem with utf8

On 12/08/2010 10:13 AM, Oleg Bartunov wrote:

Hi there,

below is the problem, which I don't have when running in shell. The
database is in UTF-8 encoding.

CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use strict;
use URI::Escape;
return uri_unescape($_[0]); $$ LANGUAGE plperlu;
CREATE FUNCTION
Time: 1.416 ms
select url_decode('comment%20passer%20le%20r%C3%A9veillon');
url_decode ------------------------------
comment passer le rveillon
^
non-printed

I get: (platform is Fedora 13, git tip, perl 5.10.1):

andrew=# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS
varchar AS $$
andrew$# use strict;
andrew$# use URI::Escape;
andrew$# return uri_unescape($_[0]); $$ LANGUAGE plperlu;
CREATE FUNCTION
andrew=# select url_decode('comment%20passer%20le%20r%C3%A9veillon');
url_decode
------------------------------
comment passer le réveillon
(1 row)

andrew=#

which makes it look like we might have some double escaping going on
here, but at least I don't get nothing :-)

Further experimentation shows even more weirdness. There's definitely
something odd about the utf8 handling. Will dig further.

cheers

andrew

adding utf8::decode($_[0]) solves the problem:

knn=# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use strict;
use URI::Escape;
utf8::decode($_[0]);
return uri_unescape($_[0]);
$$ LANGUAGE plperlu;

Oleg
On Wed, 8 Dec 2010, Andrew Dunstan wrote:

On 12/08/2010 10:13 AM, Oleg Bartunov wrote:

Hi there,

below is the problem, which I don't have when running in shell. The
database is in UTF-8 encoding.

CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use strict;
use URI::Escape;
return uri_unescape($_[0]); $$ LANGUAGE plperlu;
CREATE FUNCTION
Time: 1.416 ms
select url_decode('comment%20passer%20le%20r%C3%A9veillon');
url_decode ------------------------------
comment passer le rveillon
^
non-printed

I get: (platform is Fedora 13, git tip, perl 5.10.1):

andrew=# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS
varchar AS $$
andrew$# use strict;
andrew$# use URI::Escape;
andrew$# return uri_unescape($_[0]); $$ LANGUAGE plperlu;
CREATE FUNCTION
andrew=# select url_decode('comment%20passer%20le%20r%C3%A9veillon');
url_decode
------------------------------
comment passer le r?©veillon
(1 row)

andrew=#

which makes it look like we might have some double escaping going on here,
but at least I don't get nothing :-)

Further experimentation shows even more weirdness. There's definitely
something odd about the utf8 handling. Will dig further.

cheers

andrew

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Dec 8, 2010, at 8:13 AM, Oleg Bartunov wrote:

adding utf8::decode($_[0]) solves the problem:

knn=# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use strict;
use URI::Escape;
utf8::decode($_[0]);
return uri_unescape($_[0]); $$ LANGUAGE plperlu;

Hrm. Ideally all strings passed to PL/Perl functions would be decoded.

Best,

David

On Wed, 8 Dec 2010, David E. Wheeler wrote:

On Dec 8, 2010, at 8:13 AM, Oleg Bartunov wrote:

adding utf8::decode($_[0]) solves the problem:

knn=# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use strict;
use URI::Escape;
utf8::decode($_[0]);
return uri_unescape($_[0]); $$ LANGUAGE plperlu;

Hrm. Ideally all strings passed to PL/Perl functions would be decoded.

yes, this is what I expected.

Best,

David

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Wed, Dec 8, 2010 at 14:15, Oleg Bartunov <oleg@sai.msu.su> wrote:

On Wed, 8 Dec 2010, David E. Wheeler wrote:

On Dec 8, 2010, at 8:13 AM, Oleg Bartunov wrote:

adding utf8::decode($_[0]) solves the problem:

Hrm. Ideally all strings passed to PL/Perl functions would be decoded.

yes, this is what I expected.

Erm... no. The in and out from perl AFAICT works fine (minus a caveat
I found, see the end of the mail).

The problem here is you have the url encoded utf8 bytes "%C3%A9".
URL::Encode basically does chr(hex("c3")) and chr(hex("a9"));. Perl,
generally, will treat that as two separate unicode code points. So
you end up with two characters (one with a code point of 0xc3, the
other with 0xa9) instead of the one character you expect. If you want
\xc3\xa9 to be treated as a utf8 byte sequence, you need to tell perl
those bytes are utf8 by decoding it. Heck for all we know instead of
it being a utf8 sequence, it could have been a utf16 sequence.

You might argue this is a bug with URI::Escape as I *think* all uri's
will be utf8 encoded. Anyway, I think postgres is doing the right
thing here.

In playing around I did find what I think is a postgres bug. Perl has
2 ways it can store things internally. per perldoc perlunicode:

Using Unicode in XS
... What the "UTF8" flag means is that the sequence of octets in the
representation of the scalar is the sequence of UTF-8 encoded code
points of the characters of a string. The "UTF8" flag being off means
that each octet in this representation encodes a single character with
code point 0..255 within the string.

Postgres always prints whatever the internal representation happens to
be ignoring the UTF8 flag and the server encoding.

# create or replace function chr(i int, i2 int) returns text as $$
return chr($_[0]).chr($_[1]); $$ language plperlu;
CREATE FUNCTION

# show server_encoding;
server_encoding
-----------------
SQL_ASCII

# SELECT length(chr(128, 33));
length
--------
2

# SELECT length(chr(128, 333));
length
--------
4

Grr that should error out with "Invalid server encoding", or worst
case should return a length of 3 (it utf8 encoded 128 into 2 bytes
instead of leaving it as 1). In this case the 333 causes perl store
it internally as utf8.

Now on a utf8 database:

# show server_encoding;
server_encoding
-----------------
UTF8

# SELECT length(chr(128, 33));
ERROR: invalid byte sequence for encoding "UTF8": 0x80
CONTEXT: PL/Perl function "chr"

# SELECT length(chr(128, 333));
CONTEXT: PL/Perl function "chr"
length
--------
2

Same thing here, we just end up using the internal format. In one
case it works in the other it does not. The main point being, most of
the time it *happens* to work. But its really just by chance.

I think what we should do is use SvPVutf8() when we are UTF8 instead
of SvPV in sv2text_mbverified(). SvPV gives us a pointer to a string
in perls current internal format (maybe unicode, maybe a utf8 byte
sequence). While SvPVutf8 will always give us utf8 (may or may not be
valid!) encoded string.

Something like the attached. Thoughts? Im not very happy with the non
utf8 case-- The elog(ERROR, "invalid byte sequence") is a total
cop-out yes. But I did not see a good solution short of hand rolling
our own version of sv_utf8_downgrade(). Is it worth it?

On Dec 16, 2010, at 6:39 PM, Alex Hunsaker wrote:

You might argue this is a bug with URI::Escape as I *think* all uri's
will be utf8 encoded. Anyway, I think postgres is doing the right
thing here.

No, URI::Escape is fine. The issue is that if you don't decode text to Perl's internal form, it assumes that it's Latin-1.

In playing around I did find what I think is a postgres bug. Perl has
2 ways it can store things internally. per perldoc perlunicode:

Using Unicode in XS
... What the "UTF8" flag means is that the sequence of octets in the
representation of the scalar is the sequence of UTF-8 encoded code
points of the characters of a string. The "UTF8" flag being off means
that each octet in this representation encodes a single character with
code point 0..255 within the string.

Postgres always prints whatever the internal representation happens to
be ignoring the UTF8 flag and the server encoding.

# create or replace function chr(i int, i2 int) returns text as $$
return chr($_[0]).chr($_[1]); $$ language plperlu;
CREATE FUNCTION

# show server_encoding;
server_encoding
-----------------
SQL_ASCII

# SELECT length(chr(128, 33));
length
--------
2

# SELECT length(chr(128, 333));
length
--------
4

Grr that should error out with "Invalid server encoding", or worst
case should return a length of 3 (it utf8 encoded 128 into 2 bytes
instead of leaving it as 1). In this case the 333 causes perl store
it internally as utf8.

Well with SQL_ASCII anything goes, no?

Now on a utf8 database:

# show server_encoding;
server_encoding
-----------------
UTF8

# SELECT length(chr(128, 33));
ERROR: invalid byte sequence for encoding "UTF8": 0x80
CONTEXT: PL/Perl function "chr"

# SELECT length(chr(128, 333));
CONTEXT: PL/Perl function "chr"
length
--------
2

Same thing here, we just end up using the internal format. In one
case it works in the other it does not. The main point being, most of
the time it *happens* to work. But its really just by chance.

I think what we should do is use SvPVutf8() when we are UTF8 instead
of SvPV in sv2text_mbverified(). SvPV gives us a pointer to a string
in perls current internal format (maybe unicode, maybe a utf8 byte
sequence). While SvPVutf8 will always give us utf8 (may or may not be
valid!) encoded string.

Something like the attached. Thoughts? Im not very happy with the non
utf8 case-- The elog(ERROR, "invalid byte sequence") is a total
cop-out yes. But I did not see a good solution short of hand rolling
our own version of sv_utf8_downgrade(). Is it worth it?
<plperl_encoding.patch>

Maybe I'm misunderstanding, but it seems to me that:

* String arguments passed to PL/Perl functions should be decoded from the server encoding to Perl's internal representation before the function actually gets them.

* Values returned from PL/Perl functions that are in Perl's internal representation should be encoded into the server encoding before they're returned.

I didn't really follow all of the above; are you aiming for the same thing?

Best,

David

On Thu, Dec 16, 2010 at 20:24, David E. Wheeler <david@kineticode.com> wrote:

On Dec 16, 2010, at 6:39 PM, Alex Hunsaker wrote:

You might argue this is a bug with URI::Escape as I *think* all uri's
will be utf8 encoded.  Anyway, I think postgres is doing the right
thing here.

No, URI::Escape is fine. The issue is that if you don't decode text to Perl's internal form, it assumes that it's Latin-1.

So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?

Im saying they are not, and if you want \xc3\xa9 to be treated as
chr(233) you need to tell perl what encoding the string is in (err
well actually decode it so its in "perl space" as unicode characters
correctly).

Maybe I'm misunderstanding, but it seems to me that:

* String arguments passed to PL/Perl functions should be decoded from the server encoding to Perl's internal representation before the function actually gets them.

Currently postgres has 2 behaviors:
1) If the database is utf8, turn on the utf8 flag. According to the
perldoc snippet I quoted this should mean its a sequence of utf8 bytes
and should interpret it as such.
2) its not utf8, so we just leave it as octets.

So in "perl space" length($_[0]) returns the number of characters when
you pass in a multibyte char *not* the number of bytes. Which is
correct, so um check we do that. Right?

In the URI::Escape example we have:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
warn(length($_[0]));
return uri_unescape($_[0]); $$ LANGUAGE plperlu;

# select url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 38 at line 2

Ok that length looks right, just for grins lets try add one multibyte char:

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon☺');
WARNING: 39 CONTEXT: PL/Perl function "url_decode" at line 2.
url_decode
-------------------------------
comment passer le réveillon☺
(1 row)

Still right, now lets try the utf8::decode version that "works". Only
lets look at the length of the string we are returning instead of the
one we are passing in:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
utf8::decode($_[0]);
my $str = uri_unescape($_[0]);
warn(length($str));
return $str;
$$ LANGUAGE plperlu;

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
url_decode
-----------------------------
comment passer le réveillon
(1 row)

Looks harmless enough...

# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
27
(1 row)

Wait a minute... those lengths should match.

Post patch they do:
# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
28
(1 row)

Still confused? Yeah me too. Maybe this will help:

#!/usr/bin/perl
use URI::Escape;
my $str = uri_unescape("%c3%a9");
die "first match" if($str =~ m/\xe9/);
utf8::decode($str);
die "2nd match" if($str =~ m/\xe9/);

gives:
$ perl t.pl
2nd match at t.pl line 6.

see? Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape(). Hence the maybe it could be
considered a bug in uri_unescape().

* Values returned from PL/Perl functions that are in Perl's internal representation should be encoded into the server encoding before they're returned.
I didn't really follow all of the above; are you aiming for the same thing?

Yeah, the patch address this part. Right now we just spit out
whatever the internal format happens to be.

Anyway its all probably clear as mud, this part of perl is one of the
hardest IMO.

On Thu, Dec 16, 2010 at 07:24:46PM -0800, David Wheeler wrote:

On Dec 16, 2010, at 6:39 PM, Alex Hunsaker wrote:

Grr that should error out with "Invalid server encoding", or worst
case should return a length of 3 (it utf8 encoded 128 into 2 bytes
instead of leaving it as 1). In this case the 333 causes perl
store it internally as utf8.

Well with SQL_ASCII anything goes, no?

Anything except a byte that's all 0s :(

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, Dec 17, 2010 at 08:30, David Fetter <david@fetter.org> wrote:

On Thu, Dec 16, 2010 at 07:24:46PM -0800, David Wheeler wrote:

On Dec 16, 2010, at 6:39 PM, Alex Hunsaker wrote:

Grr that should error out with "Invalid server encoding", or worst
case should return a length of 3 (it utf8 encoded 128 into 2 bytes
instead of leaving it as 1).  In this case the 333 causes perl
store it internally as utf8.

Well with SQL_ASCII anything goes, no?

Anything except a byte that's all 0s :(

Keyword being byte, right? Last time I checked 333 wont fit in a byte
:P. In this case perl stores "333" as the utf8 that represents the
unicode code point 333. Postgres uses whatever that internal
representation is, so in our SQL_ASCII database we actually end up
getting back utf8 which _is_ valid SQL_ASCII, but I wouldn't call it
"right". The behavior im aiming for is similar to what the built-in
chr does:
# SELECT chr(333);
ERROR: requested character too large for encoding: 333

Also note this is just a simple test case, perl *could* elect to store
completely ascii strings internally as utf8. In those cases we still
"do the wrong thing", that is we get back utf8ified bytes :( Although
obviously from the lack of bug reports its quite uncommon.

On Fri, Dec 17, 2010 at 11:51, Alex Hunsaker <badalex@gmail.com> wrote:

Also note this is just a simple test case, perl *could* elect to store
completely ascii strings internally as utf8.  In those cases we still

Erm... not ascii I mean bytes >127

On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

No, URI::Escape is fine. The issue is that if you don't decode text to Perl's internal form, it assumes that it's Latin-1.

So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?

Not knowing what those mean, I'm not saying either one, to my knowledge. What I understand, however, is that Perl, given a scalar with bytes in it, will treat it as latin-1 unless the utf8 flag is turned on.

Im saying they are not, and if you want \xc3\xa9 to be treated as
chr(233) you need to tell perl what encoding the string is in (err
well actually decode it so its in "perl space" as unicode characters
correctly).

PostgreSQL should do everything it can to decode to Perl's internal format before passing arguments, and to decode from Perl's internal format on output.

Maybe I'm misunderstanding, but it seems to me that:

* String arguments passed to PL/Perl functions should be decoded from the server encoding to Perl's internal representation before the function actually gets them.

Currently postgres has 2 behaviors:
1) If the database is utf8, turn on the utf8 flag. According to the
perldoc snippet I quoted this should mean its a sequence of utf8 bytes
and should interpret it as such.

Well that works for me. I always use UTF8. Oleg, what was the encoding of your database where you saw the issue?

2) its not utf8, so we just leave it as octets.

Which mean's Perl will assume that it's Latin-1, IIUC.

So in "perl space" length($_[0]) returns the number of characters when
you pass in a multibyte char *not* the number of bytes. Which is
correct, so um check we do that. Right?

Yeah. So I just wrote and tested this function on 9.0 with Perl 5.12.2:

CREATE OR REPLACE FUNCTION perlgets(
TEXT
) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
my $text = shift;
return_next {
length => length $text,
is_utf8 => utf8::is_utf8($text) ? 1 : 0
};
$$;

In a utf-8 database:

utf8=# select * from perlgets('foo');
length │ is_utf8
────────┼─────────
8 │ t
(1 row)

In a latin-1 database:

latin=# select * from perlgets('foo');
length │ is_utf8
────────┼─────────
8 │ f
(1 row)

I would argue that in the latter case, is_utf8 should be true, too. That is, PL/Perl should decode from Latin-1 to Perl's internal form.

Interestingly, when I created a function that takes a bytea argument, utf8 was *still* enabled in the utf-8 database. That doesn't seem right to me.

In the URI::Escape example we have:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
warn(length($_[0]));
return uri_unescape($_[0]); $$ LANGUAGE plperlu;

# select url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 38 at line 2

What's the output? And what's the encoding of the database?

Ok that length looks right, just for grins lets try add one multibyte char:

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon☺');
WARNING: 39 CONTEXT: PL/Perl function "url_decode" at line 2.
url_decode
-------------------------------
comment passer le réveillon☺
(1 row)

Still right,

The length is right, but the é is wrong. It looks like Perl thinks it's latin-1. Or, rather, unescape_uri() dosn't know that it should be returning utf-8 characters. That *might* be a bug in URI::Escape.

now lets try the utf8::decode version that "works". Only
lets look at the length of the string we are returning instead of the
one we are passing in:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
utf8::decode($_[0]);
my $str = uri_unescape($_[0]);
warn(length($str));
return $str;
$$ LANGUAGE plperlu;

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
url_decode
-----------------------------
comment passer le réveillon
(1 row)

Looks harmless enough...

Looks far better, in fact. Interesting that URI::Escape does the right thing only if the utf8 flag has been turned on in the string passed to it. But in Perl it usually won't be, because the encoded string should generally have only ASCII characters.

# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
27
(1 row)

Wait a minute... those lengths should match.

Post patch they do:
# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
28
(1 row)

Still confused? Yeah me too.

Yeah…

Maybe this will help:

#!/usr/bin/perl
use URI::Escape;
my $str = uri_unescape("%c3%a9");
die "first match" if($str =~ m/\xe9/);
utf8::decode($str);
die "2nd match" if($str =~ m/\xe9/);

gives:
$ perl t.pl
2nd match at t.pl line 6.

see? Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape(). Hence the maybe it could be
considered a bug in uri_unescape().

Agreed.

* Values returned from PL/Perl functions that are in Perl's internal representation should be encoded into the server encoding before they're returned.
I didn't really follow all of the above; are you aiming for the same thing?

Yeah, the patch address this part. Right now we just spit out
whatever the internal format happens to be.

Ah, excellent.

Anyway its all probably clear as mud, this part of perl is one of the
hardest IMO.

No question.

Best,

David

On Dec 17, 2010, at 5:04 PM, David E. Wheeler wrote:

see? Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape(). Hence the maybe it could be
considered a bug in uri_unescape().

Agreed.

On second thought, no. You can in fact encode anything in a URI. URI::Escape can't know what to decode to. So *I believe* it just unescapes the raw bytes. It might be handy for it to have a new function, though, to complement its uri_escape_utf() function:

sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(@_)) }

Just to make things a bit clearer.

But that's a separate issue from the, erm, inconsistency with which PL/Perl treats encoding and decoding of its inputs and outputs.

Best,

David

On Dec 17, 2010, at 7:04 PM, David E. Wheeler wrote:

On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

No, URI::Escape is fine. The issue is that if you don't decode text to Perl's internal form, it assumes that it's Latin-1.

So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?

Not knowing what those mean, I'm not saying either one, to my knowledge. What I understand, however, is that Perl, given a scalar with bytes in it, will treat it as latin-1 unless the utf8 flag is turned on.

This is a correct assertion as to Perl's behavior. As far as PostgreSQL is/should be concerned in this case, this is the correct handling for URI::Escape, as the input string to the function was all ASCII (= valid UTF-8), so the function author would need to be responsible for the proper conversion to the internal encoding. This would just be a simple decode_utf8() call in the case that you've URI-escaped UTF-8-encoded unicode, however since URI escaping is only defined for octets, the meaning of those unescaped octets is detached from their encoding. There are similar issues with using other modules which traditionally have not distinguished between characters and bytes (as an examples Digest::MD5); Digest::MD5 does not work on wide characters, as the algorithm only deals with octets, so you need to pick a target encoding for wide characters and encode the octets themselves rather than the characters.

Im saying they are not, and if you want \xc3\xa9 to be treated as
chr(233) you need to tell perl what encoding the string is in (err
well actually decode it so its in "perl space" as unicode characters
correctly).

PostgreSQL should do everything it can to decode to Perl's internal format before passing arguments, and to decode from Perl's internal format on output.

+1 on the original sentiment, but only for the case that we're dealing with data that is passed in/out as arguments. In the case that the server_encoding is UTF-8, this is as trivial as a few macros on the underlying SVs for text-like types. If the server_encoding is SQL_ASCII (= byte soup), this is a trivial case of doing nothing with the conversion regardless of data type. For any other server_encoding, the data would need to be converted from the server_encoding to UTF-8, presumably using the built-in conversions before passing it off to the first code path. A similar handling would need to be done for the return values, again datatype-dependent.

This certainly seems like it could be inefficient in the case that we're using a non-utf8 server_encoding (there are people who do?? :-P), however from the standpoint of correctness, any plperl function that deals with this data as characters (using common things like regexes, length, ord, chr, substring, \w metachars) will have the potential of operating incorrectly when provided with data that is in a different encoding than perl's internal format. One thought I had was that we could expose the server_encoding to the plperl interpreters in a special variable to make it easy to explicitly decode if we needed, but the problems with this are: a) there's no guarantee that Encode.pm will have a alias/support for the specific server_encoding name as provided by Pg, and b) in the case of plperl (i.e., not u), there are horrendous issues when trying to deal with Safe.pm and character encoding. Recent upgrades of the Encode module included with perl 5.10+ have caused issues wherein circular dependencies between Encode and Encode::Alias have made it impossible to load in a Safe container without major pain. (There may be some better options than I'd had on a previous project, given that we're embedding our own interpreters and accessing more through the XS guts, so I'm not ruling out this possibility completely).

Perhaps we could set a function attribute or similar which indicated that we wanted to decode the input properly on input, whether or not this should be the default, or at the very least expose a function to the plperl[u]? runtime that would decode/upgrade on demand without the caller of the function needing to know the encoding of the database it is running in. This would solve issue a), because any supported server_encoding would have an internal conversion to utf8, and solves b) because we're avoiding the conversion from inside Safe and simply running our XS function on the input data. (As much as I hate the ugliness of it, if we decide the decoding behavior shouldn't be the default we could even use one of those ugly function pragmas in the function bodies.)

Maybe I'm misunderstanding, but it seems to me that:

* String arguments passed to PL/Perl functions should be decoded from the server encoding to Perl's internal representation before the function actually gets them.

Currently postgres has 2 behaviors:
1) If the database is utf8, turn on the utf8 flag. According to the
perldoc snippet I quoted this should mean its a sequence of utf8 bytes
and should interpret it as such.

Well that works for me. I always use UTF8. Oleg, what was the encoding of your database where you saw the issue?

I'm not sure what the current plperl runtime does as far as marshaling this, but it would be fairly easy to ensure the parameters came in in perl's internal format given a server_encoding of UTF8 and some type introspection to identify the string-like types/text data. (Perhaps any type which had a binary cast to text would be a sufficient definition here. Do domains automatically inherit binary casts from their originating types?)

2) its not utf8, so we just leave it as octets.

Which mean's Perl will assume that it's Latin-1, IIUC.

This is sub-optimal for non-UTF-8-encoded databases, for reasons I pointed out earlier. This would produce bogus results for any non-UTF-8, non-ASCII, non latin-1 encoding, even if it did not generally bite most people in general usage.

So in "perl space" length($_[0]) returns the number of characters when
you pass in a multibyte char *not* the number of bytes. Which is
correct, so um check we do that. Right?

Yeah. So I just wrote and tested this function on 9.0 with Perl 5.12.2:

CREATE OR REPLACE FUNCTION perlgets(
TEXT
) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
my $text = shift;
return_next {
length => length $text,
is_utf8 => utf8::is_utf8($text) ? 1 : 0
};
$$;

In a utf-8 database:

utf8=# select * from perlgets('foo');
length │ is_utf8
────────┼─────────
8 │ t
(1 row)

This example seems bogus; wouldn't length be 3 if this is the example text this was run with? Additionally, since all ASCII is trivially UTF-8, I think a better example would be using a string with hi-bit characters so if this was improperly handled the lengths wouldn't match; length($all_ascii) == length(encode_utf8($all_ascii)) vs length($hi_bit) < length(encode_utf8($hi_bit)). I don't see that this test shows us much with the test case as given. The is_utf8() function merely returns the state of the SV_utf8 flag, which doesn't speak to UTF-8 validity (i.e., this need not be set on ascii-only strings, which are still valid in the UTF-8 encoding), nor does it indicate that there are no hi-bit characters in the string (i.e., with encode_utf8($hi_bit_string)), the source string $hi_bit_string (in perl's internal format) with hi-bit characters will have the utf8 flag set, but the return value of encode_utf8 will not, even though the underlying data, as represented in perl will be identical).

In a latin-1 database:

latin=# select * from perlgets('foo');
length │ is_utf8
────────┼─────────
8 │ f
(1 row)

I would argue that in the latter case, is_utf8 should be true, too. That is, PL/Perl should decode from Latin-1 to Perl's internal form.

See above for discussion of the is_utf8 flag; if we're dealing with latin-1 data or (more precisely in this case) data that has not been decoded from the server_encoding to perl's internal format, this would exactly be the expectation for the state of that flag.

Interestingly, when I created a function that takes a bytea argument, utf8 was *still* enabled in the utf-8 database. That doesn't seem right to me.

I'm not sure what you mean here, but I do think that if bytea is identifiable as one of the input types, we should do no encoding on the data itself, which would indicate that the utf8 flag for that variable would be unset. If this is not currently handled this way, I'd be a bit surprised, as bytea should just be an array of bytes with no character semantics attached to it.

In the URI::Escape example we have:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
warn(length($_[0]));
return uri_unescape($_[0]); $$ LANGUAGE plperlu;

# select url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 38 at line 2

What's the output? And what's the encoding of the database?

Ok that length looks right, just for grins lets try add one multibyte char:

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon☺');
WARNING: 39 CONTEXT: PL/Perl function "url_decode" at line 2.
url_decode
-------------------------------
comment passer le réveillon☺
(1 row)

Still right,

The length is right, but the é is wrong. It looks like Perl thinks it's latin-1. Or, rather, unescape_uri() dosn't know that it should be returning utf-8 characters. That *might* be a bug in URI::Escape.

I think this has been addressed by others in previous emails.

now lets try the utf8::decode version that "works". Only
lets look at the length of the string we are returning instead of the
one we are passing in:

# CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar AS $$
use URI::Escape;
utf8::decode($_[0]);
my $str = uri_unescape($_[0]);
warn(length($str));
return $str;
$$ LANGUAGE plperlu;

# SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon');
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
url_decode
-----------------------------
comment passer le réveillon
(1 row)

Looks harmless enough...

Looks far better, in fact. Interesting that URI::Escape does the right thing only if the utf8 flag has been turned on in the string passed to it. But in Perl it usually won't be, because the encoded string should generally have only ASCII characters.

I think you'll find that this "correct display" is actually an artifact of your terminal type being set to a UTF-8 compatible encoding and interpreting the raw output as the UTF-8 sequence in its output display; that returned count is actually the number of octets, compare:

$ perl -MURI::Escape -e'print length(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon}))'
28

$ perl -MEncode -MURI::Escape -e'print length(decode_utf8(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon})))'
27

# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
27
(1 row)

Wait a minute... those lengths should match.

Post patch they do:
# SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
WARNING: 28 at line 5.
CONTEXT: PL/Perl function "url_decode"
length
--------
28
(1 row)

Still confused? Yeah me too.

Yeah…

As shown above, the character length for the example should be 27, while the octet length for the UTF-8 encoded version is 28. I've reviewed the source of URI::Escape, and can say definitively that: a) regular uri_escape does not handle > 255 code points in the encoding, but there exists a uri_escape_utf8 which will convert the source string to UTF8 first and then escape the encoded value, and b) uri_unescape has *no* logic in it to automatically decode from UTF8 into perl's internal format (at least as far as the version that I'm looking at, which came with 5.10.1).

Maybe this will help:

#!/usr/bin/perl
use URI::Escape;
my $str = uri_unescape("%c3%a9");
die "first match" if($str =~ m/\xe9/);
utf8::decode($str);
die "2nd match" if($str =~ m/\xe9/);

gives:
$ perl t.pl
2nd match at t.pl line 6.

see? Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape(). Hence the maybe it could be
considered a bug in uri_unescape().

Agreed.

-1; if you need to decode from an octets-only encoding, it's your responsibility to do so after you've unescaped it. Perhaps later versions of the URI::Escape module contain a uri_unescape_utf8() function, but it's trivially: sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(shift))}. This is definitely not a bug in uri_escape, as it is only defined to return octets.

* Values returned from PL/Perl functions that are in Perl's internal representation should be encoded into the server encoding before they're returned.
I didn't really follow all of the above; are you aiming for the same thing?

Yeah, the patch address this part. Right now we just spit out
whatever the internal format happens to be.

Ah, excellent.

I agree with the sentiments that: data (server_encoding) -> function parameters (-> perl internal) -> function return (-> server_encoding). This should be for any character-type data insofar as it is feasible, but ISTR there is already datatype-specific marshaling occurring.

Anyway its all probably clear as mud, this part of perl is one of the
hardest IMO.

No question.

There is definitely a lot of confusion surrounding perl's handling of character data; I hope this was able to clear a few things up.

Regards,

David
--
David Christensen
End Point Corporation
david@endpoint.com

On Fri, Dec 17, 2010 at 22:32, David Christensen <david@endpoint.com> wrote:

On Dec 17, 2010, at 7:04 PM, David E. Wheeler wrote:

On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

No, URI::Escape is fine. The issue is that if you don't decode text to Perl's internal form, it assumes that it's Latin-1.

So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?

Not knowing what those mean, I'm not saying either one, to my knowledge. What I understand, however, is that Perl, given a scalar with bytes in it, will treat it as latin-1 unless the utf8 flag is turned on.

This is a correct assertion as to Perl's behavior.  As far as PostgreSQL is/should be concerned in this case, this is the correct handling for URI::Escape,

Right, so no postgres bug here.. Postgres showing é instead of é is
right as far as its concerned.

PostgreSQL should do everything it can to decode to Perl's internal format before passing arguments, and to decode from Perl's internal format on output.

+1 on the original sentiment, but only for the case that we're dealing with data that is passed in/out as arguments.  In the case that the server_encoding is UTF-8, this is as trivial as a few macros on the underlying SVs for text-like types.  If the server_encoding is SQL_ASCII (= byte soup), this is a trivial case of doing nothing with the conversion regardless of data type.

Right and thats what we do for the above. Minus some mis-handling of
non character datatypes like bytea in the UTF-8 case.

For any other server_encoding, the data would need to be converted from the server_encoding to UTF-8, presumably using the built-in conversions before passing it off to the first code path.  A similar handling would need to be done for the return values, again datatype-dependent.

Yeah, thats what we *should* do. Right now we just leave it as byte
soup for the user to decode/encode. :(

[ correctness of perl character ops in the non utf8 case] One thought I had was that we could expose the server_encoding to the plperl interpreters in a special variable to make it easy to explicitly decode...

Should not need to do anything as complicated as that. Can just encode
the string to utf8 before we hand it off to perl.

[...]

$ perl -MURI::Escape -e'print length(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon}))'
28

$ perl -MEncode -MURI::Escape -e'print length(decode_utf8(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon})))'
27

[...]

As shown above, the character length for the example should be 27, while the octet length for the UTF-8 encoded version is 28.  I've reviewed the source of URI::Escape, and can say definitively that: a) regular uri_escape does not handle > 255 code points in the encoding, but there exists a uri_escape_utf8 which will convert the source string to UTF8 first and then escape the encoded value, and

And why should it? properly escaped URIs should have all those
escaped, I imagine. Anyway not really relevant for postgres.

b) uri_unescape has *no* logic in it to automatically decode from UTF8 into perl's internal format (at least as far as the version that I'm looking at, which came with 5.10.1).

Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape().  Hence the maybe it could be
considered a bug in uri_unescape().

Agreed.

-1; if you need to decode from an octets-only encoding, it's your responsibility to do so after you've unescaped it.

-1? thats basically what I said: "... you need to do it (decode the
utf8) *after* you call uri_unescape"

 Perhaps later versions of the URI::Escape module contain a uri_unescape_utf8() function, but it's trivially: sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(shift))}.  This is definitely not a bug in uri_escape, as it is only defined to return octets.

Ahh So -1 because I said maybe you could call it a bug in
uri_unescape(). Really, I was only saying you *might* be able to
consider it a bug-- or perhaps deficiency is a better word, in
uri_unescape iff URI's are defined to have escaped characters as a %
escaped utf8 sequence. I dont know that they do, so I don't know if
its a bug :)

On Fri, Dec 17, 2010 at 18:22, David E. Wheeler <david@kineticode.com> wrote:

On Dec 17, 2010, at 5:04 PM, David E. Wheeler wrote:

see? Either uri_unescape() should be decoding that utf8() or you need
to do it *after* you call uri_unescape().  Hence the maybe it could be
considered a bug in uri_unescape().

Agreed.

On second thought, no. You can in fact encode anything in a URI. URI::Escape can't know what to decode to. So *I believe* it just unescapes the raw bytes. It might be handy for it to have a new function, though, to complement its uri_escape_utf() function:

   sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(@_)) }

Just to make things a bit clearer.

But that's a separate issue from the, erm, inconsistency with which PL/Perl treats encoding and decoding of its inputs and outputs.

Yay! So I think we can finally agree that for Oleg's original test
case postgres was getting right. I hope ? :)

On Fri, Dec 17, 2010 at 18:04, David E. Wheeler <david@kineticode.com> wrote:

On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

Yeah. So I just wrote and tested this function on 9.0 with Perl 5.12.2:

   CREATE OR REPLACE FUNCTION perlgets(
       TEXT
   ) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
      my $text = shift;
      return_next {
          length  => length $text,
          is_utf8 => utf8::is_utf8($text) ? 1 : 0
      };
   $$;

In a utf-8 database:

   utf8=# select * from perlgets('foo');
    length │ is_utf8
   ────────┼─────────
         8 │ t
   (1 row)

In a latin-1 database:

   latin=# select * from perlgets('foo');
    length │ is_utf8
   ────────┼─────────
         8 │ f
   (1 row)

I would argue that in the latter case, is_utf8 should be true, too. That is, PL/Perl should decode from Latin-1 to Perl's internal form.

Just to reiterate in a different way what David C. said, the flag is
irrelevant in this case. Begin set on that input string is the same as
it not being set.

per perldoc perlunicode:
The "UTF8" flag being on does not mean that there are any characters
of code points greater than 255 (or 127) in the scalar or that there
are even any characters in the scalar. What the "UTF8" flag means is
that the sequence of octets in the representation of the scalar is the
sequence of UTF-8 encoded code points of the characters of a string.
The "UTF8" flag being off means that each octet in this representation
encodes a single character with code point 0..255 within the string.

Basically perl has *2* internal forms and certain strings can be
represented in both.

Interestingly, when I created a function that takes a bytea argument, utf8 was *still* enabled in the utf-8 database. That doesn't seem right to me.

Hrm, yeah that seems bogus. Ill have to play with that more.

On Dec 17, 2010, at 10:46 PM, Alex Hunsaker wrote:

But that's a separate issue from the, erm, inconsistency with which PL/Perl treats encoding and decoding of its inputs and outputs.

Yay! So I think we can finally agree that for Oleg's original test
case postgres was getting right. I hope ? :)

Yes, and so was Perl.

David

On Dec 17, 2010, at 9:32 PM, David Christensen wrote:

+1 on the original sentiment, but only for the case that we're dealing with data that is passed in/out as arguments. In the case that the server_encoding is UTF-8, this is as trivial as a few macros on the underlying SVs for text-like types. If the server_encoding is SQL_ASCII (= byte soup), this is a trivial case of doing nothing with the conversion regardless of data type. For any other server_encoding, the data would need to be converted from the server_encoding to UTF-8, presumably using the built-in conversions before passing it off to the first code path. A similar handling would need to be done for the return values, again datatype-dependent.

+1

Recent upgrades of the Encode module included with perl 5.10+ have caused issues wherein circular dependencies between Encode and Encode::Alias have made it impossible to load in a Safe container without major pain. (There may be some better options than I'd had on a previous project, given that we're embedding our own interpreters and accessing more through the XS guts, so I'm not ruling out this possibility completely).

Fortunately, thanks to Tim Bunce, PL/Perl no longer relies on Safe.pm.

Well that works for me. I always use UTF8. Oleg, what was the encoding of your database where you saw the issue?

I'm not sure what the current plperl runtime does as far as marshaling this, but it would be fairly easy to ensure the parameters came in in perl's internal format given a server_encoding of UTF8 and some type introspection to identify the string-like types/text data. (Perhaps any type which had a binary cast to text would be a sufficient definition here. Do domains automatically inherit binary casts from their originating types?)

Their labels are TEXT. I believe that the only type that should not be treated as text is bytea.

2) its not utf8, so we just leave it as octets.

Which mean's Perl will assume that it's Latin-1, IIUC.

This is sub-optimal for non-UTF-8-encoded databases, for reasons I pointed out earlier. This would produce bogus results for any non-UTF-8, non-ASCII, non latin-1 encoding, even if it did not generally bite most people in general usage.

Agreed.

This example seems bogus; wouldn't length be 3 if this is the example text this was run with? Additionally, since all ASCII is trivially UTF-8, I think a better example would be using a string with hi-bit characters so if this was improperly handled the lengths wouldn't match; length($all_ascii) == length(encode_utf8($all_ascii)) vs length($hi_bit) < length(encode_utf8($hi_bit)). I don't see that this test shows us much with the test case as given. The is_utf8() function merely returns the state of the SV_utf8 flag, which doesn't speak to UTF-8 validity (i.e., this need not be set on ascii-only strings, which are still valid in the UTF-8 encoding), nor does it indicate that there are no hi-bit characters in the string (i.e., with encode_utf8($hi_bit_string)), the source string $hi_bit_string (in perl's internal format) with hi-bit characters will have the utf8 flag set, but the return value of encode_utf8 will not, even though the underlying data, as represented in perl will be identical).

Sorry, I probably had a pasto there. how about this?

CREATE OR REPLACE FUNCTION perlgets(
TEXT
) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
my $text = shift;
return_next {
length => length $text,
is_utf8 => utf8::is_utf8($text) ? 1 : 0
};
$$;

utf8=# SELECT * FROM perlgets('“hello”');
length │ is_utf8
────────┼─────────
7 │ t

latin=# SELECT * FROM perlgets('“hello”');
length │ is_utf8
────────┼─────────
11 │ f

(Yes I used Latin-1 curly quotes in that last example). I would argue that it should output the same as the first example. That is, PL/Perl should have decoded the latin-1 before passing the text to the Perl function.

In a latin-1 database:

latin=# select * from perlgets('foo');
length │ is_utf8
────────┼─────────
8 │ f
(1 row)

I would argue that in the latter case, is_utf8 should be true, too. That is, PL/Perl should decode from Latin-1 to Perl's internal form.

See above for discussion of the is_utf8 flag; if we're dealing with latin-1 data or (more precisely in this case) data that has not been decoded from the server_encoding to perl's internal format, this would exactly be the expectation for the state of that flag.

Right. I think that it *should* be decoded.

Interestingly, when I created a function that takes a bytea argument, utf8 was *still* enabled in the utf-8 database. That doesn't seem right to me.

I'm not sure what you mean here, but I do think that if bytea is identifiable as one of the input types, we should do no encoding on the data itself, which would indicate that the utf8 flag for that variable would be unset.

Right.

If this is not currently handled this way, I'd be a bit surprised, as bytea should just be an array of bytes with no character semantics attached to it.

It looks as though it is not handled that way. The utf8 flag *is* set on a bytea string passed to a PL/Perl function in a UTF-8 database.

As shown above, the character length for the example should be 27, while the octet length for the UTF-8 encoded version is 28. I've reviewed the source of URI::Escape, and can say definitively that: a) regular uri_escape does not handle > 255 code points in the encoding, but there exists a uri_escape_utf8 which will convert the source string to UTF8 first and then escape the encoded value, and b) uri_unescape has *no* logic in it to automatically decode from UTF8 into perl's internal format (at least as far as the version that I'm looking at, which came with 5.10.1).

Right.

-1; if you need to decode from an octets-only encoding, it's your responsibility to do so after you've unescaped it. Perhaps later versions of the URI::Escape module contain a uri_unescape_utf8() function, but it's trivially: sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(shift))}. This is definitely not a bug in uri_escape, as it is only defined to return octets.

Right, I think we're agreed on that count. I wouldn't mind seeing a uri_unescape_utf8() though, as it might prevent some confusion.

Yeah, the patch address this part. Right now we just spit out
whatever the internal format happens to be.

Ah, excellent.

I agree with the sentiments that: data (server_encoding) -> function parameters (-> perl internal) -> function return (-> server_encoding). This should be for any character-type data insofar as it is feasible, but ISTR there is already datatype-specific marshaling occurring.

Dunno about that.

There is definitely a lot of confusion surrounding perl's handling of character data; I hope this was able to clear a few things up.

Yes, it helped, thanks!

David

On Sat, Dec 18, 2010 at 20:29, David E. Wheeler <david@kineticode.com> wrote:

On Dec 17, 2010, at 9:32 PM, David Christensen wrote:
   latin=# SELECT * FROM perlgets('“hello”');
    length │ is_utf8
   ────────┼─────────
        11 │ f

(Yes I used Latin-1 curly quotes in that last example).

Erm, latin1 does not have curly quotes, Windows-1252 does. Those are
utf8 quotes AFAICT so 11 is actually right (thats 3 bytes per quote so
that where 11 comes from). If latin1 did have quotes and you used
them you would have gotten the same answer as latin1 is a single byte
encoding. :) I think your terminal tripping you up here.

Postgres also gives the same length:
latin1=# select length('“hello”');
length
--------
11
(1 row)