Re: func() & select func()

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> I'm still confused and now suspicious if we could expect
> unambiguous results for the queries which constain function
> calls which cause strong side effect.

So far we have not talked about functions that actually have side
effects, just about how predictable the result of a side-effect-free
function is. It would be a serious mistake to constrain our handling
of side-effect-free functions on the basis of what's needed to make
side-effects predictable.

At the moment I do not care at all about how predictable side-effects
are --- I think that that's up to the user to deal with. We have seen
few if any complaints about misoptimization of nextval(), even though
it's theoretically been possible to have a problem with it for a long
time. For example, in
SELECT (column > 1) OR (nextval('seq') > 100) FROM ...
I believe it's been true forever that nextval won't be evaluated at
every column, but how many people complain? Saying that the behavior
is implementation-defined seems fine to me.

> Random sampling may be useful but it doesn't necessarily mean
> proper. Shouldn't we make random() an exception by adding
> another attribute for it if we expect random sampling ?

Maybe. Right now we don't distinguish random() from other functions
that are considerably more predictable, like now(). Perhaps it'd be
worthwhile to recognize more levels of function predictability.
now() could be classified as "fixed result during one transaction",
since I believe it gives back the time of the start of the current
xact. But I'm not sure it's worth worrying about just for now(). The
hard part would be figuring out a reasonable way to describe functions
that consult database tables --- those are fixed within a transaction
only if the tables they read don't change, but is it worth trying to
describe that? If so, how?

> BTW for the query
> SELECT * FROM foo where random() < 0.01 and id < 100;

> Is random() called for each row or for rows which satisfy id < 100 ?

Good question. I think it'd be a mistake to specify a single answer for
that. For this particular application, the user wouldn't care anyway.

regards, tom lane