RE: func() & select func()

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "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.
>

Agreed.
It seems too painful for optimizer to care about side-effects.

[snip]

> 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?
>

As to database lookup functions,we could expect fixed results for
one query. MVCC mechanism guarantees it and it's never a trivial
fact. However strictly speaking functions in a query may see the
change by the query itself. The change could be caused by functions
which insert/update/delete.

Regards.

Hiroshi Inoue