Reviewing PostgreSQL Admin 9.0 Cookbook

I was asked by Likesh Bhambhwani to review the PostgreSQL Admin 9.0 Cookbook written by Simon Riggs and Hannu Krosing. It will no doubt be an interesting read.

Review will be posted as soon as I'm done!

pg_upgrade and shared libraries

I ran pg_upgrade on a dev cluster of mine and it halted on some shared libraries I had forgot to install for the new cluster.

Most of them were plain contrib that I'll continue to use so no problem there but there were some testing code I had been playing around with. I didn't know in which of my test databases I had used it so I had to find out somehow.

This is what I came up with, a query to list what shared libraries are used in a database. With that information I could drop the functions depending on shared libraries I no longer used.

    p.proname as "name",
    pg_catalog.pg_get_function_arguments(p.oid) as "args",
    n.nspname as "namespace",
    p.probin::text as "lib",
    u.usename as "owner"
from pg_proc p
    join pg_user u on u.usesysid=p.proowner
    join pg_language l on l.oid=p.prolang
    join pg_namespace n on n.oid=p.pronamespace
    nspname not in ('pg_catalog', 'information_schema')
    and lanname='c'

To avoid running it manually on all databases I wrote some python code to do that for me. If it's of use to anyone I'd be more than happy to share that too.

Print pg_stat_bgwriter analysis from CSV file

From the lecture Monitoring PostgreSQL Buffer Cache Internals by Greg Smith I created a simple script to read, calculate and print the pg_stat_bgwriter analysis. It's a simple script but maybe someone else does find it useful to.

Android + PostgreSQL = ?

What would a great android application for postgresql be? That's the nexus-one-question... Join the competition!

PostgreSQL 9.0 Alpha4

Time to take PostgreSQL 9.0 Alpha4 for a spin! There's a bunch of new stuff to get to know about and try out.

EnterpriseDB + Red Hat = <3

This press release from Reuters about Red Hat investing in EnterpriseDB is great news.

Since Red Hat once manage to bring Linux to the room of Enterprise IT I'm hoping this would increase the acceptance of PostgreSQL adoption in the Enterprise world. We all know how slowly the Enterprise wheels are turning... :)

Interesting choice of words

Just read the article Is it Postgres' time to shine? and one sentence caught my attention:

Postgres is an enterprise Java database, more suitable for carrying corporate data than the Web's consumer data.

How do you interpret the first part? That PostgreSQL is a suitable database backend for enterprise Java or that PostgreSQL is written in Java?

My thought was "If I didn't know about PostgreSQL I would think that it was a database system written in Java". What's your take?

