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

Generally out of memory error persist when the memory is not enough to handle the query. When query executed without using standard plan it consum lots of memory. If shared buffer is not enough to handle this query it thrown error like out of memory. It if good practice to do "explain <query> " before executing it, will give you information about which plan it will use.  

The workaround for the "Delete..." query is you can make pl/pgsql function, which will execute faster than this query. As it is not gone for sequential scan. It will directly jump to the table.bar_id where you given appropriate condition.

---
Thanks & Regards
Dhaval Jaiswal
EnterpriseDB
www.enterprisedb.com

________________________________
From: Clemens Schwaighofer <clemens(dot)schwaighofer(at)tequila(dot)jp>
To: pgsql-general(at)postgresql(dot)org
Sent: Thursday, January 22, 2009 2:21:47 PM
Subject: [GENERAL] how to avoid that a postgres session eats up all the memory

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 ...)

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.

--
[ 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                                    ]