Re: Proposal - asynchronous functions

On Tue, Apr 26, 2011 at 1:15 PM, Sim Zacks <sim@compulab.co.il> wrote:
> We have tried a similar approach, using plpythonu, by calling import pg and
> then creating a new connection to the database. This does give you an
> autonomous transaction, but not an asynchronous function.
> My use cases are mostly where the function takes longer then the user wants
> to wait and the result is not as important to the user as it is to the
> system.
> One example is building a summary table (materialized view if you will).
> Lets say building the table takes 10 seconds and is run on a trigger for
> every update to a specific table. When the user updates the table he doesn't
> want to wait 10 seconds before the control returns.
> Another example, is a plpythonu function that FTPs a file. The file can take
> X amount of time to send and the user just needs to know that it has been
> sent. If there is a problem the user will not be informed about it directly.
> There are ways of having the function tell the system (either email or error
> table or marking a bool flag, etc) and by using this type of function the
> user declares that he understands that something might go wrong and he won't
> get a message about it. The user may also turn off his computer before the
> file is finished sending.

There's a pretty big "foot gun" there in that there's the potential
for each connection coming in from a client to spawn a further
connection that *doesn't* go away when the client does.  There's a
not-inconsiderable risk of having a ballooning set of
"post-processing" connections lurking around.

That doesn't have to be problematic, within the context of a
reasonable design.  For such cases, the "thing that lurks afterwards"
shouldn't the process that does "the postprocessing for MY
connection", but rather a singleton process (e.g. - it does something
to ensure that There Can Only Be One) that does postprocessing of that
kind of activity.

The "asynchronous bit" would consist of something like:
- queueing up My Connection's Object IDs for processing
- trying to start the singleton asynchronous process, failing,
gracefully (e.g. - without terminating any of the client's work) if
that fails.

An extra use case for this leaps out at me immediately.

It would be a plenty fine idea for a NOTIFY request to cause
asynchronous invocation of a specified stored procedure.  That would
definitely "spiff up" the usefulness of NOTIFY/LISTEN, by adding a way
of having a listener process already available on the server.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"