One of the many pitfalls of web-related work is that sometimes you just have to deal with MySQL, as it's the only database option some clients have. Particularly irksome is the penchant many web hosters have for older MySQL versions, which is a major PITA when trying to deal with even slightly complex data.
One of the many things lacking in MySQL is a true boolean datatype.
It does possess a pseudo-type
BOOL, which is silently
TINYINT(1), and from 4.1 onwards,
to quote the manual,
FALSE evaluate to 1 and 0, respectively".
Just back from Japan, where I've been busy on various private projects (using PostgreSQL wherever a database is required, of course), and haven't had much time to pursue the mailing lists, so hadn't really noticed that the 10th anniversary celebrations were underway. Congratulations all round!
Side note: Japan is a remarkably PostgreSQL-friendly country; an unscientific survey of various major bookstores in Tokyo showed that, for the open source databases, PostgreSQL books were in the majority. I'd say compared to a certain dolphin-orientated RDBMS there's about a 5:3 ratio in favour of PostgreSQL.
No, the title isn't a mistake: it occurred to me that it's almost exactly 5 years since I first used PostgreSQL. That was back in the waning days of the dot-com boom, when I was entrusted with the development of a CRM system for my former employer, Germany's first e-learning platform. The challenge was to unite an Oracle-based system with a MySQL-based system, and at least-possible cost (because the magic money pots were slowly drying up). Oh yes, the new system also had to take care of a fairly complex user authentication and session handling system for the new website.
Fortunately I had cut my SQL teeth on Oracle (version 7), and while this wasn't an option, neither was MySQL, which at the time completely lacked basic things such as foreign keys and transaction support (I think this was the period when MySQL was feuding with its transactional engine supplier about domain names). As a DB backend for an application which was to handle financial transactions, this was a total no-go (and this was long before I'd got my teeth into the infamous gotchas list). I had vaguely heard of PostgreSQL though, and decided to try it out, and while it had a few niggles (altering table definitions was a pain), I liked it.
Transactions are only useful when you actually use them, otherwise it's too late when you realize you've forgotten the
WHERE clause and would really, really like to be able to roll back the changes - as Robert Treat found out recently.
If you're lucky conventional backups or
PITR will help you avoid total disaster, but apart from the delay in extracting the necessary data from the backups and reading it in, Murphy's Law dicates that exactly the data you need won't be in the backup. Far better is to remember to start a transaction in the first place, putting recovery a blood-pressure friendly
psql has two surprisingly little-used functions which help ensure you're inside a transaction when you need to be.
phpPgAdmin in action phpPgAdmin is PostgreSQL's answer to the ever-popular phpMyAdmin. It's a PHP-based web-frontend for administering PostgreSQL databases - a serverside equivalent of pgAdmin III if you like.
Installation is very simple. The basic requirements are a PHP-enabled webserver, typically Apache, with PHP 4.1 or later, and access to a PostgreSQL server. In the following description I'm assuming a UNIXoid environment such as Linux or OS/X.
According to this post by Tim O'Reilly of O'Reilly books fame, the market for PostgreSQL books is "up 84% over a year ago", at least in the US market. This is based on data for the entire market, not just O'Reilly publications (see an earlier post for more information on the methodology; sadly O'Reilly analyzed the data with MySQL). With more and more books such as Beginning PHP and PostgreSQL 8 appearing, this is not surprising, and a further sign that PostgreSQL is gaining much-deserved mind-share.
As a followup to this interview with Robert Treat and Jason Gilmore (authors of the recently published book Beginning PHP and PostgreSQL 8), TechTarget has posted some comments from Oracle DBAs reacting to comparisions made in that interview between PostgreSQL and Oracle.
Most comments are quite favourable towards PostgreSQL, though evidently there's still a knowledge gap that needs to be bridged:
Allen was unimpressed by the fact that in PostgreSQL, stored procedure parameters are not typed. "Everything is passed as strings, even integer arrays," he said.
And one comment in the interview's feedback section is from someone who obviously doesn't know what he / she is talking about.
Slashdot has also picked up the story.
Jason Gilmore and Robert Treat kindly sent me a copy of their latest book "Beginning PHP and PostgreSQL 8: From Novice to Professional" (pictured on the right, though if you're reading this via a feed the image won't be visible). Unfortunately I haven't had time to go through it in depth yet; a detailed review will follow ASAP. (I'm in the middle of a PHP-based project, and as a non-PHP-specialist it looks like I'll be able to give it a proper "road test").
More information is available on the Apress website.
Unfortunately the conversion to the blackhole storage engine didn't go as
smoothly as planned, due to unexpected date-time dilation effects along the
event horizon. Details are sketchy ("
Query OK, ∞ rows affected, 1 warning (0.00 sec)"), but it appears the insertion
of a date with the value
0000-00-00 into the black hole
would have introduced an irreconcilable paradox into the Einsteinian
space-time continuum and caused life as we know it to end.
Fortunately I was able to import the emergency reserve system
from the backup punchcards using PostgreSQL's
The post-mortem disaster review has lead me to the conclusion that I should have obtained this particular MySQL certification first.