|
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@freja /tmp]$ Regards
|
- Re: [BUGS] Select from view crashes backend Dorte Munk-Jakobsen
