Skip site navigation (1) Skip section navigation (2)

pgsql functions and transactions?

From: Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: pgsql functions and transactions?
Date: 2004-08-26 18:18:57
Message-ID: 20040826121857.6f0f0813.betsy.barker@supportservicesinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
I read the posts on pgsql functions and transactions from the Novice list, and I think they are saying "PGSQL functions do not support transactions". Is this correct? Or is the idea that functions are automatically in a transaction?

My functions are processing a lot of data, and I'm getting the following error after 2 hours of processing on my development box:

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

I thought if I put begin transaction/commit transaction around pieces of the functions, the database could let go of some locks and I could process all the way through, as well as preserve some of the results. As it is now, I believe all the results are rolledback after the memory error. However, I cannot seem to get the begin transaction/commit in the correct places.

Any insight and/or assistance with how to solve this memory problem/rollback issue would be greatly appreciated.

Best Regards,

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

Responses

pgsql-novice by date

Next:From: Luiz K. MatsumuraDate: 2004-08-27 00:05:03
Subject: Re: DISTINCT ordering
Previous:From: MikeDate: 2004-08-26 18:12:08
Subject: Re: pgsql functions and transactions?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group