Errors with PostgreSQL

I don't know much about PostgreSQL but I'll look into it. It looks like the problem is that some users have multiple statuses in the DB that are exactly the same. MySQL and MSSQL just pick the status that appears higher in the table (the newer one in this query) but PGSQL refuses that nicety. ;)

You might try changing the query in line 579 on your local copy to use GROUP BY fbs_uid, status_fb instead of just GROUP BY fbs_uid... if that works, I'll change it ASAP.

Try this query:

SELECT DISTINCT(fbs_uid), uid as status_fb, status_time, sid FROM {facebook_status} ORDER BY sid DESC

I'm sorry, I didn't look at that code very closely. Remember that if you run this from a console you need to remove the curly braces {}.

SELECT DISTINCT(uid) as fbs_uid, status_fb, status_time, sid FROM {facebook_status} ORDER BY sid DESC

After more research, I've realized that simply using DISTINCT() does not achieve the desired result. Getting GROUP BY to comply with PostgreSQL standards makes it return the same result as DISTINCT(). The way around this is to use DISTINCT ON (), which doesn't exist in MySQL. But I should be able to trick PostgreSQL by making the query aggregate by using MAX() on the serial column...

Basically, lines 563-586 should be changed to this:

  if ($fbs_uid < -1) {
    $duid1 = "";
    $duid2 = "sid";
  }
  else {
    $duid1 = "GROUP BY fbs_uid";
    $duid2 = "MAX(sid)";
  }
  //$query is for displaying results for only one user, $query_2 is for displaying results for multiple users.  In $query_2, $duid controls grouping.
  if ( !is_numeric($num_results) ) {
    $num_results = 1;
    $query = db_query_range("SELECT status_fb, status_time FROM {facebook_status} WHERE uid = %d ORDER BY sid DESC", $fbs_uid, 0, 1);
    $query_2 = db_query_range("SELECT uid as fbs_uid, status_fb, status_time, %s FROM (SELECT * FROM {facebook_status} ORDER BY sid DESC) as x %s ORDER BY sid DESC", $duid2, $duid1, 0, 1);
  }
  else if (is_numeric($num_results) && $num_results == 0) {
    $query = db_query("SELECT status_fb, status_time FROM {facebook_status} WHERE uid = %d ORDER BY sid DESC", $fbs_uid);
    $query_2 = db_query("SELECT uid as fbs_uid, status_fb, status_time, %s FROM (SELECT * FROM {facebook_status} ORDER BY sid DESC) as x %s ORDER BY sid DESC", $duid2, $duid1);
  }
  else {
    //$num_results can only be negative if $fbs_uid is an array.
    if ($num_results < 0) {
      $num_results = -$num_results;
    }
    $query = db_query_range("SELECT status_fb, status_time FROM {facebook_status} WHERE uid = %d ORDER BY sid DESC", $fbs_uid, 0, $num_results);
    $query_2 = db_query_range("SELECT uid as fbs_uid, status_fb, status_time, %s FROM (SELECT * FROM {facebook_status} ORDER BY sid DESC) as x %s ORDER BY sid DESC", $duid2, $duid1, 0, $num_results);
  }

Thanks for testing... hopefully I haven't made an error here.

Phooey. Are you using PGSQL 8.0 or higher? I was really expecting that to work...

Okay well run this in your console and see if it works, I might just have to switch on $GLOBALS['db_type'] which is ugly but will do the job if I can find a query that works.

SELECT DISTINCT ON (uid) as fbs_uid, status_fb, status_time, sid FROM {facebook_status} ORDER BY sid DESC

Based on the syntax from the query you provided, the query should actually be:

SELECT DISTINCT ON (uid) uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC

I wasn't aware that DISTINCT ON (uid) uid as fbs_uid was valid; in MySQL the function (including parameters) become the column name so you can assign the whole thing an alias. A subquery wouldn't be any different, just less efficient.

Try this.

SELECT DISTINCT ON (fbs_uid), status_fb, status_time, sid FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC)

If that doesn't work, I'm completely out of ideas.

Presumably it's possible, I just don't know how to do it. If you can fool around and figure out a way to make DISTINCT ON (uid) work, that would be great. Otherwise I'll try to ask some gurus on IRC but most people seem to have a hard time understanding this query.

Alright, maybe these will work.

SELECT DISTINCT ON (fbs_uid) fbs_uid, status_fb, status_time, sid FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC)

SELECT DISTINCT ON (fbs_uid), fbs_uid, status_fb, status_time, sid FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC)

If either of the above work don't bother with this one. If you do try this one, even if it doesn't return an error you need to make sure all the columns are returned:

SELECT DISTINCT ON (fbs_uid) FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC)

Okay, good. Hopefully I'll get this in sometime this weekend. Thanks for testing.

SELECT DISTINCT ON (fbs_uid) fbs_uid, status_fb, status_time, sid FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC) as foo

I dont know if anyone told you, but recent PostGres (8.1.x is not recent) has critical syntax changes that drastically affect drupal. (this may not be applicable to this issue)

I just installed your 6.x dev version and got this errors on my drupal 6.9 on postgres 8.3.x

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "x.status_fb" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT * FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC) as x GROUP BY fbs_uid ORDER BY sid DESC LIMIT 1 OFFSET 0 in /var/www/sparcs/sites/all/modules/facebook_status/facebook_status.module on line 585.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "x.status_fb" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT * FROM (SELECT uid as fbs_uid, status_fb, status_time, sid FROM facebook_status ORDER BY sid DESC) as x GROUP BY fbs_uid ORDER BY sid DESC LIMIT 1 OFFSET 0 in /var/www/sparcs/sites/all/modules/facebook_status/facebook_status.module on line 585.

It's the same error. Line 585 instead of 579 probably because you had different settings.

Ok, is the proposed patched in the dev im using or am I waiting for a dev release to see the effects?
F

It's not in yet, which is why this issue is not marked as "Fixed."

Ok thanks :-)

This should be fixed locally and will be in the next release (probably a dev).