How to reset a session in a sql connection pool?

i have a connection pool using javax.sql.DataSource and PostgreSQL JDBC. The process is simple, I’m taking a virtual connection from connection pool, process it, then close the virtual connection (release the connection to connection pool). Everything works fine.

But when I get the same virtual connection second time the session have the same data from the previous execution. Mainly temp tables that created in the previous execution. How can I reset the session after each execution? Or can you suggest any work around to solve the issue without using creating temp table if not exists query syntax.

sample code

   import java.sql.Connection;
   import javax.naming.Context;
   import javax.naming.InitialContext;
   import javax.naming.NamingException;
   import javax.sql.DataSource;
    
    public class Base {
    
 
      public  Connection getConnection() {
        Context ctx = (Context) new InitialContext().lookup("java:/comp/env");
         DataSource ds = (DataSource) ctx.lookup("database");
        Connection con = ds.getConnection();
        return con;
      }

       public Connection closeConnection( Connection con){
         if (con != null || !con.isClosed()) {
                con.close();
            }
   

    }

context.xml =>

   <Resource 
          accessToUnderlyingConnectionAllowed="true" 
        auth="Container" 
        closeMethod="close" 
        driverClassName="org.postgresql.Driver" 
        maxTotal="50"
        maxWaitMillis="60000" 
        removeAbandonedOnBorrow="true"
        maxIdle="20" 
        name="Database" 
        password="p*******" 
        type="javax.sql.DataSource" 
        url="jdbc:postgresql://dbUrl/dbName" 
        username="dbUser" 
        validationQuery="select version();"
       validationQueryTimeout="60"
    />

// use case

con = getconnection()
CallableStatement st = con.prepareCall("{ doSomething()}");
st.execute()
//processing
st.close()
con.close()

in doSomething() plpgsql function i have a temp table creation (with name t_table), data processing and an array output. when i repeatedly call this from java first execution is success. But from the second execution message saying that t_table already exist is thrown. I thought that when using connection pool, the connections in the pool are session independent IE. every new connections from pool will have a new session. PostgreSQL solution is to drop that particular temp table . But there is no other solution for this from the tomcat side ?

Answer

The discard temporary command can be used to clean up a session. You can configure that as a validation query to be run when the connection is returned to the pool.

With the Tomcat JDBC pool, this would be something like this:

validationQuery="discard temporary"
testWhileIdle="false"
testOnBorrow="false"
testOnReturn="true"

If you want to clean up the session even more, you can consider using discard all instead, but that requires autocommit to be enabled, not sure if that would work for you.

If such a statement isn’t allowed, you can wrap it into a function:

create function session_cleanup()
  returns boolean
as
$$
  discard temporary;
  select true;
$$
language sql;

validationQuery="select session_cleanup()"
testWhileIdle="false"
testOnBorrow="false"
testOnReturn="true"

Advertisement

Answer