Thread: Does IMMUTABLE have any effect on functions?

Hi,

i wonder if the attribute IMMUTABLE has any effect on functions.
Maybe its not implemented yet?

I tried the following:

CREATE TABLE foo (bar int4);

CREATE FUNCTION foo(int4)
RETURNS int4
AS '
     INSERT INTO foo (bar) VALUES ($1);
     SELECT $1;
' LANGUAGE 'sql' IMMUTABLE;

...now without any transaction...

select * from foo(1);

  foo
-----
    1
(1 row)

SELECT * FROM foo;

  bar
-----
    1
(1 row)

select * from foo(1);

  foo
-----
    1
(1 row)

SELECT * FROM foo;

  bar
-----
    1
    1
(1 row)


In my expectations the 2nd function call should not have added a new row
to table "foo", beacause it was called with the same parameter and is
immutable.

The same happens when i try to call the function twice within a single
transaction.

Maybe the "IMMUTABLE" attribute is just some sort of comment?

greets, Tom Schön

Lookup the docs, but IMMUTABLE and other such tags are hints to the
optimiser. If a function is immutable then the optimiser can optimise
away any invocations. If your function isn't actually immutable you've
just caused a problem.

They don't affect the actual function at all...

On Thu, Mar 03, 2005 at 02:17:17PM +0100, Thomas Schoen wrote:
> Hi,
>
> i wonder if the attribute IMMUTABLE has any effect on functions.
> Maybe its not implemented yet?

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Thomas Schoen <t.schoen@vitrado.de> writes:
> In my expectations the 2nd function call should not have added a new row
> to table "foo", beacause it was called with the same parameter and is
> immutable.

IMMUTABLE is a promise from you to the database (a promise which you
broke, in this case) ... not vice versa.  There is no commitment to
avoid duplicate evaluations in all cases, and certainly not to do so
across multiple queries.

BTW, PG 8.0 will flat out reject this function, because it is violating
the requirement that immutable functions not have side-effects.

            regards, tom lane