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
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