Thread: Select from view crashes backend

Select from view crashes backend

From
"Dorte Munk-Jakobsen"
Date:
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

Re: Select from view crashes backend

From
Tom Lane
Date:
"Dorte Munk-Jakobsen" <dorte@softwareresearch.com> writes:
> 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.

Can't replicate this either in current sources or in 6.5.3, so it's a
pretty ancient bug.  I'd suggest moving to 6.5.3 ...

            regards, tom lane