So, another day, another new version of the PledgeBank source code live on http://www.pledgebank.org/. Actually, the changes are mostly underneath the surface, so you shouldn’t notice any specific differences, unless we’ve broken something, in which case whinge to team@pledgebank.com, as usual. That said, the new posters and SMS signup to pledges are now live, so you should now go out into the world and do Good Virtuous Stuff with them.

This is supposed to be the developers’ ‘blog, so a couple of technical things which have annoyed me today. (presumably you all read my actual web log and therefore expect me to write about things that annoy me):

• You can’t use a variable quantity in a limit clause in a subselect in PostgreSQL. “A what? In a what? What?“, I hear you cry. Well, this did come up in real life. When I was upgrading the PledgeBank code, there were various changes to the database schema which had to be made first. One of them was to change the way that the success of a pledge (i.e., what happens when it reaches its target) is recorded. Previously we had two boolean columns, like this:

  create table pledges (  -- obviously SQL tables should have singular names, but
                          -- in this case nobody asked me...
      -- ...
      success boolean
          not null
          default false,  -- indicates that the pledge has succeeded
      completionnotified boolean
          not null
          default false,  -- indicates that the creator and signers have been
                          -- told that the pledge succeeded
      -- ...
  );

  Now, this is messy and not enough to describe how the site actually works. Specifically, there are some types of messages which should be sent to creators and signers, some which should be sent only to signers who signed before the pledge succeeded (in between success and the deadline, you can still sign the pledge), some which should only be sent to non-SMS recipients, etc. etc. So instead we now have a table of messages with flags indicating where they should go to and so forth. A side-effect of this is that the above structure is replaced with this:

  create table pledges (
      -- ...
      whensucceeded timestamp,    -- indicates when pledge succeeded
      -- ...
  );

  Now, there are Real Pledges on the live site, so unlike the development site we can’t just drop the database in an update; instead, we have to port all the data over to the new data model. So what you’d like to write is, obviously,

  begin work;
  alter table pledges add column whensucceeded timestamp;
  update pledges
  set whensucceeded = (
      select signtime
      from signers
      where pledge_id = pledges.id
      order by signtime
      limit 1 offset pledges.target
  )
  where success;
  alter table pledges drop column success;
  commit work;
      -- I love using a proper database!

  Sadly, you can’t. The two arguments in the limit statement in the subselect have to be constant. (No, I don’t know if/where this is documented. I, uh, asked on IRC.) This sucks. In the end I just set whensucceeded to the current time for currently-successful pledges; it’s not right, but it’ll do.

• Python (2.3, on FreeBSD) either sets O_NONBLOCK on sockets by default, or fails to clear it when creating a socket. Result: program crashes with EAGAIN down in the FastCGI library every so often. Outstanding!

Poll: Should we turn on comments on this ‘blog? (Does anyone read it, anyway?) Mail me at chris@mysociety.org with your answers….