Re: function side effects

Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
> Apparently IMMUTABLE/STABLE should not write to database according to
> docs. Are you saying that in the real world these are ignored? If so,
> this is an important database intergrity issue as Simon pointed out.

One more time: these markings are not about whether the function writes
to the database. They are about whether its result value can be
presumed to be unchanging in various circumstances. Trying to redefine
them for another purpose is going to lead to nothing but trouble.

And no, there is not an "integrity issue" here. If the planner thinks
something is stable or immutable, it might evaluate it fewer times than
the user would wish, but that doesn't render the database inconsistent.
It just means the user doesn't get the behavior he wanted. That's no
different from any other erroneously-written query.

> It seems it is neccessary to invent new marker for not only pgpool but
> HOT/SR (and may be Slony). They need to know if a query including
> functions do writes or not *before* sending to backend. Otherwise they
> got error because they sent a write query to standby.

Well, that's something we can consider adding in 9.1, but it's far too
late for 9.0. Personally I find that goal rather suspect anyway.
I think the chances of determining this reliably in pgpool are
negligible, even if functions were marked like that. You would need to
duplicate *all* of the backend's parsing and all of its state (eg schema
search path) in order to discover anything.

regards, tom lane