PostgreSQL history

A year ago I wrote article about PostgreSQL history. Original article is in Czech language, but there is a link to Google translated article.

frequent mistakes in plpgsql design

Hello
last days I had to correct some typical errors in audited code. There are:

• non-use coalesce:

  -- bad
  BEGIN
    IF x1 IS NOT NULL THEN
      s := s || x1 || ',';
    ELSE
      s := s || 'NULL, ';
    END IF;
    IF x2 IS NOT NULL THEN ...
  
  -- good
  BEGIN
    s := coalesce(x1 || ',', 'NULL,') || ...
  

• using implicit cast date to text:

  -- bad
  BEGIN
    month = substring(current_date::text FROM 6 FOR 2)::int;
  
  -- good
  BEGIN
    month = substring(to_char(current_date,'YYYY-MM-DD') FROM 6 FOR 2)::int;
  
  -- but better
  BEGIN
    month = EXTRACT(month FROM current_date);
  

• using EXECUTE instead PERFORM
  
• using explicit cursor instead FOR IN SELECT
  
• unsecured dynamic SQL:

  --bad
  EXECUTE 'SELECT ' || column_name || ' FROM ' || table_name
             || ' WHERE ' || column_name || e'=\'' || some_variable || e'\''
    INTO var;
  
  --good
  EXECUTE 'SELECT ' quote_ident(column_name) || ' FROM ' || quote_ident(table_name)
             || WHERE ' || quote_ident(column_name) || '=' || quote_literal(some_variable)
    INTO var;
  
  -- or on 8.4 and higher
  EXECUTE 'SELECT ' quote_ident(column_name) || ' FROM ' || table_name::regclass
             || WHERE ' || quote_ident(column_name) || '= $1'
    INTO var
    USING some_variable
  
  --bad
  CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
  RETURNS void AS $$
  BEGIN
    EXECUTE 'CREATE TABLE ' || coalesce(schemaname || '.','') || tablename;
    RETRUN;
  END;
  $$ LANGUAGE plpgsql;
  
  --good
  CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
  RETURNS void AS $$
  BEGIN
    EXECUTE 'CREATE TABLE ' || coalesce(quote_ident(schemaname) || '.','') || quote_ident(tablename);
    RETURN;
  END;
  $$ LANGUAGE plpgsql;
  

• long lines - 100 chars per line is perfect
  
• too short functions - Don't wrap any and only one SQL statement
  
• Don't use PL/pgSQL for deep recursion calls
  Due internal design principles PL/pgSQL should not be well optimized for recursion calls. PL/pgSQL supports recursion, and for not too deeps calls can provide enough to satisfy sb performance, but it is very slow for deep recursion. Nice example is taken from presentation http://plv8-talk.herokuapp.com. It is perfect example how don't use PL/pgSQL ever.
  

  -- non recursion form
  CREATE OR REPLACE FUNCTION public.psqlfibnr(n integer)
   RETURNS integer
   LANGUAGE plpgsql
   IMMUTABLE STRICT
  AS $function$
  DECLARE 
    prev1 int = 0;
    prev2 int = 1;
    result int = 0;
  BEGIN
    FOR i IN 1..n
    LOOP
      result := prev1 + prev2;
      prev2 := prev1;
      prev1 := result;
    END LOOP;
    RETURN result;
  END;
  $function$
  
  -- recursion form
  CREATE OR REPLACE FUNCTION public.psqlfibr(n integer)
   RETURNS integer
   LANGUAGE plpgsql
   IMMUTABLE STRICT
  AS $function$
  BEGIN
    IF n < 2 THEN
      RETURN n;
    END IF;
    RETURN psqlfib(n-1) + psqlfib(n-2);
  END;
  $function$
  
  -- non recursive calls
  postgres=# select n, psqlfibnr(n)
                from generate_series(0,35,5) as n;
   n  | psqlfibnr 
  ----+-----------
    0 |         0
    5 |         5
   10 |        55
   15 |       610
   20 |      6765
   25 |     75025
   30 |    832040
   35 |   9227465
  (8 rows)
  
  Time: 1.178 ms
  
  -- recursive calls
  postgres=# select n, psqlfib(n)
                from generate_series(0,35,5) as n;
   n  | psqlfib
  ----+---------
    0 |       0
    5 |       5
   10 |      55
   15 |     610
   20 |    6765
   25 |   75025
   30 |  832040
   35 | 9227465
  (8 rows)
  
  Time: 282992.820 ms
  

  Speed of recursion calls in PL/pgSQL is not comparatable with recursion optimized languages like Javascript.

Don't use procedural code when you can use natural SQL tools. Use UNIQUE INDEX instead custom triggers for ensuring unequivocalness.

