Friday, December 27, 2013

new PostgreSQL extension - plpgsql_check

Three years ago I worked on complex project, that was written in PL/pgSQL almost. I wrote a regress tests, but these tests was not able to find all errors due missing coverage some paths. So I wrote simple extension, that ensured a plan preparation for all embedded SQL on start. This extension helps me to find 99% bugs. I cleaned this extension and I published it as plpgsql_lint. As a plpgsql developer I am sure, so this functionality is missing in core, so I started in core implementation. Two years later I am still on start. I didn't find how to merge a requested functionality with current code cleanly (so committers will be happy) - probably we have to do some significant plpgsql refactoring first. And now is clean, so this code will not be in prepared release PostgreSQL 9.4. Although a code is not enough for committers, it can be useful for common plpgsql developers.

I started new github project - plpgsql_check. It is plpgsql_lint (next generation). plpgsql_lint checked functions on start. plpgsql_check is coming with plpgsql_check_function. You can check any plpgsql function without execution. It can be joined with event trigger and you can ensure a extended check after function creating.

Any comments, patches, ideas are welcome - you can try send me a email or you can join to related postgresql-extensions-hacking google group.

examples:
postgres=# LOAD 'plpgsql';
LOAD
postgres=# CREATE EXTENSION plpgsql_check;
LOAD
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE

postgres=#
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM t1
  LOOP
    RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
  END LOOP;
END;
$function$;

CREATE FUNCTION

