Forum OpenACS Q&A: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL"

Collapse
4: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL" (response to 1)
Posted by Gustaf Neumann on 09/29/21 09:59 PM

from the package cronjob This package does not belong to the ~100 supported packages. Maybe the problem is there and needs somewhere a cleanup.

Just to understand: How do you call queries in the different databases? Without "-dbn ..." for postgres, with "-dbn ..." for Oracle?

lappend _ [db_string q1 {select current_database() from dual}]
lappend _ [db_string -dbn xx1 q2 {select current_database() from dual}]

You get these errors from simple queries like above, or more complex cases, such as nested db_foreach, nested transactions or the like? Do you see any pattern?

Collapse
5: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL" (response to 4)
Posted by Marty Israelsen on 09/30/21 02:13 AM

Yes, you are correct. When we call our oracle database we have to use the -dbn option, Example:

    db_1row -dbn xx1 check_closed {
        SELECT ...
    }

When we query postgres we never use the -dbn parameter:

set asof_date [db_string noxql {SELECT current_date;}]

I believe we have seen it happen on these simple types of queries before. But again it has been hard to debug

Collapse
6: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL" (response to 4)
Posted by Marty Israelsen on 09/30/21 04:36 AM

I have been doing some more debugging in db_exec proc and found some more clues. First of all the driverkey that is picked was the wrong one see below: driverkey=oracle when the driver key should have been postgres in the case. This makes me think that the db_driverkey proc is picking the wrong driver in some cases

ad_proc -public db_exec { {-subst all} type db statement_name pre_sql {ulevel 2} args } {

    A helper procedure to execute a SQL statement, potentially binding
    depending on the value of the $bind variable in the calling environment
    (if set).
    @param subst Perform Tcl substitution in xql-files. Possible values: all, none, vars, commands

} {
    set start_time [expr {[clock clicks -microseconds]/1000.0}]
    set driverkey [db_driverkey -handle_p 1 $db]

    if {$driverkey == "oracle"} {
        ns_log notice "== driverkey=$driverkey type=$type statement_name=$statement_name args=$args"
    }

I am not sure if this has anything to do with or or not but we also are using xql files for most of the postgres queries in this particular case

[29/Sep/2021:06:45:26][1.7f69fbfff700][-sched:0:3080:137-] Notice: nsdbpg(pool2): opened connection to east-intra-pgdb:5432:east_intra.
 62689 [29/Sep/2021:06:45:26][1.7f69fbfff700][-sched:0:3080:137-] Notice: == driverkey=oracle type=0or1row statement_name=dbqd.usurf-swbill.tcl.cron-procs.swb::post_service_center_charges.getEmpName args=
 62690 [29/Sep/2021:06:45:26][1.7f69fbfff700][-sched:0:3080:137-] Notice: ### db_with_handle returned error handle: "'0or1row' is not of type Oracle8" for statement
Collapse
7: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL" (response to 4)
Posted by Gustaf Neumann on 09/30/21 02:38 PM
Although i am not completely sure, what was going on, i have removed a per-thread caching level. My suspicion is that the database handle names are not unique, and since "db_driverkey" is sometimes called with a database name and sometimes with a handle, the latter variant might lead to confusion.

Please apply this patch [1].
-gn

[1] https://cvs.openacs.org/changelog/OpenACS?cs=oacs-5-10%3Agustafn%3A20210930123412

Collapse
8: Re: cronjob error: handle "0or1row" is not of type "PostgreSQL" (response to 7)
Posted by Marty Israelsen on 10/01/21 03:05 PM
Thanks Gustaf, This appears to have fixed the issue on our system:) -Marty