PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    You might want to use a trigger for this like the following (untested):

    CREATE FUNCTION cap_salary() RETURNS trigger
AS $$
BEGIN
    IF NEW.salary > 70000
    THEN
        NEW.salary = 70000;
    FI;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cap_salary_on_update
    BEFORE UPDATE ON rowan.employees
    FOR EACH ROW
    EXECUTE FUNCTION cap_salary();

    Invoking the rule system is a problem as it rewrites the query first before anything is executed. The WHERE condition would be part of the rewritten query – but it still includes an UPDATE on rowan.employees, i.e. the rule is executed again – and again and again. Note that the WHERE will be incorporated in the rewritten query and not checked before rewriting the query (this includes that all UPDATE queries will be rewritten, doesn’t matter if the condition is fulfilled).

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search