Re: Transactions within plpgsql functions?

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/