Out of memory problem (forwarded bug report) - Mailing list pgsql-hackers

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:    "Vladimír Beneš" <Vladimir.Benes@pvt.cz>
To:      "Oliver Elphick" <olly@lfix.co.uk>, <58689@bugs.debian.org>
cc:      "Mühlpachr 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


>"Vladimír Beneš" 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:    "Vladimír Beneš" <Vladimir.Benes@pvt.cz>
To:      <submit@bugs.debian.org>
cc:      "Mühlpachr 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
ID32B8FAA1                ========================================    "The LORD bless thee, and keep thee; The LORD
makehis     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