Say I have the following table:
create table A ( identifier integer not null primary key, title text not null, ... -- other fields );
When executing an UPDATE on A, I necessarily don't want to only update the targeted row, but I also want to apply updates to another row in A. I tried writing a 'rewrite rule' or 'before trigger', but I always end up with an infinite-loop:
create function A(in A, in A) returns void as $$ declare i integer; begin -- do some logic which finds other row (hardcoded in this example) i = 2; -- update old row update A set title = $2.title where identifier = $1.identifier; -- apply updates to other row update A set ... where identifier = i; end; $$ language plpgsql; create rule A as on update to A do instead select A(old, new);
The data on which I tested:
insert into A (identifier, title) values (1, 'old title 1'); insert into A (identifier, title) values (2, 'old title 2'); update A set title = 'new title 1' where identifier = 1;
The same problem also arises when using a 'before trigger', instead of a 'rewrite rule'.
Is there a way to bypass a rule/trigger if needed? I cannot alter table A disable rule A after the first row and alter table A enable rule A before returning, because table A is in use, by ourself.
I managed to do this by creating a dummy inherited table on which the 'inner update' is done, instead on the table directly. This bypasses the trigger/rule.
drop table if exists A cascade; create table A ( identifier serial not null primary key, title text not null ); create table A_ ( ) inherits (A); create or replace function A() returns trigger as $$ declare i integer; begin -- create duplicate row insert into A (title) values (new.title) returning identifier into i; -- update new row update A_ set title = new.title where identifier = i; -- do not propagate update return null; end $$ language plpgsql; create trigger A before update on A for each row execute procedure A(); insert into A (title) values ('old title 1'); insert into A (title) values ('old title 2'); update A set title = 'new title 1' where identifier = 1; select * from A;
To avoid infinite loops in triggers, you need to add an extra where clause to avoid re-affecting a row multiple times:
set bar = 'baz'
where bar <> 'baz'
The same cannot be done to avoid recursion in rules, because the new queries are tossed in when the original one is (and the new ones are) getting parsed, without any consideration for the individual queries' where clauses.