postgres - prevent rule/trigger from calling himself - infinite loop

Question

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.

update

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;

See [How to avoid recursion in a trigger in PostgreSQL](http://stackoverflow.com/a/31771023/1995738) — klin, Jun 15 '16 at 22:10

score 3 · Answer 1 · edited May 23 '17 at 12:06

3

You can use the function pg_trigger_depth to distinguish between the user-initiated UPDATE and the trigger-initiated UPDATE. You can even put it into the WHEN clause of your trigger declaration. Here are details from another question.

edited May 23 '17 at 12:06

Community

1
1

answered Jun 15 '16 at 21:56

Paul A Jungwirth

23,504
14
74
93

score 3 · Answer 2 · answered Jul 24 '11 at 09:03

3

To avoid infinite loops in triggers, you need to add an extra where clause to avoid re-affecting a row multiple times:

update foo
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.

answered Jul 24 '11 at 09:03

Denis de Bernardy

75,850
13
131
154

I am not really following what you mean? Could you elaborate a bit more please? I found an alternative solution, see my original question. What do you think about this? – Zure Citroen Jul 24 '11 at 10:31
You did something similar as my suggestion, but the other way around and using two tables instead of one. That works too, but be wary of row counts returned if you rely on `FOUND` anywhere. – Denis de Bernardy Jul 24 '11 at 10:34
I would like to avoid introducting the second, dummy table, but am not really seeing it. Could you please add some more insights or examples? Thanks a lot in advance. – Zure Citroen Jul 24 '11 at 11:07
I take it your other row is a revision or backup. So you'd basically use a when condition in the after trigger's definition (eg `when (old.title <> new.title)`), and go: `update A set title = old.title where id = some_variable and title <> old.title`. This will avoid the trigger call and the actual update except when updated. – Denis de Bernardy Jul 24 '11 at 21:01

postgres - prevent rule/trigger from calling himself - infinite loop

2 Answers2