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.Benes@pvt.cz>
To:      "Oliver Elphick" <olly@lfix.co.uk>, <58689@bugs.debian.org>
cc:      "=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.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@lfix.co.uk>
Komu: Vladimï¿½r Beneï¿½ <Vladimir.Benes@pvt.cz>; 58689@bugs.debian.org
<58689@bugs.debian.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.Benes@pvt.cz>
To:      <submit@bugs.debian.org>
cc:      "=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.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.benes@pvt.cz, vladimir.benes@sms.paegas.cz


------- End of Forwarded Message

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.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 



Tue, 22 Feb 2000 15:00:29 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Out of memory problem (forwarded bug report)
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Can someone advise, please, how to deal with this problem in 6.5.3?

My guess is that the cause is memory leaks during expression evaluation;
but without seeing the complete view definitions and underlying table
definitions, it's impossible to know what processing is being invoked
by this query...

			regards, tom lane

Tue, 22 Feb 2000 12:06:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Out of memory problem (forwarded bug report) 