Re: function side effects

On Tue, 2010-02-23 at 12:51 +0900, Tatsuo Ishii wrote:

> I'm wondering if we could detect a funcion has a side effect,
> i.e. does a write to database. This is neccessary for pgpool to decide
> if a qeury should to be sent to all of databases or not. If a query
> includes functions which do writes to database, it should send the
> query to all of databases, otherwise the contents of databases go into
> inconsistent state.
>
> Currently we have three properties of functions: IMMUTABLE, STABLE and
> VOLATILE. According to docs IMMUTABLE or STABLE functions do not write
> to database. VOLATILE functions *may* do writes to database. Maybe I
> could regard VOLATILE functions always do write, but priblem is,
> VOLATILE qfunctions such as random() and timeofday() apparently do not
> write and sending those queries that include such functions is
> overkill.
>
> Can we VOLATILE property divide into two categories, say, VOLATILE
> without write, and VOLATILE with write?

pgpool parses the query before deciding how to route it, yes?

Why not mark random() and timeofday() as stable in the pgpool catalog,
yet leave them as volatile on the database servers? It will "just work"
then.

--
Simon Riggs www.2ndQuadrant.com