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

INSERT INTO query fails in BEGIN ATOMIC block if data is obtained from atomic procedure which catches some exception

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 10.0.1
    • Fix Version/s: 10.1, 10.0.2, 9.3.7
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-10.0.1 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)

    • Steps to Reproduce:
      Hide

      1. In postgresql create the batch_test table by the following script:

      create table batch_test (a varchar);
      

      2. Add postgresql database configuration in standalone-teiid.xml

                      <datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true">
                          <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url>
                          <driver-class>org.postgresql.Driver</driver-class>
                          <driver>org.postgresql</driver>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                              <user-name>postgres</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 0</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

          <model name="test_pg">
              <property name="importer.useFullSchemaName" value="false"/>
      	<property name="importer.tableTypes" value="TABLE,VIEW"/>
      	<property name="importer.importKeys" value="false"/>
              <source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/>
          </model>
      

      4. Configure in the test-vdb.xml the following virtual procs:

          <model visible = "true" type = "VIRTUAL" name = "procs">
              <metadata type = "DDL"><![CDATA[
                create procedure px1() returns (a string) as
                begin atomic
                  begin
                    error 'aaaa';
                  end
                  exception e
                  select 'bbbbb';
                end;
                create procedure px2() returns (a string) as
                begin atomic
                  select 'bbbbb';
                end
              ]]>
              </metadata>
          </model>
      

      5. The following queries fail:

      -- fails with IJ000460: Error checking for a transaction
      begin 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      -- fails with IJ000460: Error checking for a transaction
      begin atomic 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      

      6. But the following queries work:

      begin 
        execute immediate 'begin loop on (select s.a as a from (call procs.px1()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end'; 
      end;
      
      begin 
        loop on (select s.a as a from (call procs.px2()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      
      Show
      1. In postgresql create the batch_test table by the following script: create table batch_test ( a varchar ); 2. Add postgresql database configuration in standalone-teiid.xml <datasource jndi-name= "java:/test_pg" pool-name= "test_pg" enabled= "true" use-java-context= "true" > <connection-url> jdbc:postgresql://localhost:5432/test?charSet=utf8 </connection-url> <driver-class> org.postgresql.Driver </driver-class> <driver> org.postgresql </driver> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> <prefill> false </prefill> <use-strict-min> false </use-strict-min> <flush-strategy> FailingConnectionOnly </flush-strategy> </pool> <security> <user-name> postgres </user-name> <password> xxxxxx </password> </security> <validation> <check-valid-connection-sql> select 0 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: <model name = "test_pg" > <property name = "importer.useFullSchemaName" value = " false " /> <property name = "importer.tableTypes" value = " TABLE , VIEW " /> <property name = "importer.importKeys" value = " false " /> < source name = "test_pg" translator- name = "myPg" connection -jndi- name = "java:/test_pg" /> </model> 4. Configure in the test-vdb.xml the following virtual procs: <model visible = "true" type = "VIRTUAL" name = "procs" > <metadata type = "DDL" > <![CDATA[ create procedure px1() returns (a string) as begin atomic begin error 'aaaa' ; end exception e select 'bbbbb' ; end; create procedure px2() returns (a string) as begin atomic select 'bbbbb' ; end ]]> </metadata> </model> 5. The following queries fail: -- fails with IJ000460: Error checking for a transaction begin loop on ( select s. a as a from ( call procs.px1()) as s) as x begin insert into test_pg.batch_test ( a ) values (x. a ); end end ; -- fails with IJ000460: Error checking for a transaction begin atomic loop on ( select s. a as a from ( call procs.px1()) as s) as x begin insert into test_pg.batch_test ( a ) values (x. a ); end end ; 6. But the following queries work: begin execute immediate ' begin loop on ( select s. a as a from ( call procs.px1()) as s) as x begin insert into test_pg.batch_test ( a ) values (x. a ); end end ' ; end ; begin loop on ( select s. a as a from ( call procs.px2()) as s) as x begin insert into test_pg.batch_test ( a ) values (x. a ); end end ;
    • Workaround:
      Workaround Exists
    • Workaround Description:
      Hide

      Use "set autoCommitTxn off" to disable the command level transaction.

      Show
      Use "set autoCommitTxn off" to disable the command level transaction.

      Description

      Running the query:

      begin atomic 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      

      leads to the following error in stacktrace:

      Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      2018-01-04 16:58:34,494 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue75) AIlKbjKNf5Ju TEIID30020 Processing exception for request AIlKbjKNf5Ju.28 'TEIID30504 test_pg: TEII
      D11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction'. Originally TeiidProcessingException 'IJ000459: Transaction is not active:
       tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:b3 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provide
      r JBoss JTA transaction provider)' TxConnectionManagerImpl.java:409. Enable more detailed logging to see the entire stacktrace.
      2018-01-04 16:58:39,134 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue76) AIlKbjKNf5Ju Connector worker process failed for atomic-request=AIlKbjKNf5Ju.29.3.26: org.teiid.tr
      anslator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:278)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:65)
              at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:201)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:322)
              at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
              at java.lang.reflect.Method.invoke(Method.java:498)
              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
              at com.sun.proxy.$Proxy36.execute(Unknown Source)
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
              at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
              at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:398)
              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
              at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
              at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:65)
              at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:66)
              at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:80)
              at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:88)
              at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:611)
              at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:65)
              at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:388)
              at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:297)
              at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:269)
              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
              at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:480)
              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:350)
              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
              at java.lang.Thread.run(Thread.java:745)
      Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
              at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:64)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:274)
              ... 38 more
      Caused by: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:425)
              at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:789)
              at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
              ... 40 more
      Caused by: javax.resource.ResourceException: IJ000459: Transaction is not active: tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:
      b6 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provider JBoss JTA transaction provider)
              at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:409)
              ... 42 more
      
      2018-01-04 16:58:39,137 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue76) AIlKbjKNf5Ju TEIID30020 Processing exception for request AIlKbjKNf5Ju.29 'TEIID30504 test_pg: TEII
      D11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction'. Originally TeiidProcessingException 'IJ000459: Transaction is not active:
       tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:b6 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provide
      r JBoss JTA transaction provider)' TxConnectionManagerImpl.java:409. Enable more detailed logging to see the entire stacktrace.
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: