Proposal - asynchronous functions

Asynchronous functions

*Problem*
Postgresql does not have support for asynchronous function calls.

*Solution*
An asynchronous function would allow a user to call a function and have
it return immediately, while an internal session manages the actual
processing. Any return value(s) of the function would be discarded.

*Value Added*
There are two types of primary usage for an asynchronous function:

* Building summary tables or materialized views.

* Running business logic functionality through untrusted languages

These function can be either run stand-alone or called by a trigger. The
proposed rules of an asynchronous function are:

* the result should not impact the statement run, meaning if there
is an error it should not cancel the transaction

* the user should not have to wait until the function is finished to
get control of the session back

* the long-running function should not be dependent on the user
keeping the session alive

*Current workaround*

Currently, the way to implement these types of functions are:

* Using the Listen/Notify calls.

* Adding a row to a queuing table and processing it as a cron job.

The problem with these workarounds are:

* In principal, going from the database outside, just to go back in
so that you have an external session controller, is awkward.
Listen/Notify is a great method to run a server function that is
not related to the database.

* Sometimes the connection in your daemon stops (from experience),
and there is no notification

* Some functions should be run immediately and not queued.

* They add complexity to the end user

*Proposal*

Add an Async command for functions ( ASYNC my_func(var1,var2) ) and add
an async optional keyword in trigger statements ( CREATE TRIGGER ...
EXECUTE ASYNC trig_func() ). This should cause an internal session to be
started that the function or trigger function will run in, disconnected
from the session it started in.

Sim Zacks

On Tue, Apr 26, 2011 at 3:28 AM, Sim Zacks <sim(at)compulab(dot)co(dot)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

On Tue, Apr 26, 2011 at 3:28 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> Add an Async command for functions ( ASYNC my_func(var1,var2) ) and add an
> async optional keyword in trigger statements ( CREATE TRIGGER ... EXECUTE
> ASYNC trig_func() ). This should cause an internal session to be started
> that the function or trigger function will run in, disconnected from the
> session it started in.

We've talked about a number of features that could benefit from some
kind of "worker process" facility (e.g. logical replication, parallel
query). So far no one has stepped forward to build such a facility,
and I think without that this can't even get off the ground.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> We've talked about a number of features that could benefit from some
> kind of "worker process" facility (e.g. logical replication, parallel
> query). So far no one has stepped forward to build such a facility,
> and I think without that this can't even get off the ground.

Well, this specific thing could be done by just having PG close the
client connection, not care that it's gone, and have an implied
'commit;' at the end. I'm not saying that I like this approach, but I
don't think it'd be hard to implement.

What I don't think we saw was any information about how, exactly, the OP
was planning to implement this in the backend.

Thanks,

Stephen

On Tue, Apr 26, 2011 at 8:32 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> We've talked about a number of features that could benefit from some
>> kind of "worker process" facility (e.g. logical replication, parallel
>> query).  So far no one has stepped forward to build such a facility,
>> and I think without that this can't even get off the ground.
>
> Well, this specific thing could be done by just having PG close the
> client connection, not care that it's gone, and have an implied
> 'commit;' at the end.  I'm not saying that I like this approach, but I
> don't think it'd be hard to implement.

Maybe, but that introduces a lot of complications with regards to
things like authentication. We probably want some API for a backend
to say - hey, please spawn a session with the same user ID and
database association as me, and also provide some mechanism for data
transfer between the two processes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 04/26/2011 03:15 PM, Merlin Moncure wrote:

> On Tue, Apr 26, 2011 at 3:28 AM, Sim Zacks<sim(at)compulab(dot)co(dot)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.

Sim

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Apr 26, 2011 at 8:32 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Well, this specific thing could be done by just having PG close the
> > client connection, not care that it's gone, and have an implied
> > 'commit;' at the end.  I'm not saying that I like this approach, but I
> > don't think it'd be hard to implement.
>
> Maybe, but that introduces a lot of complications with regards to
> things like authentication. We probably want some API for a backend
> to say - hey, please spawn a session with the same user ID and
> database association as me, and also provide some mechanism for data
> transfer between the two processes.

The impression I got from the OP is that this function call could be the
last (and possibly only) thing done with this connection. I wasn't
suggesting that we spawn a new backend to run it (that introduces all
kinds of complexities). The approach I was suggesting was to just have
the backend close its client connection and then process the function
and then 'commit;' and exit.

Might be interesting as a way to prefix anything, ala:

LAST delete from big_table;

poof, client is disconnected, backend keeps running, etc.

I don't know if that would really be useful to very many people or that
it's something we'd really want to do but it's an interesting idea to be
able to 'background' a process.

I'm certainly all for the bigger projects of having a cron-like
capability and/or being able to spawn off multiple backgrounded queries
from a single connection.

Thanks,

Stephen

On 04/26/2011 03:32 PM, Stephen Frost wrote:

