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

We have a system that is set up to query postgresql (as the default DB) and an Oracle database as well when you pass the -dbn parameter. We recently updated to the newest OpenACS/Naviserver back in May and have been getting certain intermittent errors from some of our cronjobs that have been running fine for years.

I was wondering if others have seen this type of error before. Examples of the errors look like the following

Error: handle: 'dml is not of type Oracle8

error:  handle "0or1row" is not of type "PostgreSQL"

It looks like the parameters to a function may be getting out of order in these cases.

It is not easily duplicatable and it seems to only happen for procs that switch between querying from postgres and oracle. I have noticed that when it does happen from cronjob I can run the same proc from the shell and it works fine

Any ideas on what may be happening? Thanks for your assistance. Marty

Thanks Gustaf,

It is a scheduled procedure that runs from the package cronjob. We are also not able to duplicate every time ourselves. So it could be a hard one to find. It does make sense that it could be a caching issue.

I have noticed that (when it does happen) we think it happens when we switch between using the default DB in queries and using the -dbn option for other queries.

When we use the new "Run Now" option in package cronjob then it does seem to work fine the times we have tried that. Also, running it from the shell seems to work fine too. But when the scheduled procedure gets in "the mode" it happens regularly on our system for those report that switch back and forth between the two DBs.

Here is our db-pool setup -- I have removed usernames and passwords as well as renamed pools etc.

ns_section ns/server/${server}/db
    if {$use_oracle eq "true"} {
        ns_param        pools              pool1,pool2,pool3,xx1Pool,xx2Pool,xx3Pool
    } else {
        ns_param        pools              pool1,pool2,pool3
    }
        ns_param        defaultpool        pool1


if {$use_oracle eq "true"} {
    ns_section ns/server/${server}/acs/database
        ns_param database_names [list main xx1 xx2 xx3 ]
        ns_param pools_main [list pool1 pool2 pool3]
        ns_param pools_xx1 [list xx1Pool]
        ns_param pools_xx2 [list xx2Pool]
        ns_param pools_xx3 [list xx3Pool]
}

ns_section ns/db/pools
        ns_param        pool1              "Pool 1"
        ns_param        pool2              "Pool 2"
        ns_param        pool3              "Pool 3"
if {$use_oracle eq "true"} {
        ns_param        xx1Pool           "xx1 Pool"
        ns_param        xx2Pool           "xx2 Pool"
        ns_param        xx2Pool           "xx3 Pool"
}

ns_section ns/db/pool/pool1
        # ns_param      maxidle            0
        # ns_param      maxopen            0
        ns_param        connections        15
        ns_param        LogMinDuration     0.01   ;# when sql logging is on, log only statements above this duration
        ns_param        logsqlerrors       $debug
    if { $database eq "oracle" } {
        ns_param        driver             ora8
        ns_param        datasource         {}
        ns_param        user               $db_name
        ns_param        password           $db_password
    } else {
        ns_param        driver             postgres
        ns_param        datasource         ${db_host}:${db_port}:${db_name}
        ns_param        user               $db_user
        ns_param        password           ""
    }

ns_section ns/db/pool/pool2
        # ns_param      maxidle            0
        # ns_param      maxopen            0
        ns_param        connections        5
        ns_param        LogMinDuration     0.01   ;# when sql logging is on, log only statements above this duration
        ns_param        logsqlerrors       $debug
    if { $database eq "oracle" } {
        ns_param        driver             ora8
        ns_param        datasource         {}
        ns_param        user               $db_name
        ns_param        password           $db_password
    } else {
        ns_param        driver             postgres
        ns_param        datasource         ${db_host}:${db_port}:${db_name}
        ns_param        user               $db_user
        ns_param        password           ""
    }

ns_section ns/db/pool/pool3
        # ns_param      maxidle            0
        # ns_param      maxopen            0
        ns_param        connections        5
        #ns_param        LogMinDuration     0.00   ;# when sql logging is on, log only statements above this duration
        ns_param        logsqlerrors       $debug
    if { $database eq "oracle" } {
        ns_param        driver             ora8
        ns_param        datasource         {}
        ns_param        user               $db_name
        ns_param        password           $db_password
    } else {
        ns_param        driver             postgres
        ns_param        datasource         ${db_host}:${db_port}:${db_name}
        ns_param        user               $db_user
        ns_param        password           ""
    }


if {$use_oracle eq "true"} {
    # Define pools for openintra db in Oracle

    ns_section ns/db/pool/xx1Pool
        ns_param   maxidle            1000000000
        ns_param   maxopen            1000000000
        ns_param   connections        5
        ns_param   verbose            
        ns_param   extendedtableinfo  true
        ns_param   logsqlerrors       
        ns_param   driver             oracle
        ns_param   datasource         
        ns_param   user               
        ns_param   password           

    ns_section ns/db/pool/xx2Pool
        ns_param   maxidle            1000000000
        ns_param   maxopen            1000000000
        ns_param   connections        5
        ns_param   verbose            
        ns_param   extendedtableinfo  true
        ns_param   logsqlerrors      
        ns_param   driver             oracle
        ns_param   datasource        
        ns_param   user               
        ns_param   password           

    ns_section ns/db/pool/xx3Pool
        ns_param   maxidle            1000000000
        ns_param   maxopen            1000000000
        ns_param   connections        5
        ns_param   verbose            
        ns_param   extendedtableinfo  true
        ns_param   logsqlerrors       
        ns_param   driver             oracle
        ns_param   datasource         
        ns_param   user               
        ns_param   password           
}

Thanks for you assistance

Marty

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

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

Great!

One could probably as well address this problem on the c-level (always generate unique IDs for the handles, also when multiple database backends are used), but probably the new solution is efficient enough (due to rwlocks for nsvs in newer NaviServer).