> -----Original Message-----
> >> | VOLATILE indicates that the function value can change even within a
> >> | single table scan, so no optimizations can be made.
> >> | Relatively few database functions are volatile in this sense; some
> >> | examples are random(), [...]
>
> > What are the arguments against adding a 4th identifier - call it
> > PER_ROW for this argument? The main reason VOLATILE is broken is that
> > it is the default and in order to minimize beginner's penalty it is
> > not treated as such in some situations. The new one could behave just
> > like VOLATILE but would never be optimized away and would always
> > evaluate once for each row in its context.
>
> So how would you document that? It sounds like the proposed level would
> behave exactly as the VOLATILE level is currently documented to behave; so
I
> guess we could shift the documentation of VOLATILE to PER_ROW (or
> whatever). How would you then describe the behavior of VOLATILE?
>
I'm not sure but however we would describe it we might as well make the
change now regardless of whether another level is added.
The main distinguishing characteristic is that VOLATILE is not guaranteed to
evaluate once-per-row if it is not dependent upon particular values within a
given row.
VOLATILE: "A Volatile function used in an ORDER BY or WHERE clause without
referencing any columns from the query itself (i.e., no parameters or all
constants) will be evaluated a single time and the result treated as a
constant (i.e., all rows will have identical values) for that part of the
query."
PER_ROW: "A per_row function will be evaluated once for every row that is
visible to the function and will be treated as a virtual column of said
relation with each "cell" having an its own value as a result of the
function call."
Using random() as an example of the two possible behaviors should further
clarify the differences quite nicely.
Quick pass - hopefully, a) this inspires someone else, and b) this is the
correct understanding in the first place.
David J.
