Thread: stack depth limit exceeded
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;
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
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
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: > > >>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 > > > > >
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: > >> 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 >> >> >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > >
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/static/plpgsql-trigger.html -- Michael Fuhr