PostgreSQL

Some people don't seem to get enumeration types. They think of them as C programmers tend to - as symbolic names for integer values.

But quiche eaters like me have quite a different point of view. Languages like Ada have had first class enumeration types for a long time (Ada is nearly 30 years old now). PostgreSQL's enum types are more of this kind, an ordered set of labels. Some people naïvely expect that underneath they will be stored as their ordinal position in the label set. In fact, for a technical reason, they are not. Rather, they are stored as globally (within the database) unique Oids. This is a bit counter-intuitive to some people, but really, it's just an implementation detail.

The biggest thing that bugs people about PostgreSQL's enums is that you can't extend them, i.e. you can't add more labels to the list. There is a workaround involving creating a new type, but it involves rewriting the tables that use them, which is unpleasant. Recently I have given some thought to that. I came up with scheme for a new enum type that would have been extensible. But as often happens, Tom Lane came up with a better idea. I've been working on fleshing that out, especially testing the possible performance impact, and I hope we can have something in 9.1. Being able to add labels to an enum set without table rewriting would make them much more usable.

D.Richard Hipp is the creator of SQLite. I met him at the speakers' dinner at SELinuxfest, attended his session on Saturday, and had a couple of conversations with him as well. He has a very friendly attitude to Postgres. He told me that whenever people ask him why he doesn't put more server class features into his product, he tells them that creating such a product has already been done, and it's called Postgres. In fact, he originally worked from Postgres documentation when he was creating SQLite.

Another thing he told me is that the large test suite he uses to test a number of databases besides his own crashes or gives incorrect results on every other database but one: Postgres.

SQLite is what I advise people to use when they want a simple embedded database. It also works well in my experience for small web sites. And its 'zero admin' nature makes it a good initial prototyping tool, too. I don't see it competing in the same space as Postgres, which has a very much richer set of features, scales better, and can handle much larger volumes of data. So to me we are not really competitors - we are more complementary than competing. I recently set up a very low volume web site for a fami;y member's business. SQLite worked well with the web framework software I was using and I was quite happy to use it.

Richard's talk was largely about the "new generation" databases. He doesn't like the term "NoSQL" much. He proposes instead that we call them "Postmodern Databases", in part because they have no notion of objective truth

Baron Schwartz of Percona had a somewhat different perspective, but many of his conclusions seemed to tend in the same direction. People should not lightly abandon the single server model of processing, he advised, and I think he's right.

I shuddered rather at Baron's description of MySQL's replication facilities, especially the lack of conflict detection and resolution. It reminded me a bit of David Sedaris' attitude to European style Christmases: "It's nothing I'd want for myself."

At the beginning of his session, he asked me if my presence indicated I was about to switch camps. I told him no, I was spying out the opposition Of course, we are all friendly, and not at daggers drawn.

I'm just leaving for Spartanburg, South Carolina to talk about new features in Postgres at SE Linuxfest.

One of the nice things about having to prepare talks is that it gives you a good reason to play with things that you have avoided playing with before (and finding new annoyances ).

So I've been getting familiar with all sorts of interesting things like Hot Standby and pg_upgrade. I'm more excited than ever about what we'll be releasing.

I've been playing at last with the new Streaming Replication and Hot Standby stuff. While it's possible to run these without also archiving log files, I gathered from Heikki's talk at pgCon that it's not necessarily a good idea. So if you do use archive logging, how do you make sure the archive doesn't just keep growing?

With older style PITR warm standby setups, pg_standby used to take care of that for us, but we can't use that with Hot Standby. Instead, there is a setting in recovery.conf that lets us call a command to do the cleanup ourselves. Slightly annoyingly, there isn't an example in the docs. Here is what I came up with:

#!/usr/bin/perl

use strict;
use File::Basename;
my ($dir, $num) = @ARGV;

my $log;
open($log,">>/home/andrew/restart_log") || die $!;
my $date = localtime();
print $log "$date: restart_point.pl\n";

foreach my $file (glob("$dir/*"))
{
        next unless -f $file;
        my $name = basename($file);
        next unless $name =~ /^[0-9A-Z]{24}$/;
        if ( $name lt $num )
        {
                print $log "deleting $file\n";
                unlink $file;
        }
}

close $log;

and the corresponding entry in the recovery.conf is:

restartpoint_command = '/home/andrew/restart_point.pl /home/andrew/pg-walspool %r'

It seems to do the right thing.