Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1912

large dependent join query produces IOException against PostgreSQL database

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Minor Minor
    • 7.7
    • 7.7
    • JDBC Connector
    • None
    • Workaround Exists
    • Hide

      Use the translator property settings to set the value for maxDependentInPredicates to 1, and maxInCriteriaSize to 10922.

      Show
      Use the translator property settings to set the value for maxDependentInPredicates to 1, and maxInCriteriaSize to 10922.

      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.

        1. TPC.vdb
          50 kB
        2. postgres-8.4-tpcr01-ds.xml
          2 kB
        3. oracle11-ds.xml
          1 kB

            rhn-engineering-shawkins Steven Hawkins
            van.halbert Van Halbert (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: