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

Question

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.

Answer 1

The trigger

It's cheaper for PostgreSQL to check the condition before calling the trigger function:

CREATE FUNCTION cap_salary() RETURNS trigger AS $f$ 
BEGIN --trigger won't fire unless the salary needs to be reduced
      NEW.salary = 70000;--so no need for an `IF`
      RETURN NEW;
END $f$ LANGUAGE plpgsql;

CREATE TRIGGER cap_salary_on_update
    BEFORE INSERT OR UPDATE OF salary ON rowan.employees 
    FOR EACH ROW 
    WHEN (NEW.salary > 70000) --this limits when the trigger fires
    EXECUTE FUNCTION cap_salary();

It achieves the same goal, but you save some execution time by avoiding the more expensive PL/pgSQL calls whenever they aren't necessary.
You can compare the trigger execution time=1114.413 calls=200003 in this demo to time=334.140 calls=60373 in this demo.

---

The rule

You only need one: demo

begin;
alter table rowan.employees rename to t_employees; 
create view rowan.employees as table rowan.t_employees;

create rule salary_limit as 
  on update to rowan.employees
  do instead update rowan.t_employees 
  set salary = case when new.salary <= 70000 
                      or new.salary is null
                    then new.salary
                    else 70000 end
  where emp_id is not distinct from new.emp_id
  returning *;

commit;

1. The real table needs to be "hidden" behind some other object. It can be a view but the rule system can just as well re-route queries from a regular table. The rule has to catch all queries that are directed at the thing posing as the table and redirect them to the real table.
2. Handling the salary limit in a case statement might be preferable over multiple conditional rules. The presence of the rule condition prevents you from adding a returning clause, which in turn breaks all calls that would want to use it. Some client applications, libraries and ORMs add the returning clause by default whether you use it or not - breaking that would spawn some hard to trace errors.
3. It's good to handle the null case for both the salary field as well as the emp_id. It has to be added to the case and the where condition should be switched from null-unsafe = to is not distinct from - otherwise the rule won't handle null salaries, or updates that target all rows and specifically those with a null in emp_id.
   The latter isn't as important in your case since emp_id is a primary key which implies it's not null - it's strictly necessary whenever there's no explicit or implied not null constraint.
4. Wrapping the setup of all this in a single, begin;...commit; transaction avoids introducing a time window during which concurrent transactions
   • temporarily lose sight of the table right after it gets renamed
   • get unrestricted access to it through the view when that gets created, before you add the rule.

Answer 2

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.

Answer 3

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).