Error: multiple ORDER BY clauses not allowed

[hallettj]

My server was working nicely for a while. But then I started seeing these Postgres errors when making /sync queries:

2013-09-14 05:27:23 UTC ERROR:  multiple ORDER BY clauses not allowed at character 131
2013-09-14 05:27:23 UTC STATEMENT:  (SELECT id, node, xml, updated FROM items
        WHERE node=$1
        AND   updated >= $2::timestamptz
        ORDER BY updated DESC
        LIMIT $3) ORDER BY updated DESC

Here is the request URL:

https://sitr.us/api/sync?since=2013-09-14T04%3A40%3A16.997Z&max=51

[hallettj]

Making this change has gotten me going again:

diff --git a/src/local/model_postgres.coffee b/src/local/model_postgres.coffee
index e7f0b78..7a2cb1e 100644
--- a/src/local/model_postgres.coffee
+++ b/src/local/model_postgres.coffee
@@ -612,7 +612,7 @@ class Transaction
                 prepArgs.push node
                 prepArgs.push timeStart
                 prepArgs.push maxItems
-            q += " ORDER BY updated DESC"
+            q += " ORDER BY updated DESC" if res?.rows?.length > 1
             db.query q, prepArgs, cb2
         , (res, cb2) ->
             items = res?.rows.map (row) ->

But I don't know if that will actually behave correctly when there are multiple rows.

[Schnouki]

What version of PostgreSQL are you using?

Could you please try reverting dbcc73a and check if it works?

[hallettj]

I tried reverting dbcc73a and that also seems to work.

I found out that my server was in an inconsistent state when I first encountered this error. I set up buddycloud under a preexisting Prosody server; so I was able to log in but my user account had not been initialized in the database. Maybe that was the cause of the problem?

[Schnouki]

I doubt it: I did the same when first installing buddycloud, and it's really a supported and "normal" use case.

What version of PostgreSQL are you using? I don't have this issue on 9.2, but maybe this behaves differently on other versions.

[hallettj]

Postgres 9.1.9-1ubuntu1

I'm sorry for neglecting to include that detail before.
On Sep 16, 2013 1:17 AM, "Thomas Jost" [email protected] wrote:

I doubt it: I did the same when first installing buddycloud, and it's
really a supported and "normal" use case.

What version of PostgreSQL are you using? I don't have this issue on 9.2,
but maybe this behaves differently on other versions.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/128#issuecomment-24494659
.