ExecMakeTableFunctionResult vs. pre-evaluated functions
I've spent today messing with making the planner substitute inline
definitions of simple SQL functions, per the comment in
src/backend/optimizer/util/clauses.c:
* XXX Possible future improvement: if the func is SQL-language, and its
* definition is simply "SELECT expression", we could parse and substitute
* the expression here. This would avoid much runtime overhead, and perhaps
* expose opportunities for constant-folding within the expression even if
* not all the func's input args are constants. It'd be appropriate to do
* that here, not in the parser, since we wouldn't want it to happen until
* after rule substitution/rewriting.
It seems to work 99%, but I'm seeing this failure in the regression
tests:
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
! ERROR: ExecMakeTableFunctionResult: expression is not a function call
which of course happens because the table-function expression has been
reduced to just a constant "1" by the time the executor sees it.
A grotty answer is to not apply constant-expression folding to table
function RTE entries. A better answer would be to make
ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
it should do if presented a non-function-call expression tree. Any
thoughts?
regards, tom lane
PS: another little problem is
regression=# explain SELECT * FROM getfoo(1) AS t1;
server closed the connection unexpectedly
but I'm sure that's just a lack of flexibility in explain.c ...
Tom Lane wrote:
It seems to work 99%, but I'm seeing this failure in the regression
tests:CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
! ERROR: ExecMakeTableFunctionResult: expression is not a function callwhich of course happens because the table-function expression has been
reduced to just a constant "1" by the time the executor sees it.A grotty answer is to not apply constant-expression folding to table
function RTE entries. A better answer would be to make
ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
it should do if presented a non-function-call expression tree. Any
thoughts?
If presented with a non-function-call expression tree, can we always evaluate
it to produce a scalar constant (if it isn't already)? If so, why not do that,
create a one row, one column tuplestore, and exit? It's really no different
than a function call that does the same, is it?
Joe
Joe Conway <mail@joeconway.com> writes:
Tom Lane wrote:
A grotty answer is to not apply constant-expression folding to table
function RTE entries. A better answer would be to make
ExecMakeTableFunctionResult more flexible, but I'm not quite sure what
it should do if presented a non-function-call expression tree. Any
thoughts?
If presented with a non-function-call expression tree, can we always evaluate
it to produce a scalar constant (if it isn't already)? If so, why not do that,
create a one row, one column tuplestore, and exit? It's really no different
than a function call that does the same, is it?
Yeah, that's probably a reasonable approach to take. It would fail if
we had an expression that returned a non-scalar value (viz. a set),
but the constant-folder won't try to fold or inline anything that
returns a set, so you shouldn't see any problem in practice.
We do need to do something about this, since even without the inlining
code there's a problem: the only reason the regression example works in
7.3 is that the constant-simplifier doesn't fire. Which it would, if
the function were marked as immutable, as would be reasonable to do.
regression=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)
regression=# CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;'
regression-# LANGUAGE SQL immutable;
CREATE FUNCTION
regression=# SELECT * FROM getfoo(1) AS t1;
ERROR: ExecMakeTableFunctionResult: expression is not a function call
regards, tom lane
Joe Conway <mail@joeconway.com> writes:
If presented with a non-function-call expression tree, can we always evaluate
it to produce a scalar constant (if it isn't already)? If so, why not do that,
create a one row, one column tuplestore, and exit? It's really no different
than a function call that does the same, is it?
Yeah, that's probably a reasonable approach to take. It would fail if
we had an expression that returned a non-scalar value (viz. a set),
but the constant-folder won't try to fold or inline anything that
returns a set, so you shouldn't see any problem in practice.
Actually, it turns out to be easy to make ExecMakeTableFunctionResult
cope with expressions returning sets as well. It's the same as the
ValuePerCall protocol we defined for table functions (no surprise,
since that protocol was deliberately modeled on the existing
implementation of set-returning expressions). So it's done.
regards, tom lane