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

On 01/24/2009 12:42 AM, Richard Huxton wrote:
> 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?

yes, I checked it with top and ps aux, it is only psql, not the
connecting postgres worker

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

it is the physical memory.

I will run the process again, and send in results for confirmation

--
[ Clemens Schwaighofer -----=====:::::~ ]
[ IT Engineer/Web Producer/Planning/Manager ]
[ E-Graphics Communications SP Digital ]
[ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp ]