"Ioannis Anagnostopoulos" <ioannis@anatec.com> writes:
> This is my first post here and as I wanted to play it safe my first option was the novice list. My problem is as
follows:
> I have a simple select command that I need for some reason to execute it around 22million times. In fact it is a
preparedcommand from a .net environment. I have used both ado.net postgres options (the freeware NPGSQL and the
commercialfrom Devart) and the behaviour is the same. What happens is that once I establish the connection with the
correspondingconnection object, a new process is created on the server as expected. When I start iterating executing
theselect command as time goes by the memory footprint of the process grows bigger and bigger. Sooner or later the
servereither collapses or becomes unworkable as the physical memory gets exhausted and the swap is heavily used. This
isjust a simple select, with a data reader that I make sure to close after each iteration. I find no reason why the
connectionprocess on the server gets so out of control. Is it caching data? If yes I don't want to. Do I need to close
theconnection every so often to free up the memory?
This problem is not resolvable with the amount of information you've
provided. Your description makes it sound like you've run into a
server-internal memory leak. While those are certainly possible, we
don't find one very often, which is why Alan was skeptical about whether
the memory growth was in the server process or not. It seems at least
as likely that you've misdiagnosed what's happening. Also, even if
there is a memory leak, a well-configured system will normally fail with
an "out of memory" error before it gets into the kind of distress that
you're describing.
So at this point I'm wondering which PG version you're running, what
non-default configuration settings you've selected, and how much RAM the
box has got. It might also be useful to see the specific query you're
running and the output that EXPLAIN gives for it.
regards, tom lane
