Horton

Thread

  1. PostgreSQL crashes using distance operator whith records where 'point' data type set to null

    Ludovic LANGE <llange@ftd.fr> — 2000-09-25T19:55:13Z

    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:llange@ftd.fr
-- Groupe FTD    -     http://www.ftd.fr

  2. Re: PostgreSQL crashes using distance operator whith records where 'point' data type set to null

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-09-25T20:36:17Z

    Yeah, there are a lot of geometric operators that neglect to check for
null inputs :-(.  I believe I have fixed this class of problems in
current sources.

			regards, tom lane

  3. Re: PostgreSQL crashes using distance operator whith records where 'point' data type set to null

    Ludovic LANGE <llange@ftd.fr> — 2000-09-25T22:11:23Z

    Hi tom,

Thanks for the answer.
Can you tell me about the files concerned by this fix of yours ? I intend to 
make a patch for the 7.0.2 sources, because, 1) I won't use a CVS version on 
my production server [ and 2) I'm using the rpm-built postgresql and I didn't 
manage to easily turn the CVS version into a RPM one ;-)  ]

Thanks in advance

On lun, 25 sep 2000, Tom Lane wrote:
> Yeah, there are a lot of geometric operators that neglect to check for
> null inputs :-(.  I believe I have fixed this class of problems in
> current sources.
>
> 			regards, tom lane

-- 
----------------------------
-- Ludovic LANGE mailto:llange@ftd.fr
-- Groupe FTD    -     http://www.ftd.fr

  4. Re: PostgreSQL crashes using distance operator whith records where 'point' data type set to null

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-09-26T03:42:49Z

    Ludovic LANGE <llange@ftd.fr> writes:
> Can you tell me about the files concerned by this fix of yours ? I intend to 
> make a patch for the 7.0.2 sources, because, 1) I won't use a CVS version on 
> my production server [ and 2) I'm using the rpm-built postgresql and I didn't
> manage to easily turn the CVS version into a RPM one ;-)  ]

If you don't want a CVS version then you don't want my fix either ---
it's part of a wholesale restructuring of the function manager.  You'll
just have to start hacking on the geometry functions for yourself...

			regards, tom lane