What is new?
lot of new checks:
- assignment stmt
- performance warnings
- return stmts
create or replace function fx() returns t2 as $$ begin return (10,20,30)::t1; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function --------------------------------------------------------------------------------- error:42804:3:RETURN:returned record type does not match expected record type Detail: Returned type integer does not match expected type numeric in column 2. (2 rows) create or replace function f1() returns setof int as $$ begin return next 1::numeric; -- tolerant, doesn't use tupmap end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------- performance:42804:3:RETURN NEXT:target variable has different type then expression result Detail: assign "numeric" value to "integer" variable Hint: Hidden casting can be a performance issue. (3 rows) create or replace function f1() returns int as $$ begin return current_date; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ---------------------------------------------------------------------------------- warning:42804:3:RETURN:target variable has different type then expression result Detail: assign "date" value to "integer" variable Hint: There are no possible explicit coercion between those types, possibly bug! (3 rows)
Last month was a discussion about speed of plpgsql. There was example of synthetic tests - for these tests plpgsql is strongly penalized. There is zero optimization for intensive mathematics calculations :
DO LANGUAGE plpgsql $$ DECLARE n real; DECLARE f integer; BEGIN FOR f IN 1..10000000 LOOP if 0=0 then n = SQRT (f); end if; END LOOP; RAISE NOTICE 'Result => %',n; END $$;
There is two issues: a) useless test 0=0 (Oracle throws it, Postgres has no any optimization for code cleaning), b) hidden (IO) cast from double to integer. Original execution runs 5sec on Oracle and 25sec on Postgres. (32 sec on my notebook).
After manual fix:
postgres=# DO LANGUAGE plpgsql $$ DECLARE n real; DECLARE f integer; BEGIN FOR f IN 1..10000000 LOOP --if 0=0 then n = SQRT (f)::real; --end if; END LOOP; RAISE NOTICE 'Result => %',n; END $$; NOTICE: Result => 3162 DO Time: 6771.010 msIt is not bad - little bit slower than Oracle on wrong benchmark (plpgslq is not targeted for numeric calculations, plpgsql is best glue for SQL statements) without any internal optimization.
postgres=# load 'plpgsql'; LOAD postgres=# create extension plpgsql_check ; CREATE EXTENSION CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 DECLARE f integer; n real; 3 BEGIN 4 FOR f IN 1..10000000 LOOP 5 --if 0=0 then 6 n = SQRT (f); 7 --end if; 8 END LOOP; 9 RAISE NOTICE 'Result => %',n; 10 END $function$ postgres=# select * from plpgsql_check_function('foo()', fatal_errors := false, performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------ performance:42804:6:assignment:target variable has different type then expression result Detail: assign "integer" value to "real" variable Hint: Hidden casting can be a performance issue. warning:00000:0:RETURN:unused declared variable Detail: variable f declared on line 2 (5 rows)
You can see two issues:
- unused variable f - integer FOR stmt has own automatic variable - so DECLARE f integer is useless
- hidden IO cast on line six, that is main performance issue
"target variable has different type then expression result"
ReplyDeletethen -> than
fixed in code - thank you
ReplyDeleteafter installing I am getting this:
ReplyDelete=#create extension plpgsql_check;
ERROR: could not load library "/usr/pgsql-9.3/lib/plpgsql_check.so": /usr/pgsql-9.3/lib/plpgsql_check.so: undefined symbol: plpgsql_parser_setup
I grabbed the zip from github, any thoughts? thanks
@Anonymous - it is ugly, but expected - there is dependency to external library - plpgsql, and this use case is not supported perfectly.
ReplyDeleteyou should to explicitly load plpgsql library first - or just call any plpgsql function:
omega=# create extension plpgsql_check ;
ERROR: could not load library "/usr/local/pgsql/lib/plpgsql_check.so": /usr/local/pgsql/lib/plpgsql_check.so: undefined symbol: plpgsql_parser_setup
omega=# load 'plpgsql';
LOAD
omega=# create extension plpgsql_check ;
CREATE EXTENSION
omega=#
Thank you, I saw that in the readme and didn't understand what it was for and didn't put 2 and 2 together!
ReplyDeleteThank you for this valuable extension.