Forum OpenACS Q&A: learn to debug an error

I would like to learn how to debug an error. anyone can point me to the right direction?

I have 2 installations of openacs. one 5.1 and one HEAD.

I think I have the same error in them. Below is the error message I get in the HEAD installation.

I would like to LEARN TO FIND a sollution to this error MYSELF.

what should I do?

I tried to grep:
"where last_queried < current_timestamp - 1" within categories package and found it in the postgres procs.

maybe the problem is the syntax used?

=====================================
06/Jul/2004:00:16:01
    Error: Ns_PgExec: result status: 7 message: ERROR:  Unable to identify an operator '-' for types 'timestamptz' and 'integer'
    You will have to retype this query using an explicit cast

06/Jul/2004:00:16:01
    Error: Database operation "dml" failed

ERROR:  Unable to identify an operator '-' for types 'timestamptz' and 'integer'
    You will have to retype this query using an explicit cast

SQL:
        delete from category_search
        where last_queried < current_timestamp - 1

Database operation "dml" failed

ERROR:  Unable to identify an operator '-' for types 'timestamptz' and 'integer'
    You will have to retype this query using an explicit cast

SQL:
        delete from category_search
        where last_queried < current_timestamp - 1

    while executing
"ns_pg_bind dml nsdb0 {
        delete from category_search
        where last_queried < current_timestamp - 1
      }"
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"
    ("postgresql" arm line 2)
    invoked from within
"switch $driverkey {
                oracle {
                    return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
                }
      ..."
    invoked from within
"db_exec dml $db $full_statement_name $sql"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle -dbn $dbn db {
            db_exec dml $db $full_statement_name $sql
        }"
    (procedure "db_dml" line 100)
    invoked from within
"db_dml delete_old_searches """
    (procedure "category_synonym::search_sweeper" line 2)
    invoked from within
"category_synonym::search_sweeper"
    ("eval" body line 1)
    invoked from within
"eval [concat [list $proc] $args]"
    (procedure "ad_run_scheduled_proc" line 42)
    invoked from within
"ad_run_scheduled_proc {t f {0 16} category_synonym::search_sweeper {} 1089038332 0 f}"
================================================

grep -r category_synonym::search_sweeper *

I guess the real trick is knowing how to read the traceback; in this case you look for the first function down the list which is not in the db API (the db API might have a few bugs but the vast majority of problems like this are in the query itself not in the db API so that's the place to start).

In this case, trunc on an interval used to return the number of days but that was removed in 7.3, now it should use date_part('days',...).

the other thing to do after you fix these is to submit a patch in bug tracker so everyone else can benefit from your bug fixing :)

There are some instructions for that here It's good if they are attached to a bug report but even if not submitting patches is the best way (short of committing directly) to make sure the fixes find their way into the release.

===========================
date_part('days',current_timestamp - max(coalesce(modified_date, insertion_date)))
===========================

and is this usefull for the community that way (I don't know how to create a diff file to upload) but would be ready to learn.

No, you don't need to and shouldn't use trunc anymore. So your previous solution should've been correct. So use only

date_part('days',current_timestamp - max(coalesce(modified_date, insertion_date)))

or EXTRACT which is the SQL standard (I'm not sure if it works already in 7.3, but in 7.4 sure). Please read http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html for information on how these functions should be used.

The state 'Resolved' in bug tracker should only be used when the bug has been fixed in OpenACS CVS repository. Now you have a solution but it's not yet fixed in CVS. If you please, you could upload a patch to bug tracker, which would enhance the possibilities that someone with commit rights will fix the bug soon:

1. Go to the directory in your checkout where the file in question lies.
2. Write cvs diff -Nu robot-detection-procs-postgresql.xql > patch-file
3. Go to your bug in bug tracker.
4. Hit "Submit a Patch".
5. Fill in the form and submit the patch file you got from diff.

I hope it will still be usefull to the community - to do it the way I did in bugtrakker.

Yves.

TIA