3 Replies Latest reply on Mar 25, 2013 10:22 AM by markaddleman

    Materialized View incorrectly issues concurrent load query

    markaddleman

      Under a heavily patched 8.1, we see the translator invoked twice, concurrently to load a materialized view involved in a query.  The translator is invoked on two different worker threads for the same request id, simultaneously.  The particular query that causes this problem is a pretty long UNION of a bunch of queries that all end up hitting the same materialized view, though the mat view itself is often joined into other views.  Here is a simplified example:

      mt: SELECT * FROM t1

      v1: SELECT mt.a,t2.a,t2.b,t2.c FROM t2 JOIN mt ON t2.a=mt.a

      v2: SELECT mt.a,t3.x,t3.y,t3.z FROM t3 JOIN mt ON t3.x=mt.a

      union query:  SELECT * FROM v1 UNION SELECT * FROM v2

       

      Attached is a log of the actual problem.  The relevant snippet is

      21 Mar 2013 14:31:40,699 EDT INFO  [com.ca.chorus.teiid.safety.SafetyHarnessExecutionFactory] (Worker1_QueryProcessorQueue242) > getConnection against com.ca.chorus.teiid.translator.sysview.SysviewExecutionFactory@720a9e97 for r4YzDVkiZisf.33

      21 Mar 2013 14:31:40,699 EDT INFO  [org.teiid.PROCESSOR.MATVIEWS] (http--127.0.0.1-8080-8) TEIID30013 Loading materialized view table #MAT_GSV.SYSTEMS_VIEW

      21 Mar 2013 14:31:40,705 EDT INFO  [com.ca.chorus.teiid.safety.SafetyHarnessExecutionFactory] (Worker3_QueryProcessorQueue243) > getConnection against com.ca.chorus.teiid.translator.sysview.SysviewExecutionFactory@720a9e97 for r4YzDVkiZisf.33

       

      I don't have a test case yet.  I'll start working on one shortly.  When I have one, I'll check against Teiid 8.3 though upgrading our production code to 8.3 isn't feasible.

        • 1. Re: Materialized View incorrectly issues concurrent load query
          markaddleman

          Please disregard.  It turns out our client application was generating a query against both the materialized view and the underlying table.  The translator saw the two queries as identical SQL and under the same request id.  This condition triggered the problem in our translator that the materialized view is intended to resolve. 

           

          An interesting feature idea falls out of this case:  It would be helpful to include metadata in the query as it is passed down to the translator that tracks the origin or point in the plan that generated the call.  Presumably, we could have dumped this metadata and seen that the two identical queries originated from different points in the plan and that would have led us to the source of the problem more quickly.

           

          If including this metadata all the time is too expensive, there might be a switch to turn it on.

          • 2. Re: Materialized View incorrectly issues concurrent load query
            shawkins

            > It would be helpful to include metadata in the query as it is passed down to the translator that tracks the origin or point in the plan that generated the call.  Presumably, we could have dumped this metadata and seen that the two identical queries originated from different points in the plan and that would have led us to the source of the problem more quickly.

             

            You could determine this from the command log. 

             

            > If including this metadata all the time is too expensive, there might be a switch to turn it on.

             

            Perhaps that would be an option to get the query plan from the CommandContext via the ExecutionContext.  However for all but trival cases it doesn't seem likely that you would want your translator to analyze plans.

             

            Steve

            • 3. Re: Materialized View incorrectly issues concurrent load query
              markaddleman

              You could determine this from the command log. 

               

              Good to know.  Thanks

              > If including this metadata all the time is too expensive, there might be a switch to turn it on.

               

              Perhaps that would be an option to get the query plan from the CommandContext via the ExecutionContext.  However for all but trival cases it doesn't seem likely that you would want your translator to analyze plans.

              I was thinking a manual switch to log the necessary information but since it's already available from the command log, I think Teiid already does what we need.