On Fri, 5 Sep 2003, Daniel Schreiber wrote:

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>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Daniel Schreiber <daniel.schreiber (AT) s1999 (DOT) tu-chemnitz.de> writes:
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.

CURRENT_USER is marked STABLE, not IMMUTABLE. Not sure what your point
is here.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Am Freitag, 5. September 2003 16:01 schrieb Stephan Szabo:
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/


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Daniel Schreiber <daniel.schreiber (AT) s1999 (DOT) tu-chemnitz.de> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Fri, 2003-09-05 at 10:01, Stephan Szabo wrote:
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

Neil Conway <neilc (AT) samurai (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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 (AT) greatgulfhomes (DOT) com
Fax: (416) 441-9085



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org