3

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.
Bram Verstraten
  • 1,414
  • 11
  • 24
Bhargav Gor
  • 521
  • 2
  • 6
  • 7
  • 2
    I don't see the trigger. Maybe you should show us the source for that, too. (is the trigger function recursive ?) – wildplasser Aug 01 '12 at 11:26

2 Answers2

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Here: http://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. – wildplasser Aug 01 '12 at 13:51
  • But for people who are looking to increase the max_stack_depth, how would they change the setting? – bWilliamson Jun 25 '19 at 09:15
  • 1
    @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. – Craig Ringer Jul 23 '19 at 04:37
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.

oguri
  • 21
  • 3