IMMUTABLE bug ?

Started by strkabout 20 years ago2 messages
#1strk
strk@keybit.net

I cant get the IMMUTABLE modifier meaning.

The 'testme' IMMUTABLE function is invoked 3 times
in the following query:

# select testme(1), testme(1), testme(1);
NOTICE: called
NOTICE: called
NOTICE: called
testme | testme | testme
--------+--------+--------
ret | ret | ret
(1 row)

Why is so ? shouldn't the IMMUTABLE keywork make
it a single call ?

From the 8.0 manual :

IMMUTABLE 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
argument list. If this option is given, any call of the function
with all-constant arguments can be immediately replaced with the
function value.

The function definition:

CREATE OR REPLACE FUNCTION testme(integer) RETURNS text AS
' BEGIN
RAISE NOTICE ''called'';
return ''ret''::text;
END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT;

PostgreSQL version 8.0.0

--strk;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: strk (#1)
Re: IMMUTABLE bug ?

strk <strk@keybit.net> writes:

Why is so ? shouldn't the IMMUTABLE keywork make
it a single call ?

No. There is no function value cache. What does happen here is that
the planner folds those calls to constants at plan time, instead of at
run time. Try

select testme(1) from some-table-with-multiple-rows

and note there's only one call not N.

regards, tom lane