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.jurtse...@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_index integer)
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; END IF;
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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
