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;

Answer 1

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.

Answer 2

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.