query does not work in PostgreSQL

I'm using mysql so I cannot address the PostgreSQL aspect of this report, but I've been looking at the hook_cron() portion of httpbl all day and am certain it isn't working at all. I've noticed that my httbl table is continually growing and not being cleaned of old results.

The first problem, I think, is that neither of the two variables, httpbl_dbcache or httpbl_drupalaccess actually exist. (Leftovers from version 5?) There is instead the httpbl_cache variable with possible values of HTTPBL_CACHE_OFF, HTTPBL_CACHE_DB or HTTPBL_CACHE_DBDRUPAL.

I changed my code to:

function httpbl_cron() {
if (variable_get('httpbl_cache', HTTPBL_CACHE_OFF) == HTTPBL_CACHE_DBDRUPAL) {
// Also check h.status so that we do not accidentally delete user's own rules.
db_query("DELETE a, h FROM {access} a JOIN {httpbl} h ON a.mask = h.hostname WHERE h.status = 1 AND h.expire <= %d", time());
db_query("DELETE FROM {httpbl} WHERE expire <= %d", time());
}
else if (variable_get('httpbl_cache', HTTPBL_CACHE_OFF) == HTTPBL_CACHE_DB) {
db_query("DELETE FROM {httpbl} WHERE expire <= %d", time());
}
}

I realize there are some redundancies there, and I have not yet gotten this to remove anything from the access table, but now my httpbl table is cleaning out old entries that have expired.

Good find, in another function there was another leftover from the Drupal 5.x branch. I've committed a fix (not for PostgreSQL compatibility yet, though), a new package should be ready soon.

Thanks for the new package. I need to amend my cron changes. As I mentioned above (#2), my previous changes were successfully cleaning out expired entries from the httpbl cache, but not so for the access table. I had worked some more on that today and finally have hook_cron() cleaning up both tables, though I'm not at all certain I am doing it "legally," nor if it is compatible with PostgreSQL. My sql is pretty weak, and I had no luck with any of the alternate examples at the top of this thread.

Below is the current hook_cron() I am using and it does clear out both tables in accordance to expiry and status conditions:

/**
 * Implementation of hook_cron().
 *
 * Cleans old results from the cache table and also Drupal access table if appropriate.
 */
function httpbl_cron() {
  // Only continue when caching is enabled
  if (variable_get('httpbl_cache', HTTPBL_CACHE_OFF) > HTTPBL_CACHE_OFF) {
  	if (variable_get('httpbl_cache', HTTPBL_CACHE_OFF) == HTTPBL_CACHE_DBDRUPAL) {
      // Also check status so that we do not accidentally delete site's custom access rules.
      db_query("DELETE FROM {access} WHERE mask IN (SELECT hostname FROM {httpbl} WHERE status > 0 AND expire <= %d)", time());
      watchdog('httpbl', 'Via cron - Removed expired httpBL hosts from access table.', array(), WATCHDOG_DEBUG);
    }

  db_query("DELETE FROM {httpbl} WHERE expire <= %d", time());
  watchdog('httpbl', 'Via cron - Removed expired hosts from httpBL cache.', array(), WATCHDOG_DEBUG);
  }
}

I'll note that I checked the httpbl table for any status condition > 0 because I had temporarily added code to also add greylisted hosts (2) to the access table, strictly for debugging purposes. The site I'm testing on continues to get a lot of greylisted visitors each day, but actual blacklisted visitors are much rarer (only 2 of them in the last 4 or 5 days). Adding both usually gave me something to test each time cron ran.

Also, as you'll see, I added some watchdog debug statements early on -- when it still wasn't working -- to help me figure out whether or not I was even making it through the cache enabled condition.

Comitted.