5

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.

2 Answers 2

3

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.

6
  • But doesn't that defy the concept of an immutable function ? Apr 5, 2018 at 11:30
  • 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.
    – dezso
    Apr 5, 2018 at 11:50
  • 1
    You could also use a := 6 * random(); RETURN a; and still be accepted. Apr 5, 2018 at 12:52
  • 2
    I think that the VOLATILE / STABLE / IMMUTABLE attribute 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 with a := 6 * random(); RETURN a;, first with IMMUTABLE and then VOLATILE and run select immutablerandom() from some_table limit 10 ; and compare results. Apr 5, 2018 at 12:56
  • 2
    In 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
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.