Re: max_stack_depth Exceeded

On Fri, 2008-09-05 at 10:35 +0200, Magnus Hagander wrote:
> Ow Mun Heng wrote:
> > Hi,
> >
> > I'm playing around with triggers to implement partitioning.
> > I hit something which I don't know what and I don't have internet here
> > at work to find out what is the cause.
> >
> >
> > ERROR : stack depth limit exceeded
> >
> > I see that this is one of the options in postgresql.conf but I don't
> > know exactly what it is.
>
> Sounds like you may have created a situation with infinite recursion.
>
> Like in some branch your trigger is inserting back into the parent
> table, thus firing the trigger again in an endless loop, instead of
> inserting it into the proper child table.

This seems simple enough.

CREATE OR REPLACE FUNCTION head_raw_all_test_2_insert_trigger()
RETURNS "trigger" AS
$BODY$
BEGIN

IF ( NEW.test_run_start_date_time >= '2008-08-18' and
NEW.test_run_start_date_time < '2008-08-19' ) THEN
INSERT INTO head_raw_all_test_2_prod_8_18 VALUES (NEW.*);
ELSEIF ( NEW.test_run_start_date_time >= '2008-08-19' and
NEW.test_run_start_date_time < '2008-08-20' ) THEN
INSERT INTO head_raw_all_test_2_prod_8_19 VALUES (NEW.*);
ELSE
INSERT INTO head_raw_all_test_2 VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

This one, though, works.

CREATE OR REPLACE FUNCTION head_raw_all_test_insert_trigger()
RETURNS "trigger" AS
$BODY$
BEGIN

IF ( NEW.dcm_evaluation_code = 'PROD' OR NEW.dcm_evaluation_code is
null) THEN
INSERT INTO head_raw_all_test_prod VALUES (NEW.*);
ELSEIF ( NEW.dcm_evaluation_code <> 'PROD' ) THEN
INSERT INTO head_raw_all_test_eval VALUES (NEW.*);
ELSE
INSERT INTO head_raw_all_test VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION head_raw_all_test_insert_trigger() OWNER TO "operator";

Am I doing something wrong? Is the ELSE condition that is making it
recurse further and further?