Sunday, December 20, 2009

Upgrading from Postgresql 8.2 to 8.3

Postgresql 8.3 is already practically ancient history, but only a month or two ago did I complete upgrading the Postgresql 8.2 cluster on my data processing server. The biggest hurdle was that 8.3's increased strictness about data types. No more can you compare a varchar to an integer without an explicit cast. I wound up having to deal with quite a few of these errors:

ERROR: operator does not exist: character varying = integer at character 33
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT ( $1 = $2 )

Perhaps it's for the best, as I did uncover a bug! In 8.2 I was unwittingly joining two tables by different datatypes, table_a.my_integer against table_b.my_varchar. Even though the two columns were supposed to have the same symantic meaning, the varchar column contained the occasional alpha character, for example "1A"! It's funny the bugs you uncover when dealing with an external data provider's data...I could write a whole blog entry on that one. ;)

Not related to this hurdle, but 8.3 has a bunch of nice features. My favorite is being able to set database parameters on a per-function bases. On my database processing server, most of the time a small value for work_mem is fine, but I have this one function that does a huge amount of database processing, so I crank up the work_mem for just that function, without over-allocating for all the other functions (wasting memory = bad).