ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

similar problem also on comment module line 314 in 6.15

That's because PostgreSQL implement strictly SELECT clause with ORDER BY.
A solution is simple. We need insert a column described in ORDER BY into SELET list.

see postgresql manual for select list.
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-SELECT-LIST

The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause. Using the clause AS output_name, another name can be specified for an output column. This name is primarily used to label the column for display. It can also be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

for example,

--- comment.module.orig	2010-03-14 23:17:22.000000000 +0900
+++ comment.module	2010-03-14 23:17:34.000000000 +0900
@@ -311,7 +311,7 @@
   // Select the $number nodes (visible to the current user) with the most
   // recent comments. This is efficient due to the index on
   // last_comment_timestamp.
-  $result = db_query_range(db_rewrite_sql("SELECT nc.nid FROM {node_comment_sta
tistics} nc WHERE nc.comment_count > 0 ORDER BY nc.last_comment_timestamp DESC",
 'nc'), 0, $number);
+  $result = db_query_range(db_rewrite_sql("SELECT nc.nid,nc.last_comment_timest
amp FROM {node_comment_statistics} nc WHERE nc.comment_count > 0 ORDER BY nc.las
t_comment_timestamp DESC", 'nc'), 0, $number);
 
   $nids = array();
   while ($row = db_fetch_object($result)) {

It is no side effect because after this code block, it retrieves only $row->nid from results.

from the issue, proposal of fix is that

original:

  if (!isset($terms[$node->vid][$key])) {
    $result = db_query(db_rewrite_sql('SELECT t.* FROM {term_node} r INNER JOIN {term_data} t ON r.tid = t.tid INNER JOIN {vocabulary} v ON t.vid = v.vid WHERE r.vid = %d ORDER BY v.weight, t.weight, t.name', 't', 'tid'), $node->vid);
    $terms[$node->vid][$key] = array();
    while ($term = db_fetch_object($result)) {
      $terms[$node->vid][$key][$term->$key] = $term;
    }
  }
  return $terms[$node->vid][$key];
}

changes

  if (!isset($terms[$node->vid][$key])) {
    $result = db_query(db_rewrite_sql('SELECT t.* FROM {term_node} r INNER JOIN {term_data} t ON r.tid = t.tid INNER JOIN {vocabulary} v ON t.vid = v.vid WHERE r.vid = %d ORDER BY  t.weight, t.name', 't', 'tid'), $node->vid);
    $terms[$node->vid][$key] = array();
    while ($term = db_fetch_object($result)) {
      $terms[$node->vid][$key][$term->$key] = $term;
    }
  }
  return $terms[$node->vid][$key];
}

That's mean remove v.weight from ORDER BY.

It looks no bad side effect because the results will set into $term array indexed by vid and key.

What ordering by v.weight does: if you have terms from several vocabularies attached to the node, the terms are always grouped per vocabulary. This seems nice to keep.

I tried solving this in a different way, which should catch all the errors. Patch is in comment #1 of #735120: Fix changes to db_distinct_field() in D6.16 / PostgreSQL.
No idea if that will get committed,assume that that issue will yield a 'proper' (as proper as possible) solution, in the end.