By usmanc on

I'm getting tons and tons of database errors from PostgreSQL. I have 98% functionality, it's only *some* things that don't work. Most of the errors below are from me trying to give anonymous users permission to view content. Should I submit a bug report? My setup is at the bottom.

Errors from watchdog:

Location: /home/admin.php?mod=user&op=permission
Message: database: DB Error: unknown error query: DELETE FROM permission WHERE rid = '1' AND tid = ''
Hostname: 192.168.1.100
********************
Location: /home/admin.php?mod=user&op=permission
Message: database: DB Error: unknown error query: INSERT INTO permission (rid, perm, tid) VALUES ('1', 'access content', '')
Hostname: 192.168.1.100
*******************
Location: /home/admin.php?mod=user&op=permission
Message: database: DB Error: unknown error query: SELECT r.rid, p.perm FROM role r LEFT JOIN permission p ON r.rid = p.rid WHERE tid = '' ORDER BY name
Hostname: 192.168.1.100
*******************
Location: /home/
Message: database: DB Error: unknown error query: SELECT COUNT(*) FROM node WHERE promote = '1' AND status = '1' ORDER BY static DESC, created DESC
Hostname: 192.168.1.100
*******************
Location: /home/admin.php?mod=node
Message: database: DB Error: unknown error query: SELECT COUNT(*) FROM node n LEFT JOIN users u ON n.uid = u.uid ORDER BY n.changed DESC
Hostname: 209.118.49.207
*******************
Location: /drupal/admin.php?mod=comment
Message: database: DB Error: unknown error query: SELECT c.*, u.name, u.uid FROM comments c LEFT JOIN users u ON u.uid = c.uid WHERE c.status = '0' ORDER BY timestamp DESC LIMIT 0, 50
Hostname: 192.168.1.100

My setup:
Drupal 4.1.0 release candidate (grabbed it 1/6/03)
Linux kernel 2.4.19
PHP 4.2.3
PostgreSQL 7.3
Apache 1.3.27

Comments

usmanc’s picture

usmanc commented

During the database setup, I ran into a small problem with this section of database.pgsql:

CREATE FUNCTION "greatest"(integer, integer) RETURNS integer AS '
BEGIN
IF $1 > $2 THEN
RETURN $1;
END IF;
RETURN $2;
END;
' LANGUAGE 'plpgsql';

The problem was that I didn't have PL/PGSQL installed, so the new function wasn't created. I had to manually install the procedural language and re-run the above code. Is it possible that somethine ELSE failed without my noticing it?

teamonkey’s picture

teamonkey commented

No, you need to install the language manually.

For those who don't know how, you'll need to run createlang plpgsql drupal (where 'drupal' is the name of your database) before installing the database file. createlang might not be in your default path though, so you might have to use 'locate createlang' to find it.

I will be updating the docs shortly.

[teamonkey]

Anonymous’s picture

(not verified) commented

SELECT COUNT(*) FROM node WHERE promote = '1' AND status = '1' ORDER BY static DESC, created DESC

For this one, Postgres wants the terms in the ORDER BY clause to also be in a GROUP BY clause:

    SELECT COUNT(*)
    FROM node
    WHERE promote = '1' AND status = '1'
&nbsp;&nbsp;&nbsp;&nbsp;<b>GROUP BY static, created</b>
&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY static DESC, created DESC

MySQL doesn't seem to complain about this, but it also yields a different result from the query with the GROUP BY clause (empty set vs. 0).

One of them isn't behaving correctly (or maybe both?), but I have a feeling that most of the Postgres errors are resulting from queries with quirks beyond standard, bread-and-butter WHERE clauses (e.g., LEFT JOIN, multicolumn ORDER BY, etc.)....

Anonymous’s picture

(not verified) commented

What's the point of an ORDER BY clause? This is a simple COUNT(*). This works perfectly in Postgres:

select count(*) from node
where promote = '1' and status = '1'

It returns 1 for me.

What you suggested also works, the query below returns 1.

select count(*) from node
where promote = '1' and status = '1'
group by static, created
order by static desc, created desc

I also tried these queries in Oracle, and it simply ignores the ORDER BY and GROUP BY clauses. I guess this how MySQL also behaves..... I think that Postgres is being too "strict".

Anonymous’s picture

(not verified) commented

It seems that when you run a query against postgres that uses COUNT(*) and a multicolumn ORDER BY clause, that you also need to include a GROUP BY clause. At first I thought it would be easy to fix. After hunting through all the code I found that the includes/pager.inc is actually rewriting a query. It's taking the SELECT &lt;columns here&gt; FROM and turning it into SELECT COUNT(*) FROM. If some logic was added to the db_query_pager() function to add the appropriate GROUP BY clause into the query, that should solve it. I may try to tackle this, but if someone should do it before me please let me know by posting a response here.

dries’s picture

dries commented

FYI, we almost have a fix ready. The issue is being discussed on the mailing list.

Anonymous’s picture

(not verified) commented

Dries,

I made this little fix and it's working. Let me know if you come up with something more snazzy.

  // OLD CODE:
  //$array = db_fetch_array(db_query(preg_replace("/SELECT.*FROM/i", "SELECT COUNT(*) FROM", $query)));

  // this is my fix
  // Rather than try to rewrite the query adding in "GROUP BY" when needed
  // I strip off the ORDER BY clause all together, it's not needed for a count anyway
  $array = db_fetch_array(db_query(preg_replace("/SELECT.*FROM/i", "SELECT COUNT(*) FROM", substr($query, 0, strpos($query, "ORDER")))));
moshe weitzman’s picture

moshe weitzman commented

this patch fixes MS SQL server's complaints as well ... I wonder though if stripping the ORDER BY off the query would be cleaner than adding a GROUP BY (as suggested by a previous comment here).

geekwisdom’s picture

geekwisdom commented

Since the ORDER BY has no affect on COUNT(*), I could see no reason to write a ton of code to get the names of the fields needed for the GROUP BY clause. But I'm open to other ideas.

By the way, I like this product of yours, thanks for GPL'ing it.