Re: Passing arguments to views

Josh Berkus wrote:
> Tom,
>
>
>>As for the dependency issue, one man's bug is another man's feature.
>>I think the fact that we don't track the internal dependencies of
>>functions is not all bad. We've certainly seen plenty of complaints
>>about how you can't easily change tables that a view is depending on
>>because the view dependencies block it...
>
>
> I'd agree with this. I write about 150,000 lines of function code a year,
> and if I had to rebuild all of the cascading functions every time I change
> a table they way I have to with views, it would probably add 20% to my
> overall application development time.
>
> BTW, the other thing that we're still TODOing on SRFs (as far as I know) is
> finding ways to change the row estimate for an SRF. It's still a flat
> 1000 in the code, which can cause a lot of bad query plans. I proposed a
> year ago that, as a first step, we allow the function owner to assign a
> static estimate variable to the function (i.e. "average rows returned =
> 5'). This doesn't solve the whole problem of SRF estimates but it would
> be a significant step forwards in being able to use them in queries.
>

This would only seem to work for trivial functions. Most functions that I write
are themselves dependent on underlying tables, and without any idea how many
rows are in the tables, and without any idea of the statistical distribution of
those rows, I can't really say anything like "average rows returned = 5".

What I have wanted for some time is a function pairing system. For each set
returning function F() I create, I would have the option of creating a
statistics function S() which returns a single integer which represents the
guess of how many rows will be returned. S() would be called by the planner,
and the return value of S() would be used to decide the plan. S() would need
access to the table statistics information. I imagine that the system would
want to prevent S() from running queries, and only allow it to call certain
defined table statistics functions and some internal math functions, thereby
avoiding any infinite recursion in the planner. (If S() ran any queries, those
queries would go yet again to the planner, and on down the infinite recursion
you might go.)

Of course, some (possibly most) people could chose not to write an S() for their
F(), and the default of 1000 rows would continue to be used. As such, this new
extension to the system would be backwards compatible to functions which don't
have an S() defined.

Thoughts?