From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Birgit Laggner <birgit(dot)laggner(at)vti(dot)bund(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: transaction control in pl/pgsql |
Date: | 2010-04-01 10:51:05 |
Message-ID: | m2j162867791004010351of991462do2ae6ddcf1f4aae71@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Nikhil G. Daddikar | 2010-04-01 10:55:15 | Re: "1-Click" installer problems |
Previous Message | Grzegorz Jaśkiewicz | 2010-04-01 10:35:09 | Re: transaction control in pl/pgsql |