Re: Stored procedure failure

Michal Hlavac wrote:
> hello, I have interesting problem...
>
> I have stored procedure, which works good, but only if input is "correct".
> Correct input is, when $1 is id, which exists in table...
> If I put non exists id, database fall down and restart with this error:
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> I think, that problem is in line:
> FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path
> ORDER BY v_path LOOP
> because without them it works well...

 > CREATE OR REPLACE FUNCTION "public"."get_part_cat_path" (integer)
 > RETURNS text AS'
 > DECLARE
 >   my_path ltree;
...
 > BEGIN
 >   SELECT v_path INTO my_path FROM c_part_cat WHERE i_part_cat_id = $1;
 >   result := ''''; first := true;
 >   FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path


I'm guessing a problem with the ltree code. Presumably in handling the
null my_path.

> Version: 7.4.5 and 7.4.3 (both versions have this problem)

Looking at the 8.0beta source, the functions (...ltree_isparent) are all
marked strict so they should just return null on a null parameter.

What happens if you set my_path to some non-null but un-matched value
before the problem line?

--
   Richard Huxton
   Archonet Ltd