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)