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 Feb 12, 2014 12:09 PMTeiid 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.