I released new version of
plpgsql_check.
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 ms
It 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
