plpgsql_check can detect bad default volatility flag
Common performance problem of plpgsql function when these functions are used from some more complex queries is using default
VOLATILE
flag. There are not possible to do more aggressive optimization of this function call. plpgsql_check
can detect this issue now:CREATE OR REPLACE FUNCTION public.flag_test1(integer) RETURNS integer LANGUAGE plpgsql STABLE AS $function$ begin return $1 + 10; end; $function$; CREATE OR REPLACE FUNCTION public.flag_test2(integer) RETURNS integer LANGUAGE plpgsql VOLATILE AS $function$ begin return (select * from fufu where a = $1 limit 1); end; $function$; postgres=# select * from plpgsql_check_function('flag_test1(int)', performance_warnings => true); ┌────────────────────────────────────────────────────────────────────┐ │ plpgsql_check_function │ ╞════════════════════════════════════════════════════════════════════╡ │ performance:00000:routine is marked as STABLE, should be IMMUTABLE │ └────────────────────────────────────────────────────────────────────┘ (1 row) postgres=# select * from plpgsql_check_function('flag_test2(int)', performance_warnings => true); ┌──────────────────────────────────────────────────────────────────────┐ │ plpgsql_check_function │ ╞══════════════════════════════════════════════════════════════════════╡ │ performance:00000:routine is marked as VOLATILE, should be STABLE │ └──────────────────────────────────────────────────────────────────────┘ (1 row)
2 Comments:
Good work, this is a nice performance boost!
Wouldn't it be cool if select * would output the function name too in a separate result column to write a more complex query to check all functions at once?
It is not a problem with LATERAL JOIN - these queries are prepared - look to README https://github.com/okbob/plpgsql_check#mass-check
SELECT p.proname, tgrelid::regclass, cf.*
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
JOIN pg_language l ON p.prolang = l.oid
JOIN pg_namespace n ON p.pronamespace = n.oid,
LATERAL plpgsql_check_function(p.oid, t.tgrelid) cf
WHERE n.nspname = 'public' and l.lanname = 'plpgsql'
Post a Comment
Subscribe to Post Comments [Atom]
<< Home