Friday, January 8, 2016

new release Orafce and plpgsql_check


I released new updates of Orafce and plpgsql_check

Details in release notes

PostgreSQL 9.5 are supported by by both extensions

Monday, October 19, 2015

Fedora 23 on Toshiba PORTÉGÉ - few unresolved issues

After years I have new notebook. The installation of Fedora 23 was without harder issues, and I am satisfied with this notebook. I found few issues, that I cannot to solve:

* sound - this issue is really strange - maybe it is PulseAudio issue. I am listening low noise from internal speakers. It is ok, when I am listing any sound. It is ok, when I disable sound. It is ok, if I use a external speakers. Working well in MS Win.

* a fan runs ever. This is known issue of Toshiba's notebooks. The usual control tools of fans doesn't work, and default configuration is not good (although it is safe). This behave can be changed in BIOS, but then the notebook is pretty slow. The noise is not too loud - but it is in higher tone than it is usual. Because it is working well in MS Win, I hope, this issue will be solvable.

* small artifacts in played video. Sometimes the video rendering is few miliseconds late. It isn't often - few times in ten minutes. I didn't test native Intel drivers yet.

These issues are small. All is working well with default configuration and default drivers (graphics, sound, wifi). This notebook has perfect display (with native resolution the Gnome 3 looks perfect), good keyboard, is really light.

Any hints how to fix these issues?

Sunday, September 27, 2015

Run PLpgSQL function before login

I wrote simple extension session_exec. This extension enforce running some specified function before user is connected to PostgreSQL. This function can be used for some complex user management.


session_preload_libraries to session_exec
session_exec.login_name to login


# first login
[pavel@dhcppc4 plpgsql_check]$ psql
WARNING:  function "login()" does not exist

postgres-# RETURNS void AS $$
postgres$# BEGIN
postgres$#   IF current_database() <> 'admindb' AND session_user = 'postgres' THEN 
postgres$#     RAISE EXCEPTION 'user postgres is blocked';
postgres$#   ELSE
postgres$#     RAISE NOTICE 'Hello, %', session_user;
postgres$#   END IF;
postgres$#   RETURN;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;

# new login
 [pavel@dhcppc4 plpgsql_check]$ psql
NOTICE:  Hello, pavel

-- try to login as Postgres
postgres=# \c postgres postgres
FATAL:  unhandled exception in login function "login"
DETAIL:  user postgres is blocked
CONTEXT:  PL/pgSQL function login() line 4 at RAISE
session_exec: perform login function "login"
Previous connection kept

This is example only. For this specific use case the modification of pg_hba.conf is better. But you can implement more complex logic in plpgsql. The login function can be specific for database or for user via ALTER DATABASE xx SET or ALTER ROLE xx SET.


When you use login function, the connect to database will be little bit slower. Use it only when it is necessary.

Wednesday, July 15, 2015

aliasing psql

When you run psql without arguments, then the psql tries to connect to database named like current user. I dislike this behave. Surely I have not database "pavel". There is trivial solution - add to your .bashrc code:
function psql { 
if [[ $# -eq 0 ]]; then
   env psql postgres
  env psql "$@"

Tuesday, July 14, 2015

simple parallel run statement in every database in PostgreSQL cluster

When I need to execute some statement in every database of some PostgreSQL cluster, I am using a script:
for db in `psql -At -c "select datname from pg_database where datname not in ('template0','template1')"`; 
  psql -At -c "select current_database(), pg_size_pretty(pg_table_size('pg_attribute')) where pg_table_size('pg_attribute') > 100 * 1024 * 1024" $db; 
Today I needed to run VACUUM statement for selected databases. So I needed to find a way, how to run this slow statement. I was to surprised how it is simple task due strong xarg command. And nice bonus - I can run this slow queries parallel - because xargs can run entered command in more workers (-P option):
# find databases with bloated pg_attribute table, and enforce VACUUM
for db in `psql -At -c "select datname from pg_database where datname not in ('template0','template1')"`; 
  psql -At -c "select current_database() where pg_table_size('pg_attribute') > 100 * 1024 * 1024" $db; 
done | xargs -P 3 -I % psql % -c "vacuum full  verbose analyze pg_attribute"


Previous example can be little bit simplified
psql postgres -At -c "select datname from pg_database where datname not in ('template0','template1')" |
xargs -P 3 -I % psql % -c "select current_database() where pg_table_size('pg_attribute') > 100 * 1024 * 1024" |
xargs -P 3 -I % psql % -c "vacuum full analyze pg_attribute"

Saturday, July 11, 2015

New Orafce and Plpgsql_check extensions released

I released a new versions of these packages: Orafce and Plpgsql_check. Its mostly bugfix releases with PostgreSQL 9.5 support.

Sunday, May 31, 2015

How to write robust database application?

  1. Fault tolerant design is cheaper than fault tolerant application and fault tolerant application is significantly cheaper than fault tolerant database and more robust. Database has not knowledge what is critical and what is not critical, what can be lost with less cost, and what must not be lost.
  2. Database is good tool for data processing, and wrong tool for communication - A design and implementation of communication server is more simply, more robust than master-master replication based communication solution. Design servers, design protocols, design services - don't lost your time with master-master replication and collision solutions.
  3. More smaller servers, databases are more robust, cheaper, practical than one super server, super database. Try to minimize dependency between components - when one component fails, all other should to work without significant problems some time (or critical services should to continue). Strong dependency  increases risk of total blackout. Think about failures. Decrease strong dependency, when it is possible and practical. Better data duplication than total blackout.
  4. Be sure, so you well understand, what kind is your application - for lot of application some 24x7 solution uselessly increase a complexity, cost, maintenance cost, time. The most simple solution is usually the best. But attention on too simply solutions!
  5. Think about deployment - strong dependency between components increase a risk of total failures, when you deploy your solution. Try to deploy any component without strong dependency on other component - you should be able to do rollback any individual component.