postgres=# select f1(); -- execution doesn't find a bug due empty table t1
  f1 
 ────

 (1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb('f1()');
─[ RECORD 1 ]───────────────────────────
functionid │ f1
lineno     │ 6
statement  │ RAISE
sqlstate   │ 42703
message    │ record "r" has no field "c"
detail     │ [null]
hint       │ [null]
level      │ error
position   │ 0
query      │ [null]

postgres=# \sf+ f1
    CREATE OR REPLACE FUNCTION public.f1()
     RETURNS void
     LANGUAGE plpgsql
1       AS $function$
2       DECLARE r record;
3       BEGIN
4         FOR r IN SELECT * FROM t1
5         LOOP
6           RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
7         END LOOP;
8       END;
9       $function$

select * from plpgsql_check_function('f1()', fatal_errors := false);
                         plpgsql_check_function                         
------------------------------------------------------------------------
 error:42703:4:SQL statement:column "c" of relation "t1" does not exist
 Query: update t1 set c = 30
 --                   ^
 error:42P01:7:RAISE:missing FROM-clause entry for table "r"
 Query: SELECT r.c
 --            ^
 error:42601:7:RAISE:too few parameters specified for RAISE
(7 rows)

postgres=# select * from plpgsql_check_function('fx()', format:='xml');
                 plpgsql_check_function                     
────────────────────────────────────────────────────────────────
 <function oid="16400">                                        ↵
   <issue>                                                     ↵
     <level>error</level>                                      ↵
     <sqlstate>42P01</Sqlstate>                                ↵
     <message>relation "foo111" does not exist</Message>       ↵
     <stmt lineno="3">RETURN</Stmt>                            ↵
     <query position="23">SELECT (select a from foo111)</Query>↵
   </Issue>                                                    ↵
  </Function>
 (1 row)

Friday, December 20, 2013

some basic PostgreSQL orchestration from Bash

I had to designed some scripts for migration, upgrade and rebuild PostgreSQL. Some fragments can be useful for others:

Prolog:
#!/bin/bash

# Installation prefix
prefix=/usr/local/pgsql

PGCTL="$prefix/bin/pg_ctl"
PGUSER=postgres

if [ ! -n "$PGDATA" ]; then
  PGDATA="$prefix/data"
fi

Ensure a stop of PostgreSQL, continue when server is down:
echo "shutdown server"
su - $PGUSER -c "$PGCTL status -D $PGDATA" 
if [ $? != 1 ]; then
  su - $PGUSER -c "$PGCTL stop -D  $PGDATA -m fast"
  if [ $? != 1 -a $? != 0 ]; then
    su - $PGUSER  -c "$PGCTL status -D  $PGDATA"
    while [ $? != 1 -a $? != 3 ]; do
      echo "PostgreSQL still running.."
      sleep 1
      su - $PGUSER -c "$PGCTL status -D  $PGDATA"
    done
  fi
fi

Start server and continue when server is ready to accept connection:
echo "start server"
/etc/init.d/pgsql start
$prefix/bin/pg_isready
while [ $? != 0 ]; do
  sleep 1;
  $prefix/bin/pg_isready
done

Tuesday, December 10, 2013

make_date, make_time functions

Years I miss a functionality for simple constructing dates and times from numeric fields.

There was a more possibilities - one via to_date function

postgres=# select to_date(2014::text || 10::text || 28::text ,'YYYYMMDD');
  to_date   
------------
 2014-10-28
(1 row)

But I was not too happy with it. It works, but it is missing any elegance (and there are a useless type transformations):

I wrote a two functions, that will be available in PostgreSQL 9.4

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f901bb50e33ad95593bb68f7b3b55eb2e47607dc

postgres=# select make_date(2014,10,28);
 make_date  
------------
 2014-10-28
(1 row)

postgres=# select make_time(10,20,30.323);
  make_time   
--------------
 10:20:30.323
(1 row)

Now I am happy - it was my dream.

Wednesday, November 20, 2013

new border styles for psql

A few years ago I wrote a first patch that add a new styles of unicode table borders in psql.

Today I release new patch for 9.3 with new two styles. Download patch from http://postgres.cz/files/patches/more_linestyles-93-20131008.diff url.
\pset linestyle unicode-double5 style
┌───────────┬──────────┬──────────┬────────────┬────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │  Collate   │   Ctype    │   Access privileges   │
┝━━━━━━━━━━━┿━━━━━━━━━━┿━━━━━━━━━━┿━━━━━━━━━━━━┿━━━━━━━━━━━━┿━━━━━━━━━━━━━━━━━━━━━━━┥
│ postgres  │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │                       │
│ template0 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴────────────┴────────────┴───────────────────────┘

\pset linestyle unicode-double6 style
┌───────────┬──────────┬──────────┬────────────┬────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │  Collate   │   Ctype    │   Access privileges   │
╞═══════════╪══════════╪══════════╪════════════╪════════════╪═══════════════════════╡
│ postgres  │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │                       │
│ template0 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴────────────┴────────────┴───────────────────────┘

Sunday, October 20, 2013

Using a custom composite types in PostgreSQL

I like a custom composite types - in PL/pgSQL. I don't use a composite types in tables ever. There is a risk of some issues with composite types on client side - a client language drivers usually doesn't work simply with custom composite types - so I use it only on server side. This functionality allow me to build simply simple OOP system, that it is enough strong for implementation some not trivial application:

I declare a composite type and auxiliary constructors:
CREATE TYPE foo_type AS (name text, size numeric, description text);

CREATE OR REPLACE FUNCTION public._foo_type(name text DEFAULT NULL::text, size numeric DEFAULT 0.0, description text DEFAULT NULL::text)
 RETURNS foo_type
 LANGUAGE plpgsql
AS $function$
DECLARE r foo_type;
BEGIN
  r.name = _foo_type.name;
  r.size = _foo_type.size;
  r.description = _foo_type.description;

  IF COALESCE(name, '') = '' OR size IS NULL THEN
    RAISE EXCEPTION 'mandatory field (name or size) is empty';
  END IF;

  IF size < 0 THEN
    RAISE EXCEPTION 'size is negative';
  END IF;

  RETURN r;
END;
$function$;
Own constructors should to cross a limited functionality - custom composite types doesn't support default values and CHECK constraints. Both should be placed in constructors.
postgres=# SELECT _foo_type(name := 'Trumpeta');
    _foo_type    
─────────────────
 (Trumpeta,0.0,)
(1 row)

Time: 3.820 ms
postgres=# SELECT _foo_type(size := 175, name := 'Trumpeta');
    _foo_type    
─────────────────
 (Trumpeta,175,)
(1 row)

Time: 1.014 ms
postgres=# SELECT _foo_type(size := -1, name := 'Trumpeta');
ERROR:  size is negative
Time: 0.877 ms
postgres=# SELECT _foo_type();
ERROR:  mandatory field (name or size) is empty
Time: 1.160 ms
I like a named parameters - code is much more self documented, and I removed a risk of swapped parameters. Later I implement a custom casting methods (functions) - sometimes I join it with postgres custom casting:
CREATE OR REPLACE FUNCTION public.cast_to_xml(foo_type)
 RETURNS xml
 LANGUAGE sql
AS $function$
  SELECT xmlforest($1.name AS "Name", $1.size AS "Size", $1.description AS "Description")
$function$;

CREATE CAST (foo_type AS xml) WITH FUNCTION cast_to_xml(foo_type);

postgres=# SELECT _foo_type(name := 'Trumpeta')::xml;
                    _foo_type                     
──────────────────────────────────────────────────
 <Name>Trumpeta</Name><Size>0.0</Size>
(1 row)
I don't like use a custom types in tables - so I need a functionality for merging to table type and casting from table type:
CREATE TABLE boo (
   alfa text,
   beta text, 
   name text NOT NULL CHECK(name <> ''),
   size numeric NOT NULL DEFAULT 0.0 CHECK (size >= 0),
  description text DEFAULT NULL
);

CREATE OR REPLACE FUNCTION public.merge(boo, foo_type)
 RETURNS boo
 LANGUAGE plpgsql
AS $function$
BEGIN
  $1.name := $2.name;
  $1.size := $2.size;
  $1.description := $2.description;
  RETURN $1;
END;
$function$;

CREATE OR REPLACE FUNCTION public.cast_to_foo_type(boo)
 RETURNS foo_type
 LANGUAGE plpgsql
AS $function$
DECLARE r foo_type;
BEGIN
  r.name := $1.name;
  r.size := $1.size;
  r.description := $1.description;
  RETURN r;
END;
$function$

CREATE CAST (boo AS foo_type) WITH FUNCTION cast_to_foo_type(boo);

CREATE OR REPLACE FUNCTION public._boo(alfa text, beta text, foo foo_type)
 RETURNS boo
 LANGUAGE plpgsql
AS $function$
DECLARE r boo;
BEGIN
  r.alfa := _boo.alfa;
  r.beta := _boo.beta;
  r := merge(r, foo);
  RETURN r;
END;
$function$
Now I can do more usual things with table type boo - insert value or transform to xml:
CREATE OR REPLACE FUNCTION public.new_boo(b boo)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE b boo; 
  BEGIN
  INSERT INTO boo VALUES((b).*);
  RETURN b.alfa;
END;
$function$

postgres=# SELECT new_boo(_boo(alfa := 'XSY',beta := 'JJS', foo := _foo_type(name:='Trumpeta')));
 new_boo 
─────────
 XSY
(1 row)

postgres=# SELECT * FROM boo;
 alfa │ beta │   name   │ size │ description 
──────┼──────┼──────────┼──────┼─────────────
 XSY  │ JJS  │ Trumpeta │  0.0 │ [null]
(1 row)

CREATE OR REPLACE FUNCTION cast_to_xml(boo)
RETURNS xml AS $$
  SELECT xmlelement(NAME "boo", xmlattributes($1.alfa AS "Alfa"), 
            xmlelement(NAME "Beta", $1.beta), $1::foo_type::xml);
$$ LANGUAGE sql;

CREATE CAST (boo AS xml) WITH FUNCTION cast_to_xml(boo)

postgres=# SELECT boo::xml FROM boo;
                                          boo                                           
────────────────────────────────────────────────────────────────────────────────────────
 <boo Alfa="XSY"><Beta>JJS</Beta><Foo><Name>Trumpeta</Name><Size>0.0</Size></Foo></boo>
(1 row)
With described conventions and patterns I was able to implement and simply manage complex stored procedures centric system that implements a number portability between N telco operators.

Second advantage of this code - it is robust and resistant to addition any column anywhere. I newer use a INSERT statement from application, so it is easy and safe to modify database schema.

Saturday, July 13, 2013

two date functions

More times I needed a function that returns specific date of current years or specific date of current month.

I wrote two functions that solve this request:

CREATE OR REPLACE FUNCTION public.this_month_day(integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('month', current_date) + ($1 - 1) * interval '1 day')::date
$function$;

CREATE OR REPLACE FUNCTION public.this_year_day(integer, integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('year', current_date) + ($1 - 1) * interval '1 month' + ($2-1) * interval '1day')::date
$function$;

postgres=# select this_year_day(7,15);
 this_year_day 
---------------
 2013-07-15
(1 row)

postgres=# select this_month_day(15);
 this_month_day 
----------------
 2013-07-15
(1 row)

Wednesday, July 3, 2013

good news for plpgsql developers - enhanced diagnostics fiedls will be available in 9.3

Noah Misch commited my patch, that allows a access to diagnostics fields from PLpgSQL. It can be used for structured custom exception (these fields are accessible on client side).

create or replace function stacked_diagnostics_test() returns void as $$
declare _column_name text;
        _constraint_name text;
        _datatype_name text;
        _table_name text;
        _schema_name text;
begin
  raise exception using
    column = '**some column name**',
    constraint = '**some constraint name**',
    datatype = '**some datatype name**',
    table = '**some table name**',
    schema = '**some schema name**';
exception when others then
  get stacked diagnostics
        _column_name = column_name,
        _constraint_name = constraint_name,
        _datatype_name = pg_datatype_name,
        _table_name = table_name,
        _schema_name = schema_name;
  raise notice 'column %, constraint %, type %, table %, schema %',
    _column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
end;
$$ language plpgsql;


select stacked_diagnostics_test();


NOTICE:  column **some column name**, constraint **some constraint name**, type **some datatype name**, table **some table name**, schema **some schema name**
 stacked_diagnostics_test 
--------------------------
 
(1 row)