Out of memory problem (forwarded bug report)

Can someone advise, please, how to deal with this problem in 6.5.3?

This is the second message, after debugging was enabled in the backend:

------- Forwarded Message

Date: Tue, 22 Feb 2000 15:28:44 +0100
From: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir(dot)Benes(at)pvt(dot)cz>
To: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>, <58689(at)bugs(dot)debian(dot)org>
cc: "=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm(at)pvt(dot)net>
Subject: Re: Bug#58689: Problem: database connection termination while processi
ng select command

Hi,

I tried this and this problem is reported in the log here:

query: select comm_type,name,tot_bytes,tot_packets from
flow_sums_days_send_200002_view where day='2000-02-21' and name not l
ProcessQuery
FATAL 1: Memory exhausted in AllocSetAlloc()

This message is invoked by unsuccess malloc() operation :-(

Well, I tried to use instead of simple select command this:

create temporary table xx as select ...
create table yy as select
select ... into zz from ...

I expected that Postgres will use less memory and that he will
continously send data to new tables but not. This hasn't any effect.

Command "top" wrote this report while my select ran:

CPU states: 98.6% user, 1.3% system, 0.0% nice, 0.0% idle
Mem: 127256K av, 124316K used, 2940K free, 29812K shrd, 2620K buff
Swap: 128516K av, 51036K used, 77480K free 7560K cached

PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
2942 postgres 20 0 141M 99M 17348 R 0 99.0 80.4 1:22 postmaster

Thank You, V. Benes

- -----Pùvodní zpráva-----
Od: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
Komu: Vladimír Bene¹ <Vladimir(dot)Benes(at)pvt(dot)cz>; 58689(at)bugs(dot)debian(dot)org
<58689(at)bugs(dot)debian(dot)org>
Datum: 22. února 2000 15:04
Pøedmìt: Re: Bug#58689: Problem: database connection termination while
processing select command

>"=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" wrote:
> >Package: postgresql
> >Version: 6.5.3-11
> >
> >
> >
> > Postgres reports error:
> >"pg.error pqReadData() -- backend closed the channel unexpectedly. This
> >probably means the backend terminated abnormally before or while
processing
> >the request."
>
>
>Please turn on debugging in the backend by editing
>/etc/postgresql/postmaster.init and setting the value of PGDEBUG to 2; you
>should also turn on PGECHO.
>
>Then restart the postmaster (/etc/init.d/postgresql restart), rerun
>the query and examine the end of the log to see what error is reported
>there.

------- End of Forwarded Message

and this was the original message:

------- Forwarded Message

Date: Tue, 22 Feb 2000 12:07:42 +0100
From: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir(dot)Benes(at)pvt(dot)cz>
To: <submit(at)bugs(dot)debian(dot)org>
cc: "=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm(at)pvt(dot)net>
Subject: Bug#58689: Problem: database connection termination while processing s
elect command

Package: postgresql
Version: 6.5.3-11

Postgres reports error:
"pg.error pqReadData() -- backend closed the channel unexpectedly. This
probably means the backend terminated abnormally before or while processing
the request."

This error is produced by processing of this correct instruction:
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_send_200002_view
where day='2000-02-21' and name not like '@%'
union all
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_receive_200002_view
where day='2000-02-21' and name not like '@%'

Both flow_sums_days_send_200002_view and
flow_sums_days_receive_200002_view are views upon table with very many rows
(today about 3 000 000). I guess limit of this data count about 10 000 000
rows.

This operation can run arbitrary long - never mind. The program
providing this select (one times per day) inserts every 5 minut new data
into this table.

I tried stop this program (daemon) and then I ran this select from psql
(with clause "limit 10"). It was success (no database session termination).

I'am sure that any TIMEOUT expired.

Perhaps cause of this problem is "commuting" of insert commands at time
when this select is executed. I can remove clause "union all" and the
program can perform sleep instruction before select processing BUT then this
problem will occures later again.

My environment at /etc/postgresql/postmaster.init:
PGBACKENDCOUNT=64
PGBUFFERS=2048
PGSORTMEM=262144
KERNEL_FILE_MAX=1032

Thank You very much, V. Benes

___________________________________________________
Ing. Vladimir Benes, pvt.net
PVT, a.s., OZ Chomutov
e-mail: vladimir(dot)benes(at)pvt(dot)cz, vladimir(dot)benes(at)sms(dot)paegas(dot)cz

------- End of Forwarded Message

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"The LORD bless thee, and keep thee; The LORD make his
face shine upon thee, and be gracious unto thee; The
LORD lift up his countenance upon thee, and give thee
peace." Numbers 6:24-26