Thread: extra function calls from query returning composite type

extra function calls from query returning composite type

From
Ronald Peterson
Date:
I added a 'raise notice' to a plpgsql function I was working on
recently, and noticed that my notification was being raised more often
than I expected.  The notification is raised in a function ('getone'
in my example below) that returns a single composite value.  This
function is then called by another function ('getset') that returns a
setof that composite value.  It appears that 'getone' is called once
for each column of my composite type.  I whittled this down to the
following example.

I get the expected result from my query, but I don't understand (what
appear to be) the extra function calls.

delaunay=# select * from getset( 1 );
NOTICE:  id: 1, foo: afoo, bar: abar
NOTICE:  id: 1, foo: afoo, bar: abar
NOTICE:  id: 2, foo: bfoo, bar: bbar
NOTICE:  id: 2, foo: bfoo, bar: bbar
NOTICE:  id: 3, foo: cfoo, bar: cbar
NOTICE:  id: 3, foo: cfoo, bar: cbar

 foo  | bar
------+------
 afoo | abar
 bfoo | bbar
 cfoo | cbar
(3 rows)

CREATE TYPE mytype AS (
  foo TEXT,
  bar TEXT
);

CREATE TABLE dat (
  set INTEGER,
  id INTEGER,
  foo TEXT,
  bar TEXT
);

INSERT INTO dat (set, id, foo, bar)
VALUES (1, 1, 'afoo', 'abar'), (1, 2, 'bfoo', 'bbar'), (1, 3, 'cfoo', 'cbar');

CREATE OR REPLACE FUNCTION
getone(rowid INTEGER)
RETURNS mytype
AS $$
DECLARE
  fooval TEXT;
  barval TEXT;
BEGIN
  SELECT foo, bar
  FROM dat
  WHERE id = rowid
  INTO fooval, barval;

  RAISE NOTICE 'id: %, foo: %, bar: %', rowid, fooval, barval;

  RETURN ROW( fooval, barval );
END;
$$ LANGUAGE PLPGSQL STRICT;

CREATE OR REPLACE FUNCTION
getset(setid INTEGER)
RETURNS SETOF mytype
AS $$
BEGIN
  RETURN QUERY
    SELECT (getone(id)).*
    FROM dat
    WHERE set = setid;
  RETURN;
END;
$$ LANGUAGE PLPGSQL STRICT;

--
Ron Peterson



Re: extra function calls from query returning composite type

From
David G Johnston
Date:
Ronald Peterson wrote
> I added a 'raise notice' to a plpgsql function I was working on
> recently, and noticed that my notification was being raised more often
> than I expected.  The notification is raised in a function ('getone'
> in my example below) that returns a single composite value.  This
> function is then called by another function ('getset') that returns a
> setof that composite value.  It appears that 'getone' is called once
> for each column of my composite type.  I whittled this down to the
> following example.
>
> I get the expected result from my query, but I don't understand (what
> appear to be) the extra function calls.

Working as intended.  It is an implementation artifact.  You have two
options if you are using the latest couple of releases.

Use LATERAL
Use CTE/WITH

WITH funceval AS (
SELECT func_call(...)
)
SELECT (func_call).* FROM funceval

In the CTE version you cause the function to fully resolve without referring
to any of its component columns and then, in the outer query, explode the
result of the composite type.

The LATERAL syntax is documented but basically (not tested or personally
have I had a chance to use the feature myself)...

SELECT *
FROM dat LATERAL getone(dat.id)
;

David J.




--
View this message in context:
http://postgresql.nabble.com/extra-function-calls-from-query-returning-composite-type-tp5832275p5832282.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: extra function calls from query returning composite type

From
Tom Lane
Date:
Ronald Peterson <ron@hub.yellowbank.com> writes:
> I added a 'raise notice' to a plpgsql function I was working on
> recently, and noticed that my notification was being raised more often
> than I expected.  The notification is raised in a function ('getone'
> in my example below) that returns a single composite value.  This
> function is then called by another function ('getset') that returns a
> setof that composite value.  It appears that 'getone' is called once
> for each column of my composite type.  I whittled this down to the
> following example.

> I get the expected result from my query, but I don't understand (what
> appear to be) the extra function calls.

This:

