Monday, July 18, 2011

updated plpgsql_lint released

Hello

I uploaded a bugfix version of plpgsql_lint.

http://kix.fsv.cvut.cz/~stehule/download/plpgsql_lint_2011-07-20.tgz

It support a functions with OUT parameters now: 

CREATE OR REPLACE FUNCTION public.gg(OUT a integer, OUT b integer)
 RETURNS record
 LANGUAGE sql
AS $function$ select 10,20 $function$

CREATE OR REPLACE FUNCTION public.t1()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare r record;
begin 
  raise notice 'start';
  r := gg();
  raise notice '% %', r.a, r.x; -- bug function returns (a,b)
  return;
end;
$function$

postgres=# select t1();
ERROR:  record "r" has no field "x" -- this bug was found before own execution, there is no message "start"
CONTEXT:  SQL statement "SELECT r.x"
PL/pgSQL function "t1" line 5 at RAISE
it running after fixing: 
postgres=# select t1();
NOTICE:  start
NOTICE:  10 20
 t1 
────
 
(1 row)

Time: 10.287 ms

posted by Pavel Stěhule @ 3:22 AM   1 Comments

Thursday, July 14, 2011

plpgsql lint

Hello I wrote a new contrib module plpgsql_lint for PostgreSQL 9.0. It is a validator of embeded SQL in plpgsql functions. PLpgSQL runtime checks a syntax when function is created, but it doesn't check a semantic of sql - so you can refer to not existing tables, columns, variables. These errors are found when interpret try to evaluate some SQL: 
 postgres=# select * from tablename ;
 columnname 
────────────
         10
