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

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

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.12.1, 8.13
    • 8.11.3, 8.11.5
    • JDBC Connector
    • None

    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.

      Attachments

        1. cola_markets.sql
          0.5 kB
        2. cola_mkt.gif
          cola_mkt.gif
          7 kB
        3. Spatial-vdb.xml
          0.3 kB

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            tom9729 Tom Arnold (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: