Обсуждение: out of memory

Hi Everybody,

I saw something I had never seen with psql.

> canon=#
> canon=# select * from public.genotype;
> out of memory
> [tsakai@vixen tws]$ echo $?
> 1
> [tsakai@vixen tws]$

The version is 8.2.4 and platform is redhat linux
on dell server.  The table has 36 million rows.

I don't suppose there is anything I can do to prevent
such failure (short of installing more memory)?

Regards,


Tena Sakai
tsakai@gallo.ucsf.edu

"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> canon=3D# select * from public.genotype;
>> out of memory

> The version is 8.2.4 and platform is redhat linux
> on dell server.  The table has 36 million rows.

Try \set FETCH_COUNT 1000

            regards, tom lane

Thank you, Tom.

But there's a problem, for which I hope you have
more wisdom.  The reason why I tried that query
is because someone tried the same thing via odbc
from pc quering postgres database and got the
same error ("out of memory").

What can a pc user do?  Anything?

Regards,


Tena
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tue 9/11/2007 8:58 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] out of memory

"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:
>> canon=3D# select * from public.genotype;
>> out of memory

> The version is 8.2.4 and platform is redhat linux
> on dell server.  The table has 36 million rows.

Try \set FETCH_COUNT 1000

                        regards, tom lane

On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:

> But there's a problem, for which I hope you have
> more wisdom.  The reason why I tried that query
> is because someone tried the same thing via odbc
> from pc quering postgres database and got the
> same error ("out of memory").
>
> What can a pc user do?  Anything?

Tell the user that he/she should use an appropiate WHERE clause. You
might also consider adding more swap to the machine. Then it will go
horribly slow, but it will work.

HTH,

Tino.

--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz

Tino Schwarze <postgresql@tisc.de> writes:
> On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:
>> But there's a problem, for which I hope you have
>> more wisdom.  The reason why I tried that query
>> is because someone tried the same thing via odbc
>> from pc quering postgres database and got the
>> same error ("out of memory").
>>
>> What can a pc user do?  Anything?

> Tell the user that he/she should use an appropiate WHERE clause. You
> might also consider adding more swap to the machine. Then it will go
> horribly slow, but it will work.

Or turn the query into a cursor so you can FETCH a reasonable number of
rows at a time.

            regards, tom lane

Many thanks, Tom and Tino.

Tena Sakai
tsakai@gallo.ucsf.edu


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 9/12/2007 7:20 AM
To: Tino Schwarze
Cc: pgsql-admin@postgresql.org; Tena Sakai
Subject: Re: [ADMIN] out of memory

Tino Schwarze <postgresql@tisc.de> writes:
> On Tue, Sep 11, 2007 at 09:15:56PM -0700, Tena Sakai wrote:
>> But there's a problem, for which I hope you have
>> more wisdom.  The reason why I tried that query
>> is because someone tried the same thing via odbc
>> from pc quering postgres database and got the
>> same error ("out of memory").
>>
>> What can a pc user do?  Anything?

> Tell the user that he/she should use an appropiate WHERE clause. You
> might also consider adding more swap to the machine. Then it will go
> horribly slow, but it will work.

Or turn the query into a cursor so you can FETCH a reasonable number of
rows at a time.

                        regards, tom lane