PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can these functions be classified as Immutable functions?

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

I have read the documentation about Immutable functions in Postgres but still confused about the following examples as to classify them as Immutable or not! All three are different cases and are as follows:

  1. Function having raise notice inside them
CREATE OR REPLACE FUNCTION text_equals(text, text) 
RETURNS boolean AS $$
BEGIN
    RAISE NOTICE 'Comparing two texts';
    RETURN $1 = $2;
END;
$$ LANGUAGE plpgsql;
  1. Function calling another Immutable function.
  2. Function returning current_user inside it
CREATE OR REPLACE FUNCTION fun()
RETURNS TEXT AS $$
DECLARE
    current_user_text TEXT;
BEGIN
    current_user_text := 'Current user is ' || current_user;
    RETURN current_user_text;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Third point above, I guess can’t be an immutable function but just wanted to check when function is a security definer which user gets printed out

Tags:

2

Answers


  1. 0

    No, Your function is not immutable.

    This has nothing to do with current user or security definer etc. It has everything to do with returning an indeterminant value. If the returned value can be calculated given only the input parameters, the function is immutable.

    In this regard, the question would likely have been clearer, and basically asked the same question, if instead the function returned current_timestamp.

    Functions that raise notices based on anything other than the parameters should not marked immutable, because:

    If [IMMUTABLE] is given, any call of the function with all-constant arguments can be immediately replaced with the function value

    so the same result will not be produced.

    Login or Signup to reply.
  2. 0

    Yes, yes, no, (but there’s a trick*):

    1. You can raise all you want. Function volatility is about warranting results based on inputs.
    2. An immutable function that uses only immutable functions remains immutable. If you think about it, there’s no other way to build one: all operators are actually functions, so without that you wouldn’t be able to build much of anything except constant-returning functions.
    3. No, a functiom returning the current_user produces results that in no way depend on its arguments. The doc puts it shortly:

      same results given the same arguments forever

    *The trick is that to an extent, it’s all up to you. You can declare something as immutable even though it’s clearly not. Thing is, PostgreSQL double-checks before things like function inlining and will disregard your declaration of immutability if it catches you lying.

    You’ll also get very unexpected results if it runs with your lie for optimisation purposes and swaps out your function call for a constant that it got from it earlier – if the result was supposed to change based on some circumstances, it suddenly won’t.

    You can test these claims: demo at db<>fiddle

    create function f1(a int) returns int immutable parallel safe language sql 
  return (case when a%2=0 then a else 0 end);

create function f2(a int)returns int immutable parallel safe language plpgsql as $f$
begin
 raise log 'hello.';
 raise debug 'hello?';
 raise warning 'hello!';
 return case when a%3=0 then f1(a) else 0 end;
end $f$;

create function f3(a int) returns int immutable parallel safe language sql 
  return (case when a%5=0 then f2(a) else 0 end);
 
explain analyze verbose
select f2(6) from generate_series(1,1e5)a;

    Here, the function got folded to a constant. PostgreSQL doesn’t really care whether it was really immutable or not, it just got one value from it, trusted your declaration that the result isn’t supposed to change if it gives the same argument over and over, so it just kept returning that first result, instead of calling it multiple times:

    QUERY PLAN
    Function Scan on pg_catalog.generate_series a (cost=0.00..10.00 rows=1000 width=4) (actual time=59.406..72.772 rows=100000 loops=1)
      Output: 6
      Function Call: generate_series(‘1’::numeric, ‘100000’::numeric)
    Planning Time: 0.745 ms
    Execution Time: 78.768 ms

    Here there was some level of verification, because f3 is a sql function declared as immutable: it checked whether it calls non-immutables, then inlined it. Again, f2 is plgpsql so it’s trusted to be immutable (and it’s not inlineable), f1 and f3 are also verified for inlining:

    explain analyze verbose
select f3(a::int) from generate_series(1,1e5)a;
    QUERY PLAN
    Function Scan on pg_catalog.generate_series a (cost=0.00..270.00 rows=1000 width=4) (actual time=26.254..1647.123 rows=100000 loops=1)
      Output: CASE WHEN (((a)::integer % 5) = 0) THEN f2((a)::integer) ELSE 0 END
      Function Call: generate_series(‘1’::numeric, ‘100000’::numeric)
    Planning Time: 0.083 ms
    Execution Time: 1655.356 ms

    With the current user thing, you keep giving the same arguments and getting different results:

    create role role1; grant postgres to role1;

create function f4(a text) returns text security invoker immutable parallel safe
language sql return (a||' '||current_user);
create function f5(a text) returns text security definer immutable parallel safe
language sql return (a||' '||current_user);

set role postgres;
select current_user,f4('hello');select current_user,f5('hello');
    current_user f4
    postgres hello postgres
    current_user f5
    postgres hello postgres 
    set role role1;
select current_user,f4('hello');select current_user,f5('hello');
    current_user f4
    role1 hello role1
    current_user f5
    role1 hello postgres
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search