One of the other things I've been looking it as ways to implement background processing in PHP. In my recent talk on sending mail from php I mention that you want to avoid sending mail directly from a web page. A couple of people have asked me how to implement that, and one of the suggestions I have is to queue your mail in a database table and have some other process act on that table.

The idea is that you have a PHP CLI script that, in an infinite loop, sleeps for a short time then polls the database to see if it needs to do some work. While that will work just fine, wouldn't it be great if the database woke you up only when you needed to do some work?

I've been working on a patch originally contributed by David Begley that adds support for LISTEN/NOTIFY processing to the Postgres PDO driver. With the patch you can write a CLI script that looks a bit like this:

<?php
   $db = new PDO('pgsql:');
   $db->exec('LISTEN work');
   dispatch_work();
   while (true) {
      if (is_array($db->pgsqlGetNotify(PDO::FETCH_NUM, 360))) {
          dispatch_work();
      }
   }
?>

This script will effectively sleep for 360 seconds, or until someone else issues a 'NOTIFY work' query against the database, like this:

<?php
   $db->beginTransaction();
   $q = $db->prepare('insert into work(...) values (...)');
   $q->execute($params);
   $db->exec('NOTIFY work');
   $db->commit();
?>

When the transaction commits, the CLI script will wake up and return an array containing 'work' and a process id; the script will then call dispatch_work() which is some function that queries the database to find out exactly what it needs to do, and then does it.

This technique allows you to save CPU resources on the database server by avoiding repeated queries against the server. The classic polling overhead trade-off is to increase the time interval between polls at the cost of increased latency. The LISTEN/NOTIFY approach is vastly superior; you do zero work until the database wakes you up to do it--and it wakes you up almost immediately after the NOTIFY statement is committed. The transactional tie-in is nice; if something causes your insert to be rolled back, your NOTIFY will roll-back too.

Once PHP 5.2.0 is out the door (it's too late to sneak it into the release candidate), you can expect to see a PECL release of PDO::PGSQL with this feature.