> In the case of User-Defined functions, the user should be defining it
> as Deterministic.
The user CAN already define his functions as
"Deterministic=IMMUTABLE"... the problem is that many of us will define
functions as immutable, when in fact they are not. And do that by
mistake... and there's nothing postgres can do about that.
> Can we frame a set of guidelines, or may be some test procedure, which
> can declare a certain function as deterministic?
You mean postgres should check your function if it is really immutable ?
I can't imagine any way to do it correctly in reasonable time :-)
Imagine a function of 10 parameters which returns the sum of the
parameters all the time except for parameters all 1 it will randomly
return a value _once in a thousand executions_... please find a generic
algorithm which spots this function as not immutable in reasonable
execution time ;-)
So this example is a bit extreme, but don't underestimate the user ;-)
> I am just saying from the top of my mind. Even otherwise, if we can
> even restrict this indexing to only Built-in deterministic functions.,
> don't you think it would help the cause of a majority? I have just
> made the proposal to create the index with snapshot a optional one.
Restrictions like this are always confusing for the end user (i.e. why
can I use built-ins here and not my own ?). I leave to the actual coders
to say anything about code maintenance concerns...
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not