max_stack_depth Exceeded

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.