Thread: Transactions within plpgsql functions?
Is there any way to have transactions work inside plpgsql procedures? So far none of my attempts have worked. It's kind of frustrating to have to wait days for results and especially so when the procedure terminates unsuccessfully saying that I've done too many operations (more than 2**32 database operations in my setup). Yes - I know that I could have procedures calling procedures to get what I want, but this seems a little awkward. Thanks for any assistance. Celia McInnis
On Mon, Jun 27, 2005 at 10:02:16AM -0500, Celia McInnis wrote: > > Is there any way to have transactions work inside plpgsql procedures? So far > none of my attempts have worked. It's kind of frustrating to have to wait > days for results and especially so when the procedure terminates > unsuccessfully saying that I've done too many operations (more than 2**32 > database operations in my setup). If the only trouble is that you're exceeding the limit on the number of operations in a transaction, then you might benefit from using a language other than PL/pgSQL that doesn't increment the command counter for every little thing it does. Whether that'll help depends on how many non-database operations you're performing (arithmetic calculations, string manipulation, etc.). If you'd like functions to continue after other kinds of errors and if you're using at least 8.0, then you could use PL/pgSQL's error- trapping mechanism. http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Do you really have functions that take days to run, or was that an exaggeration? What are you doing that performs so many operations? Is that the problem you're trying to solve, or is it something else? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Thanks for your response. I'll respond to your questions/comments in line: On Mon, 27 Jun 2005 15:41:29 -0600, Michael Fuhr wrote > > If the only trouble is that you're exceeding the limit on the number > of operations in a transaction, then you might benefit from using > a language other than PL/pgSQL that doesn't increment the command > counter for every little thing it does. Whether that'll help depends > on how many non-database operations you're performing (arithmetic > calculations, string manipulation, etc.). Which language? I like the look of plperl but actually didn't use it because it is likely that I will be forced to move my work into oracle :-( at some point and it looked as if plpgsql is fairly close to oracle's procedural language. Any advice here would be much appreciated. > > If you'd like functions to continue after other kinds of errors and > if you're using at least 8.0, then you could use PL/pgSQL's error- > trapping mechanism. I'm using 8.0.1. I'll check into the error trapping, but my function is producing correct results, just VERY SLOWLY. > http://www.postgresql.org/docs/8.0/static/plpgsql-control- > structures.html#PLPGSQL-ERROR-TRAPPING > > Do you really have functions that take days to run, or was that an > exaggeration? Yes, I have functions which take days to run, this was not an exaggeration. Being new to postgresql, I may be doing something stupid that's resulting in the slowness, but, everything looks legitimate to me... The one thing that I have found awkward is dealing with nulls when inserting the results in my output table. (I use the COALESCE function for every entry in my insert command). > What are you doing that performs so many operations? Calculating sets of quartiles on all kinds of subsets of columns in large datasets, etc., and yes, the columns are indexed. > Is that the problem you're trying to solve, or is it something else? Yes - it would be good to speed up the function. If it happened "quickly", I wouldn't care about solving partial work.
On Tue, Jun 28, 2005 at 09:23:24AM -0500, Celia McInnis wrote: > On Mon, 27 Jun 2005 15:41:29 -0600, Michael Fuhr wrote > > > > If the only trouble is that you're exceeding the limit on the number > > of operations in a transaction, then you might benefit from using > > a language other than PL/pgSQL that doesn't increment the command > > counter for every little thing it does. > > Which language? I like the look of plperl but actually didn't use it because > it is likely that I will be forced to move my work into oracle :-( at some > point and it looked as if plpgsql is fairly close to oracle's procedural > language. Any advice here would be much appreciated. I had in mind PL/Perl, PL/Python, or PL/Tcl -- the standard languages that PostgreSQL supports -- but there are also third-party implementations for Ruby, Java, R, etc., and there's always C :-) But if you anticipate porting away from PostgreSQL and similarity to PL/pgSQL is an issue, then you might be stuck. > > Do you really have functions that take days to run, or was that an > > exaggeration? > > Yes, I have functions which take days to run, this was not an exaggeration. > Being new to postgresql, I may be doing something stupid that's resulting in > the slowness, but, everything looks legitimate to me... The one thing that I > have found awkward is dealing with nulls when inserting the results in my > output table. (I use the COALESCE function for every entry in my insert > command). You might want to ask for help in pgsql-performance. If you're missing anything that could improve efficiency then the people on that list might be able to help. > > What are you doing that performs so many operations? > > Calculating sets of quartiles on all kinds of subsets of columns in large > datasets, etc., and yes, the columns are indexed. Again, pgsql-performance might be able to help verify that queries are being performed as efficiently as possible. Also, if you're doing statistical analysis, then you might want to check out PL/R. http://gborg.postgresql.org/project/plr/projdisplay.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/