PostgreSQL stuff

Just ran into the issue with "Pooler Error: Unknown startup parameter" when connecting to PostgreSQL 9.0 using pgbouncer. A quick search turned up the following discussion pointing out that adding ignore_startup_parameters = application_name resolves the issue.

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!

It's a joy when tools just work.

I needed to create a new repository from a subdir of another repository while preserving history. Since I'm pretty new to mercurial and was not sure there was a clean way of doing this.

After a quick search and a couple of tests I found out that convert was the thing to use. (It works with a lot of other SCM tools too.)

1. Init the new repo
2. Create a filemap
3. Run convert

The filemap is just a text file with files to include or exclude. In my case I need just to include the subdir I wanted. There was a small gottcha here since I once had renamed the subdir. I needed to include the previous name in the filemap to preserve the entire history.

                                                                         
$ hg convert --filemap filemap /path/to/src/repo /path/to/new/repo             

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.

A little off topic but I'm very pleased to see that the timetable for Django 1.2 seems to be holding since they just entered feature freeze with beta 1.

Among my favorite of features to come is, of course, support for multiple database backends. Other goodies are smart IF-tags in templates and template caching.

I really don't know if this is funny or sad:

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;

Got a little confused interpreting the stats entries from pgbouncer in my syslog. For a second didn't realize it was the stat period average shown (as described in the usage docs). Silly me. :)

In an article at High Scalability this article explaining HBase on a conceptual level was referenced. It's a very good starting point for understanding the basic concept of HBase (and BigTable) and it's no more than a five minute read.

Meet the Meeting Ticker.