Re: how to avoid that a postgres session eats up all the memory

Clemens Schwaighofer wrote:
> On 01/22/2009 07:11 PM, Richard Huxton wrote:
>> Clemens Schwaighofer wrote:
>>> Hi,
>>>
>>> I just literally trashed my test server with one delete statement
>>> because the psql used up all its memory and started to swap like crazy.
>>>
>>> my delete looked like this
>>>
>>> DELETE FROM table_foo WHERE bar_id IN (SELECT DISTINCT bar_id FROM bar
>>> LEFT JOIN ... LEFT JOIN ... LEFT JOIN .... WHERE  table.bar_id IS NULL
>>> AND ...)
>> Is it your psql client or PostgreSQL backend that used up all the
>> memory? I can't see how a DELETE can use up memory in psql.
>
> psql used up all my physical memory. I didn't thought so too, but I
> learned something new. a DELETE can use up all my memeory.

Can we establish that it really is psql? Can you check with "ps aux" or
"top" that it's psql and not a backend ("postgres") running the query?

Also, can we establish that it is memory that we're running out of -
again, "ps", "top" or "free -m" should show that.

--
  Richard Huxton
  Archonet Ltd