Increased memory utilization by pgsql backend after upgrade from 9.1.3 to 9.2.6

Hi,

We upgraded our PG version from 9.1.3 to 9.2.6. After that, noticed a huge
jump in the memory consumed by PG backend process during a delete query on
one of our DB tables. The heap memory as reported in /proc/PID/smaps
increased from 25MB to 600 MB. There are quite a few triggers setup on this
table and I determined that the jump happens when one particular trigger
function (SQL function) is exceuted. The queries executed in this function
(around 3000 line SQL function) are quite complex, quite a few unions,
joins on tables containing around 100K records. The function is created
using the syntax:

CREATE OR REPLACE FUNCTION FUNC1 (p_old TABLETYPE, p_new TABLETYPE, p_id
character(50), p_event_type text)
RETURNS integer AS
$BODY$

......
......
......

$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

The memory gets released once the backend process exits but in our
application we have multiple threads opening connections to PG and
executing these delete queries. Connection pooling is being used and this
memory is not getting released when the connection is idle; so at one point
the machine goes OOM.

For the upgrade, we have migrated the data used pg_dump and pg_restore.

System Configuration:
CentOS release 5.6 (Final)
Mem: 9 GB
CPU : 4 core - Intel(R) Xeon(R) CPU E5620 @ 2.40GHz

PostgresQL configuration:
max_connections = 250
shared_buffers = 1536MB
work_mem = 12MB
maintenance_work_mem = 384MB
effective_cache_size = 3072MB

I have used psql to test the delete queries and used /proc/PID/smaps to
check the memory usage of the launched backend.

The heap in 'smaps' is shown as below:
04e3c000-29a70000 rw-p 04e3c000 00:00 0
[heap]
Size: 602320 kB
Rss: 596596 kB
Shared_Clean: 0 kB
Shared_Dirty: 60 kB
Private_Clean: 0 kB
Private_Dirty: 596536 kB
Swap: 0 kB
Pss: 596544 kB

We are using the RHEL-5 64-bit PotgresQL RPMs present on the PG website:
postgresql92-9.2.6-1PGDG.rhel5.x86_64.rpm
postgresql92-contrib-9.2.6-1PGDG.rhel5.x86_64.rpm
postgresql92-libs-9.2.6-1PGDG.rhel5.x86_64.rpm
postgresql92-server-9.2.6-1PGDG.rhel5.x86_64.rpm

I have now compiled the PG source code and installed a version with debug
symbols on the same machine. But I do not know how to determine what is
taking up so much memory. Is there any data, that I can collect by
connecting gdb, which will help ?

I tried getting the memory stats (enabled SHOW_MEMORY_STATS) and captured
the attached data but I do not know how to interpret this data and also not
sure whether this statistics captures the heap memory used by the SQL
function.

Queries I have are:

1. Has there been any change in the way memory is allocated/released when
SQL functions are triggered in a backend?

2. How can I determine what is taking up so much memory; basically how do I
proceed further on this one?

3. I guess it is some data which is cached when the SQL function runs the
first time in the backend because if I delete another row of the same table
in the same PSQL session the memory does not jump again by that amount. Is
there a way to indicate that such caching should not be done?

Thanks,
Datta.