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
