Select from view crashes backend

From: "Dorte Munk-Jakobsen" <dorte(at)softwareresearch(dot)com>
To: <pgsql-bugs(at)hub(dot)org>
Subject: Select from view crashes backend
Date: 2000-04-07 12:17:13
Message-ID: 001c01bfa08b$36e77520$1e20e8d4@dorte
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have a problem with a select from a view that crashes the backend. I am working with version 6.4.2 and cannot change that at present. My hope is that somebody knows of a patch and approximately where/when I should look for it. I have tried to search the mailinglist archive but I cannot get any result.
The configuration is:
Architecture : Intel Pentium
Operating System : Linux 2.0.34 ELF (Redhat 5.1)
PostgreSQL version : PostgreSQL-6.4.2
Compiler used : gcc 2.7.2.3

Here is the more detailed description of the problem:

I have two tables t1 and t2 upon which I build a view with values from the two tables plus 4 calculated values. When I select all from this view all is fine, but I need to know if a given value is between two of the calculated values, this all works fine as long as the value I am looking for is not negative, when it is negative I get the following message:

pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.

The following is a small example which reproduces the problem:

aclub=> create table t1 (id serial, city text, lat float, long float);
NOTICE: CREATE TABLE will create implicit sequence t1_id_seq for SERIAL column t1.id
NOTICE: CREATE TABLE/UNIQUE will create implicit index t1_id_key for table t1
CREATE

aclub=> insert into t1 (city, lat, long) values ('city1', 1.1, 1.1);
INSERT 676321 1
aclub=> insert into t1 (city, lat, long) values ('city1', 1.2, -1.1);
INSERT 676322 1
aclub=> create table t2 (id, serial, name text, city int, distance float );
NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for SERIAL column t2.id
NOTICE: CREATE TABLE/UNIQUE will create implicit index t2_id_key for table t2
CREATE
aclub=> insert into t2 (name,city,distance) values ('dorte',1,0.35);
INSERT 676357 1
aclub=> insert into t2 (name,city,distance) values ('joe',2,0.35);
INSERT 676358 1
aclub=> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676359 1
aclub=> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676360 1
aclub=> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676361 1
aclub=>create view t2_lat_long as
select t2.id, t2.city as city, lat,
long, lat + distance*0.0089879 as lat_max
, lat - distance*0.0089879 as lat_min
, long - distance*0.0089879 as lon_min
, long + distance*0.0089879 as lon_max
from t1,t2
where t1.id=t2.city
and t2.distance < 888;
aclub=>select * from t2_lat_long;
id|city|lat|long| lat_max| lat_min| lon_min| lon_max
--+----+---+----+-----------+-----------+------------+------------
1| 1|1.1| 1.1|1.103145765|1.096854235| 1.096854235| 1.103145765
3| 1|1.1| 1.1| 1.4145765| 0.7854235| 0.7854235| 1.4145765
4| 1|1.1| 1.1| 1.4145765| 0.7854235| 0.7854235| 1.4145765
5| 1|1.1| 1.1| 1.4145765| 0.7854235| 0.7854235| 1.4145765
2| 2|1.2|-1.1|1.203145765|1.196854235|-1.103145765|-1.096854235
(5 rows)

aclub=>select id from t2_lat_long
where 47.209999 between lat_min and lat_max
and 1.550000 between lon_min and lon_max
;
id
--
(0 rows)

EOF
aclub=> select id from t2_lat_long
where 47.209999 between lat_min and lat_max
and -1.550000 between lon_min and lon_max
;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
[dorte(at)freja /tmp]$

Regards

Dorte Munk-Jakobsen
Dorte(at)SoftwareResearch(dot)com

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2000-04-07 14:12:00 Re: Select from view crashes backend
Previous Message Bernard ISAMBERT 2000-04-07 10:05:28 ECPG problem