Continuous queries and Connection pools

[markaddleman]

We are writing a continuous query aware H2 translator.  The basic notion is that the execution factory's getMetadata method adds triggers to listen for inserts, upates and deletes on each table in H2.  The desired behavior of the translator is that when a client issues a continuous query, the translator returns results immediately and then waits until the trigger indicates a change in any of the tables involved in the execution, then re-queries.  The problem is how connections are handled.  While the translator is waiting, there's no reason to keep the JDBC connection out of the pool but I can't find good lifecycle method to throw DataNotAvailable.  I'm beginning to believe there either needs to be a new form of DNA that instructs Teiid to close the execution and restart the lifecycle entirely on dataAvailable() or a new lifecycle method that occurs between Execution.close() and reset() that provides an opportunity to throw DNA after the connection is closed.  What do you think?

Right now, my temporary solution is to override ExecutionFactory's getConnection() and return a lazy initializing connection but that's pretty dirty.

[shawkins]

> While the translator is waiting, there's no reason to keep the JDBC connection out of the pool but I can't find good lifecycle method to throw DataNotAvailable.

The usage paradigm with DataNotAvailable before was for asynch executions.  Typically an asynch execution would not hold a connection or any large amount of state.

> I'm beginning to believe there either needs to be a new form of DNA that instructs Teiid to close the execution and restart the lifecycle entirely on dataAvailable()

You can currently perform a restart on your end if all of your translators  report back that their at the end of rows, then the next execution will start after any lingering processing is finished. 

> or a new lifecycle method that occurs between Execution.close() and reset() that provides an opportunity to throw DNA after the connection is closed.

There's nothing from the engine's perspective that is happening between close and reset that it would need to ask the translator/execution about.  What would a DNA after close but before reset mean?

> Right now, my temporary solution is to override ExecutionFactory's getConnection() and return a lazy initializing connection but that's pretty dirty.

That's probably along the lines of the right approach (at least with how Teiid works currently).  It seems like the only bit that's really dirty is hardcoding how the ExecutionFactory gets the DataSource, or do you see any other downfalls?

Steve

[markaddleman]

> I'm beginning to believe there either needs to be a new form of DNA that instructs Teiid to close the execution and restart the lifecycle entirely on dataAvailable()

You can currently perform a restart on your end if all of your translators  report back that their at the end of rows, then the next execution will start after any lingering processing is finished. 

> or a new lifecycle method that occurs between Execution.close() and reset() that provides an opportunity to throw DNA after the connection is closed.

There's nothing from the engine's perspective that is happening between close and reset that it would need to ask the translator/execution about.  What would a DNA after close but before reset mean?

I'd like to pause the execution after ExecutionFactory.closeConnect() is called and before the next ExecutionFactory.getConnection().  Perhaps the proper solution is twofold:  (1) Don't use a reusable execution and (2) Teiid introduces a new lifecycle method to the execution factory.  It would be something like void beforeNextExecution(Command, ExecutionContext, RuntimeMetadata).  It would be called before getConnection() on subsequent executions in a continuous query.  Execution factories would be allowed to throw DNA from here.  Calling dataAvailable() would, ideally, begin the lifecycle at getConnection() followed by createExecution() rather than calling into beforeNextExecution again.

> Right now, my temporary solution is to override ExecutionFactory's getConnection() and return a lazy initializing connection but that's pretty dirty.

That's probably along the lines of the right approach (at least with how Teiid works currently).  It seems like the only bit that's really dirty is hardcoding how the ExecutionFactory gets the DataSource, or do you see any other downfalls?

No, that's the only downside I see.  It's really not that bad.

[shawkins]

> (1) Don't use a reusable execution and

Reusable seems fine as you still are getting a close at the end of each execution cycle.

> (2) Teiid introduces a new lifecycle method to the execution factory.  It would be something like void beforeNextExecution(Command, ExecutionContext, RuntimeMetadata).

How does the executionfactory (rather than an execution) without a connection make the determination that data won't be available?  With the current logic the preference would be to throw DataNotAvailable from the subsequent execute rather than putting in another lifecycle method. 

The core issue for you still seems to be defeating the standard connection lifecycle, which would not be a common case.   This could be tied to the reusable extension lifecyle - for example close the connection if reset throws a DataNotAvailable and call reset even on the first execution.  At worst you would just be obtaining a connection and quickly closing it.  Or we can simply provide you with a reference to the connection factory to facilitate the customization of taking over the connection management.

Steve