Thread: plperl no longer provides string representations of composite values
Composite types are converted to and from Perl hashes since commit
REL9_1_ALPHA4~146 (Convert Postgres arrays to Perl arrays on PL/perl input
arguments; 2011-02-17), but are not stringified as claimed by the commit
message and release notes (unless nested in an array).
To illustrate:
CREATE TYPE foo AS (bar INTEGER, baz TEXT);
DO $$ my $val = spi_exec_query(q< SELECT ROW(42,'test')::foo AS col >)->{rows}->[0]->{col}; elog(NOTICE, "$val
".ref($val))$$ LANGUAGE plperl;
NOTICE: HASH(0xb864a744) HASH
DO $$ my $val = spi_exec_query(q< SELECT ARRAY[ ROW(42,'test')::foo ] AS col >)->{rows}->[0]->{col}; elog(NOTICE, "$val
".ref($val))$$ LANGUAGE plperl;
NOTICE: {"(42,test)"} PostgreSQL::InServer::ARRAY
On pg 9.0 the expected (but unblessed) strings (42,test) and {"(42,test)"}
are returned respectively.
To make matters worse, received values cannot be used in queries because
spi_exec_prepared() simply ignores hash arguments:
# DO $$ my $q = spi_prepare('SELECT $1 AS col', 'foo' ); elog(NOTICE, spi_exec_prepared($q, { bar=>42, baz=>'test' }
)->{rows}->[0]->{col})$$ LANGUAGE plperl;
ERROR: spi_exec_prepared: expected 1 argument(s), 0 passed at line 1.
Again except if nested in an array:
# DO $$ my $q = spi_prepare('SELECT $1 AS col', 'foo[]'); elog(NOTICE, spi_exec_prepared($q, [{ bar=>42, baz=>'test'
}])->{rows}->[0]->{col})$$ LANGUAGE plperl;
NOTICE: {"(42,test)"}
While the intended feature would be very welcome as well, not being able
to convert to text is a serious regression.
Thanks and regards,
--
Mischa
On Fri, Oct 28, 2011 at 16:42, Mischa POSLAWSKY <postgres@shiar.org> wrote:
> Composite types are converted to and from Perl hashes since commit
> REL9_1_ALPHA4~146 (Convert Postgres arrays to Perl arrays on PL/perl input
> arguments; 2011-02-17), but are not stringified as claimed by the commit
> message and release notes (unless nested in an array).
>
> To illustrate:
>
> CREATE TYPE foo AS (bar INTEGER, baz TEXT);
> DO $$ my $val =3D spi_exec_query(q< SELECT =C2=A0 =C2=A0 =C2=A0 =C2=A0ROW=
(42,'test')::foo =C2=A0 AS col >)->{rows}->[0]->{col}; elog(NOTICE, "$val "=
.ref($val)) $$ LANGUAGE plperl;
> NOTICE: =C2=A0HASH(0xb864a744) HASH
I take it this is your complaint.
> DO $$ my $val =3D spi_exec_query(q< SELECT ARRAY[ ROW(42,'test')::foo ] A=
S col >)->{rows}->[0]->{col}; elog(NOTICE, "$val ".ref($val)) $$ LANGUAGE p=
lperl;
> NOTICE: {"(42,test)"} PostgreSQL::InServer::ARRAY
And you are saying this is OK.
You'll note the release notes and commit message only mention that
arrays have the backwards compatible mode. We didn't make a backwards
compatible string version for composites as you could already pass
those in prior to 9.1 (so really nothing changed). What did change is
we unified most of the code so the fact that spi could return arrays
also meant it could return hashes.
So yes that seems like a bit of an oversight. We could fix it by doing
something similar to what we did for arrays. That is turn composites
from hashrefs to blessed objects and overload their string operator.
I'm a bit worried that cure would be worse than whats "broken". Any
code that was using ref to test for 'HASH' would now break. Arrays did
not really have this concern because they were always a string prior
to 9.1. Composites on the other hand have been around a lot longer--
and while I know I don't have any checks like that in /my/ plperl
code, I don't think its too far fetched to imagine someone else does.
I suppose this is a long winded way of saying i'm not very inclined to
dump time into "fixing" this unless there is some kind of consensus
that we should.
Anyway I see 2 work arounds:
- You could change your query to return text:
SELECT ROW(42,'test')::foo::text
- use the new encode_typed_literal() function 9.1 provides
> To make matters worse, received values cannot be used in queries because
> spi_exec_prepared() simply ignores hash arguments:
>
> # DO $$ my $q =3D spi_prepare('SELECT $1 AS col', 'foo' =C2=A0); elog(NOT=
ICE, spi_exec_prepared($q, =C2=A0{ bar=3D>42, baz=3D>'test' } )->{rows}->[0=
]->{col}) $$ LANGUAGE plperl;
> ERROR: =C2=A0spi_exec_prepared: expected 1 argument(s), 0 passed at line =
1.
Not really, the error here is you can't call spi_exec_prepared() like that =
try:
spi_exec_prepared($q, {}, {bar=3D>42,...});
Per the documentation the prototype is:
spi_exec_prepared(plan [, attributes], arguments)
We can't distinguish attributes (which are a hashref) from a composite
type (also a hashref), so you need to pass an explicit value for
attributes. That's why its complaining about it expected 1 argument
but 0 were passed. If you pass some other type of reference (like an
array) we know it can't be attributes so we use that as the first
argument.
It may not be pretty, but we didn't want to break the api :-(.
Re: plperl no longer provides string representations of composite values
From
Mischa POSLAWSKY
Date:
Alex Hunsaker skribis 2011-10-28 22:03 (-0600):
> On Fri, Oct 28, 2011 at 16:42, Mischa POSLAWSKY <postgres@shiar.org> wrote:
> > Composite types are converted to and from Perl hashes since commit
> > REL9_1_ALPHA4~146 (Convert Postgres arrays to Perl arrays on PL/perl input
> > arguments; 2011-02-17), but are not stringified as claimed by the commit
> > message and release notes (unless nested in an array).
> >
> > To illustrate:
> >
> > CREATE TYPE foo AS (bar INTEGER, baz TEXT);
> > DO $$ my $val = spi_exec_query(q< SELECT ROW(42,'test')::foo AS col >)->{rows}->[0]->{col}; elog(NOTICE,
"$val".ref($val)) $$ LANGUAGE plperl;
> > NOTICE: HASH(0xb864a744) HASH
>
> I take it this is your complaint.
>
> > DO $$ my $val = spi_exec_query(q< SELECT ARRAY[ ROW(42,'test')::foo ] AS col >)->{rows}->[0]->{col}; elog(NOTICE,
"$val".ref($val)) $$ LANGUAGE plperl;
> > NOTICE: {"(42,test)"} PostgreSQL::InServer::ARRAY
>
> And you are saying this is OK.
Exactly.
> You'll note the release notes and commit message only mention that
> arrays have the backwards compatible mode. We didn't make a backwards
> compatible string version for composites as you could already pass
> those in prior to 9.1 (so really nothing changed). What did change is
> we unified most of the code so the fact that spi could return arrays
> also meant it could return hashes.
Isn't that a more important reason to provide compatibility objects though?
Regardless of accepted output, the changed input breaks all code written to
parse and/or modify a string value.
> So yes that seems like a bit of an oversight. We could fix it by doing
> something similar to what we did for arrays. That is turn composites
> from hashrefs to blessed objects and overload their string operator.
> I'm a bit worried that cure would be worse than whats "broken". Any
> code that was using ref to test for 'HASH' would now break. Arrays did
> not really have this concern because they were always a string prior
> to 9.1. Composites on the other hand have been around a lot longer--
I'm curious about where except for voluntary return values; in all of my
usage I've always received string representations upto 9.0.
> and while I know I don't have any checks like that in /my/ plperl
> code, I don't think its too far fetched to imagine someone else does.
> I suppose this is a long winded way of saying i'm not very inclined to
> dump time into "fixing" this unless there is some kind of consensus
> that we should.
>
> Anyway I see 2 work arounds:
> - You could change your query to return text:
> SELECT ROW(42,'test')::foo::text
Don't have a choice for function/trigger parameters.
> - use the new encode_typed_literal() function 9.1 provides
This helps, but seems to be unusable in cases where the exact composite type
is unknown.
> > # DO $$ my $q = spi_prepare('SELECT $1 AS col', 'foo' ); elog(NOTICE, spi_exec_prepared($q, { bar=>42,
baz=>'test'} )->{rows}->[0]->{col}) $$ LANGUAGE plperl;
> > ERROR: spi_exec_prepared: expected 1 argument(s), 0 passed at line 1.
>
> Not really, the error here is you can't call spi_exec_prepared() like that try:
> spi_exec_prepared($q, {}, {bar=>42,...});
>
> Per the documentation the prototype is:
> spi_exec_prepared(plan [, attributes], arguments)
I see; sorry, that's fine then.
--
Mischa