Friday, August 29, 2014

lot of fixes in plpgsql_check

if you use it, please update

Adam Bartoszewicz sent instructions and compiled ddl for plpgsql_check for windows

Adam wrote (this is a how to build a PostgreSQL extension on MS Windows):

My general steps to compile plpgsql_check plugin on Windows 7:
  1. Download and install PostgreSQL 9.3.4 for Win32 from http://www.enterprisedb.com
  2. Download and install Microsoft Visual C++ 2010 Express
  3. Lern tutorial http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
  4. The plpgsql_check depends on plpgsql and we need to add plpgsql.lib to the library list. Unfortunately PostgreSQL 9.4.3 does not contain this library.
  5. Create a plpgsql.lib from plpgsql.dll as described in http://adrianhenke.wordpress.com/2008/12/05/create-lib-file-from-dll
  6. Change plpgsql_check.c file, add PGDLLEXPORT line before evry extension function, as described in http://blog.2ndquadrant.com/compiling-postgresql-extensions-visual-studio-windows
    ...PGDLLEXPORT
    Datum plpgsql_check_function_tb(PG_FUNCTION_ARGS);
    PGDLLEXPORT
    Datum plpgsql_check_function(PG_FUNCTION_ARGS);
    ...
    PGDLLEXPORT
    Datum
    plpgsql_check_function(PG_FUNCTION_ARGS)
    {
    Oid            funcoid = PG_GETARG_OID(0);
    ...
    PGDLLEXPORT
    Datum
    plpgsql_check_function_tb(PG_FUNCTION_ARGS)
    {
    Oid            funcoid = PG_GETARG_OID(0);
    ...
    
  7. Build plpgsql_check.dll
  8. Install plugin
    1. copy plpgsql_check.dll to PostgreSQL\9.3\lib
    2. copy plpgsql_check.control and plpgsql_check--0.8.sql to PostgreSQL\9.3\share\extension

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