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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCJxd6Y5Twig3Ge+YRArWcAKDBtJztMODWWhrJSaSb7p A/LEl5wwCeJ3n8
OILn4VT3mGF0Mjh8E8rEU+4=
=2LJg
-----END PGP SIGNATURE-----

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

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