I'm not touching a lot of different tables per se, but I have about 10 functions that each process one or more cursor
thatall 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
intothis 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
hourswith 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@sss.pgh.pa.us> wrote:
> Betsy Barker <betsy.barker@supportservicesinc.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