Thread: stable functions
Hi all. Can functions whose effect is to create functions (yep!) be labelled as "stable"? Thanks. -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988]
2007/5/31, Vincenzo Romano <vincenzo.romano@gmail.com>: > Hi all. > > Can functions whose effect is to create functions (yep!) > be labelled as "stable"? According to the docs, no. "STABLE indicates that the function cannot modify the database (...) any function that has side-effects must be classified volatile" http://www.postgresql.org/docs/8.2/static/sql-createfunction.html If you labeled creatorfunc(param) as stable, ant then issued "SELECT creatorfunc(sometable.param) FROM sometable", there is a theoretical risk that creatorfunc would be called only _once_ for each distinct value of param. -- Filip Rembiałkowski
On Thursday 31 May 2007 13:23:36 Filip Rembiałkowski wrote: > 2007/5/31, Vincenzo Romano <vincenzo.romano@gmail.com>: > > Hi all. > > > > Can functions whose effect is to create functions (yep!) > > be labelled as "stable"? > > According to the docs, no. > > "STABLE indicates that the function cannot modify the database (...) > any function that has side-effects must be classified volatile" > > http://www.postgresql.org/docs/8.2/static/sql-createfunction.html > > If you labeled creatorfunc(param) as stable, ant then issued "SELECT > creatorfunc(sometable.param) FROM sometable", there is a theoretical > risk that creatorfunc would be called only _once_ for each distinct > value of param. This is the complete quote: <quote> STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. </quote> They talk about table scans which should not involce the information schema tables, the only tables that get modified by a fubction whose sole effect it to create other functions.creatorfunc Nonetheless your remark makes a lot of sense and I'm still in dubt. In my case the "creatorfunc" has no parameters and returns void as it reads data from configuration tables. And it should be OK if ti were run only once. I'd say that some more explaination in the documentatio would be great under "33.6. Function Volatility Categories". -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988]
2007/5/31, Vincenzo Romano <vincenzo.romano@gmail.com>: > Nonetheless your remark makes a lot of sense and I'm still in dubt. > In my case the "creatorfunc" has no parameters and returns void as it reads > data from configuration tables. > And it should be OK if ti were run only once. AFAIK, the only practical sense of defining functions as stable/immutable is the runtime query optimization. If you don't need any optimization, and the function takes no params, why not leave it as volatile? > I'd say that some more explaination in the documentatio would be great under > "33.6. Function Volatility Categories". For me it's clear enough. -- Filip Rembiałkowski
Vincenzo Romano <vincenzo.romano@gmail.com> writes:
> <quote>
>> STABLE indicates that the function cannot modify the database,
> They talk about table scans which should not involce the information schema
> tables, the only tables that get modified by a fubction whose sole effect it
> to create other functions.creatorfunc
Modifying the system catalogs counts as modifying the database.
regards, tom lane