Обсуждение: Using nulls with earthdistance operator crashes backend

Hello!

Here's now to reproduce my bug:
* Start with Postgres 7.1.2
(specifically:  PostgreSQL 7.1 on i386--freebsd4.3, compiled by GCC 2.95.3
)
* Install earthdistance operator from the contrib directory.
* try this:
cascade=> select null <@> '1,1'::point;

## The result I get:
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

###############

I expected simply for "null" to be returned as the result. I can work
around
this by including an extra step to make sure that my data is not null
before
it's passed off a SQL statement like this.

  Thanks!

   -mark

http://mark.stosberg.com/

Mark Stosberg <mark@summersault.com> writes:
> * Install earthdistance operator from the contrib directory.
> * try this:
> cascade=> select null <@> '1,1'::point;

> ## The result I get:
> pqReadData() -- backend closed the channel unexpectedly.

Probably the earthdistance functions are not NULL-safe and need to be
marked "isStrict" in CREATE FUNCTION.  Would you be willing to do the
legwork on working up a patch for that?

            regards, tom lane

I'll give it a shot and post the patch or let you know I'm stuck. :) Thanks!

  -mark

Tom Lane wrote:
>
> Mark Stosberg <mark@summersault.com> writes:
> > * Install earthdistance operator from the contrib directory.
> > * try this:
> > cascade=> select null <@> '1,1'::point;
>
> > ## The result I get:
> > pqReadData() -- backend closed the channel unexpectedly.
>
> Probably the earthdistance functions are not NULL-safe and need to be
> marked "isStrict" in CREATE FUNCTION.  Would you be willing to do the
> legwork on working up a patch for that?
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--

http://mark.stosberg.com/

Mark Stosberg <mark@summersault.com> writes:
> Here's a patch using "isstrict":

Oh, there's just the one function?  Sorry for making you go to the work
of submitting a patch ;-) ... I thought there'd be more to it.  Will
apply.

            regards, tom lane

> Tom Lane wrote:
> >
> > Mark Stosberg <mark@summersault.com> writes:
> > > * Install earthdistance operator from the contrib directory.
> > > * try this:
> > > cascade=> select null <@> '1,1'::point;
> >
> > > ## The result I get:
> > > pqReadData() -- backend closed the channel unexpectedly.
> >
> > Probably the earthdistance functions are not NULL-safe and need to be
> > marked "isStrict" in CREATE FUNCTION.  Would you be willing to do the
> > legwork on working up a patch for that?

Tom,

Here's a patch using "isstrict":

############
--- earthdistance.sql.in.org    Thu Aug 16 17:08:19 2001
+++ earthdistance.sql.in        Thu Aug 16 17:09:01 2001
@@ -3,7 +3,8 @@

 DROP FUNCTION geo_distance (point, point);
 CREATE FUNCTION geo_distance (point, point) RETURNS float8
-  AS 'MODULE_PATHNAME' LANGUAGE 'c';
+  AS 'MODULE_PATHNAME' LANGUAGE 'c'
+  WITH (isstrict);

 SELECT geo_distance ('(1,2)'::point, '(3,4)'::point);
#############

Now when I run the "crasher" SQL above, I get one empty row back:

sumsault_test=# select null <@> '1,1'::point;
 ?column?
----------

(1 row)
#############

I look forward to seeing you at the Open Source Database Summit!

  -mark

 . . . . . . . . . . . . . . . . . . . . . . . . . .
   Mark Stosberg              Principal Developer
   mark@summersault.com       Summersault, LLC
   v: 765-939-9301 ext 223    website development
 . . . . . http://www.summersault.com/ . . . . . . .

This will be fixed in 7.2:

    test=> select null <@> '1,1'::point;
     ?column?
    ----------

    (1 row)


>
> Hello!
>
> Here's now to reproduce my bug:
> * Start with Postgres 7.1.2
> (specifically:  PostgreSQL 7.1 on i386--freebsd4.3, compiled by GCC 2.95.3
> )
> * Install earthdistance operator from the contrib directory.
> * try this:
> cascade=> select null <@> '1,1'::point;
>
> ## The result I get:
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> ###############
>
> I expected simply for "null" to be returned as the result. I can work
> around
> this by including an extra step to make sure that my data is not null
> before
> it's passed off a SQL statement like this.
>
>   Thanks!
>
>    -mark
>
> http://mark.stosberg.com/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026