dynamobi/dynamobi — Issue #817: [FRG-53] support for state-saving across user-defined routine invocations
Author: dynamobi-build
Created: 2011-12-30 03:50:27+00:00
State: open
Labels: FRG, Runtime

[reporter="jvs", created="Tue, 21 Feb 2006 15:12:09 -0500 (GMT-05:00)"]
It is often useful to be able to maintain state across invocations of a UDR. The most common case is when precompilation is used as an optimization. For example, a UDF may take a regular expression as a literal parameter, compile an instance of java.util.regex.Matcher, and use it to process another parameter for each invocation. Another use is for writing non-deterministic UDR's (e.g. a random-number generator UDF which needs to use the same underlying java.util.Random object across invocations).

The SQL standard external routine mechanism only allows binding to a static method, without any notion of context across invocations. Static data members are not a good solution because (a) they can be accessed from multiple threads, so they require synchronization, and (b) they can result in memory bloat since a UDR class which maintains a map or pool has no information about when an object is no longer needed (other than garbage collector memory pressure if something like a SoftHashMap is used).

The proposed feature is to enhance Farrago's runtime system to address this requirement, because Farrago has the best notion of what's going on during execution. To the UDR, it would look something like thread-local storage: the ability to set an Object value, load the current value, and receive a notification to deallocate any associated resources (when query execution completes). The actual interface remains to be defined; it will be a class with static methods. FarragoRuntimeContext would take care of context-switching by setting a real thread-local before each UDR invocation, so that each usage of a UDR within a particular running instance of a SQL statement would see its own private state. For example, in the following query,

select rand() as x, rand() as y

from t;

the two rand usages would have independent state (as would any executions of the same cached query from other sessions).

Note that reliance on this feature would make a UDR implementation dependent on Farrago (i.e. not portable to other SQL implementations).