Wednesday, May 25, 2016

Orafce 3.3.0 was released

Tuesday, May 24, 2016

plpgsql_check 1.0.5 released

New version of plpgsql_check is available. The most important change is support for future PostgreSQL 9.6

https://manager.pgxn.org/distributions/plpgsql_check/1.0.5
https://github.com/okbob/plpgsql_check/releases/tag/v1.0.5

Friday, January 8, 2016

new release Orafce and plpgsql_check

Hi

I released new updates of Orafce and plpgsql_check

Details in release notes

https://github.com/orafce/orafce/releases/tag/VERSION_3_2_1
https://github.com/okbob/plpgsql_check/releases/tag/v1.0.3

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.

Configuration:

session_preload_libraries to session_exec
session_exec.login_name to login

Usage:

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

postgres=# CREATE OR REPLACE FUNCTION login()
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.

Attention:

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
else
  env psql "$@"
fi
}

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')"`; 
do 
  psql -At -c "select current_database(), pg_size_pretty(pg_table_size('pg_attribute')) where pg_table_size('pg_attribute') > 100 * 1024 * 1024" $db; 
done
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')"`; 
do 
  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"

Update

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"