PostgreSQL stuff

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!

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.

select
    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
where
    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.

From the lecture Monitoring PostgreSQL Buffer Cache Internals by Greg Smith I created a simple bgwriter-print.py 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.

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

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.

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... :)

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

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.)

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. :)

begin;
    -- 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
    CREATE OR       REPLACE FUNCTION strip_tags(TEXT) RETURNS TEXT AS $$
        SELECT regexp_replace(
            regexp_replace($1,
               E'<[^>]*?(\s* href \s* = \s* ([\'"]) ([^>]*?) ([\'"]) ) [^>]*?>',
               E' (\\3) ',
                'gx'),
            E'(< [^>]*? >)',
            E'',
             'gx')
    $$ LANGUAGE SQL;

Spread the word, PostgreSQL 8.4 is out!

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:

select
    coalesce(mytable.day, foo.day) as day,
    coalesce(mytable.sum, foo.sum) as sum
from
    (select day, sum(metric)
     from mytable group by day) as mytable
right outer join
    (select date '2009-02-01' + foo.day      as day, 0 as sum
     from generate_series(0,6) as foo(day)) as foo
on mytable.day=foo.day
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?

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';
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=# create database template1 template=template0;
CREATE DATABASE
postgres=# update pg_database set datistemplate = true where datname='template1';
UPDATE 1
postgres=#

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.

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?

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

create database dbname with owner=adminuser;

\connect dbname
begin;
    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;
end;

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

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

-- 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:

  a 
-----
   0
   1
   1
   2
   3
   5
   8
  13
  21
  34
  55
  89
 144
(13 rows)

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)
        self.name = name
    def run(self):
        cursor = self.connection.cursor()
        cursor.execute('vacuum full %s' % (self.name))
            
            
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))
            time.sleep(1)



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

# 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.

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...)

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).

        <em:maxVersion>3.0.*</em:maxVersion>
      </Description>

Alter the binary called in pgarchives.js:

    process('/usr/bin/open', Array('/Applications/Firefox.app/', 'http://archives.postgresql.org/message-id/' + msgid), false);
}

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

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?

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 = 'http://www.mirrorservice.org/sites/ftp.ibiblio.org/pub/docs/books/gutenberg/2/1/0/2100/2100-8.txt'
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)")
conn.commit()
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))
conn.commit()

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