Обсуждение: Stack Depth

Bob Pawley wrote:
> Hi Folks
>
> I have three triggers and associated functions that fire on one insert
> and moves row ID information to five different tables..
>
> I am getting an error message "stack depth limit exceeded".
>
> Is this normal for, what I consider, a small amount of information transfer?
>
> If so, how do I change the stack depth limit? I can't find anything in
> the documentation that describes this task.
>
> I understand the default stack depth is about 2 meg. How do I determine
> what the stack depth should be so that I avoid this error?
>
> Bob Pawley

check max_stack_depth in your postgresql.conf file

Bob Pawley wrote:

> Hi Folks
>
> I have three triggers and associated functions that fire on one insert
> and moves row ID information to five different tables..
>
> I am getting an error message "stack depth limit exceeded".
>
> [snip]

You've almost certainly got a recursive trigger in there.  The default
stack depth should be good for hundreds of function calls, but if your
triggers are recursive then no depth will be enough.

Regards,

Paul Tillotson

On Wed, Feb 01, 2006 at 03:48:38PM -0800, Bricklen Anderson wrote:
> Bob Pawley wrote:
> >I understand the default stack depth is about 2 meg. How do I determine
> >what the stack depth should be so that I avoid this error?
>
> check max_stack_depth in your postgresql.conf file

Before mucking with max_stack_depth, investigate whether the triggers
are causing infinite recursion (an insert fires a trigger, which
does an insert, which fires a trigger, which does an insert, ad
infinitum).  If that's happening then increasing the stack depth
will merely prolong the agony.

--
Michael Fuhr

Thanks all

I do think I have a recursive problem. It's just hard to see at the moment.

Bob


----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bricklen Anderson" <banderson@presinet.com>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Wednesday, February 01, 2006 4:52 PM
Subject: Re: [GENERAL] Stack Depth


> On Wed, Feb 01, 2006 at 03:48:38PM -0800, Bricklen Anderson wrote:
>> Bob Pawley wrote:
>> >I understand the default stack depth is about 2 meg. How do I determine
>> >what the stack depth should be so that I avoid this error?
>>
>> check max_stack_depth in your postgresql.conf file
>
> Before mucking with max_stack_depth, investigate whether the triggers
> are causing infinite recursion (an insert fires a trigger, which
> does an insert, which fires a trigger, which does an insert, ad
> infinitum).  If that's happening then increasing the stack depth
> will merely prolong the agony.
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

On Wed, Feb 01, 2006 at 05:04:22PM -0800, Bob Pawley wrote:
> I do think I have a recursive problem. It's just hard to see at the moment.

RAISE is your friend.  If you can't figure out where the recursion
is coming from then add debugging output to the trigger functions
to see what gets called when.

--
Michael Fuhr

Thanks Michael

I'll keep this for future use. I beleive I was using insert into and update
when I should have been using update only.

Bob


----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Bricklen Anderson" <banderson@presinet.com>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Wednesday, February 01, 2006 5:22 PM
Subject: Re: [GENERAL] Stack Depth


> On Wed, Feb 01, 2006 at 05:04:22PM -0800, Bob Pawley wrote:
>> I do think I have a recursive problem. It's just hard to see at the
>> moment.
>
> RAISE is your friend.  If you can't figure out where the recursion
> is coming from then add debugging output to the trigger functions
> to see what gets called when.
>
> --
> Michael Fuhr