The Bride of Progress Report on WordPress-pg 1.5

More PHP and SQL…

$id_result = $wpdb->get_row("SHOW TABLE STATUS LIKE '$wpdb->posts'");
$post_ID = $id_result->Auto_increment;

…then after 20 more lines…

$postquery ="INSERT INTO $wpdb->posts
(ID, …) VALUES ('$post_ID', …)";

This is a race condition. While being formally defined by FOLDOC as “anomalous behavior due to unexpected critical dependence on the relative timing of events”, in this case it can be more easily thought of as a race between two individuals trying to do the same thing. A pair of people racing to make it to a revolving door when the door will only fit one of them. A group of cars racing to make the ferry when only one more car will fit on the boat. That sort of idea…

This code replaced old code that was doing something similarly vulnerable to a race condition. Except that the old code also protected against incidents where the auto_increment counter is out of touch with reality somehow. (More weirdness with auto_increment is documented at Ian Barwick’s MySQL Gotchas page.) In both the old and new code, we’re determining what the new post id is going to be, then going off to do other things, then we’re inserting the post with that new post id. That is unless someone else who got that new post id the same way we did, did the other things, and then did the insert before we got our chance, in which case there is an error and the post creation fails (with an error that the average user is not going to understand since there is nothing looking for this sort of failure). There can be only one (successful post with a given id).

Frankly, I don’t actually know if there is a way to handle this safely in MySQL without either recognizing the error that comes back and trying again with a different ID that (hopefully) will work or just locking the table through the whole process. Both of which are far from elegant. Luckily, the frequent posting that would trigger this bug isn’t likely with your average blog and the commenting scheme uses different code which doesn’t need to obtain the comment id ahead of time.

In PostgreSQL, there are utility functions for dealing with the sequences used for auto-incrementing that let you get the next value safe from possible collisions or races. The downside to this approach is that if your transaction fails for some other reason the value you received will never be used by anyone. However since it is an internal id number that is never actually visible to the end users in most cases, it doesn’t really matter if there are gaps in the ids. There will be gaps in the ids if you delete posts anyway.

All we have to do for the PostgreSQL port is run this query instead:

SELECT next_val('${wpdb->posts}_id_seq')

With this we wind up both safe from the race condition for obtaining the next post id and with a very elegant looking solution to the potential problem. I’m sure those guys in Sweden will get around to implementing a feature akin to this soon. Maybe it’s there and I just haven’t heard of it yet.

Some editorializing follows, please cover your eyes if discussion about censorship by corporations bothers you…

Since this writing could be taken by some as “disparaging” the MySQL product, I’m apparently not authorized to use any MySQL AB’s trademarks in this blog entry, including the trade name “MySQL”. Don’t believe me? Read their rather draconian trademark policy for yourself. Good thing this country still has fair use laws, so I don’t need authorization from the manufacturer to write critical evaluations of things.