Csaba Nagy wrote:
>> 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 think you're overly pessimistic here ;-) This classification can be done quite
efficiently as long as your language is "static enough". The trick is not to
execute the function, but to scan the code to find all other functions and SQL
statements a given function may possibly call. If your function calls no SQL
statements, and only other functions already marked IMMUTABLE, then it must be
IMMUTABLE itself.
It does seem that only pl/pgsql is "static enough" for this to work, though,
making this idea rather unappealing.
>> 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...
Yes, and some built-ins have gotten that classification wrong too in the past
IIRC. Which probably is a good reason not to trust our users to get it right ;-)
greetings, Florian Pflug