On Thu, Apr 1, 2010 at 6:22 AM, Birgit Laggner <birgit.laggner@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
