Re: BUG #5235: Segmentation fault under high load through JDBC - Mailing list pgsql-bugs

You are right, it crushes on following statement: "select
instr(ad_parent_tree(?,?),'|'||?||'|') AS isItsOwnChild from dual;"

max_stack_depth is commented out, I think it has the default value: #max_stack_depth = 2MB

I'm attaching related functions.

Oleg

Robert Haas wrote:
> 2009/12/8 Oleg Jurtšenko <oleg.jurtsenko@fts.ee>:
>
>> Once more:
>>
>> http://www.fts.ee/pgsqldebug.tgz - with loging enabled
>> http://www.ftse.ee/pg_core.tar.bzip2 - full core dump
>>
>
> It looks like you've got a pl/pgsql function that called itself
> recursively 1417 times before running out of stack space.  What do you
> have max_stack_depth set to?
>
> ...Robert
>
-- Function: instr(character varying, character varying, integer, integer)

-- DROP FUNCTION instr(character varying, character varying, integer, integer);

CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search character varying, beg_index integer,
occur_indexinteger)
 
  RETURNS integer AS
$BODY$DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer; BEGIN
    IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL) OR (occur_index IS NULL)) THEN RETURN 0;
ENDIF;
 
IF beg_index > 0 THEN
    beg := beg_index;
    temp_str := substring(string FROM beg_index);

    FOR i IN 1..occur_index LOOP
        pos := position(string_to_search IN temp_str);
         IF i = 1 THEN
            beg := beg + pos - 1;
        ELSE
            beg := beg + pos;
        END IF;
         temp_str := substring(string FROM beg + 1);
    END LOOP;          
    IF pos = 0 THEN
        RETURN 0;
    ELSE
        RETURN beg;
    END IF;
ELSE
    ss_length := char_length(string_to_search);
    length := char_length(string);
    beg := length + beg_index - ss_length + 2;
     WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos := position(string_to_search IN temp_str);
         IF pos > 0 THEN
            occur_number := occur_number + 1;
             IF occur_number = occur_index THEN
                RETURN beg;
            END IF;
        END IF;
         beg := beg - 1;
    END LOOP;
     RETURN 0;
END IF; 
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;
ALTER FUNCTION instr(character varying, character varying, integer, integer) OWNER TO tad;

-- Function: instr(character varying, character varying, integer)

-- DROP FUNCTION instr(character varying, character varying, integer);

CREATE OR REPLACE FUNCTION instr(string character varying, string_to_search character varying, beg_index integer)
  RETURNS integer AS
$BODY$DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF ((string IS NULL) OR (string_to_search IS NULL) OR (beg_index IS NULL)) THEN RETURN 0; END IF;
    IF beg_index > 0 THEN
      temp_str := substring(string FROM beg_index);
      pos := position(string_to_search IN temp_str);
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN pos + beg_index - 1;
      END IF;
    ELSE
      ss_length := char_length(string_to_search);
      length := char_length(string);
      beg := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          RETURN beg;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;
ALTER FUNCTION instr(character varying, character varying, integer) OWNER TO tad;

-- Function: instr(character varying, character varying)

-- DROP FUNCTION instr(character varying, character varying);

CREATE OR REPLACE FUNCTION instr(character varying, character varying)
  RETURNS integer AS
$BODY$DECLARE
    pos integer;
BEGIN
    pos:= instr($1, $2, 1);
    RETURN pos;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;
ALTER FUNCTION instr(character varying, character varying) OWNER TO tad;

-- Function: ad_parent_tree(character varying, character varying)

-- DROP FUNCTION ad_parent_tree(character varying, character varying);

CREATE OR REPLACE FUNCTION ad_parent_tree(p_tree_id character varying, p_node_id character varying)
  RETURNS character varying AS
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
* Version 1.1  with a permitted attribution clause; you may not  use this
* file except in compliance with the License. You  may  obtain  a copy of
* the License at http://www.openbravo.com/legal/license.html
* Software distributed under the License  is  distributed  on  an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific  language  governing  rights  and  limitations
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SL
* All portions are Copyright (C) 2001-2008 Openbravo SL
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
p VARCHAR(32767); --OBTG:VARCHAR2--
  vp VARCHAR(32); --OBTG:VARCHAR2--
BEGIN

  SELECT parent_id
    INTO vp
    FROM ad_treenode
   WHERE node_id = p_node_id
     AND ad_tree_id = p_tree_id;


    p := ad_parent_tree(p_tree_id, vp);
    IF p != ' ' THEN
      RETURN '|'||vp||'|'||p;
    END IF;
    RETURN '|'||vp||'|';
  EXCEPTION WHEN OTHERS THEN RETURN ' ';
END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION ad_parent_tree(character varying, character varying) OWNER TO tad;