Thread: 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