Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

postgresql - Update a table with a trigger after update

I have two tables

 batch (batch_id,start_date,end_date,batch_strength,is_locked)
 sem (user_id,is_active,no_of_days)

I have executed the trigger procedure given below then update the table using query

CREATE OR REPLACE FUNCTION em_batch_update()
  RETURNS trigger AS $em_sem_batch$
BEGIN

UPDATE batch set is_locked='TRUE'
where (start_date
       + (select no_of_days from sem
          WHERE is_active='TRUE' and user_id='OSEM')
      ) <= current_date;

return NEW;

END;
$em_sem_batch$  LANGUAGE plpgsql;

CREATE TRIGGER em_sem_batch
BEFORE UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE em_batch_update();

update em_batch set batch_strength=20 where batch_id='OD001C001B3';

Error occured:

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

There are several ways to prevent the infinite recursion you built into your trigger, the most elegant and performant probably adding a WHERE clause to the UPDATE statement in your trigger function:

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

UPDATE batch b
SET    is_locked = TRUE
FROM   sem s
WHERE  s.is_active
AND    s.user_id = 'OSEM'
AND    b.start_date <= (current_date - s.no_of_days)
AND    b.is_locked IS DISTINCT FROM TRUE; -- prevent infinite recursion!

RETURN NULL;

END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER em_sem_batch
BEFORE UPDATE ON batch
FOR EACH STATEMENT
EXECUTE PROCEDURE em_batch_update();

I changed a few other things to move towards sanity:

Trigger functions invoked by per-statement triggers should always return NULL.

  • batch.is_locked and sem.is_active look like boolean columns. Use a proper boolean data type for them. My code is building on it.

  • I also rewrote your UPDATE query completely. In particular the condition on batch.start_date so that an index can be used if available.

  • If batch.is_locked is defined NOT NULL, the WHERE condition can be simplified to:

        AND    b.is_locked = FALSE;
    

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...