One of the most irritating things about PostgreSQL is it’s inability to perform count() in a timely manner. It takes forever because it’s MVCC (Multi Version Concurrency) engine, which makes it so it has to iterate through all the rows to get an accurate count.

In the past, you could work around it by scheduling a VACUUM every hour and using this:

SELECT reltuples AS count FROM pg_class WHERE relname = ‘table name’ AND relkind = ‘r’;

But now there seems to be quite a bit of discussion about rewriting aggregates like count() in PostgreSQL. In 8.3 Beta, count() has been cleaned up a lot so it’s much faster and performs better under high load. There’s also been discussion about caching count()’s, which may prove difficult because of the Multi-Version nature of PostgreSQL.

I can move my thumbs!!!

The nerves finally reach those little depleted muscles in my hands (opponens pollicis I think) and I can move them a little bit. The bad news it they wasted so much that I can hardly use them.

I’ve been working on a small MySQL project on the side for a while now, and today I decided it was ready to go live, even though not all functionality I want is in place, but knowing myself, I’d probably just let it sit on the backburner for a month or two while I work on paying projects.

So here’s an interesting one involving views in MySQL. When I took it live, I noticed strange error messages and no content. After some digging around, I found one particular line saying the following:

Unable to connect for user ‘cameron’ @’localhost’

I thought, “Interesting, that database doesn’t belong to that user… Come to think of it, that username doesn’t even exist”.

Apparently when you create a view, any other user accessing it has to connect as the user who created it. In my case, I created the view on my workstation, so it tried to connect under the username, “cameron”, which didn’t exist on the actual server. Pretty damn retarded if you ask me.

For most projects, I use PostgreSQL. It’s great, it scales extremely well across multi-core multi-cpu machines, it’s loaded with features, and it handles everything you can throw at it with grace. I also try to maintain respect for MySQL, and the last thing I want to start is a flame war, but dammit, every time I try doing something with MySQL, I run across bugs, things that weren’t thought out, and very unelegant solutions.

Just for instance, today I was laying out some tables for a MySQL project I’m working on in my spare time, and I came across one of the most retarded things ever: you can’t create a field with “now()” as the default value, unless the field is an actual timestamp (ie., doesn’t work for date, time, etc). Now that wouldn’t be so bad if you could use current_date() for the default value, but apparently MySQL won’t let me do that either.

Here’s the result on MySQL 5.0:

mysql> CREATE TABLE test (d DATE NOT NULL DEFAULT CURRENT_DATE());

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CURRENT_DATE())’ at line 1

Update: Comments are disabled due to spamming.