Re: Proposal - asynchronous functions

On 04/26/2011 06:32 PM, Merlin Moncure wrote:

> On Tue, Apr 26, 2011 at 9:24 AM, Robert Haas<robertmhaas@gmail.com>  wrote:
>> On Tue, Apr 26, 2011 at 10:02 AM, David Fetter<david@fetter.org>  wrote:
>>> On Tue, Apr 26, 2011 at 04:17:48PM +0300, Sim Zacks wrote:
>>>> On 04/26/2011 03:15 PM, Merlin Moncure wrote:
>>>>
>>>>> On Tue, Apr 26, 2011 at 3:28 AM, Sim Zacks<sim@compulab.co.il>    wrote:
>>>>>> Asynchronous functions
>>>>>>
>>>>>> *Problem*
>>>>>> Postgresql does not have support for asynchronous function calls.
>>>>> Well, there is asynchronous support from the client of course.  Thus
>>>>> you can set up a asynchronous call back to the database with dblink.
>>>>> There is some discussion about formalizing this feature -- you might
>>>>> want to read up on autonomous transactions and how they might be used
>>>>> to do what you are proposing.
>>>>>
>>>>> merlin
>>>> I am looking for specifically server support and not client support.
>>>> Part of the proposal is that if the client goes away, it will still
>>>> continue to finish.
>>> This is exactly autonomous transactions.  Please read this thread to
>>> see how.
>>>
>>> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
>> It's not the same thing at all.  An autonomous function is (or appears
>> to be) two simultaneous toplevel transactions within the same backend.
>>   This is a request for an *asynchronous* function, which would run
>> concurrently with foreground processing.
> It's not exactly the same, but in the greater spirit of things I think
> David is correct.  If you make async dblink call, you get parallel
> processing from a single function entry point.   Autonomous
> transaction implementations I've heard are basically taking this
> approach and de-kludging it, and give you a lot of the same stuff,
> like being able to do work in parallel.  I'm curious if the feature
> meets the OP's requirements.
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.

Sim