Fwd: 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.