Continuous Support for INSERT INTO / MERGE INTO ?

[markaddleman]

We have queues that we need hardened into tables for later processing.  For convenience, we wire the queues up to translators supporting continuous queries.  It would be super convenient to start our application with a series of continuous queries of the form INSERT INTO table_x SELECT * FROM queue_x and let Teiid manage the data transfer.  Although a little less compelling for us, it would also be nice to support continuous MERGE INTO ... SELECT FROM... as well.

I haven't written custom translators that support INSERT statements, so I'm not sure how if a continuous query would complicate transaction mgmt but I suspect not.

[markaddleman]

It occurs to me that with TEIID-2577, I could write a translator that exposes a stored procedure to accomplish this.  The stored procedure would take two parameters, a SELECT statement and a temp table name.  The semantics would be to perform the equivalent of INSERT INTO/SELECT FROM continuously if the stored procedure was executed in continuous mode.  When the client cancelled the stored procedure statement, it would shut down the INSERT INTO/SELECT FROM processing.  I suppose, the stored procedure could also return a result set indicating the number of rows processed on every batch.

Thoughts?

[shawkins]

The reluctance to support continuous updates is around the scope of the transaction and the client/server handling of the results.  What you're asking for is a slight variation of that which is piping the continuous select into a table.  The stored procedure approach should work, or I'll make sure that you can do the same thing using an anonymous procedure block with 8.5:

stmt.submitExecute("begin merge into tbl select * from something; select rowcount; end");

...

Steve

[markaddleman]

> The reluctance to support continuous updates is around the scope of the transaction and the client/server handling of the results

I suspected as much. 

> stmt.submitExecute("begin merge into tbl select * from something; select rowcount; end");

I want to make sure I understand the execution restart semantics.  Normally, an execution begins again when all participating data sources indicate dataAvailable.  In the stored proc case, I assume this is true for each SELECT individually so the MERGE command begins as soon as the "something" table is ready, then the second SELECT begins as soon as its source is ready (in your example, I guess there is no second source but in general...) and so on until all statements within the proc are completed.  Then, the engine begins executing the stored proc again:  the MERGE command begins again as soon as the "something" table is ready and so on.

Is that right?  If so, I think we have a winner.

[shawkins]

Yes that is how it would be processed.  A test showing this was added - https://github.com/teiid/teiid/blob/master/test-integration/common/src/test/java/org/teiid/systemmodel/TestAsynch.java testAsynchContinuousMergeBlock

The alternative would be to use a non-windowed style of result from the select, such that it would just throw data not available until completed - the trick there is that the translator would need to know when to stop feeding results.

Steve