Re: dirty read from plpgsql

On 6/07/2011 6:54 PM, Willy-Bas Loos wrote:
> I'd like to do a dirty read from plpgsql, so that i can stop the
> function that is in a long loop without rolling back the work that it
> did.
> All i want to read is a flag that says 'stop'.
> I've figured 2 ways of doing that, both of which i don't like very much.
> * with a sequence: while value = nextval(seq)-1 loop  . To stop, run
> nextval(seq) from another session.
> * with copy: run copy from within the function. To stop, overwrite the
> file that is to be copied into the function.

Another similarly icky option: every n iterations, release and re-take
an advisory lock using the try_ versions of the functions. Have your
loop cancelling function take the lock and hold it. When the next lock
check of the long-running function comes around it'll fail to get the
lock and can bail out.

Are GUC changes visible across sessions? If so, a custom GUC might be
another way to do it. I haven't tried or tested this.

Finally, if you don't mind file-system access every 'n' iterations, you
can use a plperlu (or whatever your PL of choice is) function to test
for the presence of an empty marker file somewhere readable by the
server. You can create that file to stop the big batch job, either using
another plperlu function or via the shell.

> "use a different language" has occurred to me. It is an option,
> especially if there's no better way to stop plpgsql.
>
All you'd have to do is call out to a filesystem-access-capable
language. It'd be more efficient to port the whole function, though,
you're right.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/