ACS Web Server & PostgreSQL
Hello,
I am trying to put up a inter-/intra-net using he ACS (ArsDigita Community
System) and PostgreSQL. I had installed PG 7.0.2 and got the AOL-Server and OpenACS
(with a postgres driver) up and running without too much trouble. Most things
worked nicely, but the Intranet didn't. A note somewhere in the documentation
suggested that this was a bug in PostgreSQL 7.0.x and mentioned that was fixed
in 7.1x, which should be available from the CVS. Hence, I downloaded 7.1 Beta3
(source), compiled and installed it mainly just 'Out Of the Box'. Now some things
were not working in the ACS, so reinitialized the database and started up again.
When creating the initial Administrator in ACS the following SQL is being
executed:
select ug.group_id as subgroup_id, group_name as subgroup_name, ug.registration_date,
ug.approved_p, count(user_id) as n_members,
upper(group_name)
from user_groups ug, user_group_map ugm
where parent_group_id=1
and ug.group_id=ugm.group_id
group by ug.group_id, group_name, ug.registration_date, ug.approved_p
union
select ug.group_id as subgroup_id, group_name as subgroup_name, ug.registration_date,
ug.approved_p, 0 as n_members,
upper(group_name)
from user_groups ug
where parent_group_id=1 and
not exists (select 1 from user_group_map ugm
where ug.group_id=ugm.group_id)
group by ug.group_id, group_name, ug.registration_date, ug.approved_p
order by upper(group_name)
This statement must have worked fine in 7.0.2, but now a get an error:
Error: Ns_PgExec: result status: 7 message: ERROR: Attribute 'group_name' not found
After several tries, I ended up just modifying the order by clause to:
order by upper
And then it worked. Is this a bug in 7.0.2, 7.1x or in the (ACS) SQL? And what would
be the right SQL for this.
Greetings,
Leif
(leif@danmos.dk)
leif@danmos.dk writes:
select ug.group_id as subgroup_id, group_name as subgroup_name, ug.registration_date,
ug.approved_p, count(user_id) as n_members,
upper(group_name)
from user_groups ug, user_group_map ugm
where parent_group_id=1
and ug.group_id=ugm.group_id
group by ug.group_id, group_name, ug.registration_date, ug.approved_p
union
[snip]
group by ug.group_id, group_name, ug.registration_date, ug.approved_p
order by upper(group_name)This statement must have worked fine in 7.0.2, but now a get an error:
Error: Ns_PgExec: result status: 7 message: ERROR: Attribute 'group_name' not found
After several tries, I ended up just modifying the order by clause to:
order by upper
This works because the expression 'upper(group_name)' happens to be
labelled 'upper' due to the lack of an AS clause, but this assumption is
not portable. It would be better to either use an AS clause an order by
that alias, or simply use the column number, as in 'ORDER BY 6'.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.net> writes:
leif@danmos.dk writes:
This statement must have worked fine in 7.0.2, but now a get an error:
Error: Ns_PgExec: result status: 7 message: ERROR: Attribute 'group_name' not found
After several tries, I ended up just modifying the order by clause to:
order by upper
This works because the expression 'upper(group_name)' happens to be
labelled 'upper' due to the lack of an AS clause, but this assumption is
not portable. It would be better to either use an AS clause an order by
that alias, or simply use the column number, as in 'ORDER BY 6'.
It only worked by sheer luck in 7.0.* and earlier. As Peter says, the
statement violates the SQL standard --- you can only ORDER BY an output
column name or number according to SQL. We support the extension of
ordering by an expression for simple SELECTs, but not for UNIONs.
7.0 didn't realize the difficulties involved, and tried to interpret
such an expression the same as it would do for just the first component
SELECT. (Thought experiment: how would you interpret this command if
there is no "group_name" attribute in the second SELECT? What if it
exists, but refers to something completely different than in the first?)
This example happened to end up being converted to 'ORDER BY 6', which
will work, but there are lots of similar cases where 7.0 produces totally
bogus ordering. 7.1 only accepts cases it can actually do correctly.
7.1's error message does need to be improved, and I have that on the
to-do list, but it's not done yet ...
regards, tom lane