Thread: dirty read from plpgsql

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.

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/

On Wed, Jul 06, 2011 at 12:54:21PM +0200, 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'.

this doesn't need dirty read.
just read committed.
make table with flags, and insert there row which says "stop". make sure
the insert gets committed.

every so often, in your function check flags in the table, and since the
change got committed - it will be visible, and function will stop.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

erm, you're right (re-tested that today)
I don't know what happened the other day. The query updating the flag
would not return until the test function was done.
I must have made the test duration too short, so that it was only appearances.
whatever, it works. thanks.

WBL

On Wed, Jul 6, 2011 at 2:36 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Wed, Jul 06, 2011 at 12:54:21PM +0200, 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'.
>
> this doesn't need dirty read.
> just read committed.
> make table with flags, and insert there row which says "stop". make sure
> the insert gets committed.
>
> every so often, in your function check flags in the table, and since the
> change got committed - it will be visible, and function will stop.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact with it.
>                                                             http://depesz.com/
>



--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw