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

PostgreSQL - Large Objects may not be used in auto-commit mode.

    Details

      Description

      Using Teiid 8.11.5 with PostgreSQL 9.4 and PostGIS. Doing a simple query with a spatial filter causes this stacktrace in the log.

      select mkt_id from cola_markets where st_intersects(shape, st_geomfromtext('point(9 8)'));
      

      20:29:06,694 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue72) uL81ZQ6FDlJL Connector worker process failed for atomic-request=uL81ZQ6FDlJL.22.0.8: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['javax.sql.rowset.serial.SerialBlob@223261cf'] SQL: SELECT g_0."mkt_id" FROM "public"."cola_markets" AS g_0 WHERE st_intersects(g_0."shape", st_geomfromwkb(?, 0)) = TRUE]
      	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.11.5.jar:8.11.5]
      	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:341)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_79]
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_79]
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_79]
      	at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_79]
      	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
      	at com.sun.proxy.$Proxy46.execute(Unknown Source)
      	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
      	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
      	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
      	at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_79]
      	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
      	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
      	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
      	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_79]
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_79]
      	at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_79]
      Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
      	at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:239)
      	at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:226)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(AbstractJdbc2Statement.java:3048)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1836)
      	at org.postgresql.jdbc3.AbstractJdbc3Statement.setObject(AbstractJdbc3Statement.java:1481)
      	at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:46)
      	at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:70)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1868)
      	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:957)
      	at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(JDBCExecutionFactory.java:936) [translator-jdbc-8.11.5.jar:8.11.5]
      	at org.teiid.translator.jdbc.JDBCBaseExecution.bind(JDBCBaseExecution.java:107) [translator-jdbc-8.11.5.jar:8.11.5]
      	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:122) [translator-jdbc-8.11.5.jar:8.11.5]
      	... 18 more
       
      20:29:06,720 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue73) uL81ZQ6FDlJL TEIID30020 Processing exception for request uL81ZQ6FDlJL.22 'TEIID30504 postgres: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['javax.sql.rowset.serial.SerialBlob@223261cf'] SQL: SELECT g_0."mkt_id" FROM "public"."cola_markets" AS g_0 WHERE st_intersects(g_0."shape", st_geomfromwkb(?, 0)) = TRUE]'. Originally TeiidProcessingException 'Large Objects may not be used in auto-commit mode.' LargeObjectManager.java:239. Enable more detailed logging to see the entire stacktrace.
      

      It looks like the issue is binding the WKB blob. Overriding the PostgreSQL translator and setting useStreamsForLobs() to true seems to fix the issue.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                tom9729 Tom Arnold
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: