Testing PostgreSQL is fun

I spent part of the weekend debugging the failing PostgreSQL tests we currently have on Ghost. The failures were intermittent and hard to reproduce locally, but eventually I got behind what was causing them.

Whenever you request data from a database without an orderBy clause it can be returned in any order. The SQL standard does not define a default order even if you have indices set up. Interestingly enough MySQL and SQLite seem to consistently return data in the order it was inserted. PostgreSQL however sometimes returns results out of the order that some tests expected the data to be in.

Selection Order

One of the tests that regularly failed was depending on the insertMorePostsTags method that inserts additional fixtures. This method first fetches all posts and tags and then adds relations between posts and a specific injection tag.

for (i = 0; i < max; i += 1) {
     DataGenerator.forKnex.createPostsTags(posts[i], injectionTagId)

Take a minute and think about the assumptions that the above lines make.

Got it? The loop goes through all posts a attaches the injection tag by its id. However, since there’s no defined order in SELECT statements there’s no guarantee that the posts always appear in same order. Subsequently we can’t make sure that the tag always gets associated to the same post.

This wouldn’t be an issue if the test was simply checking for the number of posts with the injection tag. Instead it checks for the number of posts that have the status published and the injection tag. While we attach the tag to max (where max = 50) posts we can’t guarantee that we always end up with the same number of published posts with that tag.

Insertion Order

The posts are inserted by the insertMorePosts method in the same file.

for (j = 0; j < max; j += 1) {
    status = j % 2 ? 'draft' : 'published';
      DataGenerator.forKnex.createGenericPost(k++, status, lang)


What’s interesting about this is that we don’t select data from the database. We build up a set of posts and use a multi-insert (handled by knex.insert) to insert all posts into the database. The loop suggests that we will end up with max posts that alternatingly have the status draft and published. The assumption here is that posts are inserted in the exact order we generate them in the loop. Again, the SQL standard does not define in which order records should be inserted into the database when using a multi-insert. We will definitely end up with max posts and half of them will have the status draft and the other half the status published, but there’s no guarantee on an alternating order (if we were to sort them by id).

Selection Order II

A similar, and yet slightly different issue can be found in one of the integration tests. The test checks whether it’s possible to attach an old tag and two new tags to an existing post.

// the tag API expects tags to be provided like {id: 1, name: 'draft'}
var tagData = seededTagNames.map(function (tagName, i) { return {id: i + 1, name: tagName}; });

// Add the tag that exists in the database
tagData.push({id: 2, name: 'tag2'});

// Add the tags that doesn’t exist in the database
tagData.push({id: 3, name: 'tag3'});
tagData.push({id: 4, name: 'tag4'});

return postModel.set('tags', tagData).save();

Above code adds the tags and saves them.

return PostModel.read(
  { id: postModel.id, status: 'all'}, 
  { withRelated: ['tags']}

We then reload the post with the related tags and check whether they were all properly attached to the post. Since we only want tags to exist once in the tag table we also want to check that the already existing tag was reused.

var tagModels = reloadedPost.related('tags').models;

// make sure it hasn't just added a new tag with the same name

In this case the issue moved up a layer since we’re here looking at Tag model objects. The line assumes that the related tags are returned in a specific order which we however did not define when fetching them from the database.

Lessons learned

There are some simple solutions for the issues that I mentioned and a few things to learn from that for testing.

To fix the insertion problems we can use when.sequence to insert items one after another. When fetching data and assuming that it comes out in a certain order we should explicitly fetch it in that order by using ORDER BY. However, it’s better to avoid said assumptions and rewrite the tests so that they don't depend on the order of the data. What the test in ’Selection Order II‘ was testing can easily be rewritten by checking that the number of tags hasn't increased.

To debug such issues I found it important to get a database dump from exactly the line before the test fails. It might seem a bit hacky but the best way I could find was to simply process.exit(1) before the failing test.

Solms, Germany