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

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Critical Critical
    • 10.1, 10.0.2, 9.3.7
    • 10.0.1
    • Query Engine
    • None
    • 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 Exists
    • Hide

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

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

      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.

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: