Saturday, October 08, 2005

PostgreSQL, Perl and DBI

I recently had need to write a short script to take care of renaming a column during a PostgreSQL 7.2 (ala Debian Woody) to 7.4 (ala Debian Sarge) upgrade. Here are some gotchas that I overcame.

The underlying need for this change is PostgreSQL's "helpful" autotruncation of overlength symbol names. In 7.2 the limit was about 31 characters, in 7.4 it's about 63. The database in question had a column with a 36 character name which was being helpfully autotruncated, both during schema creation and use. After upgrading an existing database to 7.4, the column names in use were no longer being translated, but the column carried through during the upgrade had had its name truncated upon creation and, therefore, now appeared to be a different column name. At this point, the application stopped working. The script's job was to make the change, but to be graceful about it; to only attempt the change if it had not already been made. i.e. to remain idempotent.
#! /usr/bin/perl

use strict;
use DBI;
Pretty obvious.
my $dbh = DBI->connect ( "dbi:Pg:dbname=databasname;host=hostname", "usename", "password", { AutoCommit => 0, RaiseError => 1 }) or die "failed to connect to db";
Fairly straightforward, but note the RaiseError which obviates the need to put "or die" after every subsequent statement. This is ideal for a "do one thing or exit with error" script. More complex activties may require Perl's less-than-entirely-elegant exception catching mechanism (eval {statements}).
my $sth = $dbh->column_info(undef, undef, "tablename", "oldcolumnname");
Neat! DBI includes some standard methods for reflecting over the schema. Note, however, that what this really does is to set up a query, which then needs executing.

if ($sth->fetchrow_hashref() != undef)
$dbh->do("ALTER TABLE...
Yes, you read that correctly; a commit after a DDL (Data Definition Language) statment. Has 7.4 made DDL transactional? I didn't chase this down, but merely note that, without it, it didn't work. Thinking about it, setting AutoCommit on at the outset would presumably have had the same effect. I'm still astonished at the consequences of this though: "DROP TABLE foo; ROLLBACK;". Wow.
Just housekeeping. It is unlikely that you'd ever have multiple matches, but I did have some hiccups without this in place. The DBI docs say that you shold never need to do this.