On Tue, Apr 26, 2011 at 1:15 PM, Sim Zacks <s...@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?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
