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 t=
hat somebody knows of a patch and approximately where/when I should look fo=
r 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 calculat=
ed 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 proc=
essing the request.
We have lost the connection to the backend, so further processing is imposs=
ible. Terminating.
The following is a small example which reproduces the problem:
aclub=3D> create table t1 (id serial, city text, lat float, long float);
NOTICE: CREATE TABLE will create implicit sequence t1_id_seq for SERIAL co=
lumn t1.id
NOTICE: CREATE TABLE/UNIQUE will create implicit index t1_id_key for table=
t1
CREATE
aclub=3D> insert into t1 (city, lat, long) values ('city1', 1.1, 1.1);
INSERT 676321 1
aclub=3D> insert into t1 (city, lat, long) values ('city1', 1.2, -1.1);
INSERT 676322 1
aclub=3D> create table t2 (id, serial, name text, city int, distance float =
);
NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for SERIAL co=
lumn t2.id
NOTICE: CREATE TABLE/UNIQUE will create implicit index t2_id_key for table=
t2
CREATE
aclub=3D> insert into t2 (name,city,distance) values ('dorte',1,0.35);
INSERT 676357 1
aclub=3D> insert into t2 (name,city,distance) values ('joe',2,0.35);
INSERT 676358 1
aclub=3D> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676359 1
aclub=3D> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676360 1
aclub=3D> insert into t2 (name,city,distance) values ('dorte',1,35);
INSERT 676361 1
aclub=3D>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=3Dt2.city
and t2.distance < 888;
aclub=3D>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=3D>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=3D> 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 proc=
essing the request.
We have lost the connection to the backend, so further processing is imposs=
ible. Terminating.
[dorte@freja /tmp]$=20
Regards
Dorte Munk-Jakobsen
Dorte@SoftwareResearch.com
