queries on pgsql

Fixed and commited (http://drupal.org/cvs?commit=497166).

Now the question is ... are you okay with handpatching your own site, or do you need me to make a development release?

If okay with handpatching, you can try the patch from cvs, or you can modify the function spambot_account_ip_addresses() in the file spambot.module so it reads like the following:

function spambot_account_ip_addresses($account) {
  $hostnames = array();

  // Retrieve IPs from any sessions which may still exist
  $result = db_query("SELECT DISTINCT hostname FROM {sessions} WHERE uid = %d", $account->uid);
  while ($object = db_fetch_object($result)) {
    $hostnames[] = $object->hostname;
  }

  // Retrieve IPs from comments
  if (module_exists('comments')) {
    $result = db_query("SELECT DISTINCT hostname FROM {comments} WHERE uid = %d", $account->uid);
    while ($object = db_fetch_object($result)) {
      $hostnames[] = $object->hostname;
    }
  }

  // Retrieve IPs from statistics
  if (module_exists('statistics')) {
    $result = db_query("SELECT DISTINCT hostname FROM {accesslog} WHERE uid = %d", $account->uid);
    while ($object = db_fetch_object($result)) {
      $hostnames[] = $object->hostname;
    }
  }

  // Retrieve IPs from user stats
  if (module_exists('user_stats')) {
    $result = db_query("SELECT DISTINCT ip_address FROM {user_stats_ips} WHERE uid = %d", $account->uid);
    while ($object = db_fetch_object($result)) {
      $hostnames[] = $object->ip_address;
    }
  }
 
  $hostnames = array_unique($hostnames);
  return $hostnames;
}

Note: Copy and paste this, don't attempt to type it by hand. And always have a backup handy.

I also got this error today and came up with my own fix before searching the issue queue. Your suggested fix has simply dropped the ORDER BY timestamp DESC, but if the timestamp ordering is valuable the following alternative fix works in Postgres as well as MySQL:

SELECT hostname FROM {_table_} WHERE uid = %d GROUP BY hostname ORDER BY MAX(timestamp) DESC

Where _table_ is sessions, comments or accesslog

@2:

Thanks for the suggestion, but I'd have to politely decline.

Timestamp ordering was a nice-to-have at the beginning when it was retrieving ip addresses from a single table. However, now that multiple tables are used, it's more trouble than it's worth (in my opinion).

After using spambot.module myself, I haven't found timestamp ordering useful at all.