Re: Transactions within plpgsql functions?

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.