I have a function that does some mathematical calculations. It calls another function, which is volatile. I would like to know if the calling function can be declared as either immutable or stable, or it must necessarily be volatile as well.
Add a comment
|
2 Answers
The documentation describes IMMUTABLE as
IMMUTABLE indicates that the function cannot modify the database and 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. [...]
This means you can do the following:
CREATE OR REPLACE FUNCTION immutablerandom()
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE a numeric;
BEGIN
a := random();
RETURN 1;
END;
$$;
random() is a volatile function, and immutablerandom() fulfills the criteria of the definition of IMMUTABLE. If this actually makes sense is a different thing - you have to discard the results of the volatile function altogether, and in practice I cannot see a case where I could use it.
-
-
Well, it is just useless in my view - as said, you cannot return that value (or anything derived from it, except some extreme cases like f: [everything] -> 1 :), cannot modify the DB and such. As everything is immutable that complies with the definition above, you are still free to do this.– dezsoApr 5, 2018 at 11:50
-
1You could also use
a := 6 * random(); RETURN a;and still be accepted. Apr 5, 2018 at 12:52 -
2I think that the
VOLATILE / STABLE / IMMUTABLEattribute you declare at the function definition are there to to tell the optimizer if certain optimizations can be applied or not. Try dezso's definition witha := 6 * random(); RETURN a;, first withIMMUTABLEand thenVOLATILEand runselect immutablerandom() from some_table limit 10 ;and compare results. Apr 5, 2018 at 12:56 -
2In one case you'll get the same 10 "random" numbers (as the function call will be optimized to 1 call - you declared it IMMUTABLE!), in the second, you'll get truly random, as the function will be called 10 times. Apr 5, 2018 at 12:58
If you declare the function as immutable then PostgreSQL will generally believe you. If it is not actually immutable and that causes something to break (corrupted functional indexes being an obvious example off the top of my head) then you get to keep both pieces. So yes, you can do it. But it is running with scissors.
For example, say you create a claimed-to-be-immutable function which calls the single-argument form of to_tsvector (and so is not truly immutable), and build a functional index using that function. Then if you change the setting for default_text_search_config, your queries will stop returning the correct results until you manually rebuild the index. It won't be corrupt in the sense of crashing the database, it will just return the wrong rows.
