Misfeatures #1

From http://dev.mysql.com/doc/refman/5.0/en/extensions-to-ansi.html:

MySQL Server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL Server doesn’t support the standard SQL || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it’s easy to convert use of the || operator to MySQL Server.

Which brain-dead moron came up with this gem of a misfeature?   What’s C got to do with anything? (And I like C).  What they meant to say was “Because of this nice syntax, seasoned users of other systems will waste their time trawling through the docs to find out why on earth a most basic SQL standard doesn’t work as it should.” I shall limit myself to saying that this example epitomises the MySQL philosophy, and is frankly the kind of mindset more commonly associated with Redmond.

PostgreSQL 8.3 – on its way

PostgreSQL 8.3 is now in Beta2 and judging by the change log it’s going to be a good’n. There’s one new feature in particular which comes along at just the right time for me & phuser.com, plus some other very welcome enhancements.

 

More on those headlines features in a moment. The word is that v8.3 will be a performance release. Looking at the official change log, this is only evident by a list of fairly gritty technical changes – asynchronous commits, heap-only tuples, distributed checkpoints, lazy XID assignment, on-disk data size savings – you get the picture. It sounds cool (never a bad thing!), but the important thing is that all the hard work by developer community appears have really paid off. For example Jeff Davis has demonstrated very significant performance improvements over v8.2.5. (On an aside, recent SPEC tests showed that v8.2 was no slouch either, falling 15% short of Oracle 10g on a hardware costing almost 15% less ($65,500 Sun vs $74,000 HP hardware; $0 vs $110,000 software, respectively). Not content with this, the Man in the Hat says that the PostgreSQL Core Team plan to be faster than Oracle, possibly starting with v8.3.

 

So what of the new features in this release?

 

The Full-Text Search component is now considered mature enough and functional enough to finally make the move from contrib/ to core. This is nice – one less thing to worry about when configuring new platforms, and finally removes MySQL’s long-standing advantage of having (albeit inferior) FTS out of the box.

 

But the one v8.3 feature I’m really looking forward to playing with (and hopefully using in earnest!) is the new set of XML functions. Perhaps I’ll write about these in more detail when I have had a chance to try them out, but in short they should make it much easier to generate XML representations of data from SQL. One subset of functions in particular maps tables to XML, apparently making it very easy to dump tables and queries as XML. This is just what I need to implement Phuser’s upcoming export feature, allowing our users to get their Phuse data out in a consistent, portable format. It should be very cool!

 

Of course, we’re still in Beta. There will be the traditional series of additional betas and RCs before we get the official release. But now is the time to start reviewing the change log and seeing if you need to make any modifications to your apps in order to make the switch. Download a beta and do some testing. If you find anything fishy, report it to the Postgres devs – they need to know. Having said that, in three and a half years of intensive Phuser development, I have never once encountered a bug, flaw or crash in PostgreSQL, even in betas. It really is as rock-solid as they say, and a real tribute to the chaps who make it happen and their rigorous release engineering processes.

 

Sanity-saving tip for database-upgrade-migration hell

As a web app in beta, phuser.com is still evolving rapidly. This means that I am often faced with the trauma of migrating a dataset from one version to another, sometimes with substantial changes in the database schema. I’ve developed various tricks for this – some are ugly but effective and involve things like sed, whilst others score more highly on the cool-o-meter.  Being the nice fellow that I am I will spare you the former and show you one of the latter.

Wot no support contract?

It’s summer 2004, and I am considering which software platform we should use to host our new project, phuser.com. ‘ System Provisioning’ (a.k.a. ‘making it real’) of course covers many aspects, none of which I’m going to go into today.

One outcome of this was a choice of database. Social networking sites, even antidotes thereof, can’t get far without a database to power them. And so, to gloss over the whys and wherefores, it came to pass that I chose what claimed to be ‘The Most Advanced Open Source Database’. Happily the choice of PostgreSQL was founded on more than a strap-line, and in the intervening couple of years I have had every reason to be very satisfied with my original decision.

I look forward to writing more about that journey, but that too will have to wait. Today I’d like to relay one particular experience.