Thread: [BUGS] Multiple evaluation of single reference to function with out parameters
[BUGS] Multiple evaluation of single reference to function with out parameters
From
Joel Hoffman
Date:
Hi,
If I create a function with more than one out parameter, and then refer to it inside parentheses as a record, e.g. select (function()).*, the function appears to be evaluated multiple times, once for every column returned. This seems to be true regardless of whether it's defined as volatile or immutable.
Here's an example:If I create a function with more than one out parameter, and then refer to it inside parentheses as a record, e.g. select (function()).*, the function appears to be evaluated multiple times, once for every column returned. This seems to be true regardless of whether it's defined as volatile or immutable.
# create or replace function foobar(out foo integer, out bar integer) volatile language plpgsql as $$ begin raise notice 'Called'; foo := 1; bar := 2; end; $$;
CREATE FUNCTION
If I call it the usual way, it's only evaluated once:
# select * from foobar();
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
foo | bar
-----+-----
1 | 2
(1 row)
Here the function was called once, and the results are returned correctly. However, if I call it this way,
# select (foobar()).*;
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
NOTICE: 00000: Called
LOCATION: exec_stmt_raise, pl_exec.c:3165
foo | bar
-----+-----
1 | 2
(1 row)
This way the function seems to be called separately for each column it returns, but the results are only returned once. If I define it with three out parameters, it's called three times.
As far as I can tell, this behavior has been the same since at least version 8.2 and up through 10 beta 4, but I can't find any references to it and it seems very surprising. It could certainly cause unexpected results if the function has side effects. Is this a bug?
Joel
Re: [BUGS] Multiple evaluation of single reference to function without parameters
From
"David G. Johnston"
Date:
On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com> wrote:
If I create a function with more than one out parameter, and then refer to it inside parentheses as a record, e.g. select (function()).*, the function appears to be evaluated multiple times, once for every column returned. This seems to be true regardless of whether it's defined as volatile or immutable.
...
As far as I can tell, this behavior has been the same since at least version 8.2 and up through 10 beta 4, but I can't find any references to it and it seems very surprising. It could certainly cause unexpected results if the function has side effects. Is this a bug?
It is not a bug or likely to be fixed. LATERAL makes the need for function invocation in the manner you describe nearly unnecessary and you can use OFFSET 0 in other cases to put the function call in a subquery and place the (composite).* expressionin the upper-level.
There is a cautionary note somewhere in docs about this. It is a parser byproduct. The star gets expanded at parse time to individual and independent column names. What happens is exactly what you'd expect if you tried to write the query without resorting to using ".*"
David J.
Re: [BUGS] Multiple evaluation of single reference to function without parameters
From
Joel Hoffman
Date:
Thanks for the response. Here's the documentation reference:
https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE
Not a bug, but I do think it's very surprising behavior. https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE
On Fri, Sep 22, 2017 at 5:22 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com> wrote:If I create a function with more than one out parameter, and then refer to it inside parentheses as a record, e.g. select (function()).*, the function appears to be evaluated multiple times, once for every column returned. This seems to be true regardless of whether it's defined as volatile or immutable....As far as I can tell, this behavior has been the same since at least version 8.2 and up through 10 beta 4, but I can't find any references to it and it seems very surprising. It could certainly cause unexpected results if the function has side effects. Is this a bug?It is not a bug or likely to be fixed. LATERAL makes the need for function invocation in the manner you describe nearly unnecessary and you can use OFFSET 0 in other cases to put the function call in a subquery and place the (composite).* expressionin the upper-level.There is a cautionary note somewhere in docs about this. It is a parser byproduct. The star gets expanded at parse time to individual and independent column names. What happens is exactly what you'd expect if you tried to write the query without resorting to using ".*"David J.
Re: [BUGS] Multiple evaluation of single reference to function without parameters
From
Pavel Stehule
Date:
2017-09-23 4:49 GMT+02:00 Joel Hoffman <joel.hoffman@gmail.com>:
Thanks for the response. Here's the documentation reference:Not a bug, but I do think it's very surprising behavior.
https://www.postgresql.org/docs/9.6/static/rowtypes.html# ROWTYPES-USAGE
yes, but the fix is very simple - you just need only one level of nested query more - or you call function from FROM clause.
The reason of this behave is given by implementation, that is very simple. Nobody had too issues with it and nobody sent a patch to change it.
Regards
Pavel
JoelOn Fri, Sep 22, 2017 at 5:22 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:On Friday, September 22, 2017, Joel Hoffman <joel.hoffman@gmail.com> wrote:If I create a function with more than one out parameter, and then refer to it inside parentheses as a record, e.g. select (function()).*, the function appears to be evaluated multiple times, once for every column returned. This seems to be true regardless of whether it's defined as volatile or immutable....As far as I can tell, this behavior has been the same since at least version 8.2 and up through 10 beta 4, but I can't find any references to it and it seems very surprising. It could certainly cause unexpected results if the function has side effects. Is this a bug?It is not a bug or likely to be fixed. LATERAL makes the need for function invocation in the manner you describe nearly unnecessary and you can use OFFSET 0 in other cases to put the function call in a subquery and place the (composite).* expressionin the upper-level.There is a cautionary note somewhere in docs about this. It is a parser byproduct. The star gets expanded at parse time to individual and independent column names. What happens is exactly what you'd expect if you tried to write the query without resorting to using ".*"David J.