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

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.

> so basically it runs a select to see what entries do not have any
> reference data and then should delete them all.
>
> Now, my question is. How can I setup postgres to not use up all the
> memory and then make the server useless. How can I set it up, so it dies
> with "out of memory" before I have to restart the server.

You shouldn't need to restart the server at all - what operating system
are you running?

What memory settings are you using? How much RAM do you have? How much
do you want to keep for other applications?

--
Richard Huxton
Archonet Ltd