postgresqlrecursiontriggersrulesinfinite

Updating a table with a view and getting "infinite recursion detected in rules for relation "employees"


Below is the code I am using in a PostgreSQL 16 database. Every time I try to update the salary I get:

ERROR: infinite recursion detected in rules for relation "employees"
SQL state: 42P17"

Object used

create table rowan.employees (emp_id serial primary key, name varchar(100), salary numeric);

Inserting values

insert into rowan.employees (name, salary) values 
('John Doe', 50000),
('Jane Doe', 60000),
('Rafael Orta',80000);

Creation of the rule

create or replace rule update_salary  
as on update to rowan.employees
where new.salary > 70000
and pg_trigger_depth() = 0
do instead
  update rowan.employees
  set salary = 70000
  where emp_id = new.emp_id;

Performing the Update

update rowan.employees set salary = 80000 where emp_id = 3;

Checking the values

select * from rowan.employees;

I am trying to do the command below:

update rowan.employees set salary = 80000 where emp_id = 3;

I was expecting for it to update the salary to be 70000.


Solution

    1. Listen to the comments and use triggers instead of rules.

    2. If you want to use a rule then a simple example:

    create table rule_test (id integer, fld_1 integer);
    insert into rule_test values (1, 5), (2, 7);
    create view rule_test_vw as select * from rule_test ;
    
    create or replace rule update_do_nothing as on update to rule_test_vw do instead nothing;
    create or replace rule update_do_something as on update to rule_test_vw where new.fld_1 > 10 do instead update rule_test set fld_1 = 10 where id = new.id;
    create or replace rule update_do_passthru as on update to rule_test_vw where new.fld_1 <= 10 do instead update rule_test set fld_1 = new.fld_1 where id = new.id;
    
    update rule_test_vw set fld_1 = 15 where id = 1;
    update rule_test_vw set fld_1 = 3 where id = 2;
    
    select * from rule_test;
     id | fld_1 
    ----+-------
      1 |    10
      2 |     3
    

    Basically create a view over the table and use do instead against the view with the instead acting against the table. That will deal with the recursion issue.