RE: func() & select func()

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.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