Re: BUG #2683: spi_exec_query in plperl returns column names which are not marked as UTF8

The following bug has been logged online:

Bug reference: 2683
Logged by: Vitali Stupin
Email address: Vitali(dot)Stupin(at)ria(dot)ee
PostgreSQL version: 8.1.4
Operating system: sparc-sun-solaris2.10
Description: spi_exec_query in plperl returns column names which are
not marked as UTF8
Details:

If database uses UTF8 encoding, then spi_exec_query in plperl should return
query results in UTF8 encoding. But unfortunately only data is marked as
UTF8, while column names are not.

The following test function demonstrates this bug:
CREATE OR REPLACE FUNCTION spi_test("varchar")
RETURNS varchar AS
$BODY$
my $result = spi_exec_query($_[0]);
my @row_keys = keys %{$result->{rows}};
elog(WARNING, 'Column name: "' . $row_keys[0] . '" is UTF8: ' .
(utf8::is_utf8($row_keys[0]) ? 'y' : 'n'));
my $test_result = join(',', @row_keys) . '|';
for $row_key (@row_keys){
$value = $result->{rows}[0]->{$row_key};
elog(WARNING, 'Value: "' . $value . '" is UTF8: ' . (utf8::is_utf8($value)
? 'y' : 'n'));
$test_result .= $value . '|';
}
elog(WARNING, 'Result: "' . $test_result . '" is UTF8: ' .
(utf8::is_utf8($test_result) ? 'y' : 'n'));
return $test_result;
$BODY$
LANGUAGE 'plperlu' VOLATILE;

When it is called as:
select spi_test('select ''val_'' AS "col_"')

The following output is produced:
"col_|val_|"

And the generated warnings are:
WARNING: Column name: "col_" is UTF8: n
WARNING: Value: "val_" is UTF8: y
WARNING: Result: "col_|val_|" is UTF8: y

Therefore it is possible to make a conclusion, that after execution of
query, column names contain a valid UTF8 string without UTF8 flag. When two
strings, one of which is marked as UTF8 and the other marked as non UTF8,
are concatenated, Perl automatically tries to convert column name into UTF8
as if it was in ISO-8859-1 encoding. As a result new string contains invalid
column name.

This bug can also be reproduced if spi_exec_query makes SELECT from the
actual table, when column names contain UTF8 characters.

This bug was also reproduced on:
PostgreSQL 8.1.3 on sparc-sun-solaris2.10;
PostgreSQL 8.1.3 on i686-pc-linux-gnu.

"Vitali Stupin" <Vitali(dot)Stupin(at)ria(dot)ee> writes:
> If database uses UTF8 encoding, then spi_exec_query in plperl should return
> query results in UTF8 encoding. But unfortunately only data is marked as
> UTF8, while column names are not.

It looks to me like basically everywhere in plperl.c that does newSVpv()
should follow it with

#if PERL_BCDVERSION >= 0x5006000L
if (GetDatabaseEncoding() == PG_UTF8)
SvUTF8_on(sv);
#endif

whereas currently there are only a couple of places that do that.

I'm tempted to consolidate this into a function on the order of
newSVstring(const char *) or some such. Comments?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> #if PERL_BCDVERSION >= 0x5006000L
...
> #endif
...
> I'm tempted to consolidate this into a function on the order of
> newSVstring(const char *) or some such. Comments?

+1