(1 row)

        CREATE OR REPLACE FUNCTION public.foo(x integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       declare r record;
3       begin
4         if x > 0 then
5           for r in select columnam from tablename
6           loop
7             x := x + r.colname;
8           end loop;
9         end if;
10        return x;
11      end;
12      $function$
Function foo contains a two errors - usage of non existing column "columnnam" and reference on non existing field in record "colname". These bugs are not detected when I run function with negative argument: 
postgres=# select foo(-1);
 foo 
─────
  -1
(1 row)

Time: 0.352 ms
with active plpgsql_lint module, these bugs are diagnosed without dependency on function's parameter value: 
postgres=# load 'plpgsql';
LOAD
Time: 0.139 ms
postgres=# load 'plpgsql_lint';
LOAD
Time: 0.137 ms

postgres=# select foo(-1);
ERROR:  column "columnam" does not exist
LINE 1: select columnam from tablename
               ^
QUERY:  select columnam from tablename
CONTEXT:  PL/pgSQL function "foo" line 4 at FOR over SELECT rows

-- after fixing FOR statement
postgres=# select foo(-1);
ERROR:  record "r" has no field "colname"
CONTEXT:  SQL statement "SELECT x + r.colname"
PL/pgSQL function "foo" line 6 at assignment
This module helps to find all errors in code.

It can do a little bit more - it watches a coverage of plpgsql statements in nodes:

you can get a value of coverage tests: 

postgres=# select statement_coverage('foo');
 statement_coverage 
────────────────────
                0.5
(1 row)

Time: 0.423 ms
postgres=# select decision_coverage('foo');
 decision_coverage 
───────────────────
                 0
(1 row)

Time: 0.333 ms
and you can see, what lines of function was executed: 
postgres=# select coverage_get_model('foo');
                      coverage_get_model                      
──────────────────────────────────────────────────────────────
 <blo count="0" lineno="2" stmt="statement block">           
   <ifs count="1" lineno="3" stmt="IF">                      
     <ift>                                                   
       <lop count="0" lineno="4" stmt="FOR over SELECT rows">
         <stm count="0" lineno="6" stmt="assignment">       
       </stm></lop>                                                
     </ift>                                                  
   </ifs>                                                    
   <ret count="1" lineno="9" stmt="RETURN">                 
 </ret>
</blo>    
(1 row)

Installation

You have to put code to contrib directory and compile and install from source code. A other installation methods are not supported now: 
[pavel@pavel-stehule postgresql-9.0.4]$ pwd
/home/pavel/src/postgresql-9.0.4
[pavel@pavel-stehule postgresql-9.0.4]$ cd contrib/plpgsql_lint
[pavel@pavel-stehule plpgsql_lint]$ make clean
rm -f plpgsql_lint.so   libplpgsql_lint.a 
rm -f plpgsql_coverage.so plpgsql_lint.so plpgsql_coverage.o plpgsql_lint.o
rm -f plpgsql_lint.sql
rm -f plpgsql_coverage.o plpgsql_lint.o
[pavel@pavel-stehule plpgsql_lint]$ make all
sed 's,MODULE_PATHNAME,$libdir/plpgsql_lint,g' plpgsql_lint.sql.in >plpgsql_lint.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plpgsql_coverage.o plpgsql_coverage.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql90/lib',--enable-new-dtags  -shared -o plpgsql_coverage.so plpgsql_coverage.o
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plpgsql_lint.o plpgsql_lint.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -fpic -I../../src/pl/plpgsql/src -shared -o plpgsql_lint.so plpgsql_coverage.o plpgsql_lint.o -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql90/lib',--enable-new-dtags  
[pavel@pavel-stehule plpgsql_lint]$ su
Password: 
[root@pavel-stehule plpgsql_lint]# make install
/bin/mkdir -p '/usr/local/pgsql90/lib'
/bin/mkdir -p '/usr/local/pgsql90/share/contrib'
/bin/mkdir -p '/usr/local/pgsql90/lib'
/bin/sh ../../config/install-sh -c -m 755  plpgsql_lint.so '/usr/local/pgsql90/lib/plpgsql_lint.so'
/bin/sh ../../config/install-sh -c -m 644 plpgsql_lint.sql '/usr/local/pgsql90/share/contrib'
 /bin/sh ../../config/install-sh -c -m 755  plpgsql_coverage.so '/usr/local/pgsql90/lib'
 /bin/sh ../../config/install-sh -c -m 755  plpgsql_lint.so '/usr/local/pgsql90/lib'
and in console as user postgres: 
test=# load 'plpgsql';
LOAD
Time: 0.232 ms
test=# \i /usr/local/pgsql90/share/contrib/plpgsql_lint.sql 
CREATE FUNCTION
Time: 21.790 ms
CREATE FUNCTION
Time: 8.356 ms
CREATE FUNCTION
Time: 8.145 ms
CREATE FUNCTION
Time: 8.487 ms
test=#
After loading module with command LOAD: 
test=# load 'plpgsql';
LOAD
test=# load 'plpgsql_lint';
LOAD
the modul is active.

Limits

It cannot to work with temporary objects - because these objects does not exist when validation is processed.

It cannot to work with record variables, that has a value assigned via assign statement: 

create or replace function fx(out a int, out b int)
as $$ ... $$

DECLARE r record;
BEGIN
  r := fx();
  RAISE NOTICE '% %', r.a, r.b; -- this is not possible with plpgsql_lint
  ...
you should to fix function or disable lint: 
CREATE TYPE fx_type AS (a int, b int);
CREATE OR REPLACE FUNCTION fx() 
RETURNS fx_type AS $$ .. $$

DECLARE r fx_type; -- don't use combination record and assign statement
BEGIN
  ..
Blocking lint of some function is possible via option 
test=# CREATE OR REPLACE FUNCTION public.foo(x integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare r record;
begin
  if x > 0 then
    for r in select columnname from tablename
    loop
      x := x + r.columnname;
    end loop;
  end if; 
  return x;
end;
$function$ set plpgsql.disable_lint to on; -- don't use a validator on this function
This module is released under BSD license and source codes are downloadable from http://kix.fsv.cvut.cz/~stehule/download/plpgsql_lint_2011-07-15.tgz.

posted by Pavel Stěhule @ 10:03 PM   6 Comments