Postgres Pro
Downloads
Home   >   mailing lists

Thread: foreign keys and memory consumption

foreign keys and memory consumption

From
Jan Poslusny
Date:
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

Re: foreign keys and memory consumption

From
Simon Riggs
Date:
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