-- bad usage
CREATE OR REPLACE FUNCTION ensure_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS(SELECT * FROM tab WHERE id = NEW.id) THEN
    RAISE ERROR 'id is not unique';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- good (use unique index)
CREATE UNIQUE INDEX ON tab(id);

enabling access from stored procedure to host variables

I am trying to join two different worlds - stored procedures (server side) and host environment (client side). Why? We have a good server side language - plpgsql, and we have good script environment - psql console. Console has own environment, but it is not accessible from plpgsql. I wrote two functions, that enables it:

postgres /home/pavel $ psql postgres -v myvar=hello
psql (9.3devel)
Type "help" for help.

postgres=# \echo :myvar
hello
postgres=# do $$begin raise notice 'myvar=>>%<<', get_hostvar_text('myvar'); end; $$ language plpgsql;
NOTICE:  myvar=>>hello<<
DO

postgres=# do $$begin perform set_hostvar_text('myvar', 'hello world'); end $$ language plpgsql;
DO
postgres=# \echo :myvar
hello world

This is just concept without any optimization. It can be cached on server side, it can be better integrated to language.

better integration psql with bash

I wrote very simple patch for psql, that enable shell format. Enabling this format ensures, so psql output will be optimized for processing in bash - and you can write scripts like:

( psql -t -P format=shell postgres <<EOF                                                                             
SELECT d.datname as "Name",                                                                                          
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",                                                              
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",                                                     
       d.datcollate as "Collate",                                                                                    
       d.datctype as "Ctype",                                                                                        
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"                                            
FROM pg_catalog.pg_database d                                                                                        
ORDER BY 1;                                                                                                          
EOF                                                                                                                  
) | while read dbname owner encoding collate ctype priv;                                                             
    do                                                                                                               
      echo "DBNAME=$dbname OWNER=$owner PRIVILEGES=$priv";                                                           
    done;

or with bash associative arrays:

( psql -t -x -P format=shell postgres <<EOF                                                                          
SELECT pg_catalog.pg_get_userbyid(d.datdba) as "Owner",                                                              
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",                                                     
       d.datcollate as "Collate",                                                                                    
       d.datctype as "Ctype",                                                                                        
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"                                            
   FROM pg_catalog.pg_database d                                                                                     
  ORDER BY 1;
EOF                                                                                                                  
) | (declare -A row                                                                                                  
while read r                                                                                                         
    do                                                                                                               
      declare -A row="$r"                                                                                            
      for field in "${!row[@]}"                                                                                      
      do                                                                                                             
        echo  "$field -> ${row[$field]}"                                                                             
      done;                                                                                                          
      echo;                                                                                                          
    done;)

the output of query and "shell" format looks like:

Name Owner Encoding Collate Ctype Access\ privileges
jqerqwer,\ werwer pavel UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\]
postgres postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\]
some\ stupid\ name pavel UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\]
template0 postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 =c/postgres\\npostgres=CTc/postgres
template1 postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 =c/postgres\\npostgres=CTc/postgres

or (for assoc. arrays)

( c l )
( [Name]=jqerqwer,\ werwer [Owner]=pavel [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] )
( [Name]=postgres [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] )
( [Name]=some\ stupid\ name [Owner]=pavel [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] )
( [Name]=template0 [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]==c/postgres\\npostgres=CTc/postgres )
( [Name]=template1 [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]==c/postgres\\npostgres=CTc/postgres )

I invite any ideas and notes

new version of plpgsql_lint released (for PostgreSQL 9.0 and 9.1)

Just notice: I moved a development of plpgsql_lint to github.

• removed coverage tests - no usefull as I though
• cleaned code and enhance support for cursors

Change a unknown record's field in PL/pgSQL

Hello

Week ago was a request on stackoverflow about change of composite variable's field by dynamic SQL. I wrote a first very simply solution:

CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select $1.*;
  execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
  select into $1 * from aux;
  drop table aux;
  return $1;
end;
$function$

It's works, but it's slow and it consumes lot of shared memory (impracticable for repeated using in one transaction). Next version was better - it is faster and isn't hungry:

CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a 
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof($1)::oid) 
  LOOP
    IF _name = $2 THEN
      _value := $3;
    ELSE
      EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; 
  RETURN $1;
END;
$function$;

There are a few slower points: EXECUTE in loop, array's update in loop.

But Erwin Brandstetter found a probably best and most simply solution - there are no more ways on PL/pgSQL level.

CREATE OR REPLACE FUNCTION public.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof($1)::oid) 
      ORDER BY a.attnum
   ) x
);

EXECUTE '
SELECT ' || _list || '
FROM   (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END;
$body$ LANGUAGE plpgsql;

There is no PL/pgSQL's loop and there is just one EXECUTE. More - this code is just simple - without lot of string (quoting) operations.

Very nice, Erwin!

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