Re: function side effects

> > 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.
>
> I was talking about this to someone in Cuba and one conclusion we
> reached was that this was a fairly difficult task -- consider that
> someone may choose to define an innocent-looking operator using a
> volatile function. If you only examine things that look like functions
> in the query you will miss those. The only way to figure out whether a
> query has a write effect is to ask the server about the whole query.

In general you are right. However in most database application
systems, it is possible that all functions are properly designed and
implemented (at least they want so). In this world, more or less
PostgreSQL functions are just a part of their applications. If they
trust their client side applications, why they cannot trust PostgreSQL
custom functions as well?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp