Monday, August 18, 2014

plpgsql_check 0.9

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

5 Comments:

At August 18, 2014 at 1:55 AM , Anonymous Anonymous said...

"target variable has different type then expression result"

then -> than

 
At August 18, 2014 at 2:45 AM , Blogger Pavel Stěhule said...

fixed in code - thank you

 
At August 20, 2014 at 6:05 AM , Anonymous Anonymous said...

after installing I am getting this:

=#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

 
At August 20, 2014 at 6:11 AM , Blogger Pavel Stěhule said...

@Anonymous - it is ugly, but expected - there is dependency to external library - plpgsql, and this use case is not supported perfectly.

you 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=#

 
At August 20, 2014 at 6:23 AM , Anonymous Anonymous said...

Thank you, I saw that in the readme and didn't understand what it was for and didn't put 2 and 2 together!

Thank you for this valuable extension.

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home