I suggested at one point raising the minimum requirement for Perl to
5.6 (which came out way back in 2000, so we're unlikely to upset anyone).
If we haven't done that already, this would be a good chance as we
can get rid of that ugly #if block.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200610151328
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFMnA9vJuQZxSWSsgRAoS8AKDxCVgCLggaDH+d1BbcEROZORqhEwCg6qe+
wrVsJMi+EKGvnmVGK4MroaM=
=Oi3J
-----END PGP SIGNATURE-----

I wrote:
> It looks to me like basically everywhere in plperl.c that does newSVpv()
> should follow it with
>
> #if PERL_BCDVERSION >= 0x5006000L
> if (GetDatabaseEncoding() == PG_UTF8)
> SvUTF8_on(sv);
> #endif

Experimentation proved that this was insufficient to fix Vitali's
problem --- the string he's unhappy about is actually a hash key entry,
and there's no documented way to mark the second argument of hv_store()
as being a UTF-8 string. Some digging in the Perl source code found
that since at least Perl 5.8.0, hv_fetch and hv_store recognize a
negative key length as meaning a UTF-8 key (ick!!), so I used that hack.
I am not sure there is any reasonable fix available in Perl 5.6.x.

Attached patch applied to HEAD, but I'm not going to risk back-patching
it without some field testing.

regards, tom lane

On Sun, Oct 15, 2006 at 05:34:21PM -0000, Greg Sabino Mullane wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Tom Lane wrote:
> > #if PERL_BCDVERSION >= 0x5006000L
> ...
> > #endif
> ...
> > I'm tempted to consolidate this into a function on the order of
> > newSVstring(const char *) or some such. Comments?
>
> +1
>
> I suggested at one point raising the minimum requirement for Perl to
> 5.6 (which came out way back in 2000, so we're unlikely to upset
> anyone). If we haven't done that already, this would be a good
> chance as we can get rid of that ugly #if block.

I'd like to suggest that raise that minimum requirement for Perl to
5.8 for 8.3, as Perl 5.8 will be about five years old by then. I
understand that some people are running versions of some operating
system or other so ancient that Perl 5.8 did not come pre-installed
with it, but we can't be supporting other projects backwards into
eternity. Supporting our own is already plenty of work.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
> I'd like to suggest that raise that minimum requirement for Perl to
> 5.8 for 8.3, as Perl 5.8 will be about five years old by then.

Well, we're still supporting some OS versions that are way over five
years old. ISTM the real question is what do we buy if we make such
a restriction? Getting rid of a few small ifdefs doesn't seem like
an adequate reason. Is there some major improvement we could make?

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane asked:
> Well, we're still supporting some OS versions that are way over five
> years old. ISTM the real question is what do we buy if we make such
> a restriction? Getting rid of a few small ifdefs doesn't seem like
> an adequate reason. Is there some major improvement we could make?

Well, as you just pointed out in the last commit, Unicode-aware hashes.
Unicode in general was cleaned up and overhauled in 5.8, so if pl/perl
is going to make a serious attempt to support it, it probably should
require 5.8.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200610151657
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFMqG1vJuQZxSWSsgRApmpAJ9B29AhaBGnEA6h7o5FgemlrIUgzgCgtTZu
QZkaGYy0iH0JnHoZGoE/KRE=
=hgIs
-----END PGP SIGNATURE-----

Tom Lane wrote:
> I wrote:
>> It looks to me like basically everywhere in plperl.c that does newSVpv()
>> should follow it with
>>
>> #if PERL_BCDVERSION >= 0x5006000L
>> if (GetDatabaseEncoding() == PG_UTF8)
>> SvUTF8_on(sv);
>> #endif
>
> Experimentation proved that this was insufficient to fix Vitali's
> problem --- the string he's unhappy about is actually a hash key entry,
> and there's no documented way to mark the second argument of hv_store()
> as being a UTF-8 string. Some digging in the Perl source code found
> that since at least Perl 5.8.0, hv_fetch and hv_store recognize a
> negative key length as meaning a UTF-8 key (ick!!), so I used that hack.
> I am not sure there is any reasonable fix available in Perl 5.6.x.
>
> Attached patch applied to HEAD, but I'm not going to risk back-patching
> it without some field testing.
>

Hmm. That negative pointer hack is mighty ugly.

I am also wondering, now that it's been raised, if we need to issue a "use
utf8;" in the startup code, so that literals in the code get the right
encoding.

cheers

andrew

On Sun, Oct 15, 2006 at 04:43:17PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > I'd like to suggest that raise that minimum requirement for Perl
> > to 5.8 for 8.3, as Perl 5.8 will be about five years old by then.
>
> Well, we're still supporting some OS versions that are way over five
> years old. ISTM the real question is what do we buy if we make such
> a restriction? Getting rid of a few small ifdefs doesn't seem like
> an adequate reason. Is there some major improvement we could make?

UTF-8 handling much improved in 5.8. Just anecdotally, a former Very
Large Client is really suffering from having to maintain two branches
of every piece of Perl code in the house that touches anything UTF-8.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
> On Sun, Oct 15, 2006 at 04:43:17PM -0400, Tom Lane wrote:
>> ISTM the real question is what do we buy if we make such
>> a restriction? Getting rid of a few small ifdefs doesn't seem like
>> an adequate reason. Is there some major improvement we could make?

> UTF-8 handling much improved in 5.8.

And? Sure there are good reasons why someone might want to use 5.8
instead of 5.6, but how is that a reason for us to remove 5.6 support?

regards, tom lane

"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:
> I am also wondering, now that it's been raised, if we need to issue a "use
> utf8;" in the startup code, so that literals in the code get the right
> encoding.

Good question. I took care to ensure that the code strings passed to
Perl are marked as UTF8; perhaps that makes it happen implicitly?
If not, are there any downsides to issuing "use utf8"?

regards, tom lane

On Sun, Oct 15, 2006 at 04:50:15PM -0500, Andrew Dunstan wrote:
> Tom Lane wrote:
> > I wrote:
> >> It looks to me like basically everywhere in plperl.c that does
> >> newSVpv() should follow it with
> >>
> >> #if PERL_BCDVERSION >= 0x5006000L
> >> if (GetDatabaseEncoding() == PG_UTF8)
> >> SvUTF8_on(sv);
> >> #endif
> >
> > Experimentation proved that this was insufficient to fix Vitali's
> > problem --- the string he's unhappy about is actually a hash key
> > entry, and there's no documented way to mark the second argument
> > of hv_store() as being a UTF-8 string. Some digging in the Perl
> > source code found that since at least Perl 5.8.0, hv_fetch and
> > hv_store recognize a negative key length as meaning a UTF-8 key
> > (ick!!), so I used that hack. I am not sure there is any
> > reasonable fix available in Perl 5.6.x.
> >
> > Attached patch applied to HEAD, but I'm not going to risk
> > back-patching it without some field testing.
>
> Hmm. That negative pointer hack is mighty ugly.
>
> I am also wondering, now that it's been raised, if we need to issue
> a "use utf8;" in the startup code, so that literals in the code get
> the right encoding.

That would be a reason to go to 5.8, as 'use utf8;' is tricky at best
in 5.6.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Sun, Oct 15, 2006 at 06:12:43PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Sun, Oct 15, 2006 at 04:43:17PM -0400, Tom Lane wrote:
> >> ISTM the real question is what do we buy if we make such a
> >> restriction? Getting rid of a few small ifdefs doesn't seem like
> >> an adequate reason. Is there some major improvement we could
> >> make?
>
> > UTF-8 handling much improved in 5.8.
>
> And? Sure there are good reasons why someone might want to use 5.8
> instead of 5.6, but how is that a reason for us to remove 5.6
> support?

At some point, we will find something where we will have to duplicate
some large hunk of 5.8's functionality to support 5.6. Why wait for
that to happen? Murphy's Law says it will happen in a bug fix between
two minor releases.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
> At some point, we will find something where we will have to duplicate
> some large hunk of 5.8's functionality to support 5.6.

No, we won't; we are not in the business of fixing Perl bugs. You
haven't given any reason why someone who is using 5.6 and is happy with
it shouldn't be able to continue to use it with PG.

regards, tom lane

On Sun, Oct 15, 2006 at 07:07:18PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > At some point, we will find something where we will have to duplicate
> > some large hunk of 5.8's functionality to support 5.6.
>
> No, we won't; we are not in the business of fixing Perl bugs.

My point exactly.

> You haven't given any reason why someone who is using 5.6 and is
> happy with it shouldn't be able to continue to use it with PG.

In my experience, people aren't "happy with" 5.6. Instead, they've
got a mandate to maintain support for it, and this could be an
argument for upgrading.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter wrote:
> On Sun, Oct 15, 2006 at 07:07:18PM -0400, Tom Lane wrote:
> > David Fetter <david(at)fetter(dot)org> writes:
> > > At some point, we will find something where we will have to duplicate
> > > some large hunk of 5.8's functionality to support 5.6.
> >
> > No, we won't; we are not in the business of fixing Perl bugs.
>
> My point exactly.
>
> > You haven't given any reason why someone who is using 5.6 and is
> > happy with it shouldn't be able to continue to use it with PG.
>
> In my experience, people aren't "happy with" 5.6. Instead, they've
> got a mandate to maintain support for it, and this could be an
> argument for upgrading.

Or an argument to dump PostgreSQL.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Oct 16, 2006 at 12:10:42AM -0400, Bruce Momjian wrote:
> David Fetter wrote:
> > On Sun, Oct 15, 2006 at 07:07:18PM -0400, Tom Lane wrote:
> > > David Fetter <david(at)fetter(dot)org> writes:
> > > > At some point, we will find something where we will have to
> > > > duplicate some large hunk of 5.8's functionality to support
> > > > 5.6.
> > >
> > > No, we won't; we are not in the business of fixing Perl bugs.
> >
> > My point exactly.
> >
> > > You haven't given any reason why someone who is using 5.6 and is
> > > happy with it shouldn't be able to continue to use it with PG.
> >
> > In my experience, people aren't "happy with" 5.6. Instead,
> > they've got a mandate to maintain support for it, and this could
> > be an argument for upgrading.
>
> Or an argument to dump PostgreSQL.

I don't know about your experience, but in mine, outfits that hang on
that tightly to outdated software aren't the kind I find any profit in
supporting, either dollar-wise or in terms of personal satisfaction.

That aside, we have a choice here:

1. We can continue to support 5.6 until we can't any more, and
statistically speaking that "can't any more" is quite likely to happen
between two minor releases. In that event, we look flaky by changing
the requirements in mid-cycle.

2. We can give people ample advance notice that we're phasing out
support for Perl 5.6 and earlier, simplify our code base, and look
professional.

Which do you prefer?

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
> 1. We can continue to support 5.6 until we can't any more, and
> statistically speaking that "can't any more" is quite likely to happen
> between two minor releases.

That's a silly and unfounded assertion. What sort of event do you
foresee that is going to make us conclude that we have to remove 5.6
support in a minor release?

regards, tom lane

On Sun, Oct 15, 2006 at 06:15:27PM -0400, Tom Lane wrote:
> "Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:
> > I am also wondering, now that it's been raised, if we need to issue a "use
> > utf8;" in the startup code, so that literals in the code get the right
> > encoding.
>
> Good question. I took care to ensure that the code strings passed to
> Perl are marked as UTF8; perhaps that makes it happen implicitly?
> If not, are there any downsides to issuing "use utf8"?

What "use utf8" does is allow the *source* to be in utf8, thus affecting
what's a valid identifier and such. It doesn't affect the data, for
that you need "use encoding 'utf8'".

It's clear whether you actually want to allow people to put utf8
characters directly into their source (especially if the database is
not in utf8 encoding anyway). There is always the \u{xxxx} escape.

The perlunicode man page describe it better, though I only have
perl5.8. In know the perl5.6 model was different and somewhat more
awkward to use.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> It's clear whether you actually want to allow people to put utf8
> characters directly into their source (especially if the database is
> not in utf8 encoding anyway). There is always the \u{xxxx} escape.

Well, if the database encoding isn't utf8 then we'd not issue any such
command anyway. But if it is, then AFAICS the text of pg_proc entries
could be expected to be utf8 too.

regards, tom lane

On Mon, Oct 16, 2006 at 10:00:13AM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > 1. We can continue to support 5.6 until we can't any more, and
> > statistically speaking that "can't any more" is quite likely to
> > happen between two minor releases.
>
> That's a silly and unfounded assertion. What sort of event do you
> foresee that is going to make us conclude that we have to remove 5.6
> support in a minor release?

I did mention this earlier: a Unicode misbehavior in 5.6 that causes a
data loss or crash, which is not present in 5.8.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!