OK, here's a nasty little secret ...
PG doesn't properly handle "order by" combined with "union". I knew
this when we started porting the ACS to Postgres, but decided to
ignore the problem. The "union" clauses are a result of unfolding
outer joins, and the "order by" only orders the last clause. This is
a non-fatal annoyance in most cases. I knew that eventually we'd get
both outer joins and fixed union statements, and figured that leaving
the "order by" clauses in would result in the pages magically working
better once unions were fixed. I also figured that removing the
non-working "order by" clauses wouldn't result in any improvement to the
query's output in the interim, so why bother?
Well, PG 7.1 got outer joins but didn't get fixed union statements,
which won't happen until PG 7.2.
And to top it off, PG 7.1 is stricter on "union" and "order by"
clauses, and there are some combinations it won't allow at all. The
error message you're getting is as a result of the new restrictions.
I can't blame the PG folk for adding it, as the query as written never
worked exactly the way one would think it did anyway. But it is a
pain in the rear for us.
I ran into this exact query myself about a week ago, when I built a
small OpenACS+PG7.1 site for a project my girlfriend's working on. I
just rewrote it as an outer join. But ... I can't check that in, of
course, because PG 7.0 doesn't have outer joins. I tried rewriting
the query in various ways to get PG to accept the "order by" clause,
but without success.
Are you running beta3 or beta4?
You might want to post this query to the pg hackers list to see if it
is intended that this particular case give an error message.
So far I've only run into a couple of queries that the new checks trip
up, but I've not tried more complex modules like e-commerce and intranet.
I wish ACS 4.1 were more complete, users will really need to use 3.2x
for some time and PG 7.1 offers some very significant advantages over
PG 7.0, like lots of bug fixes and much higher insert/update speed.
