Saturday, October 18, 2014

styles for unicode borders are merged (PostgreSQL 9.5)

Following feature is less important for performance, but for somebody can be important for aesthetic reasons - now you can use a styles for unicode table borders. Possible styles are only two, but you can set a border, header and column style. It is a 6 combinations. Next you have a 3 styles for borders generally - so it together 18 possible combinations of psql table output:
postgres=# \pset unicode_header_linestyle double 
Unicode border linestyle is "double".
postgres=# \pset linestyle unicode 
Line style is unicode.
postgres=# \l
                                  List of databases
   Name    │  Owner   │ Encoding │   Collate   │    Ctype    │   Access privileges   
═══════════╪══════════╪══════════╪═════════════╪═════════════╪═══════════════════════
 postgres  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ 
 template0 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵
           │          │          │             │             │ postgres=CTc/postgres
 template1 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵
           │          │          │             │             │ postgres=CTc/postgres
(3 rows)

postgres=# \pset border 0
Border style is 0.

postgres=# \l
                            List of databases
  Name     Owner   Encoding   Collate      Ctype      Access privileges   
═════════ ════════ ════════ ═══════════ ═══════════ ═════════════════════
postgres  postgres UTF8     en_US.UTF-8 en_US.UTF-8 
template0 postgres UTF8     en_US.UTF-8 en_US.UTF-8 =c/postgres          ↵
                                                    postgres=CTc/postgres
template1 postgres UTF8     en_US.UTF-8 en_US.UTF-8 =c/postgres          ↵
                                                    postgres=CTc/postgres
(3 rows)


postgres=# \pset border 2
Border style is 2.
postgres=# \l
                                   List of databases
┌───────────┬──────────┬──────────┬─────────────┬─────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │   Collate   │    Ctype    │   Access privileges   │
╞═══════════╪══════════╪══════════╪═════════════╪═════════════╪═══════════════════════╡
│ postgres  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │                       │
│ template0 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵│
│           │          │          │             │             │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵│
│           │          │          │             │             │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴─────────────┴─────────────┴───────────────────────┘
(3 rows)

postgres=# \pset unicode_border_linestyle double 
Unicode border linestyle is "double".
postgres=# \l
                                   List of databases
╔═══════════╤══════════╤══════════╤═════════════╤═════════════╤═══════════════════════╗
║   Name    │  Owner   │ Encoding │   Collate   │    Ctype    │   Access privileges   ║
╠═══════════╪══════════╪══════════╪═════════════╪═════════════╪═══════════════════════╣
║ postgres  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │                       ║
║ template0 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵║
║           │          │          │             │             │ postgres=CTc/postgres ║
║ template1 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ =c/postgres          ↵║
║           │          │          │             │             │ postgres=CTc/postgres ║
╚═══════════╧══════════╧══════════╧═════════════╧═════════════╧═══════════════════════╝
(3 rows)

postgres=# \pset border 1
Border style is 1.
postgres=# \pset unicode_column_linestyle double
Unicode column linestyle is "double".
postgres=# \l
                                  List of databases
   Name    ║  Owner   ║ Encoding ║   Collate   ║    Ctype    ║   Access privileges   
═══════════╬══════════╬══════════╬═════════════╬═════════════╬═══════════════════════
 postgres  ║ postgres ║ UTF8     ║ en_US.UTF-8 ║ en_US.UTF-8 ║ 
 template0 ║ postgres ║ UTF8     ║ en_US.UTF-8 ║ en_US.UTF-8 ║ =c/postgres          ↵
           ║          ║          ║             ║             ║ postgres=CTc/postgres
 template1 ║ postgres ║ UTF8     ║ en_US.UTF-8 ║ en_US.UTF-8 ║ =c/postgres          ↵
           ║          ║          ║             ║             ║ postgres=CTc/postgres
(3 rows)

Wednesday, September 17, 2014

plpgsql_check rpm packages are available for PostgreSQL9.3 for RHEL7, 6

If you have a RHEL6, 7 based Linux distro and use PostgreSQL 9.3 from community repository, you can install plpgsql_check simply via yum.

Tuesday, September 16, 2014

nice unix filter pv

I search some filter, that can count a processed rows and can to show a progress. It exists and it is pv

# import to vertica
zcat data.sql | pv -s 16986105538 -p -t -r | vsql

ALTER TABLE
0:13:56 [4.22MB/s] [==============>                                                                                               ] 14%

More http://linux.die.net/man/1/pv

I used it for import PostgreSQL dump file to Vertica
cat data2.sql | sed "s/FROM stdin/FROM LOCAL stdin DIRECT DELIMITER E'\\\t'/g" | pv -t -p -a -b -s 125494564903 | vsql

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

Friday, June 27, 2014

plpgsql_check is available for PostgreSQL 9.2

Short note: I did backport of plpgsql_check to PostgreSQL 9.2.

What is plpgsql_check? It is plpgsql validator without necessity to run checked function and all paths inside.
postgres=# select oid::regprocedure, plpgsql_check_function(oid::regprocedure) from pg_proc where prolang = 12599 and prorettype <> 2279;
      oid      │                         plpgsql_check_function                          
───────────────┼─────────────────────────────────────────────────────────────────────────
 bubu(integer) │ error:42702:3:RETURN:column reference "a" is ambiguous
 bubu(integer) │ Query: SELECT (select * from t where t.a = a)
 bubu(integer) │ --                                         ^
 bubu(integer) │ Detail: It could refer to either a PL/pgSQL variable or a table column.
(4 rows)

Time: 4.857 ms

Friday, June 6, 2014

New PostgreSQL 9.4 article

I upgraded a mediawiki 1.23 on our Czech PostgreSQL site. New important feature is a integration of Google Translator. Now a articles from this site can be read in seven languages. New one is my fresh article about PostgreSQL 9.4 new features