Re: pgsql functions and transactions?

I'm not touching a lot of different tables per se, but I have about 10 functions that each process one or more cursor that all combined end up creating about 45,000 records. The functions cascade. In otherwords the first function gets the associations, then for each association a function gets all the facilities, then for each facility I do one set of calculations, then for each of those calculations I do another set of calculations. That continues for about 500 facilities. Then I go back through and combine facilities into different groupings and again do all the calculations. So, there is a lot of processing going on.

And like I said, I get the error on my development box with 512 M of RAM. Production has 3 G of RAM. Maybe I won't run into this issue on production, but I'm trying to solve it on development so that I don't have to worry about it in production. Note: I'm trying to replace some functionality that is currently running in J2EE java objects and takes 12 hours with this set of stored procedure functions, and this is extremely important as I'm sure you understand.

Tom,
Can I ask you what you mean by "are you touching a whole lot of different tables in one transaction? " Do I have a transaction? Where is it? Does it start when I am at the top of the first function and end when I complete that main function? Or do I have separate transactions at the beginning and end of each subfunction?

Thank you,

Betsy Barker

On Thu, 26 Aug 2004 23:09:12 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com> writes:
> > WARNING: ShmemAlloc: out of memory
> > WARNING: Error occurred while executing PL/pgSQL function get_facility_percentiles
> > WARNING: line 37 at execute statement
> > ERROR: LockAcquire: lock table 1 is out of memory
>
> Hmm, are you touching a whole lot of different tables in one
> transaction? If so you may need to raise the max_locks_per_transaction
> parameter.
>
> If that doesn't help, we need more details about what you're doing.
>
> regards, tom lane
>

--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38