Re: foreign keys and memory consumption

On Tue, 2007-10-09 at 11:28 +0200, Jan Poslusny wrote:

> pg 8.2.4 on Fedora Core 6 x86-64, mostly default postgres.conf just
> shared memory buffers increased to 256M. 1GB RAM.
> I attempt to insert ~200k rows into table in one transaction from psql
> console, calling stored function of plperlu language, which inserts row
> by row via spi_exec_prepared. If table contains some foreign key
> (id_parent int4 not null references this_table(id) on delete cascade),
> memory allocated for processing child process grows and grows (roughly,
> 1GB for first 100k rows is consumed) and is not released neither after
> successful end of transaction nor after Ctrl-C.
> If table does not contain that foreign key, consumed memory does not
> grow in this way - all is O.K.
> I do not know if I am missing something or if it is necessary or if it
> is memory leak. If somebody will be interesting, I can provide my
> postgres.conf and I can write script isolating and demonstrating this
> phenomena.

It's a known side effect of the way FKs work currently.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com