Sunday, January 18, 2015

how to push parameters to DO statement from command line

PostgreSQL DO statement doesn't support parametrization. But with psql variables we are able to "inject" do statement safely and we can do it:
bash-4.1$ cat test.sh 
#!/bin/bash

echo "
set myvars.msgcount TO :'msgcount'; 
DO \$\$ 
BEGIN 
  FOR i IN 1..current_setting('myvars.msgcount')::int LOOP 
    RAISE NOTICE 'Hello';
  END LOOP; 
END \$\$" | psql postgres -v msgcount=$1

Usage:
bash-4.1$ ./test.sh 3
SET
Time: 0.386 ms
NOTICE:  Hello
NOTICE:  Hello
NOTICE:  Hello
DO
Time: 1.849 ms

Thursday, January 15, 2015

most simply implementation of history table with hstore extension

Postgres has this nice extension (hstore) lot of years. It can be used for simulation some features of doc databases - or can be used for implementation of generic triggers for history table:

I have a table test and table history:

CREATE TABLE test(a int, b int, c int);

CREATE TABLE history(
  event_time timestamp(2),
  executed_by text, 
  origin_value hstore, 
  new_value hstore
);

CREATE OR REPLACE FUNCTION history_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(event_time, executed_by, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION history_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(event_time, executed_by, origin_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(OLD));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION history_update()
RETURNS TRIGGER AS $$
DECLARE
  hs_new hstore := hstore(NEW);
  hs_old hstore := hstore(OLD);
BEGIN
  INSERT INTO history(event_time, executed_by, origin_value, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, hs_old - hs_new, hs_new - hs_old);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_history_insert AFTER INSERT ON test
  FOR EACH ROW EXECUTE PROCEDURE history_insert();

CREATE TRIGGER test_history_delete AFTER DELETE ON test
  FOR EACH ROW EXECUTE PROCEDURE history_delete();

CREATE TRIGGER test_history_update AFTER UPDATE ON test
  FOR EACH ROW EXECUTE PROCEDURE history_update();
Result:
INSERT INTO test VALUES(1000, 1001, 1002);
UPDATE test SET a = 10, c = 20;
DELETE FROM test;

postgres=# SELECT * FROM history ;
       event_time       | executed_by |           origin_value            |               new_value               
------------------------+-------------+-----------------------------------+---------------------------------------
 2015-01-15 20:59:05.52 | pavel       |                                   | "a"=>"1000", "b"=>"1001", "c"=>"1002"
 2015-01-15 20:59:05.6  | pavel       | "a"=>"1000", "c"=>"1002"          | "a"=>"10", "c"=>"20"
 2015-01-15 20:59:06.51 | pavel       | "a"=>"10", "b"=>"1001", "c"=>"20" | 
(3 rows)

Tested on PostgreSQL 9.2

Saturday, December 20, 2014

plpgsql_check is available via PGXN repository

Only small notice - plpgsql_check is available from PGXN repository. More about PGXN on FAQ page.

Sunday, December 14, 2014

plpgsql_check is ready for OS X

mscottie found a magic option -undefined dynamic_lookup. With this option we are able to compile plpgsql_check on OS X platform.

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