[teiid-issues] [JBoss JIRA] (TEIID-1912) large dependent join query produces IOException against PostgreSQL database

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-1912.
-----------------------------------

    Resolution: Done


Updated the pg defaults to be safely under the signed short max.  Also updated the planner logic to attempt to keep the total number of in values less than (max dependent sets)*(max in values)
                
> large dependent join query produces IOException against PostgreSQL database
> ---------------------------------------------------------------------------
>
>                 Key: TEIID-1912
>                 URL: https://issues.jboss.org/browse/TEIID-1912
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 7.7
>         Environment: Teiid 7.7.0 (built with latest today) in JBoss AS 5.1 against Oracle 10 and PostgreSQL 9.4 databases
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>            Priority: Minor
>             Fix For: 7.7
>
>         Attachments: oracle11-ds.xml, postgres-8.4-tpcr01-ds.xml, TPC.vdb
>
>
> Running the following inner merge query:
> SELECT ORDERS.O_ORDERKEY, ORDERS.O_ORDERDATE, ORDERS.O_CLERK, CUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, CUSTOMER.C_ACCTBAL 
> FROM TPCR01_PostgreSQLVirt.ORDERS, TPCR01_OracleVirt.CUSTOMER WHERE (CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY) 
> AND (ORDERS.O_ORDERDATE < '1992-02-06 00:00:00') AND (CUSTOMER.C_ACCTBAL > 5100) 
> produces exception:
> 1 [PSQLException]An I/O error occured while sending to the backend.
> 2 [IOException]Tried to send an out-of-range integer as a 2-byte value: 50000
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:90)
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:272)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:342)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:138)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:135)
> 	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> 	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> 	at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118)
> 	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
> 	at java.lang.Thread.run(Thread.java:680)
> From googling the exception, I found the suggestion (i've yet to find where Postgres specifically states the max allowable) to split the parameters into 30,000 chunks.   So I changed the PostgreSQLExecutionFactory by adding the following the CTOR:  setMaxDependentInPredicates(30);    
> and this allowed the query to finish.    Not to say this is the final solution, but it's an option that worked.  

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira