PostgreSQL Discussion - stack depth limit exceeded

Author

stack depth limit exceeded

Jamie Deppeler

2005-08-29, 3:23 am

At the moment i am trying to execute a very simple function but i am
getting the following error stack depth limit exceeded

function

CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
trigger AS
$body$
begin
update contacts.person
set "contact" = new.firstname
where person."primary" = new."primary";
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Fuhr

2005-08-29, 3:23 am

On Mon, Aug 29, 2005 at 12:01:59PM +1000, Jamie Deppeler wrote:
> At the moment i am trying to execute a very simple function but i am
> getting the following error stack depth limit exceeded

That's often a sign of infinite recursion, i.e., a function that
keeps calling itself, either directly or indirectly.

> CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
> trigger AS
> $body$
> begin
> update contacts.person
> set "contact" = new.firstname
> where person."primary" = new."primary";
> return null;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

What's the trigger definition look like? I'd guess that the update
on contacts.person has a trigger that somehow gets back to this
function, which updates contacts.person, which invokes the trigger,
etc.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-08-29, 3:23 am

Jamie Deppeler <jamie@doitonce.net.au> writes:
> At the moment i am trying to execute a very simple function but i am
> getting the following error stack depth limit exceeded

You didn't really show the complete context, but seeing that this is a
trigger and it's trying to do an "UPDATE person" internally, I'll bet
a nickel that the trigger itself is on update events on person, and
therefore that you've written an infinite recursion.

Had you shown more context, I could have given some advice on a better
way to do it. If you're trying to alter the row that's about to be
stored, you just have to assign to field(s) of the NEW row within the
trigger. If you want to do something else, you need to explain what.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jamie Deppeler

2005-08-29, 3:23 am

What i am trying to do is update the field contact with field values in
firstname and lastname

Trigger

CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
ON FOR EACH ROW
EXECUTE PROCEDURE "contacts"."addContactField"();

Procedure

CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
trigger AS
$body$
begin
update contacts.person
set "contact" = new.firstname
where person."primary" = new."primary";
return null;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Tom Lane wrote:

>Jamie Deppeler <jamie@doitonce.net.au> writes:
>
>
>
>You didn't really show the complete context, but seeing that this is a
>trigger and it's trying to do an "UPDATE person" internally, I'll bet
>a nickel that the trigger itself is on update events on person, and
>therefore that you've written an infinite recursion.
>
>Had you shown more context, I could have given some advice on a better
>way to do it. If you're trying to alter the row that's about to be
>stored, you just have to assign to field(s) of the NEW row within the
>trigger. If you want to do something else, you need to explain what.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Frank L. Parks

2005-08-29, 11:23 am

I think that you forgot the table name.

CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
ON contacts FOR EACH ROW
EXECUTE PROCEDURE "contacts"."addContactField"();

Frank

Jamie Deppeler wrote:

> What i am trying to do is update the field contact with field values
> in firstname and lastname
>
> Trigger
>
> CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
> ON FOR EACH ROW
> EXECUTE PROCEDURE "contacts"."addContactField"();
>
> Procedure
>
> CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
> trigger AS
> $body$
> begin
> update contacts.person
> set "contact" = new.firstname
> where person."primary" = new."primary";
> return null;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> Tom Lane wrote:
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Fuhr

2005-08-30, 3:24 am

On Mon, Aug 29, 2005 at 01:45:32PM +1000, Jamie Deppeler wrote:
>
> CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE
> ON FOR EACH ROW
> EXECUTE PROCEDURE "contacts"."addContactField"();

Please show the actual commands that you're running; the above fails
with a syntax error because it's missing a table name. Is this
trigger on contacts.person?

> CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS
> trigger AS
> $body$
> begin
> update contacts.person
> set "contact" = new.firstname
> where person."primary" = new."primary";
> return null;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

As Tom mentioned, if you want to modify the record being inserted
then simply assign a value to one of NEW's columns and have the
function return NEW. In such a case the function will need to be
called in a BEFORE trigger. See "Triggers" and "Trigger Procedures"
in the documentation for more information:

http://www.postgresql.org/docs/8.0/static/triggers.html
http://www.postgresql.org/docs/8.0/...ql-trigger.html

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links