Teiid creates a new connection for each execution in correlated nested table where the nested expression is a procedure call with return type as "blob".

[haifen_bi]

Teiid creates a new connection for each execution in correlated nested table where the nested expression is a procedure call with return type as "blob".
It happened for both resource adapter and datasource.

I have created a view as follows:

<model name="TEST" type="VIRTUAL" visible = "true">
          <metadata type="DDL"><![CDATA[
   CREATE VIEW V1 (
     id string PRIMARY KEY,
     next_val blob 
   )
   AS SELECT t1.id, t2.*
    from t1, TABLE(call p1(t1.id))t2
        
  ]]> </metadata>
</model>

t1 and p1 are a table and a procedure (return type is blob)  in the source model.

Connection pool's max size is configured as 5:
<pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>5</max-pool-size>
        <prefill>true</prefill>
        <use-strict-min>false</use-strict-min>
    </pool>

Execute "select * from V1" will throw following exception if the return rows are more than 5:

10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7) org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)
10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at com.lgc.edm.teiid.translator.EdmSqlServerExecutionFactory.getConnection(EdmSqlServerExecutionFactory.java:90)
10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at com.lgc.edm.teiid.translator.EdmSqlServerExecutionFactory.getConnection(EdmSqlServerExecution
Factory.java:38)
10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:190)
10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.BaseDelegatingExecutionFactory.getConnection(BaseDelegatingExecutionFactory.java:116)
10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:218)
10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:446)
10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:159)
10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:156)
10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.FutureTask.run(FutureTask.java:138)
10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.lang.Thread.run(Thread.java:662)
10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)
10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:279)
10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      ... 17 more
10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
10:40:42,830 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractC
onnectionManager.java:390)
10:40:42,830 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnec
tionManagerImpl.java:368)
10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractCon
nectionManager.java:464)
10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)
10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      ... 18 more
10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
10:40:42,862 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:384)
10:40:42,862 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:
397)
10:40:42,877 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)
10:40:42,877 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

During the troubleshooting, I found that if I changed return type to "string"/"integer" in the procedure, same query was successful because Teiid did't not create a new connection for each execution of procedure.

Is this a defect? We are using Teiid 8.4.0 final.

Thank you for the help.

[tom9729]

Could you post the query plan for both cases?

set showplan on;

select * from v1;

show plan;

Query plan is returned in PLAN_TEXT column.

Thanks,

Tom

[shawkins]

You are seeing the interaction of a couple of things.  The first is that with a return blob (or any lob) Teiid by default will assume that the source execution must be kept alive so that the blob value can be streamed.  Then if you are not under a transaction, each invocation will indeed go back to the connection pool and get a new connection.   When you have the return type of string, the connections are being immediately returned to the pool and reused by the subsequent procedure calls.

If you are under a transaction and your connection pool is using track connections by transaction, then this will work as expected.  If your translator is configured with copyLobs (can be done as an translator property in the vdb.xml), then Teiid will create a copy of the lob and make the connection available for reuse.  Or if your source supports using lobs after a Statement.close(), then you can use a custom translator that returns ExecutionFactory.areLobsUsableAfterClose as true.  Otherwise we would need an enhancement to support connection reuse in this case.

Steve

[haifen_bi]

Thanks Steve and Tom.

I added the "copyLobs" property in the translator and now I can query "select * from v1" without any exception.

Thanks again for the help.

Haifen