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.idNOTICE: CREATE TABLE/UNIQUE
will create implicit index t1_id_key for table t1CREATE
aclub=> insert into t1 (city, lat, long)
values ('city1', 1.1, 1.1);INSERT 676321 1aclub=> insert into t1
(city, lat, long) values ('city1', 1.2, -1.1);INSERT 676322 1aclub=>
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.idNOTICE: CREATE TABLE/UNIQUE will create implicit
index t2_id_key for table t2CREATEaclub=> insert into t2
(name,city,distance) values ('dorte',1,0.35);INSERT 676357 1aclub=>
insert into t2 (name,city,distance) values ('joe',2,0.35);INSERT 676358
1aclub=> insert into t2 (name,city,distance) values
('dorte',1,35);INSERT 676359 1aclub=> insert into t2
(name,city,distance) values ('dorte',1,35);INSERT 676360 1aclub=>
insert into t2 (name,city,distance) values ('dorte',1,35);INSERT 676361
1aclub=>create view t2_lat_long asselect 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_maxfrom t1,t2where t1.id=t2.cityand
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_longwhere 47.20 between lat_min and lat_maxand 1.55
between lon_min and lon_max;id--(0 rows)
EOFaclub=> select id from
t2_lat_longwhere 47.20 between lat_min and lat_maxand -1.55
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
Dorte Munk-Jakobsen[EMAIL PROTECTED]
