Re: transaction control in pl/pgsql

On Thu, Apr 1, 2010 at 6:22 AM, Birgit Laggner
<birgit(dot)laggner(at)vti(dot)bund(dot)de> wrote:
> Dear list,
>
> I have some data (big size) and I've written a long function in pl/pgsql
> which processes the data in several steps. At a test run my function
> aborted because of memory exhaustion. My guess is, that everything what
> happens during the function transaction is stored in the memory - until
> it's full... So, my idea for solving that problem would be to cut the
> big function into smaller functions. But, I don't want to write 30
> function calls at the end - I would rather like to have one function
> which is calling all these small functions, so I would only have to
> write one sql-query at the end. What I fear is either, that, if this
> function calls the other functions, everything is only one trancaction
> again and I get memory overflow once more.

I don't know all the specifics of your case but ultimately there are
limits to what you can reasonably do in a single transaction,
especially if you are writing to the database. If you push the limit
the database starts to push back. Transactions generally should be as
short as possible. Long transactions inhibit the ability of the
database to do certain types of maintenance on itself and have other
issues like bad performance and memory exhaustion.

Regardless, of how many separate functions/savepoints/begin/end blocks
your 'outer' function calls, your entire set of work is going to
operate within the context of a single transaction. This is an iron
clad rule which (at present) there is no work around for. For this
reason certain classes of data processing must unhappily be done on
the client side, introducing another language and forcing all the data
back and forth through the protocol.

In the future, it may be possible to execute pl/pgsql-ish type of code
in the backend that allows explicit transaction control. This feature
might be a 'stored procedure', or there might be some other type of
nomenclature to distinguish functions that manage their own
transaction state.

merlin