SEO

Hi
I have a an application that uses a lot of stored functions, some of them
which create temporary tables and refcursors that point to the tables.
Something that comes up occasionally are errors like

the jdbc code looks like
Connection con = poolingDataSource.getConnection();
con.setAutoCommit(false);
CallableStatement proc = con.prepareCall("{ ? = call \"insAccount\" ( ?)
}");

proc.registerOutParameter(1, Types.OTHER);
proc.setString(2,userName);
this.registerCallableStatementForCancel(proc);
logger.debug("registerCallableStatementForCancel() : exit"+userName);
/* another thread will call statement.cancel() if timeout exceeded*/
proc.execute();
this.unregisterCallableStatementForCancel(proc);
logger.debug("unregisterCallableStatementForCancel (): exit"+userName);
ResultSet rset = (ResultSet)proc.getObject(1);

Occasionally the prepareCall just hangs, as logging statements show that it
does not enter the execute(). The called procedures may or may not user
temporary tables. But the functions hang just the same.
Is there anyway i can avoid the hang condition or the next best thing some
king of time out mechanism. I have one setup in the event that execute
hangs. But i cannot think of how to cancel a prepareCall().

The application has a commons-dbcp maintaining a connection pool to the
database server. The jdbc driver used is the jdbc3. The database version is
7.4.5. It has been deployed on a Debian Linux system with kernel version
2.6.8.

Thanks
Akhil Srinivasan

Akhil Srinivasan wrote:

> CallableStatement proc = con.prepareCall("{ ? = call
> \"insAccount\" ( ? ) }");
>
> proc.registerOutParameter(1, Types.OTHER);
> proc.setString(2,userName);
> this.registerCallableStatementForCancel(proc);
> logger.debug("registerCallableStatementForCancel() : exit"+userName);
> /* another thread will call statement.cancel() if timeout exceeded*/
> proc.execute();
> this.unregisterCallableStatementForCancel(proc);
> logger.debug("unregisterCallableStatementForCancel ():
> exit"+userName);
> ResultSet rset = (ResultSet)proc.getObject(1);
>

> Occasionally the prepareCall just hangs, as logging statements show that
> it does not enter the execute().

I find this unlikely as prepareCall doesn't do any I/O or thread-related
work. It could be getting stuck in a loop, but it seems odd that it
would only happen intermittently. More likely seems to be that you have
a problem in registerCallableStatementForCancel.

Can you add more logging immediately before calling
registerCallableStatementForCancel() to eliminate that possibility?

Alternatively, can you get a thread dump (on Linux/Solaris, send a
SIGQUIT to the Java process) when it hangs? That'd show us exactly where
things have stopped.

-O

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi,
Problem solved, the connection pool was running out of connections as we
were catching the exception after the connections were released.
Thanks to all for the help.

Akhil Srinivasan

On 6/21/05, Oliver Jowett <oliver@opencloud.com> wrote:
>
> Akhil Srinivasan wrote:
>
> > CallableStatement proc = con.prepareCall("{ ? = call
> > \"insAccount\" ( ? ) }");
> >
> > proc.registerOutParameter(1, Types.OTHER);
> > proc.setString(2,userName);
> > this.registerCallableStatementForCancel(proc);
> > logger.debug("registerCallableStatementForCancel() : exit"+userName);
> > /* another thread will call statement.cancel() if timeout exceeded*/
> > proc.execute();
> > this.unregisterCallableStatementForCancel(proc);
> > logger.debug("unregisterCallableStatementForCancel ():
> > exit"+userName);
> > ResultSet rset = (ResultSet)proc.getObject(1);
> >
>
> > Occasionally the prepareCall just hangs, as logging statements show that
> > it does not enter the execute().
>
> I find this unlikely as prepareCall doesn't do any I/O or thread-related
> work. It could be getting stuck in a loop, but it seems odd that it
> would only happen intermittently. More likely seems to be that you have
> a problem in registerCallableStatementForCancel.
>
> Can you add more logging immediately before calling
> registerCallableStatementForCancel() to eliminate that possibility?
>
> Alternatively, can you get a thread dump (on Linux/Solaris, send a
> SIGQUIT to the Java process) when it hangs? That'd show us exactly where
> things have stopped.
>
> -O
>