Re: swap storm created by 8.2.3

Richard Huxton wrote:
> Joseph Shraibman wrote:
>> I'm running:
>>
>> PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
>> (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)
>>
>> My memory settings are:
>>
>> work_mem = 64MB
>> shared_buffers = 128MB
>> temp_buffers = 32MB
>>
>> I ran a query that was "SELECT field, count(*) INTO TEMP temptable"
>> and it grew to be 10gig (as reported by top)
>
> What was the real query?

First I selected 90634 rows (3 ints) into the first temp table, then I
did "select intfield1, count(intfield2) FROM realtable rt WHERE rt.id =
temptable.id and other conditions on rt here GROUP BY intfield1".  The
size of the second temp table should have been no more than 60000 rows.

> How many rows are we talking about?
>
>  > and brought the whole machine
>> to its knees.  How do I keep this from happening again?
>
> Set your per-user limits (man ulimit or man bash) to restrict PG's
> overall memory consumption.
>
What happens when PG hits that limit?  Will it start using disk space
for TEMP tables then?