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

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