[GENERAL] foreign keys and memory consumption
Hi all, 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. Regards, pajout ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
