Skip site navigation (1) Skip section navigation (2)

Re: Transactions within plpgsql functions?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Celia McInnis <celia(at)drmath(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Transactions within plpgsql functions?
Date: 2005-06-27 21:41:29
Message-ID: 20050627214129.GA16543@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-06-28 05:40:32
Subject: Re: DANGER Windows version might hurt you
Previous:From: Michael GlaesemannDate: 2005-06-27 20:41:07
Subject: Re: PostGis Extension

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group