max_stack_depth error in postgresql

Question

I Create trigger to store the Salary Amount but When I Fire The Query For Insert

INSERT INTO employees(
            employee_id, first_name, last_name, email, phone_number,  hire_date, 
            job_id, salary, commission_pct, manager_id, department_id)
    VALUES (2002,'poiuy','patel','bhargavgor@dfghj',9898562123,'2012-07-31 00:00:00','IT_PROG',4500.00,0.00,100,60);

Then It Will Show Me The Following Error To set the limit of the max_stack_depth So Can Any One Give Me The Idea TO Solve This Error..

I Try Also To Change The Value Of max_stack_depth in Configuration File But It IS Not Working

Error Like Following

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

I don't see the trigger. Maybe you should show us the source for that, too. (is the trigger function recursive ?) — wildplasser
– wildplasser, Commented Aug 1, 2012 at 11:26

Community · Accepted Answer · 2017-05-23 11:47:26Z

4

I'd say you have an ON INSERT OR UPDATE trigger on employees that, directly or indirectly, does an UPDATE to the employees table without checking if it was invoked directly or via a trigger.

This is often a programming mistake, where you're doing an UPDATE on the employee table instead of having your BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger modify the value of NEW.

Sometimes it's mutual recursion between two triggers, which is harder to deal with. Unfortunately I'm not aware of any way to detect whether a trigger was invoked by a direct client statement or via another stored proc or trigger. Design changes to avoid the mutual recursion are typically required.

See Prevent recursive trigger in PostgreSQL.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Aug 1, 2012 at 13:29

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wildplasser Over a year ago

Here: stackoverflow.com/a/11702666/905902 , I used a per row boolean flag to avoid avalanche cascades (It's a neat hack that might be useful sometimes). I checked in the documentation to see if there would be any magic variable to tell if some function is executed from within a trigger. There isn't.

bWilliamson Over a year ago

But for people who are looking to increase the max_stack_depth, how would they change the setting?

Craig Ringer Over a year ago

@bWilliamson If you need to, you're probably doing something wrong. PostgreSQL isn't very well suited to deeply recursive calls, you should refactor to use an iterative model or preferable to use more relational set operations. Look into whether recursive CTEs (which are actually iterative), i.e. WITH RECURSIVE, can help you.

oguri · Accepted Answer · 2012-08-01 11:27:47Z

2

Can you post what error message you are getting when you are changing the max_stack_depth.

" ulimit -s " in linux systems will give the stack depth. Put the max_stack_depth one or two less than your actual server limit(ulimit -s).

After setting this please do reload.

answered Aug 1, 2012 at 11:27

oguri

213 bronze badges

Collectives™ on Stack Overflow

max_stack_depth error in postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related