Re: [GENERAL] dirty read from plpgsql
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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dirty read from plpgsql
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
