Обсуждение: Returning Composite Types from C functions

Hi all,

CREATE TYPE my_type AS (a int,b int,c int,d int,e int
);

CREATE FUNCTION text_to_my_type(text)
RETURNS my_type
AS 'my_lib.so'
LANGUAGE 'C' IMMUTABLE STRICT;


CREATE CAST (text AS my_type) WITH FUNCTION text_to_my_type (text);

SELECT ('1:2:3:4:5'::text::my_type).*;

This results in the text_to_my_type(text) function being called no less
than 5 times. Once for each element.

Is this the desired behaviour, or a bug?

On Sat, Jun 18, 2005 at 09:18:34PM +1000, John Hansen wrote:
> 
> SELECT ('1:2:3:4:5'::text::my_type).*;
> 
> This results in the text_to_my_type(text) function being called no less
> than 5 times. Once for each element.
> 
> Is this the desired behaviour, or a bug?

It's a known behavior with functions that return composite types.
Apparently it's not easy to fix:

http://archives.postgresql.org/pgsql-hackers/2005-04/msg00971.php

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr [mailto:mike@fuhr.org] Wrote:
> Sent: Saturday, June 18, 2005 9:56 PM
> To: John Hansen
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Returning Composite Types from C functions
>
> On Sat, Jun 18, 2005 at 09:18:34PM +1000, John Hansen wrote:
> >
> > SELECT ('1:2:3:4:5'::text::my_type).*;
> >
> > This results in the text_to_my_type(text) function being called no
> > less than 5 times. Once for each element.
> >
> > Is this the desired behaviour, or a bug?
>
> It's a known behavior with functions that return composite types.
> Apparently it's not easy to fix:
>
> http://archives.postgresql.org/pgsql-hackers/2005-04/msg00971.php

There is a workaround tho, so should be fixable:

SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;

Or am I missing something?

>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
>

... John

On Sat, Jun 18, 2005 at 10:03:38PM +1000, John Hansen wrote:
> 
> There is a workaround tho, so should be fixable:
> 
> SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;
> 
> Or am I missing something?

I don't know enough about PostgreSQL internals so I'll have to defer
to the developers.  But here's a case where the above workaround
doesn't work -- in my tests, the cast function is called once per
column per row, or ten times:

CREATE TABLE foo (t text);
INSERT INTO foo VALUES ('1:2:3:4:5');
INSERT INTO foo VALUES ('6:7:8:9:10');
SELECT (a.b).* FROM (SELECT t::my_type AS b FROM foo) AS a;

What do you get?  Can you think of a workaround for this case?
Maybe one of the developers can comment on why your example calls
the function only once and mine calls it multiple times per row,
even though they look similar.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

"John Hansen" <john@geeknet.com.au> writes:
> There is a workaround tho, so should be fixable:
> SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS b) AS a;
> Or am I missing something?

Try it ;-)
        regards, tom lane

Yes, it worked for me,...

But my point is the workaround shouldn't be nescessary....

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Saturday, June 18, 2005 11:36 PM
> To: John Hansen
> Cc: Michael Fuhr; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Returning Composite Types from C functions
>
> "John Hansen" <john@geeknet.com.au> writes:
> > There is a workaround tho, so should be fixable:
> > SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS
> b) AS a;
> > Or am I missing something?
>
> Try it ;-)
>
>             regards, tom lane
>
>

"John Hansen" <john@geeknet.com.au> writes:
>>> SELECT (a.b).* FROM (SELECT ('1:2:3:4:5'::text::my_type) AS 
>>> b) AS a; 
>>> Or am I missing something?
>> 
>> Try it ;-)

> Yes, it worked for me,... 

It depends on your test case, but in many situations the planner will
flatten that into the same result as the other.  The basic problem is
that "(foo).*" is expanded to "(foo).f1, (foo).f2, ..." which is OK if
foo is just a variable referring to a subquery output --- but if the
subquery gets flattened into the parent then your function appears
textually multiple times in the resulting query.

There's been some discussion of disabling flattening when the subquery
output targetlist contains any volatile functions, but that seems like
rather a performance-losing answer.  It doesn't completely address the
complaint anyway since even a non-volatile function might be expensive
to compute.
        regards, tom lane

Michael Fuhr <mike@fuhr.org> writes:
> Maybe one of the developers can comment on why your example calls
> the function only once and mine calls it multiple times per row,
> even though they look similar.

Look at the EXPLAIN results --- one case gets flattened into a single
plan node and the other doesn't.  I know exactly where that particular
skeleton is buried, too:
/* * Hack: don't try to pull up a subquery with an empty jointree. * query_planner() will correctly generate a Result
planfor a * jointree that's totally empty, but I don't think the right things * happen if an empty FromExpr appears
lowerdown in a jointree. Not * worth working hard on this, just to collapse SubqueryScan/Result * into Result... */if
(subquery->jointree->fromlist== NIL)    return false;
 

        regards, tom lane