CODEWITHSUNDEEP
databasepostgresqlodoo
hany
hany

Reputation: 49

OperationalError: stack depth limit exceeded

Working with openerp 6.1, I want to make a trigger to create two phone calls with dates different from the date in the inserted row (one after 6 months and one after one year).

I created this function:

CREATE OR REPLACE FUNCTION create_first_phonecall()
  RETURNS trigger AS
$BODY$
BEGIN
select * from crm_phonecall where id= (select max(id) from crm_phonecall);
   insert into crm_phonecall (name,date,state,active,user_id,x_contractnumber,x_instdate)
values('Hany', CURRENT_TIMESTAMP,'open',true,16,123456789999,CURRENT_TIMESTAMP);
return null;
END;
$BODY$
  LANGUAGE 'plpgsql'

Then I created the following trigger:

CREATE TRIGGER create_first_phonecall
    AFTER insert ON crm_phonecall
    EXECUTE PROCEDURE create_first_phonecall();

But when i try to insert a phone call i get the following error:

Client Traceback (most recent call last):
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\common\http.py", line 180, in dispatch
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\controllers\main.py", line 970, in create
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\common\openerplib\main.py", line 250, in proxy
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\common\openerplib\main.py", line 117, in proxy
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\common\http.py", line 608, in send


Server Traceback (most recent call last):
  File "C:\Program Files (x86)\OpenERP\Server\server\openerp\addons\web\common\http.py", line 593, in send
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\netsvc.py", line 360, in dispatch_rpc
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\service\web_services.py", line 586, in dispatch
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\osv\osv.py", line 167, in execute_kw
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\osv\osv.py", line 121, in wrapper
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\osv\osv.py", line 176, in execute
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\osv\osv.py", line 164, in execute_cr
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\osv\orm.py", line 4194, in create
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\sql_db.py", line 152, in wrapper
  File "C:\Program Files (x86)\OpenERP\Server\server\.\openerp\sql_db.py", line 212, in execute
OperationalError: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."res_users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
SQL statement "INSERT INTO crm_phonecall (name,date,state,active,user_id,x_contractnumber,x_instdate) values('Hany', CURRENT_TIMESTAMP,'open',true,16,123456789999,CURRENT_TIMESTAMP)"
PL/pgSQL function "create_first_phonecall" line 3 at SQL statement
SQL statement "INSERT INTO crm_phonecall (name,date,state,active,user_id,x_contractnumber,x_instdate) values('Hany', CURRENT_TIMESTAMP,'open',true,16,123456789999,CURRENT_TIMESTAMP)"
PL/pgSQL function "create_first_phonecall" line 3 at SQL statement
SQL statement "INSERT INTO crm_phonecall (name,date,state,active,user_id,x_contractnumber,x_instdate) values('Hany', CURRENT_TIMESTAMP,'open',true,16,123456789999,CURRENT_TIMESTAMP)"
...

(Many more of the same message from Postgres.)

Can anyone help me with that?

Upvotes: 1

Views: 1426

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658062

RETURN NULL does not cancel the operation in an AFTER trigger. The trigger inserts a new row, which triggers the next INSERT ... which results in an endless loop.
Thus the stack overflow - yes, you have come to the right site. ;)

You need a "break condition": something to let Postgres see the difference between automatically added and initially inserted rows. (My first draft with a BEFORE alone trigger did not solve this.) Could be anything that's clearly defined. Like a boolean flag:

ALTER TABLE crm_phonecall ADD COLUMN auto_insert bool;

Set the flag to TRUE in automatically inserted rows and break for such rows:

CREATE OR REPLACE FUNCTION create_first_phonecall()
  RETURNS trigger AS
$func$
BEGIN

-- Removed incoherent code

IF NEW.auto_insert THEN
   -- do nothing
ELSE
   INSERT INTO crm_phonecall
         (name,    ..., auto_insert)
   VALUES('Hany',  ..., TRUE );        -- automatically inserted row
END IF;

RETURN NULL;

END
$func$ LANGUAGE plpgsql;

And make the trigger BEFORE, not AFTER:

CREATE TRIGGER create_first_phonecall
BEFORE INSERT ON crm_phonecall
EXECUTE PROCEDURE create_first_phonecall();

Alternative: RULE

The alternative would be a RULE. Typically, triggers are simpler to handle, but a RULE could avoid the endless loop, too.

Upvotes: 1

Related Questions