> What I don't think we saw was any information about how, exactly, the OP
> was planning to implement this in the backend.
>
> Thanks,
>
> Stephen
I'm at stage 1 of this proposal, meaning I know exactly what I want. I
am checking with the hackers list to see if this is a desirable feature
before going to a postgres developer to talk about actually building the
feature.

On 04/26/2011 04:22 PM, Stephen Frost wrote:

> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Tue, Apr 26, 2011 at 8:32 AM, Stephen Frost<sfrost(at)snowman(dot)net> wrote:
>>> Well, this specific thing could be done by just having PG close the
>>> client connection, not care that it's gone, and have an implied
>>> 'commit;' at the end. I'm not saying that I like this approach, but I
>>> don't think it'd be hard to implement.
>> Maybe, but that introduces a lot of complications with regards to
>> things like authentication. We probably want some API for a backend
>> to say - hey, please spawn a session with the same user ID and
>> database association as me, and also provide some mechanism for data
>> transfer between the two processes.
> The impression I got from the OP is that this function call could be the
> last (and possibly only) thing done with this connection. I wasn't
> suggesting that we spawn a new backend to run it (that introduces all
> kinds of complexities). The approach I was suggesting was to just have
> the backend close its client connection and then process the function
> and then 'commit;' and exit.
>
My thought was that it actually would require its own process. One use
case is a function might be called from within another function, but it
does not want to wait for a return. Then the original function would
finish processing and return. The second function would be run with the
security of the user who called the function, but would be "managed" as
a separate connection without a client (or as a client on the server to
be more precise)

Sim

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(at)compulab(dot)co(dot)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

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Apr 26, 2011 at 10:02 AM, David Fetter <david(at)fetter(dot)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(at)compulab(dot)co(dot)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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Apr 26, 2011 at 9:24 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Apr 26, 2011 at 10:02 AM, David Fetter <david(at)fetter(dot)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(at)compulab(dot)co(dot)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.

merlin

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

> On Tue, Apr 26, 2011 at 9:24 AM, Robert Haas<robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Apr 26, 2011 at 10:02 AM, David Fetter<david(at)fetter(dot)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(at)compulab(dot)co(dot)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

On Tue, Apr 26, 2011 at 1:15 PM, Sim Zacks <sim(at)compulab(dot)co(dot)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?"

Robert,

On 04/26/2011 02:25 PM, Robert Haas wrote:
> We've talked about a number of features that could benefit from some
> kind of "worker process" facility (e.g. logical replication, parallel
> query). So far no one has stepped forward to build such a facility,
> and I think without that this can't even get off the ground.

Remember the bgworker patches extracted from Postgres-R?

[ Interestingly enough, one of the complaints I heard back then (not
necessarily from you) was that there's no user for bgworkers, yet.
Smells a lot like a chicken and egg problem to me. ]

Regards

Markus

Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> On 04/26/2011 02:25 PM, Robert Haas wrote:
>> We've talked about a number of features that could benefit from
>> some kind of "worker process" facility (e.g. logical replication,
>> parallel query). So far no one has stepped forward to build such
>> a facility, and I think without that this can't even get off the
>> ground.
>
> Remember the bgworker patches extracted from Postgres-R?

Yeah, that crossed my mind.

> [ Interestingly enough, one of the complaints I heard back then
> (not necessarily from you) was that there's no user for bgworkers,
> yet. Smells a lot like a chicken and egg problem to me. ]

My recollection is that people wanted two or three solid use cases
so that what was implemented could be shown to be generalized.
Perhaps this brings us to critical mass to re-introduce the idea.

-Kevin

On Apr 26, 2011, at 3:32 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> Remember the bgworker patches extracted from Postgres-R?

Oh, right. I should have remembered that.

> [ Interestingly enough, one of the complaints I heard back then (not
> necessarily from you) was that there's no user for bgworkers, yet.
> Smells a lot like a chicken and egg problem to me. ]

IIRC, we kind of got stuck on the prerequisite wamalloc patch, and that sunk the whole thing. :-(

...Robert

On 04/26/2011 11:17 PM, Robert Haas wrote:
> IIRC, we kind of got stuck on the prerequisite wamalloc patch, and that sunk the whole thing. :-(

Right, that prerequisite was the largest stumbling block. As I
certainly mentioned back then, it should be possible to get rid of the
imessages dependency (and thus wamalloc). So whoever really wants to
implement asynchronous functions (or autonomous transactions) is more
than welcome to try that.

Please keep in mind that you'd need an alternative communication path.
Not only for the bgworker infrastructure itself, but for communication
between the requesting backend and the bgworker (except for
fire-and-forget jobs like autovacuum, of course. OTOH even those could
benefit from communicating back their state to the coordinator.. eh..
autovacuum launcher).

Regards

Markus

It sounds like there is interest in this feature, can it get added to
the TODO list?