Hello,
I'd like to file the following bug report for two 'bugs' that may (or may
not) have the same origin.
The context is the following :
* PostgreSQL v7.0.2 from RPMs :
' PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 '
postgresql-jdbc-7.0.2-2
postgresql-odbc-7.0.2-2
postgresql-server-7.0.2-2
postgresql-tcl-7.0.2-2
postgresql-devel-7.0.2-2
postgresql-7.0.2-2
* Computer :
Pentium III 550MHz, 256 Mo
Red Hat 6.2 base
Kernel 2.2.16
libc-2.1.3
Bug description :
The problem is the following :
I use geometric computation in my database, and in this context, I'm trying
to compute distances using PostgreSQL operator '<->'.
It happens that the coordinates I'm using are sometimes NULL, and in this
case, it leads to a backend termination (which is not very clean...)
-The first 'bug' is when you try to compute the distance between two points,
and one of them is NULL. The backend terminates. The workaround is to test
for nullity, and to do it before computing the distance.
-The second 'bug' is very strange, but, as I did not find a workaround, is
very annoying for me.
It happens the following way :
I have two 'point' fields (bar and bar2) in my table foo. I'm trying to
compute the best distance between a given point (parameter) and one of the
two fields. For doing this, I'm using the following request : (this is not
really postgresql-compatible SQL, but it is easier to read)
SELECT * FROM foo
WHERE (distance(param1, bar) < range)
OR (distance(param1, bar2) < range)
When I use this request, and when I test for nullity of bar and bar2 before,
everything seems perfect.
But when I add one condition that WOULD return a result containing a NULL
point record, the backend terminates (EVEN when I test for nullity before the
distance computation) :
SELECT * FROM foo
WHERE text='hello1'
( (distance(param1, bar) < range)
OR (distance(param1, bar2) < range) )
[if I use a condition that WOULDN'T return a result, it works :
SELECT * FROM foo
WHERE text='hello0'
( (distance(param1, bar) < range)
OR (distance(param1, bar2) < range) )
as I know that I haven't any records containing 'hello0' in the 'text' field]
Steps to reproduce :
1) Create a 'test' database :
=
createdb test
2) Fill the 'test' database with a table 'foo' (using the 'wrong_db.sql' file)
==
bash$ psql test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# \i wrong_db.sql
You are now connected as new user postgres.
DROP
CREATE
INSERT 78923 1
3) (Bug # 1) Now, try to compute a distance on a record where a 'point' data
type is NULL:
===
test=# \i trythis1.sql
bar | text | bar2
---++---
(0,0) | hello1 | (0,0)
(1 row)
psql:trythis1.sql:2: pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:trythis1.sql:2: connection to server was lost
bash$
[Note : the first statement is a workaround (explicitely testing for the
non-nullity before doing the distance computation)]
4) (Bug # 2) Now, try to compute two distances on a record where some 'point'
data types are NULL and with another condition : (reconnect before)
=
test=# \i trythis2.sql
bar | text | bar2
-+--+--
(0 rows)
bar | text | bar2
---++---
(0,0) | hello1 | (0,0)
(1 row)
psql:trythis2.sql:3: pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:trythis2.sql:3: connection to server was lost
[Note : I've not found a workaround ]
5) With no NULL records, it seems to work
=
Re-experiment with database from file 'good_db.sql'
Regards,
--
-- Ludovic LANGE mailto:[EMAIL PROTECTED]
-- Groupe FTD- http://www.ftd.fr
\connect - postgres
DROP TABLE "foo";
CREATE TABLE "foo" (
"bar" point,
"text" character varying,
"bar2" point
);
INSERT INTO "foo" ("bar","text","bar2") VALUES ('(0,0)','hello1','(0,0)');
\connect - postgres
DROP TABLE "foo";
CREATE TABLE "foo" (
"bar" point,
"text" character varying,
"bar2" point
);
INSERT INTO "foo" ("bar","text","bar2") VALUES ('(0,0)','hello1','(0,0)');
INSERT INTO "foo" ("bar","text","bar2") VALUES ('(0,0)','hello2',NU
