вторник, 3 апреля 2012 г.

PostgreSQL mostly online REINDEX

What do you do, when you want to do REINDEX online? Build new index concurrently, drop old one and rename new. Only you can't do it for indexes supporting primary keys and other constraints without dropping constraint. So, in any case, you have to REINDEX them. And let everyone to be waiting...
And if you have several hundred indexes, it is not very convenient to do it manually. Also note that CREATE INDEX CONCURRENTLY can't be EXECUTE'd from plpgsql function. I wish we had concurrent reindex in PostgreSQL or at least autonomous transactions to deal with the latter problem... But, we must work with available functionality...
So, the decision for me was to write PL/pgSQL script which will generate necessary SQL commands and feed them to psql.
The script itself is the following:

DO
$$
DECLARE
ind record;
str text;
str_drop text;
str_rename text;
BEGIN
FOR ind IN (SELECT i.oid,i.relname FROM
pg_class r,pg_class i , pg_index ir, pg_namespace ns
WHERE ir.indexrelid=i.oid AND ir.indrelid=r.oid AND ns.oid=r.relnamespace
AND ns.nspname='public' AND NOT ir.indisprimary \
AND i.oid NOT IN
(SELECT conindid FROM pg_constraint)) LOOP
str:=replace(
pg_get_indexdef(ind.oid),
'INDEX '||ind.relname|| ' ON ',
'INDEX CONCURRENTLY '||ind.relname|| '_X999 ON ');
str_drop:='DROP INDEX ' || ind.relname;
str_rename:='ALTER INDEX ' || ind.relname||'_X999 RENAME TO ' ||ind.relname ;
RAISE NOTICE '%', str;
RAISE NOTICE '%', str_drop;
RAISE NOTICE '%', str_rename;
END LOOP;
FOR ind IN (SELECT i.oid,i.relname FROM
pg_class r,pg_class i, pg_index ir, pg_namespace ns
WHERE ir.indexrelid=i.oid AND ir.indrelid=r.oid AND ns.oid=r.relnamespace
AND ns.nspname='public' AND (ir.indisprimary
OR i.oid IN (SELECT conindid FROM pg_constraint)) LOOP
str:='REINDEX INDEX ' || ind.relname;
raise notice '%', str;
end loop;
END;
$$ LANGUAGE PLPGSQL;


Now with something like this:

$ psql -d DBNAME -f generate_reindex.plpgsql" 2>&1| awk -F 'NOTICE:' '{ if (NF==2) {print $2 ";" } ; }' | psql -d DBNAME

we can mostly concurrently rebuild all indexes in database DBNAME. And last thing to remember is to add
\set ON_ERROR_STOP
to your ~/.psqlrc file just to be safe...

четверг, 22 марта 2012 г.

Redmine/Webrick rc script

I've just installed redmine 1.3.2 on FreeBSD server and met several inconviniences.

  1. It is not in ports

  2. It depends on old versions of different ruby gems, which are already not in ports.
    In particular, it didn't want to work with rack 1.4.1 and I had to do

    # gem install rake -v=1.1.0


  3. It doesn't have rc script to startup Webrick web server (I didn't want to install apache on this host)


So, I've taken mongrel_cluster rc script and made redmine rc script from it. It is here:

#!/bin/sh
# PROVIDE: redmine
# REQUIRE: DAEMON
# KEYWORD: shutdown
#
# This script is modified by placing the following variables inside
# /etc/rc.conf:
#
# redmine_enable (bool):
# Set it to YES to enable this service.
# Default: NO
# redmine_dir (path):
# The directory containing redmine
# Default: /usr/local/redmine/
# redmine_user (username):
# The user to run redmine as
# Default: redmine
# redmine_args (string):
# Additional command flags for ruby
# Default: "-e production -d"

. /etc/rc.subr

name=redmine
rcvar=redmine_enable

command="/usr/local/bin/ruby"

load_rc_config $name

: ${redmine_enable="NO"}
: ${redmine_dir="/usr/local/redmine/"}
: ${redmine_args="-e production -d"}
: ${redmine_user="redmine"}

start_cmd="redmine_cmd start"
stop_cmd="redmine_cmd stop"
restart_cmd="redmine_cmd restart"
status_cmd="redmine_cmd status"

redmine_cmd()
{

if [ ! -d "${redmine_dir}/." ]; then
warn "${redmine_dir} is not a directory."
return 1
fi

case $1 in
"start")
su -l ${redmine_user} -c "cd ${redmine_dir} && pwd && eval $command script/server webrick $redmine_args"
;;
"stop")
if [ -f ${redmine_dir}/tmp/pids/server.pid ] ; then
PID=$(/usr/bin/head -1 ${redmine_dir}/tmp/pids/server.pid)
/bin/kill -s int $PID 2>/dev/null
sleep 3
/bin/kill -s 0 $PID 2>/dev/null && /bin/kill -s kill $PID;
fi
;;
"restart")
redmine_cmd stop
redmine_cmd start
;;
"status")
if [ -f ${redmine_dir}/tmp/pids/server.pid ] ; then
echo "PID file exists"
PID=$(/usr/bin/head -1 ${redmine_dir}/tmp/pids/server.pid)
/bin/kill -s 0 $PID 2>/dev/null && echo "Server is running with pid $PID" && exit 0;
echo "But server is not running..." && exit 1;
fi
echo "Server is not running" && exit 0;
;;
esac
}

run_rc_command "$1"


The interesting thing to note is that to terminate webrick correctly, you have to send it SIGINT, not SIGTERM.

четверг, 15 марта 2012 г.

Remember TRUNCATE peculiarities

Just remember the following about TRUNCATE:
1) It is DDL operation and so is not transactional in Oracle (issuing TRUNCATE will make an implicit commit)
2) It is not MVCC-safe in PostgreSQL.

