michael schurter

Warning: IANADBA
I was just reading the release notes for PostgreSQL 8.3 beta1, and there’s quite a lot of exciting improvements coming:

• Full text search is now a built-in feature
• Support for the SQL/XML standard, including a new xml builtin data type (For the enterprisy users)
• enum data types (I’m not a big fan of enums in a database, but lots of people like them.)
• UUID data type, similar to that defined by RFC 4122. (Very useful feature in some clustering situations. Some people even argue GUIDs/UUIDs should replace integer surrogate keys most of the time.)
• ORDER BY … NULLS FIRST/LAST (A minor little feature, but I remember wishing for it once upon a time.)
• And some excellent performance improvements, especially if you’re data doesn’t have to be flushed to disk to finish a transaction (potentially dangerous, but acceptable for many use cases).

While I haven’t had the pleasure of using PostgreSQL for a few months, I prefer it over MySQL and recommend it to anyone looking for an open source database with advanced features and excellent reliability.

It appears Debian GNU/Linux 4.0, codenamed Etch, (quite a mouthful) has been released as the latest stable version of Debian.

Go download it using BitTorrent to be cool.

Tip #1:  You do not have to download all of the CDs, just get one and Debian’s installer will download the rest.)

I’m a happy and loyal Debian user and strongly encourage any Linux power users to try out Etch. Ubuntu is still my recommendation for users who want things to “Just Work.” However, I highly recommend Debian for any kind of server (file, print, application, database, web, e-mail).

Tip #2: Check out Debian Multimedia for all of your proprietary codec needs!

Mini-Review

I’ve been using Etch (and Sid) for a while now, so its tough to do a real review from a “normal” end user’s perspective.

Some highlights:

• Developer’s should be happy as PHP, Python, Mono, MySQL, and PostgreSQL are all fairly up-to-date and extremely easy to install.
• Gnome is stable and speedy.
• The graphical installer brings Debian out of the 1990s and offers tons of features.
• As usual, Debian’s package repository is huge. Forget vendor-supplied RPMs or custom installers, if its not in Debian’s repository (or Debian Multimedia), you probably don’t need it.

Some downsides:

• Relatively ancient version of the standard C library (2.3.6) means poor compatibility with 3rd party binaries (which Debian has always hated anyway).
• Gnome is 2 stable versions behind in Debian (2.14 vs. 2.18). This is very frustrating as Gnome constantly produces nice incremental improvements.
• Debian just isn’t as easy to use by default as Ubuntu. Debian needs to install and integrate some of the more cutting edge desktop apps like Beagle, Tomboy, Deskbar, and Ubuntu’s nicer package management interfaces.
• Secure APT is a great feature, but the error messages it can produce are going to scare the pants off of new users. More effort needs to be made to integrate all of the various places user’s encounter public key crypto. Right now its very schizophrenic and confusing: GPG errors are too common, and command line magic that even confuses me is necessary to add new repositories.
• Not related to the release, Debian’s support system is a confusing jumble of web interfaces, mailing lists, IRC channels, and an excellent wiki (that I rarely remember to use). Hopefully this is something a new Debian DPL will address.

The harddrive died in the server that dumps our firewall’s syslog output into a nice PostgreSQL database for easy reporting. PostgreSQL didn’t do anything evil in order to kill the drive, but because it syncs data to the harddrive for safety, it kept my ancient server’s harddrive spinning 24/7.

I should mention this was my fault. I really should have turned fsync off to let the server cache the trickle of ICMP packets that keep our firewall awake all night. Instead the old Quantum Fireball Plus LM harddrive wrote every byte right as it came in.

Luckily the harddrive still worked enough that I could attach it to another computer via a IDE-USB adapter and copy off any data I wanted to keep.

Below are the SMART statistics for the drive as collected by smartmontools and munin. As I was able to retrieve data off the disk, I would say SMART did its job even if it didn’t give me much warning.

I’m trying to learn TurboGears, but I got stumped yesterday trying to figure out how to connect to PostgreSQL via SSL.

Turns out this works in SQLObject (TurboGear’s default ORM):

sqlobject.dburi="postgres://username:pass@servername/mydb sslmode=require"

Turns out the syntax I was trying to use works with SQLAlchemy (which it appears most cool kids use):

postgres://username:pass@servername/mydb?sslmode=require

Notice the only difference is the question mark between the database and extra parameters. Kind of makes me want to cry…

To all my friends who have listened to me extol the virtues of Python, let me also add that learning a new framework sucks. Especially when that framework is a moving target.

Just noticed PostgreSQL 8.2 was released yesterday. I chose PostgreSQL 8.x for a large project when MySQL 5 either wasn’t out or was too new for my comfort.

I learned database application programming on MySQL, but now I choose PostgreSQL whenever I have the option. PostrgeSQL is just packed with features that MySQL either doesn’t have or just recently implemented so you need a cutting edge version.

It seems to me that PostgreSQL has a much better user community as well. I know there are many excellent DBA’s using MySQL, but the MySQL community seems dominated by PHP hackers who found MySQL pre-installed on their $5/mo. shared hosting account.

If you’ve never tried PostgreSQL, I highly recommend it, especially if you’ve ever implemented autonumber tables in MySQL to get around flaky autonumber generation. PostgreSQL uses sequences which are basically single field/row tables for maintaining autonumbers. There are a number of sequence related functions in PostgreSQL that makes work with autonumbers not only easy, but also predictable.

Oh and pgAdmin III beats MySQL’s awkward and buggy GUI offerings any day.

Now that SchoolWorks has officially been released, I guess I can begin posting about my attempts to get it running on Linux.

First of all SchoolWorks is school administration software (or SIS) built using:

• ASP.NET 2.0 (in Windows using Microsoft Visual Studio 2005)
• PostgreSQL (thanks to Npgsql)
• Crystal Reports (great for report designers, abhorrent for developers)

We also used the following support tools:

• Bugzilla
• Subversion and TortoiseSVN
• PgAdmin III

While Visual Studio and ASP.NET can be frustrating at times, the only part of SchoolWorks that I avoid like the plague is working with Crystal Reports.

Recently I’ve been trying to build SchoolWorks using Mono on Linux. I’ve successfully built Mono (libgdiplus + mono + mcs + xsp) on my personal Debian Etch, but I haven’t gotten much of SchoolWorks to compile yet.

The first missing feature was Decimal.TryParse which we use a lot in SchoolWorks. I submitted (my very first!) patch to the mono-devel mailing list in hopes of getting support for Decimal.TryParse added.

I’ll try to keep posting my progress as it should be interesting to SchoolWorks users as well as .NET/Mono developers.