Thread: Unclear documentation (IMMUTABLE functions)
Hi, A question on IMMUTABLE functions: do they only have to return the same value during a session/connection or during the databse server's lifetime? The documentation does not say anything about that. I wrote a function that uses the CURRENT_USER session variable, and for my current usage it is very important, that it returns a different value for each database connection. The current implementation is session, but it is quite important that this behaviour will be the same in future releases. If it must be the same during server lifetime, I had to rewrite the function to have the CURRENT_USER variable passed as parameter (that would not be a big issue, of course). Thanks for your attention, Daniel Schreiber -- Daniel Schreiber | ICQ: 220903493 GPG encrypted Mail welcome! Key ID: 25A6B489 Chemnitzer Linux-Tag: http://www.tu-chemnitz.de/linux/tag/
On Fri, 5 Sep 2003, Daniel Schreiber wrote:
> Hi,
>
> A question on IMMUTABLE functions: do they only have to return the same value
> during a session/connection or during the databse server's lifetime? The
> documentation does not say anything about that.
The function should always return the same value result the same inputs.
I think you'll want to rewrite to take it as an argument (otherwise there
might be uses that get converted to a constant where you want it to vary).
This is the section in create function reference page about immutable. I'd
thought it was clear, but do you have a better suggested wording?
<para>
<literal>IMMUTABLE</literal> indicates that the function always
returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its parameter list. If this option is given,
any call of the function with all-constant arguments can be
immediately replaced with the function value.
</para>
Daniel Schreiber <daniel.schreiber@s1999.tu-chemnitz.de> writes:
> A question on IMMUTABLE functions: do they only have to return the same value
> during a session/connection or during the databse server's lifetime?
Lifetime, I'd say, since among other things IMMUTABLE is considered to
mean that it's okay to build an index on the function's results.
Of course, you can cheat: the system makes no attempt to verify whether
a function that you say is IMMUTABLE really is. There are cases where
it's useful to lie about the mutability property of a function. What
you are basically controlling here is whether the planner will deem it
safe to pre-evaluate a call of the function. Unless you actually do
build a functional index using a function, I doubt it could matter
whether the results change from one session to the next.
> I wrote a function that uses the CURRENT_USER session variable, and for my
> current usage it is very important, that it returns a different value for
> each database connection.
CURRENT_USER is marked STABLE, not IMMUTABLE. Not sure what your point
is here.
regards, tom lane
Am Freitag, 5. September 2003 16:01 schrieb Stephan Szabo: > On Fri, 5 Sep 2003, Daniel Schreiber wrote: > > Hi, > > > > A question on IMMUTABLE functions: do they only have to return the same > > value during a session/connection or during the databse server's > > lifetime? The documentation does not say anything about that. > > The function should always return the same value result the same inputs. > I think you'll want to rewrite to take it as an argument (otherwise there > might be uses that get converted to a constant where you want it to vary). > > This is the section in create function reference page about immutable. I'd > thought it was clear, but do you have a better suggested wording? > > <para> > <literal>IMMUTABLE</literal> indicates that the function always > returns the same result when given the same argument values; that > is, it does not do database lookups or otherwise use information not > directly present in its parameter list. If this option is given, > any call of the function with all-constant arguments can be > immediately replaced with the function value. > </para> The explanation that Tom gave, was quite clear I think. What about: <para> <literal>IMMUTABLE</literal> indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its parameter list. If this option is given, any call at any time of the function with all-constant arguments can be immediately replaced with the function value. What you are basically controlling here is whether the planner will deem it safe to pre-evaluate a call of the function. </para> Thanks for the quick help, Daniel -- Daniel Schreiber | ICQ: 220903493 GPG encrypted Mail welcome! Key ID: 25A6B489 Chemnitzer Linux-Tag: http://www.tu-chemnitz.de/linux/tag/
Am Freitag, 5. September 2003 17:05 schrieb Tom Lane: > Daniel Schreiber <daniel.schreiber@s1999.tu-chemnitz.de> writes: > > A question on IMMUTABLE functions: do they only have to return the same > > value during a session/connection or during the databse server's > > lifetime? > > Lifetime, I'd say, since among other things IMMUTABLE is considered to > mean that it's okay to build an index on the function's results. > > Of course, you can cheat: the system makes no attempt to verify whether > a function that you say is IMMUTABLE really is. There are cases where > it's useful to lie about the mutability property of a function. What > you are basically controlling here is whether the planner will deem it > safe to pre-evaluate a call of the function. Unless you actually do > build a functional index using a function, I doubt it could matter > whether the results change from one session to the next. Okay, thanks, I think I got it now. > > I wrote a function that uses the CURRENT_USER session variable, and for > > my current usage it is very important, that it returns a different value > > for each database connection. > > CURRENT_USER is marked STABLE, not IMMUTABLE. Not sure what your point > is here. Okay, thanks for the help. I will rewrite the function as STABLE. Btw: is there documentation which parts in the environment are STABLE/IMMUTABLE? Thanks, Daniel Schreiber -- Daniel Schreiber | ICQ: 220903493 GPG encrypted Mail welcome! Key ID: 25A6B489 Chemnitzer Linux-Tag: http://www.tu-chemnitz.de/linux/tag/
Daniel Schreiber <daniel.schreiber@s1999.tu-chemnitz.de> writes:
> Okay, thanks for the help. I will rewrite the function as STABLE. Btw: is
> there documentation which parts in the environment are STABLE/IMMUTABLE?
Look in pg_proc (the provolatile column is 'i', 's', or 'v' IIRC).
I don't think psql's \df will show this column, which might be a mistake.
regards, tom lane
On Fri, 2003-09-05 at 10:01, Stephan Szabo wrote:
> This is the section in create function reference page about immutable. I'd
> thought it was clear, but do you have a better suggested wording?
While we're on the subject, this adjacent paragraph of the docs seems
unclear:
STABLE indicates that within a single table scan the function
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. Also note that the current_timestamp
family of functions qualify as stable, since their values do not
change within a transaction.
So, can a STABLE function change across SQL statements (as the beginning
of the paragraph implies), or across transactions (as the end of the
paragraph implies)?
-Neil
Neil Conway <neilc@samurai.com> writes:
> While we're on the subject, this adjacent paragraph of the docs seems
> unclear:
> STABLE indicates that within a single table scan the function
> 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. Also note that the current_timestamp
> family of functions qualify as stable, since their values do not
> change within a transaction.
> So, can a STABLE function change across SQL statements (as the beginning
> of the paragraph implies), or across transactions (as the end of the
> paragraph implies)?
The former. current_timestamp is at the less-volatile end of the range
of behaviors that are legal for STABLE functions. Nonetheless, it is
one, and a rather important member of the category at that. If we had a
standard function that was at the more-volatile end of the range, I'd be
happy to mention it as another example; but I don't think we have any.
What the system actually uses these categories for are (AFAIR) three
decisions. In decreasing order of the amount of stability needed:
1. Is it okay to make a functional index on this function? (It had
better hold still forever.)
2. Can I fold a function call with constant arguments to a constant at
planning time? (It had better hold still for as long as the plan
will survive, which is presently not further than the end of the
current session. But if we ever start caching plans across backends,
this will start to look a lot like #1, so there's not a separate
provolatile category for this.)
3. Is it okay to use this function call in an indexscan constraint?
(It had better hold still while the index scan is active.)
Case 3 is the one that motivates having a STABLE category. The real
definition of STABLE is "can't change value within a single indexscan".
To make use of an index, you have to assume that the value you're
comparing to the index column will hold still across the whole scan.
As an example, "where foo = random()" isn't indexable; we have to fall
back to the naive SQL semantics that says to evaluate the WHERE
condition at every row. But "where msgdate >= current_date()" is safe
to index, and so would be a function call that involved database lookup,
because the active snapshot can't change within an indexscan.
I think we may also insist that functions appearing in merge/hash join
conditions be STABLE, for more or less the same reasons as indexscans.
regards, tom lane
Not my place to say, but... I think this likely should be worded something like this (if true): ... Also note that the current_timestamp family of functions qualify as stable, since their values do not change within SQL statement, and to be more concise the current_timestamp functions do not change within a transaction. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org > [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Neil Conway > Sent: Friday, September 05, 2003 2:32 PM > To: Stephan Szabo > Cc: Daniel Schreiber; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] Unclear documentation (IMMUTABLE functions) > > > On Fri, 2003-09-05 at 10:01, Stephan Szabo wrote: > > This is the section in create function reference page about > immutable. I'd > > thought it was clear, but do you have a better suggested wording? > > While we're on the subject, this adjacent paragraph of the docs seems > unclear: > > STABLE indicates that within a single table scan the function > 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. Also note that the current_timestamp > family of functions qualify as stable, since their > values do not > change within a transaction. > > So, can a STABLE function change across SQL statements (as > the beginning > of the paragraph implies), or across transactions (as the end of the > paragraph implies)? > > -Neil > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >