Sho Fukamachi Online

Oh, boy. Nothing exposes one’s shitty programming habits like using a new, stricter databse like PostgreSQL. All over the place I’m discovering, and fixing, code in which I’d demonstrated a lax attitude to data integrity – from trying to insert invalid data into columns I’d previously specified should not be null (datetimes, mostly) to declaring booleans with 1 or 0 instead of true and false.

It’s annoying, sure, but it’s also quite satisfying. A database that won’t take any shit is great for your programming discipline and must lead to better, more reliable code. I really can’t believe how much MySQL allows you to get away with, and I’m very glad my eyes were opened to the problems with the data I was storing before I had to manually repair million-line tables in production.

I have discovered a few annoyances with PostgreSQL, though – just to be fair. Its sequence system is pretty silly – after importing data you have to then go and explicitly set new sequence numbers for any autoincrement fields (PgSQL calls them “serials”). A useful facility but I think the default should just be to increment the sequence on import. I have of course written a script to automate this but still.

Another complaint is regarding the security, which if anything seems *too* strict. When you use Schemas to share logical databases between multiple users, any user but the owner of the “foreign” schema must have privileges granted explicitly on not only every table they plan to use in that schema, but on the aforementioned sequence tables too! I can understand the first, kind of, although there should be an option to grant privileges at the schema level, but the second is just silly – if you have rights to add new rows to a table, it is implied you should also have rights to increment the sequence. A needless bit of complexity.

That said, I’m overall delighted with the migration and everything was up and running fine. It’s not now, since I decided to completely excise my nasty multi-database hacks and simplify the data structures, removing all potential conflicts and separating tables into their logical homes. I’m about half way through doing that. And again, I’m really happy that I’m doing this now – what may take a day or two with development-stage databases might take weeks to do with live production servers – not to mention all the code that would have been built on top of the original suboptimal data structure. I’d actually been just about to write a whole lot more of that – something I’d actually been putting off because I knew what a mess the databases were, and was reluctant to dig myself any deeper a hole – but now I’m really looking forward to flying through what should be a much simpler, more intuitive job.

Tags: database, postgresql

This entry was posted on Friday, February 8th, 2008 at 12:47 pm and is filed under development, programming. You can follow any responses to this entry through the feed. You can leave a response, or trackback from your own site.