on Code

PostgreSQL 8.3 Upgrade Notes

We recently upgraded our production PostgreSQL 8.2.x database to 8.3. One of the nice things about PostgreSQL is you can toss it on your laptop, restore your 8.2 database, and beat it senseless to figure out if anything breaks. Which, naturally, I did.

Two heads-up things in case you are upgrading to 8.3:

  • I had one PHP call that was working in 8.2 croak on me. Basically it was doing a table join, and the join field in one table was an integer and the join field in the other table was character (varchar). 8.2 didn’t have a problem with it, but 8.3 spit back an error. A simple cast to change one type to the other fixed it. For this type of operation, it looks like 8.3 is a little less tolerant of type mismatches than 8.2 was. Arguably, the SQL call was sloppy and shouldn’t have run in either version. Still, be alert.
  • MapServer (5.0.2) totally croaked. After hunting around, I found this had to do with the response PostGIS was giving to a version request MapServer was making. To fix it, you have to change the DATA statement for your PostGIS layers in your map file from:
    DATA “the_geom from postgis_layer”
    to:
    DATA “the_geom from postgis_layer USING UNIQUE gid”
    That fixed the problem. I saw one post online that also included the projection in the data line (DATA “the_geom from postgis_layer USING UNIQUE gid USING SRID=2264”), but I didn’t find that necessary. Note that I’m using the MapServer binaries from MS4W; I don’t know if this problem occurs on other platforms. A quick fix, but figuring it out was a bit of a pain.
That’s all I ran in to. Other than that, the upgrade was smooth sailing.

Why go through all that effort to upgrade you say? 8.3 is a major upgrade, and offers all kinds of new features and improvements.

  • Speed. They advertised up to a 30% performance increase over 8.2, and I can tell you they weren’t kidding. Statistics collection is now enabled by default, and there are new features to improve the performance of large and frequently updated tables and databases.
  • Supports full text searches out of the box. Take that MySQL.
  • XML data type, including a lot of XML parsing and helper functions, checks for well-formed XML, and more.
  • CSV log output, making for easier auditing and performance analysis.
  • Order by nulls first/last. Some databases put nulls on top when doing a sort, others on the bottom. Now you can specify where they go during a sort. This seems small, but I’ve run into this annoyance more than once.
  • Stack Builer lets you easily install PostgreSQL extensions, like PostGIS. It makes it a lot easier to install new features and keep up with current releases. I used it to install PostGIS 1.3.2 initially, and then used it to upgrade to 1.3.3 after it was available, and both went off without a hitch.
  • Tons more.
It’s a relatively painless upgrade with lots of new goodies, so I’d recommend getting your testing in and doing it sooner rather than later. I’ve been running 8.3 in production for a few weeks, and it has been smooth sailing.