Re: Problem with recursive SPL procedure

On Thu, 2005-10-27 at 13:09, Doug Conrey wrote: > First: this is IDS 7.31.UD7, running on SCO 5.0.6. > > One of my developers had a problem that was absolutely perfect for > recursion. He wrote the following SPL procedure: > > CREATE PROCEDURE get_cvx_kids (p_id INTEGER)> DEFINE v_id INTEGER; > DEFINE v_id2 INTEGER; > > FOREACH SELECT loc_id INTO v_id FROM 'dba'.location_msh WHERE parent_id > = p_id > IF v_id IS NOT NULL THEN EXECUTE PROCEDURE get_cvx_kids (v_id) ; > END IF; > END FOREACH > insert into tmp_id values (p_id);> END PROCEDURE > ; > > The tmp_id table is a non-logged temp table with one integer field. He > tells me it works fine if he runs it with a parameter that doesn't have > a large number of results. When he ran it with the root location, where > the results should be every row, the instance crashed (Assert Failure: > No Exception Handler). The location_msh table has 7732 rows in it. When > they were running this there was no index on parent_id. This happened on > two different servers, though with the same data on both. > He said that to work around this he can unroll the recursion in a series > of nested foreach loops, because there's a limit to the depth this can > go, but that's pretty ugly. I'm wondering if anyone has seen this sort > of behavior, and whether it's something we did, or a problem with the > engine, etc. > > The error from online.log: > > 17:47:22 Assert Failed: No Exception Handler > 17:47:22 Informix Dynamic Server Version 7.31.UD7 > 17:47:22 Who: Session(232623, oci@c0a80274, 2276, 722003392) > Thread(496060, sqlexec, 2b068e8c, 3) > File: mtex.c Line: 471 > 17:47:22 Results: Exception Caught. Type: MT_EX_OS, Context: mem > 17:47:22 Action: Please notify Informix Technical Support. > 17:47:26 See Also: /tmp/af.95a41589 > 17:47:26 mtex.c, line 471, thread 496060, proc id 1887, No Exception > Handler. > 17:47:26 PANIC: Attempting to bring system down My gut instinct, based on no specific knowledge of that error message, is to check whether the tree structure that location_msh describes is indeed a tree and free from any cycles which would lead to an infinite recursion. Hope this helps, -- Carsten Haese - Software Engineer | Phone: (419) 794-2531 Unique Systems, Inc. | FAX: (419) 893-2840 1687 Woodlands Drive | Cell: (419) 343-7045 Maumee, OH 43537 | Email: carsten@uniqsys.com sending to informix-list