Forum OpenACS Q&A: Installation questions and suggestions

Collapse
1: Installation questions and suggestions
Posted by Ken Chakiris on 04/04/00 08:40 PM
I finally got the system working.  In the course of going through the
acceptance tests I got a number of errors which seem to be tracable to
not having compiled postgres with tcl support and not having loaded
postgres-pgtcl.sql. Specifically the to_date() function is not working
properly. Is this necessary for the system to work?

Someone should create some perl scripts (maybe it will be me!) that
automates the acceptance testing. Going throught them by hand is error
prone and tedious.

I would like my users to be able to post large comments. At least 32K.
What are the trade-offs and consequences of increasing the block size
to 32K in postgres?

Collapse
2: Response to Installation questions and suggestions (response to 1)
Posted by Roberto Mello on 04/04/00 08:52 PM
You shouldn't need to run the acceptance tests to find these errors. Just following the installation instructions would help enough.

Postgres 7 has a to_date and to_char builti-in (right ?). They _are_ necessary for the system to work properly. You only postgres-pgtcl if you're running PG 6.5 (I'd rather go with PG 7 since that's where the ACS/pg development effort is going).

About the Perl script, you are welcome to help. I still think we should stick with Tcl wherever possible, otherwise we'll never make AOLserver very popular ("what kind of webserver has Tcl as embedded language but uses Perl for its installation ?" is a question I get very often). It simply raises questions about the credibility of AOLserver. I know, and you know, that a programmer will learn the tools needed to accomplish a task, but not everyone knows that. Tcl is good enough for parsing some text files and outputting it (besides being more readable and consistent with AOLserver and ACS). You have to learn it to deal with ACS anyways.

Increasing the blocksize in PG leaves less pages being cached. This will all change as ACS/pg starts making use of the lzText capabilities of PG 7 (one more reason to go with PG 7 from now).

Collapse
3: Response to Installation questions and suggestions (response to 1)
Posted by Ken Chakiris on 04/04/00 09:09 PM
I am running postgres 7.0b3. I think there is something wrong with the to_date function. This is what I get when I do the first test on the acceptance test page. 
bsim=> select to_date('1-1-2000', 'MM-DD-YYYY') as y2k from dual ;

y2k
-------------
0002-12-29 BC
(1 row)

bsim=> select * from dual ;
sysdate
----------------------
2000-04-04 14:05:25-07
(1 row)
Why is this?
Collapse
4: Response to Installation questions and suggestions (response to 1)
Posted by Ken Chakiris on 04/04/00 11:43 PM
The to_date() function seems to be working fine.

However the upper() function is not parsed correctly in the GROUPby clause. The following code from admin-community-view.tcl(line 73) was producing server errors when I was testing the Bboard system. 

if { $n_postings < 2 } {
    set sql "select distinct email, count(*) as how_many_posts
from bboard , users
where bboard.user_id = users.user_id
and topic_id = $topic_id
and posting_time >= to_date('$start_date','YYYY-MM-DD')
and posting_time <= to_date('$end_date','YYYY-MM-DD')
group by email
order by upper(email)"
} else {
    set sql "select distinct email, count(*) as how_many_posts
from bboard, users
where topic_id = $topic_id
and posting_time >= to_date('$start_date','YYYY-MM-DD')
and posting_time <= to_date('$end_date','YYYY-MM-DD')
and bboard.user_id = users.user_id
group by email
having count(*) >= $n_postings
order by upper(email)"
}


set selection [ns_db select $db $sql]
The code dies at the set selection [ns_db select $db $sql] clause. If I remove the upper() functions it seems to work fine. I fired up the psql in emacs and found out that this $sql string was returning the error message: 
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
Is this a bug in my installation or in the code or in postgres?
Collapse
5: Response to Installation questions and suggestions (response to 1)
Posted by Ben Adida on 04/05/00 01:13 AM
No bug in your installation, we're slowly overcoming all of these select distinct statements which are a PG 7.0 thing that didn't show up in PG 6.5. You shouldn't take out the upper() call in the order by, instead you should simply select additional fields to include the same exact things that you're ordering by.

The port is getting there, but there are still some issues :)

-Ben

Collapse
6: Response to Installation questions and suggestions (response to 1)
Posted by Don Baccus on 04/05/00 01:25 AM
In fact, in the case given you can do "select distinct upper(email)", which more correctly conveys the sense of the query anyway (the email address you give as your login name is always treated in a case-insensitive manner by the ACS).

The restriction that was added to PG 7.0 makes it standard compliant, and was added because earlier versions of PG actually gave incorrect results for certain flavors of "distinct ... order by" queries.

Collapse
7: Response to Installation questions and suggestions (response to 1)
Posted by Ken Chakiris on 04/05/00 02:16 AM
I took Ben's suggestion and simply added an extra upper(email). This works. I did a file search in emacs for "order by *.upper" using grep in the ../../www directory and found about 90 or so lines that need to be changed.  I am probably going to change them myself.  So that we won't duplicate efforts, how do we share the bug fixes that we make ourselves.  Do you a CVS system working somewhere that people can upload fixes to?
Collapse
8: Response to Installation questions and suggestions (response to 1)
Posted by Ben Adida on 04/05/00 03:21 AM
Ken,

We've already fixed a lot of them for upcoming beta3, plus remember that it only matters if there is a select distinct (the others shouldn't be changed), so I think there are quite a bit fewer than 90. If you want to participate, sign up at SourceForge.net, and send me email!

Collapse
9: Response to Installation questions and suggestions (response to 1)
Posted by Brent Fulgham on 05/01/00 07:07 PM
For what it's worth, my system doesn't do well with the "acceptance tests" either.  I see the same date issue mentioned above, plus various selects don't work at all.

On the other hand, the system itself seems to be working quite well, so I am guessing these "errors" have more to do with the differences between Postgres and Oracle than any "error" in the loading process.