Re: transaction control in pl/pgsql

2010/4/1 Birgit Laggner <birgit(dot)laggner(at)vti(dot)bund(dot)de>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>