2010/4/1 Birgit Laggner <birgit.laggner@xxxxxxxxxxx>: > 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. plpgsql can you implicit subtransaction - every block with protected section is evaluated under subtransaction. But I don't think, so subtransaction help in your case. You do some what is memory expensive - example: larger SRF function in plpgsql, badly used hash aggregation, maybe using of deffered triggers. Subtransaction doesn't help. Try to use temp tables instead. Regards Pavel Stehule > > I've read the documentation regarding this on > http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html : > > "It is important not to confuse the use of BEGIN/END for grouping > statements in PL/pgSQL with the similarly-named SQL commands for > transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do > not start or end a transaction. Functions and trigger procedures are > always executed within a transaction established by an outer query — > they cannot start or commit that transaction, since there would be no > context for them to execute in." > > Somewhere else I've read: "PostgreSQL does not have nested transactions." > > I'm still not sure if I got it right or if there are other possibilities > to solve my problem. Any suggestions would be appreciated! > > Thanks and regards, > > Birgit. > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
