By default we assume that stored procedures can perform an update. Using them in a select may result in multiple executions, so in the txnAutoWrap=DETECT (or ON) mode we'll assume a transaction is needed. There are a couple of things you can do to change that. You can set the UPDATE COUNT property on the procedure to 0 to indicate that it is read-only. However if your transaction isolation level is REPEATABLE READ or SERIALIZABLE, then we'll still assume that a transaction is needed. 8.1 has made the detection logic better, but there still is room for improvement in this scenario, since the procedure is only going to get invoked once. So ultimately you can also just set txnAutoWrap to OFF to prevent any automatic wrapping. See the client guide on connection properties, or txnAutoWrap can even be set via a SET statement.
Thanks Steven, I'll take a look on UPDATE COUNT and txnAutoWrap.
Does the thrown DataNotAvailableException(timeout) affects procedure during normal (non-continuous) execution? I thought the DataNotAvailableException should be ignored if we run query in non-embedded or non-async mode?
Here is why I am asking: I am trying to build a stored procedure which will continuously generate a single row of data after specified delay intervals.
Take a look please on the attached TimerangeExecution and TimerangeExecutionFactory - the logic is very simple. I throw a couple of DataNotAvailableException right after the row of data delivered to a client. Executed continuously, it perfectly returns me a "delayed" rows of data:
"call Times.timerange(0, 50000, 10000)";
13:59:18,209 INFO [stdout] (Worker2_QueryProcessorQueue19) ### Retrieved row:[0, 10000, 10000]
13:59:28,284 INFO [stdout] (Worker4_QueryProcessorQueue28) ### Retrieved row:[10000, 20000, 10000]
13:59:38,336 INFO [stdout] (Worker5_QueryProcessorQueue37) ### Retrieved row:[20000, 30000, 10000]
13:59:48,357 INFO [stdout] (Worker3_QueryProcessorQueue47) ### Retrieved row:[30000, 40000, 10000]
When I run this query non-continuously (from Eclipse Database Executor for example), I am getting only one row (which is correct), but I also see a side-effect: a ~10 seconds delay between request passed to Teiid and results returned back (see also a sample from server.log.txt attached for this particular execution).
And the last thing:
I remember you mentioned some time ago that the delay specified by exception during continuous execution is not guaranteed, and the actual delay can be shorter ( https://community.jboss.org/thread/201581?tstart=0 ). For us will be important that interval between delivered rows will be not shorter than specified interval. So instead of just throwing a bunch of DataNotAvailableExceptions like I do above, I can record the current time during execution() method, and then in next() method - continue throwing exception until the (currentTime + delay) passed. Seems it works for me, I did not tried it under heavy load to see if executions are indeed not blocking. But I still have a feeling that I abuse continuous executions and I am forcing them to to do something which they were not originally designed for. Do you have any thoughts on that please?
> Does the thrown DataNotAvailableException(timeout) affects procedure during normal (non-continuous) execution? I thought the DataNotAvailableException should be ignored if we run query in non-embedded or non-async mode?
Yes, it is still observed regardless of the calling mode. In a non-asynch mode you could even use it as a simplistic trottling mechanism.
> When I run this query non-continuously (from Eclipse Database Executor for example), I am getting only one row (which is correct), but I also see a side-effect: a ~10 seconds delay between request passed to Teiid and results returned back (see also a sample from server.log.txt attached for this particular execution).
I'll see if I can determine what's happening.
> Do you have any thoughts on that please?
At this point it's still a pretty open ended feature, so there's plenty of room to figure out exactly what it should be. My first thought would be that the use of the DataNotAvialable with a polling delay is a fairly crude mechansim. And is really only needed in situations when you don't know if results are available. It would make more sense to use the DataNotAvailable.NO_POLLING exception and manually call ExecutionContext.dataAvailable when you want the engine to resume. An alternative would be a diiferent DataNotAvailable exception that takes a Date that would be interpretted as wait until then.
Steven, my problem is that I do not see the DataNotAvailableException.NO_POLLING is actually stops the polling process. I cannot find any good unit test availalble in teiid sources, but you might use my sample translator to verify.
I was not completely correct in my last comment. I want to have my translator to be self-sustain. If NO_POLLING called then, as I understand, something outside the translator should call the ExecutionContext.dataAvailable().
I think this could be tied to the priory based worker queues we talked before, with affecting the normal traffic. Where NO_POLLING based queries can be queued to a different worker queue. We could devise different ways that a query can be placed in here. ex: hint, DataNotAvaialble etc. What I do not understand is how does ExecutionContext.dataAvailable() can be called by translator when it will not have access to thread. If they used Timer or async threads then under heavy load this will not scale.
I am exactly suggesting the use of a Timer or related class. Scaling is not an issue (for example a single instance per ExecutionFactory or even a static one) as the callback is extremely lightweight, but I'll come back to this suggestion in a minute.
Ramesh, I'm not exactly sure what you mean by a different worker queue. When a plan has no pending work it is not in a thread pool queue - the IDLE state can be entered even for non-asynch plans for example when the plan has been blocked or has finished before the client has request all of the results.
A little more background would be good here. The original use of the asynch connector concept was for the old MMX design where you could have remote connectors using a request/response model, but there was mechanism for a callback. So the best that we could do was poll for results. The poll delay was specified as part of the connector configuration, but was refined as a general purpose exception mechanism in Teiid 6 - but still was polling based. The poll interval was supposed to be the maximum amount of time before the source was polled again. In many circumstances the engine would check sooner in the hopes that work could progress. This would occur anytime that the plan transitions back to the MORE WORK state prior to the poll interval expiration. As Andriy has seen that can even occur aginst the first execution due to the read-ahead logic in the DataTierTupleSource, or can occur if there are multiple sources involved and any one of them which had previously been blocked continues.
So for what Andriy is wanting the poll interval to be there is a mismatch with the concept. Coming back to the suggestion. Without having a different DataNotAvailable mechanism (such as one based upon a do not call me until Date similar to the java concurrent logic) the execution may get called early. That leaves the approximation mechanism as not relying on the polling delays (and using his own timer or whatever to have full control) or alternatively he can recalculate the polling delay with each attempt at an execution before rethrowing an exception.
Steven, thanks for the background info.
A small update aboout the original topic. I've set the transaction auto wrap to TXN_WRAP_OFF, and seems now I am allowed to execute "select" call to stored procedure continuously. Unfortunately, another problem arizes: the NPE in DependentProcedureAccessNode.hasNextCommand(). For some reason, during the continuous execution the number of retrieved rows doubled (look for "Retrieved row" string in attached npe.txt). I have ReusableExecution, so that's might be a reason?
UPDATE: no, it is not related to implementing the ReusableExdecution. hte problem still exist. For reference see attached the sample java class methods.
Message was edited by: Andriy Rokhmanov
A little explanation why I really would like to have stored procedure executed continuously using "SELECT..." statement. Looks like it is the only way how to call a stored procedure using WITH... clause.
The statement like "with A as (call ...)..." is not valid SQL statement, but "with A as (select ...)..." is correct.
Yes, there is probably a bug there with the NPE. I would guess that the reset of the plan state is not fully correct. I'll have to recreate your sample to understand if you are getting duplicate results. You can call a procedure as a query expression by using it as a table function. That would be SELECT * FROM TABLE (CALL ...) AS X. For legacy support the TABLE keyword is optional.
Thanks Steven for advice about SELECT * FROM TABLE, I'll try that.
Quick question: is it possible to identify from translator side what type of execution we are currently running - a continuous or no? I am not sure how I can get a RequestOptions.isContinuous() object on translator side.
CommandContext.isContinuous was added for 8.1.
I just checked: CommandContext.isContinuous() returns false when run continuously and true when executed non-continuously from Database Explorer. I guess it should be opposite?
Serves me right for not having a test. It should be better now.