>While I trust your judgement actual knowledge is nice. The fact that =
PostGIS is raising the complaint should make us rethink those =
assumptions; and nothing in their code or the minimal
> example strikes me as being improper. That said, I am surprised you =
didn't recommend that the function should be moved to a FROM clause - =
though I believe the code could still optimize > that way without the =
offset 0.
> The PostGIS example involves constructing geometries from a =
definition. I suspect that is not cheap, which suggests that maybe we =
could recommend increasing the cost of the function above
> the default and refusing to flatten a function if the cost is =
sufficiently high?
Guys I tried increasing cost with the ST_Reclass function to 2000 and it =
didn't help. The function is called for every single output. The =
only thing that helps is setting this to VOLATILE as Tom suggests. I =
guess we can do that as some sort of fix for these kinds of functions =
though I'm not sure if that would cause other issues.
> As for claiming that this is broken and should be reverted: nyet. In =
the
> first place, there is certainly no PG documentation anywhere that =
promises
> single evaluation of a function written in the manner shown here. =20
> But what does the typical user expect? That should be the digested =
standard: whether it is documented should be secondary. It is not =
documented that the given query will be multiply=20
> evaluated either - and the fact that the query, as written, only =
include the function call once would suggest the user expect that it =
will only be evaluated once,
A good chunk of PostGIS functions are very costly and return a raster, =
geometry, geography object which is then further acted on in queries. =
As David noted, the general user -- partly because of observation I =
guess assumed the behavior that a function such as that would only be =
called once. I suppose if we were to change all such things to =
VOLATILE, that would be fine, but I'm concerned about the vast number of =
PostGIS users who further wrap the PostGIS functions and don't know to =
do this.
=20
> I cannot help but feel there is a patch here that allows us to negate =
the regression while keeping the benefits - but it's beyond my skills to =
prove that let alone write it.
> David J.
Does this change affect any function that takes a complex object and =
then has that complex object return another complex object? If that is =
the case, this would be disastrous for PostGIS users upgrading to 9.5 =
unless we change all the functions we have that have this behavior to =
VOLATILE. I would be okay with that as long as it doesn't cause =
performance issues for older versions of PostgreSQL and doesn't cause =
performance issues elsewhere.
Thanks,
Regina
=20