>     SELECT (getone(id)).*

is implemented as SELECT (getone(id)).foo, (getone(id)).bar

If you're using 9.3 or later you could avoid that by recasting the
call as LATERAL, ie

  SELECT go.*
    FROM dat, LATERAL getone(id) AS go
    WHERE set = setid;

            regards, tom lane


Re: extra function calls from query returning composite type

From
Ronald Peterson
Date:
Thanks much.  Didn't know about LATERAL.  That's a solution.  Seems like the implementation could be improved though.  The existence of LATERAL seems to imply that it's possible.  Why introduce more complicated syntax?  Of course the syntax applies to more situations than this one.  But this case seems like it could be improved.
I hate complaining.  Especially about my favorite database.  But when a composite type has many columns, this inefficiency really adds up.  And it's pretty invisible, unless you really look into it.
It's on my list of things to do to buy Tom Lane a beer.  It should, in my opinion, be on everyone's list of things to do who is on this list.  This problem has nothing to do with it.  I'm hoping that, altogether, we buy Tom enough beer that that he considers making this query more efficient.  This might involve impairing his better judgement, but I'm willing to drive to the country of Pennsylvania or wherever it is Tom hangs his hat these days to to buy a beer in the cause of improving this query.  Maybe two beers.  I hope you will all chip in a few beers yourselves, and maybe we can fix this esoteric problem that probably only concerns me.

On Mon, Dec 29, 2014 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ronald Peterson <ron@hub.yellowbank.com> writes:
> I added a 'raise notice' to a plpgsql function I was working on
> recently, and noticed that my notification was being raised more often
> than I expected.  The notification is raised in a function ('getone'
> in my example below) that returns a single composite value.  This
> function is then called by another function ('getset') that returns a
> setof that composite value.  It appears that 'getone' is called once
> for each column of my composite type.  I whittled this down to the
> following example.

> I get the expected result from my query, but I don't understand (what
> appear to be) the extra function calls.

This:

>     SELECT (getone(id)).*

is implemented as SELECT (getone(id)).foo, (getone(id)).bar

If you're using 9.3 or later you could avoid that by recasting the
call as LATERAL, ie

  SELECT go.*
    FROM dat, LATERAL getone(id) AS go
    WHERE set = setid;

                        regards, tom lane



--
--
Ron Peterson



Re: extra function calls from query returning composite type

From
David G Johnston
Date:
Ronald Peterson wrote
> The existence of LATERAL seems to imply that it's possible.

The presence of LATERAL, which applies during FROM clause processing,
implies nothing about what is possible during SELECT-list processing.

This was a problem for a long time and if an easy/possible solution was
possible without LATERAL it would have been done.  LATERAL took a while to
get around to but neatly solves the problem.

I'll agree the being able to write: SELECT col1, col2, func_call(...).*,
col3 for non-set-returning-functions seems to have some value (though such a
function is likely to be defined stable or immutable and so side-steps the
problem - I think) but the most common reason for wanting SELECT
func_call(...) is for a set-returning-function with table-based inputs.

Since your example was set-returning it doesn't add anything to the argument
for not using LATERAL; and there are other problems than just
multiple-evaluation that occur when placing a set-returning-function into
the select-list.

David J.





--
View this message in context:
http://postgresql.nabble.com/extra-function-calls-from-query-returning-composite-type-tp5832275p5832606.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: extra function calls from query returning composite type

From
Merlin Moncure
Date:
On Wed, Dec 31, 2014 at 5:41 PM, Ronald Peterson <ron@hub.yellowbank.com> wrote:
> Thanks much.  Didn't know about LATERAL.  That's a solution.  Seems like the
> implementation could be improved though.  The existence of LATERAL seems to
> imply that it's possible.  Why introduce more complicated syntax?  Of course
> the syntax applies to more situations than this one.  But this case seems
> like it could be improved.

Well, LATERAL is SQL standard syntax, and the SQL standard expressly
forbids using 'lateral refrences' between table expressions (see here:
http://rhaas.blogspot.com/2010/04/finding-our-way-to-lateral.html for
some background info).  As to why they did it, I don't know, but
there' probably some complicated reason where not having that syntax
broke or made ambiguous existing stuff.  Given that, it's not likely
to change.

merlin