(As a side note I don't think that article was particularly interesting but I suspect CNET News to have a fair amount of management among their readers.)

strip html tags but keep href attribute value

To be able to match hostnames in links and at the same time get the benefits of tsearch I created a small function to strip of html tags while keeping the link intact for tsearch to tokenize.

The second regexp_replace is really not necessary since tsearch will ignore any HTML tags, or rather see them as XML tokens.

I'm sure there are more clever ways of accomplish the same thing but this seemed as a fine compromise for the moment. Thoughts and comments are of course welcome. :)

    -- strip tags function
    -- we use this to strip all html tags but still preserving the href
    -- attribute value so tsearch later can match host.
    -- Does two runs:
    -- 1) strip all tags containg the attribute href but preserve the 
    --    attribute value and put it in parentheses.
    -- 2) strip of any remaining tags
        SELECT regexp_replace(
               E'<[^>]*?(\s* href \s* = \s* ([\'"]) ([^>]*?) ([\'"]) ) [^>]*?>',
               E' (\\3) ',
            E'(< [^>]*? >)',

PostgreSQL 8.4 released

Spread the word, PostgreSQL 8.4 is out!

generate_series to cover for missing data in reports

Problem: Generating a nice report grouped by date while some dates are missing data. (I admit the topic indicates something a little bit more exciting...)

Previously I have solved this kind of problem in the client code or by having some sort of calendar table to join with. This time I don't have any client code (yet) and I have no other tables to use. Lucky me I'm using PostgreSQL since it has the generate_series function.

So basically all I have to do is a RIGHT OUTER JOIN with generate_series as dates:

select date '2009-02-01' + generate_series(0,6);

An full example to play with:

drop table if exists mytable;
create table mytable(id serial, day date,     metric int);
insert into mytable(day, metric) values('2009-02-01', random()*10),
('2009-02-01', random()*10), ('2009-02-01', random()*10),
('2009-02-03', random()*10), ('2009-02-03', random()*10),
('2009-02-03', random()*10), ('2009-02-03', random()*10),
('2009-02-04', random()*10), ('2009-02-04', random()*10),
('2009-02-05', random()*10), ('2009-02-05', random()*10),
('2009-02-05', random()*10), ('2009-02-05', random()*10),
('2009-02-07', random()*10), ('2009-02-07', random()*10);

Now try it out:

    coalesce(, as day,
    coalesce(mytable.sum, foo.sum) as sum
    (select day, sum(metric)
     from mytable group by day) as mytable
right outer join
    (select date '2009-02-01' +      as day, 0 as sum
     from generate_series(0,6) as foo(day)) as foo
order by day;

As stated earlier I have usually solved this problem in client code. I recall doing so both when using MSSQL and Oracle although I haven't worked with them for a long while now. I can't recall ever having done any similar reports while using MySQL.

I'm curious about the current state of Oracle, MSSQL and MySQL since I'm not actively using them. Is there something similar as generate_series? What about DB2?

Rebuild template1

A colleague of mine got his template1 corrupted and I just told him to recreate it from template0. But there's a catch of course, template1 being a template database.

The quick fix for this is to connect as superuser, set template1 as not being a template database, drop and recreate the template1 from template0:

$ psql -U postgres postgres
postgres=# update pg_database set datistemplate = false where datname='template1';
postgres=# drop database template1;
postgres=# create database template1 template=template0;
postgres=# update pg_database set datistemplate = true where datname='template1';

I also found an old, but not outdated, article by Josh Berkus about pretty much the same issue, explaining it more thorough, on the PostgreSQL Wiki.

A sane default

Without any changes to template1 a sane default when creating a new database could be something like the script below.

It creates a newdatabase newdb with two users, adminuser for administration and rouser for read only access. (I just realized I forgot a rwuser but never mind.)

The only thing that bugs me is the tedious work of granting access to rouser when adding stuff to the database. Any takers on that one?

    create role dbname nosuperuser nocreatedb nocreaterole noinherit nologin;
    create role adminuser nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'secretpassword';
    grant dbname to adminuser;

create database dbname with owner=adminuser;

\connect dbname
    revoke all on database dbname from public;
    create role rouser nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'secret';
    grant all on schema public to adminuser with grant option;
    revoke all on schema public from public;
    grant connect on database dbname to rouser;
    grant usage on schema public to rouser;

\connect dbname adminuser
    create table account(id serial, created timestamp  default now(), name text not null, password text not null);
    grant select on account to rouser;

-- drop database dbname;
-- drop role rouser;
-- drop role adminuser;
-- drop role dbname;

Fibonacci sequence using WITH RECURSIVE

-- Fibonacci sequence
-- Example to demo WITH RECURSIVE in PostgreSQL 8.4
with recursive f as (
    select 0 as a, 1 as b
    union all
    select b as a, a+b from f where a < 100
) select a from f;

Results in:

(13 rows)

Follow up: Vacuum Full progress

Ok, to follow up my recent post I created a small example of how the progress indication could be done. The example is very simple and is only a proof of concept, nothing more. There is no support for multiple file nodes, only relation and index is considered, etc.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import psycopg2
import os
import time
from threading import Thread

class Vacuum(Thread):
    def __init__(self, dsn, name):
        super(Vacuum, self).__init__()
        self.connection = psycopg2.connect(dsn)
        self.connection.set_isolation_level(0) = name
    def run(self):
        cursor = self.connection.cursor()
        cursor.execute('vacuum full %s' % (
class Progress(Thread):
    def __init__(self, dsn, name, pgdata):
        super(Progress, self).__init__()
        self.connection = psycopg2.connect(dsn)
        self.table = name
        self.objects = {}
        cursor = self.connection.cursor()
        cursor.execute("select oid from pg_database where datname=current_database();")   
        db_oid = cursor.fetchone()[0]
        self.path = '%s/base/%s' % (pgdata, str(db_oid))
        cursor.execute("select oid from pg_class where relname='%s'" % (name))
        oid = cursor.fetchone()[0]
        epoch = os.path.getmtime(self.path + '/' + str(oid))
        orig = time.strftime('%H:%M', time.localtime(epoch))
        self.objects[name] = {'filenode': oid, 'epoch': epoch,    'original_time': orig}
        index_query = "select c2.relname, c2.relfilenode FROM pg_catalog.pg_class c, pg_catalog.  pg_class c2, pg_catalog.pg_index i WHERE c.oid=%s::integer AND c.oid = i.indrelid AND i.indexrelid = c2.oid;"
        cursor.execute(index_query % (oid))
        for row in cursor.fetchall():
            name, oid = row[0], row[1]
            epoch = os.path.getmtime(self.path + '/' + str(oid))
            orig = time.strftime('%H:%M', time.localtime(epoch))
            self.objects[name] = {'filenode': oid, 'epoch':       epoch, 'original_time': orig}

    def _check(self):
        done = 0
        for name, attrs in self.objects.items():
            epoch = os.path.getmtime(self.path + '/' + str(attrs['filenode']))
            if epoch > attrs['epoch']:
                self.objects[name]['done_epoch'] = epoch
                done += 1
        return done

    def run(self):
        done = 0
        print "done: %s of %s" % (done, len(self.      objects))
        cursor = self.connection.cursor()
        while len(self.objects) > done:
            tmp, done = done, self._check()
            if tmp != done:
                print "done: %s of %s" % (done,        len(self.objects))

if __name__ == '__main__':
    import sys
    dsn = 'dbname=testdb user=testuser password=secret host='
    vacuum = Vacuum(dsn, 'db_category')
    progress = Progress(dsn, 'db_category', '/path/to/my/pgdata')

# vi: set fileencoding=utf-8 :

To do something serious about this the implementation would have to understand which objects have file nodes, support multiple file nodes, time each run and try to estimate progress, etc.

I would also need to understand exactly how VACUUM FULL behaves, when files are written for example.

Estimate full vacuum time

Tonight during an upgrade of our systems I was doing a full vacuum on a couple of tables and I started to wonder how long it would take.

After poping the question on #postgresql@freenode Steve Atkins (I think it was) suggested I could get kind of a feeling of progress by looking at the file nodes.

So I did and it gave sort of a feeling about what stuff was happening but it was kind of a hassle to do. (I had one psql open and one xterm looking at the relfileno in one and the file node modification time in the other.)

This got me thinking that if I get a list of file nodes involved before I start the (full) vacuum I should be able to keep track of progress by using something like inotify. The idea is still a bit rough and I need to look into exactly what goes on during a full vacuum (like when are things written to disk) but I still think it's doable.

Would this kind of approach be useful for anything or am I just rambling? (After all it is 4:20am here now...)

pgarchives extension Shredder on OS X

Since I like to play around with things I just had to give Magnus Haganders PG Archives extension a try.

Since I run the alpha of Thunderbird on my MacBook I had to make two minor alterations.

Bump the maxVersion in install.rdf to get it to install (Thanks Magnus).


Alter the binary called in pgarchives.js:

    process('/usr/bin/open', Array('/Applications/', '' + msgid), false);

A silly post I know but I had nothing publicly interesting to write about today... :-)

Figure out processes startup environment

Sometimes it is necessary to figure out what environment variables a process was started with. If you're on a *nix system having a procfs you can just take a look in /proc/<pid>/environ.

This is a sample from a server running a PostgreSQL instance:

roppert@piper ~ $ ps aux | grep postgres
postgres 23603  0.0  1.0  37996  3864 ?        Ss   Dec02   0:05 /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true
postgres 23606  0.0  1.0  37996  3896 ?        Ss   Dec02   0:00 postgres: writer process                               
postgres 23607  0.0  1.0  37996  3880 ?        Ss   Dec02   0:00 postgres: wal writer process                           
postgres 23608  0.0  1.0  37996  3940 ?        Ss   Dec02   0:00 postgres: autovacuum launcher process                  
postgres 23609  0.0  0.6   9316  2408 ?        Ss   Dec02   0:07 postgres: stats collector process                      
roppert  31814  0.0  0.1   4612   740 pts/4    R+   23:29   0:00 grep --colour=auto postgres
roppert@piper ~ $ sudo cat /proc/23603/environ 
TERM=screenSHELL=/bin/bashUSER=postgresPATH=/bin:/usr/bin_=/usr/lib/postgresql-8.3/bin/postgresPWD=/var/lib/postgresqlPGLOCALEDIR=/usr/share/postgresql-8.3/localePGSYSCONFDIR=/etc/postgresql-8.3HOME=/var/lib/postgresqlSHLVL=2LOGNAME=postgresPGDATA=/var/lib/postgresql/8.3/datarobertg@piper ~ $ 

I have no clue on how to do this on a Windows system. Anyone?

Playing around with full text search in PostgreSQL

I was finally able to play around with the full text search (former tsearch2 that's now a part of core in PostgreSQL 8.3).

The docs shows three ways of using the full text search:

  1. Ad hoc
  2. Indexed
  3. Pre-stored tsvector

I will use a small Python application to populate my database (modify as needed):

import urllib2
import re
import psycopg2
url = ''
raw = ''.join(urllib2.urlopen(url).readlines())
raw = raw.decode('iso-8859-1')
conn = psycopg2.connect("dbname=test")
curs = conn.cursor()
curs.execute("drop table if exists bible; create table bible(id serial primary  key, location text, quote text)")
for quote in re.findall(r'[0-9]{3}\:[0-9]{3} .*', raw):
    location, text = quote.split(' ',1)
    text = text.strip()
    curs.execute("insert into bible(location, quote) values(%s,%s)", (location, text))

With this test data loaded into our database we'll it's time to test a ad hoc full text search:

test=# select location, quote from bible where to_tsvector('swedish', quote) @@ to_tsquery('swedish', 'herre & gud & son');
 location |                              quote
 029:021  | Så säger HERREN Sebaot, Israels Gud, om Ahab, Kolajas son, och
 001:032  | Han skall bliva stor och kallas den Högstes Son, och Herren Gud
(2 rows)

Time: 587.860 ms

Let's create a GIN index on 'quote' and run our query again.

test$# create index quote_idx ON bible using gin(to_tsvector('swedish', quote));
test$# select location, quote from bible where to_tsvector('swedish', quote) @@ to_tsquery('swedish', 'herre & gud & son');
 location |                              quote
 029:021  | Så säger HERREN Sebaot, Israels Gud, om Ahab, Kolajas son, och
 001:032  | Han skall bliva stor och kallas den Högstes Son, och Herren Gud
(2 rows)

Time: 2.940 ms

There's a substantial difference between using index and not using index (of

To try the pre-stored tsvector we alter the table to add a column and populate
it with the tsvector.

test=# alter table bible add column quotesearchable_index tsvector;
Time: 11.422 ms
test=# update bible set quotesearchable_index = to_tsvector('swedish', quote);
UPDATE 31139
Time: 2367.304 ms
test=# create index quotesearchable_idx ON bible using gin(quotesearchable_index);
Time: 799.223 ms
test=# select location, quote from bible where quotesearchable_index  @@ to_tsquery('swedish', 'lamm & offra & sabbatsdag');
 location |                              quote
 028:009  | Men på sabbatsdagen skall du offra två årsgamla felfria lamm, så
(1 row)

Time: 0.829 ms
test=# select location, quote from bible where to_tsvector('swedish', quote) @@ to_tsquery('swedish', 'lamm & offra & sabbatsdag');
 location |                              quote             
 028:009  | Men på sabbatsdagen skall du offra två årsgamla felfria lamm, så
(1 row)

Time: 0.935 ms

As can see with the comparable runs there is pretty much no difference here. I would guess the difference starts to show with big datasets.

Oh, and if you go with the pre-stored solution don't forget to create a trigger on insert and update that updates the tsvector.

I also tested a forth approach by storing the tsvector in it's own table and then join the original table when searching. That approach also needs the triggers for insert and update but also for delete. I'm not sure if this approach has some significant to offer but it was still fun to test.


plpgsql and replacing pgmemcache with pgmemcached

Today I ran into a bit of a problem while replacing pgmemcache with pgmemcached (gotta love the naming on these two).
pgmemcache returns True or False depending on success or failure, for example:

society=# select memcache_replace('boguskey', 'nonsense data');
(1 row)

Time: 2.504 ms

With pgmemcached we get the following behavior:

society=# select pgmemcached_replace('boguskey', 'nonsense data');
ERROR:  memcached_result: NOT STORED

Since we're calling memcache from within triggers it is no good when the triggers starts to break on the account of exceptions being raised. So to deal with this we could, in the case of *_replace catch others (to catch all exceptions) or internal_error (to catch the specific exception thrown by pgmemcached) or change the trigger to use *_set function insted since it behaves the same in both cases.

Is it wise to use *_set in this case? Every block using exception handling will create a subtransaction hence it could have an inpact on performance, especially when used inside triggers.

I have yet to run some perfomance testing to see what impact *_set vs *_replace with exception handling will have on our database.

The lessons learned today was: BEGIN ... EXCEPTION and to use \set VERBOSITY 'verbose' to get the exact error code returned by a third party library (in our case XX000 which is internal_error). I also picked up others since I was unaware of it.

RSS 2.0