Thread: 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

Hello,

  Thanks to Peter and Tom for their comments on the ACS sql's. That cleared the
picture and I have fixed quite a lot of other sql's based on that.


    Greetings,

  Leif
  (leif@danmos.dk)