Обсуждение: max_stack_depth Exceeded
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.
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. //Magnus
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?
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
Magnus Hagander <magnus@hagander.net> writes:
> Ow Mun Heng wrote:
>> Am I doing something wrong?
> 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.
Of course, the way to have the row be inserted into the parent table is
to just let the trigger return it, instead of returning null.
regards, tom lane
-----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Friday, September 05, 2008 11:22 PM To: Magnus Hagander Cc: Ow Mun Heng; pgsql-general@postgresql.org Subject: Re: [GENERAL] max_stack_depth Exceeded Magnus Hagander <magnus@hagander.net> writes: > Ow Mun Heng wrote: >> Am I doing something wrong? > 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. >>Of course, the way to have the row be inserted into the parent table is >>to just let the trigger return it, instead of returning null. Er.. which is how? I'm new in this so, syntax would be appreciated.