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

In my Pentium 4 box 2GHz with 256 MB of RAM I run a pg server 8.0.3
under netbsd 2.0.2 in which among other tables  there's a "huge" table
letture02 made of 657,000 records and 98 numerical columns.

Using psql
and the "heavy" view letture24btnondom (see below) I face the following
fatal error:
..........
# select * from letture24btnondom ;
out of
memory for query result
..........

Now, it happens that I pg_dumped
the database from an original ** freebsd ** installation on another
older pc (pentium 3 128 MB) where ** under psql I set up the same query
and ran it successfully many times.
Besides, via ODBC I can open as a
table the letture24btnondom view without any problems.

What should I
do for psql?

Ciao
Vittorio

letture24btnondom view:

 SELECT letture02.
contatore, letture02.data, (letture02.let1 + letture02.let2 + letture02.
let3 + letture02.let4) / 4::numeric AS ore1, (letture02.let5 +
letture02.let6 + letture02.let7 + letture02.let8) / 4::numeric AS ore2,
(letture02.let9 + letture02.let10 + letture02.let11 + letture02.let12)
/ 4::numeric AS ore3, (letture02.let13 + letture02.let14 + letture02.
let15 + letture02.let16) / 4::numeric AS ore4, (letture02.let17 +
letture02.let18 + letture02.let19 + letture02.let20) / 4::numeric AS
ore5, (letture02.let21 + letture02.let22 + letture02.let23 + letture02.
let24) / 4::numeric AS ore6, (letture02.let25 + letture02.let26 +
letture02.let27 + letture02.let28) / 4::numeric AS ore7, (letture02.
let29 + letture02.let30 + letture02.let31 + letture02.let32) / 4::
numeric AS ore8, (letture02.let33 + letture02.let34 + letture02.let35 +
letture02.let36) / 4::numeric AS ore9, (letture02.let37 + letture02.
let38 + letture02.let39 + letture02.let40) / 4::numeric AS ore10,
(letture02.let41 + letture02.let42 + letture02.let43 + letture02.let44)
/ 4::numeric AS ore11, (letture02.let45 + letture02.let46 + letture02.
let47 + letture02.let48) / 4::numeric AS ore12, (letture02.let49 +
letture02.let50 + letture02.let51 + letture02.let52) / 4::numeric AS
ore13, (letture02.let53 + letture02.let54 + letture02.let55 + letture02.
let56) / 4::numeric AS ore14, (letture02.let57 + letture02.let58 +
letture02.let59 + letture02.let60) / 4::numeric AS ore15, (letture02.
let61 + letture02.let62 + letture02.let63 + letture02.let64) / 4::
numeric AS ore16, (letture02.let65 + letture02.let66 + letture02.let67
+ letture02.let68) / 4::numeric AS ore17, (letture02.let69 + letture02.
let70 + letture02.let71 + letture02.let72) / 4::numeric AS ore18,
(letture02.let73 + letture02.let74 + letture02.let75 + letture02.let76)
/ 4::numeric AS ore19, (letture02.let77 + letture02.let78 + letture02.
let79 + letture02.let80) / 4::numeric AS ore20, (letture02.let81 +
letture02.let82 + letture02.let83 + letture02.let84) / 4::numeric AS
ore21, (letture02.let85 + letture02.let86 + letture02.let87 + letture02.
let88) / 4::numeric AS ore22, (letture02.let89 + letture02.let90 +
letture02.let91 + letture02.let92) / 4::numeric AS ore23, (letture02.
let93 + letture02.let94 + letture02.let95 + letture02.let96) / 4::
numeric AS ore24
   FROM letture02
  WHERE letture02.contatore >= 2000
AND letture02.contatore <= 2999;

Vittorio wrote:
> In my Pentium 4 box 2GHz with 256 MB of RAM I run a pg server 8.0.3
> under netbsd 2.0.2 in which among other tables  there's a "huge" table
> letture02 made of 657,000 records and 98 numerical columns.
>
> Using psql
> and the "heavy" view letture24btnondom (see below) I face the following
> fatal error:
> ..........
> # select * from letture24btnondom ;
> out of
> memory for query result

How many rows does this return?
How much memory does psql take up when it reports this?
Do you have any memory limits defined for the user doing the query?
Can you get it to work with something like LIMIT 10 added to the query?
What about LIMIT 50,100,200 etc?

> Now, it happens that I pg_dumped
> the database from an original ** freebsd ** installation on another
> older pc (pentium 3 128 MB) where ** under psql I set up the same query
> and ran it successfully many times.
> Besides, via ODBC I can open as a
> table the letture24btnondom view without any problems.

I'd guess ODBC is using a cursor, so you'll only get a few rows at a
time. With psql, it'll fetch all the rows at once.

--
   Richard Huxton
   Archonet Ltd

Alle 15:07, mercoledì 19 ottobre 2005, Richard Huxton ha scritto:
> Vittorio wrote:
> > In my Pentium 4 box 2GHz with 256 MB of RAM I run a pg server 8.0.3
> > under netbsd 2.0.2 in which among other tables  there's a "huge" table
> > letture02 made of 657,000 records and 98 numerical columns.
> >
> > Using psql
> > and the "heavy" view letture24btnondom (see below) I face the following
> > fatal error:
> > ..........
> > # select * from letture24btnondom ;
> > out of
> > memory for query result
>
> How many rows does this return?

All 657,000 records

> How much memory does psql take up when it reports this?

How could I know that amount of memory?

> Do you have any memory limits defined for the user doing the query?

No that I'm aware of.
Could you please specify this point better?


Ciao
Vittorio

> Can you get it to work with something like LIMIT 10 added to the query?
> What about LIMIT 50,100,200 etc?
>
> > Now, it happens that I pg_dumped
> > the database from an original ** freebsd ** installation on another
> > older pc (pentium 3 128 MB) where ** under psql I set up the same query
> > and ran it successfully many times.
> > Besides, via ODBC I can open as a
> > table the letture24btnondom view without any problems.
>
> I'd guess ODBC is using a cursor, so you'll only get a few rows at a
> time. With psql, it'll fetch all the rows at once.