What does it mean?


alp=> create table t199 as select * from pg_opclass ;
SELECT 117
alp=> BEGIN ;
BEGIN
alp=> TRUNCATE t199;
TRUNCATE TABLE
alp=> ROLLBACK ;
ROLLBACK ^
alp=> SELECT count(*) from t199;
count
-------
117
(1 row)

So far, everything looks good: TRUNCATE was rolled back. Transactional DDL is a powerful PostgreSQL feature. But let's start parallel transaction. In first session I'll issue

alp=> \set PROMPT1 'T1> '

and in second:

alp=> \set PROMPT1 'T1> '


So, let's fun begin.
 
T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> TRUNCATE t199;
TRUNCATE TABLE
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
count
-------
0
(1 row)

Oops... What has happened? Let's repeat...


T2> INSERT INTO t199 SELECT * from pg_opclass;
INSERT 0 117

T1> BEGIN;
BEGIN
T1> SET transaction_isolation TO serializable;
SET
T1> UPDATE employees SET email=email;
UPDATE 103

T2> BEGIN ;
BEGIN
T2> DELETE FROM t199;
DELETE 117
T2> COMMIT ;
COMMIT

T1> SELECT count(*) from t199;
count
-------
117
(1 row)


Do you see the difference? T2's DELETE didn't modified T1 snapshot... But TRUNCATE did. And what about "UPDATE employees"? It is just necessary to get real XID and snapshot for transaction.
So, the main idea: be attentive with "TRUNCATE". It is not just "fast DELETE", it has some not-so-evident consequences.

понедельник, 27 февраля 2012 г.

Minor PostgreSQL updates and different unpleasant surprises

Today I've updated two our DBMS servers to PostgreSQL 9.0.7. It was a minor update, everything went smoothly: I just updated ports and let pormaster to do its job...
But I got an interesting consequence - something was fixed and our server began to write error log in Russian. Oh, shit! It wasn't pleasant. Have you ever tried to find something about particular error using its localized description?
I've checked my environment: LC_* variables were not set, pgsql's user class was not "russian", nothing has changed. However, I got a nice log with localized reports... One more trouble - they were messed up in some way (don't know, whose fault was this - OS, PostgreSQL) and hardly readable. I really don't understand, why OS or any service should ever print any errors or logs in native language, this practice leads to difficulties in their interpretation.
In any way, it turned out that in postgresql.conf lc_messages were set to "ru_RU.UTF-8", maybe since initdb run, but before update this GUC didn't have such influence. Something was fixed, and behavior changed. So, keep in mind, even minor updates may lead to different surprises...

воскресенье, 26 февраля 2012 г.

Libreoffice is a slow piece of shit

Subj!!! I hate this piece of software. It is fucken slow, when
a) it works with PostgreSQL-based datasource (e.g. as a bibliography source),
b) it works awfully slow with document, in which we store editing history...
The bad news are that there is little alternatives. Maybe the next time I wish to write some big and complex document, I'll try to do it in Google Docs...

вторник, 14 февраля 2012 г.

French symbols without azerty

So, sometimes I have to type some text in French. And I dislike AZERTY layout, it's too unfamiliar. So, what should I do? Right, to use altgr-intl US layout. To do this you just need to set XKB Variant to "altgr-intl" (something like this in hal policy file - /usr/local/etc/hal/fdi/policy/x11-input.fdi on my FreeBSD system):

<?xml version="1.0" encoding="ISO-8859-1"?>
<deviceinfo version="0.2">
<device>
<match key="info.capabilities" contains="input.keyboard">
<merge key="input.x11_options.XkbModel" type="string"> pc105</merge>
<merge key="input.x11_options.XkbLayout" type="string">us,ru</merge>
<merge key="input.x11_options.XkbOptions" type="string">grp:rwin_toggle,grp_led:scroll</merge>
<merge key="input.x11_options.XkbVariant" type="string">altgr-intl,winkeys</merge>
</match>
</device>
</deviceinfo>


Now, to type something with ^ , like û, you just type RightAlt+6 (^ symbol is there) and press u. To type è - RightAlt+` and e... Big list of shortcuts is here. And if you don't touch right alt this layout behaves just like basic us layout...

понедельник, 6 февраля 2012 г.

Illumian is here...

Today I've tried the first Illumian version. This is illumos-based Nexenta OS. Just a few remarks.
What I liked:
1) VTs finally work in illumos installer, it's good to have a possibility of going to text console and checking installer's log.
2) It seems, the installer can install system on mirror pool. C'est beau.
3) APT... I finally shouldn't learn one more package system and apt is IMHO faster then IPS.
4) It's still Solaris: you have DTrace, ZFS, projects, fair share scheduler...
5) git change number in place of OS version looks cool :)

# uname -a
SunOS oi-test 5.11 4cece89cac3e i86pc i386 i86pc Solaris

What I disliked:
1) Zones don't work:

root@oi-test:~# zoneadm -z myzone install
sh[1]: exec: /usr/lib/brand/ipkg/pkgcreatezone: not found

And Solaris without zones is a very strange thing...
2) /usr/gnu/bin in front of path... How can you use GNU chmod with ZFS ACL?
3) Soft, soft, soft... Where are you?

root@oi-test:~# apt-cache pkgnames |grep postgres

4) apt-clone doesn't work

This is just a first glance on this OS. It is very raw and is still in development. I doubt that calling this release 1.0 was a good idea. It seems, we will have a production-ready OS not sooner then in half of year, but still... I wonder, what Illumos-based OS will be first to reach production quality: OI or Illumian? It seems OI is in better form nowadays, but the fact is that OI team has failed to create a stable release in about 1.5 years.