Re: max_stack_depth Exceeded

Ow Mun Heng wrote:
> 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?

If your trigger is defined on the head_raw_all_test_2 table, then yes.
Because it will do a new insert there, and the new insert will fire the
trigger again, which will do a new insert, which wil lfire the trigger etc.

//Magnus