Thread: extra function calls from query returning composite type
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
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;
--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
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.
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
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
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.
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