[webdev] Re: FW: Errors on The Aclug Community

[Tom Hull <thull@xxxxxxxxxxx>]

Dale W Hodge wrote:
> 
> I'm trying to run OpenACS on Debian. Things were working fine until I updated
> the database to version 7.2.1.  Enclosed is the message from the OpenACS
> logwatch daemon:

Yecch! I don't have a good answer at this point, and may not unless I load
7.2.1 myself. (I'm running 7.1.2 at home; 7.1.3 on the website.) The notes
below are just some thoughts on debugging the problem. Good luck.

The 7.2 doc says that you shouldn't have to dump and reload the database.
But you might do another createdb and reload the schema, just to see if
an error pops up elsewhere. I haven't found a detailed 7.1 -> 7.2 release
note.

>  -----
> Errors since 08/Jun/2002:17:31:03
> 
> [08/Jun/2002:17:31:44]
>     Error: Ns_PgExec: result status: 7 message: ERROR:  Unable to identify an
> operator '<=' for types 'date' and 'timestamp without time zone'
>         You will have to retype this query using an explicit cast

This error report comes from the postgres.so module; the error below is the
same error one level up, and the one below it is yet another level up. The
latter shows you the tcl stack, which'll help find the suspect code.

It looks to me like what's happened is that the built-in function now() used
to return 'timestamp with time zone' (a/k/a 'timestamp') and now it's returning
'timestamp without time zone'. It looks like 7.1 only has a 'timestamp' type,
which is equivalent to 'timestamp with time zone' -- cf. 7.2 doc:

  http://www.postgresql.org/idocs/index.php?datatype-datetime.html

Some informal tests on 7.1.2 look like type 'date' implicitly casts to type
'timestamp with time zone' by adding time 00:00:00 and assuming the same time
zone. So that, e.g.:

  select current_date < current_timestamp; --> t
  select current_date = current_timestamp; --> f
  select current_date = current_timestamp::date; --> t

One thing you might try to do is:

  $ psql aclug
  aclug=# \df sysdate
  aclug=# \df now
  aclug=# \q
  $

On my machine, both sysdate and now are 'timestamp with time zone'. sysdate()
(see below) is a function defined in /www/doc/sql/postgres.sql, which is
basically a wrapper for now().

One thing I don't understand is that the openacs code uses 'datetime' as a
data type, yet psql \dT doesn't list it (nor does \dd), and I can't find a
create type definition. 'datetime' is used, like, hundreds of times in openacs
code.

> [08/Jun/2002:17:31:44]
>     Error: dbinit: error(localhost::aclug,ERROR:  Unable to identify an 
> operator
> '<=' for types 'date' and 'timestamp without time zone'
>         You will have to retype this query using an explicit cast
>     ): 'select spam_id, creation_user, from_address, body_plain, body_html,
> body_aol, title, user_class_query, user_class_description, send_date, status,
> last_user_id_sent, template_p from spam_history
>         where send_date::date <= sysdate()
>         and (status = 'unsent' or status = 'interrupted')
>         order by send_date
>         for update'

Looks like this comes from /tcl/spam-daemon.tcl. (If you start at the bottom, 
you
can figure this out easier than I did.) You could try changing

  where send_date::date

to

  where send_date::timestamp

You'll need to restart aolserver afterwards (true for any changes in /tcl/).

> [08/Jun/2002:17:31:44]
>     Error: Database operation "select" failed (exception NSDB, "Query was not 
> a
> statement returning rows.")
>     Database operation "select" failed (exception NSDB, "Query was not a
> statement returning rows.")
>         while executing
>     "ns_db select $db "select spam_id, creation_user, from_address, 
> body_plain,
> body_html, body_aol, title, user_class_query, user_class_description, 
> send_..."
>         (procedure "send_scheduled_spam_messages" line 41)
>         invoked from within
>     "send_scheduled_spam_messages"
>         ("eval" body line 1)
>         invoked from within
>     "eval [concat [list $proc] $args]"
>         (procedure "ad_run_scheduled_proc" line 43)
>         invoked from within
>     "ad_run_scheduled_proc {t f 1810 send_scheduled_spam_messages {} 
> 1023568254
> 0 t}"
>     Notice: Running scheduled proc wd_mail_errors...

Alright, this is the same error, as told by the tcl interpreter.

-- 
/*
 *  Tom Hull * thull at kscable.com * http://www.tomhull.com/
 */