postgresql pl/sh asynchronous execution of external process

Question

I have a postgresql-plsh function

    CREATE OR REPLACE FUNCTION MDN_REG_LOCATE(MDN VARCHAR(50), CALLID VARCHAR(50)) RETURN AS '
    #!/bin/sh
    /home/infoobjects/Projects/java/execute.sh $1 $2 &
logger -t "data" "$1$2"
    ' LANGUAGE plsh;

and execute.sh call's a java process(method) which takes 3 minutes to execute . I have made the script asynchronous with appending & at the end of script(execute.sh) My problem is that the postgresql function still waits for the result to come and does not act in asynchronous manner although the shell script behaves asynchronously because logger in above function logs up after jst a call to MDN_REG_LOCATE() but still this postgresql function(MDN_REG_LOCATE) waits for complete process of 3 minutes to over , I don't know what I am lacking , please help me in this .

Thanks In Adv.

First, PL/sh is usually the wrong answer. You're likely much better off with a client that `LISTEN`s and a trigger that sends a `NOTIFY` when it's time to do the job. The client can then execute the task with the parameters sent in the `NOTIFY` or fetched from a queue table. — Craig Ringer, Oct 11 '12 at 12:18

score 3 · Answer 1 · edited May 23 '17 at 10:24

Simply backgrounding a process isn't enough; it's still attached to its parent process. See this answer.

You're likely to be much better off reworking your logger program to keep a persistent connection to the database where it LISTENs for tasks. Your client (or a trigger, or whatever would normally invoke your PL/Sh function) sends a NOTIFY with the parameters as a payload, or (for older Pg versions) INSERTs a row into a queue table then sends a NOTIFY to tell the listening client to look at the queue table.

The listening client can then run the background process with no worries about holding up the database. Best of all, the NOTIFY is transactional; it's only delivered when the transaction that sent it commits, at the time it commits.

postgresql pl/sh asynchronous execution of external process

1 Answers1