Thread

Problem: database connection termination while processing select command

Vladimír Beneš <vladimir.benes@pvt.cz> — 2000-02-22T08:55:05Z

Your name               : Vladimir Benes
Your email address      : vladimir.benes@pvt.cz


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Debian Linux 2.2.13

  PostgreSQL version (example: PostgreSQL-6.5.3): Debian package:
    Package: postgresql
    Status: install ok installed
    Priority: optional
    Section: misc
    Installed-Size: 1936
    Maintainer: Oliver Elphick <Oliver.Elphick@lfix.co.uk>
    Version: 6.5.3-11


  Compiler used (example:  gcc 2.8.0)           :
    prepared Debian package - not recompiled here

Please enter a FULL description of your problem:
------------------------------------------------

    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


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

    Run select instruction at table with really very many rows.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

    Sorry, I don't know. Perhaps chance of seting any timeout - for example
disabling this one...


        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