Trigger loop question

Mike Nolan <nolan@gw.tssi.com> writes:

If I set up an on update trigger for table 'A' that updates the
corresponding column in table 'B', and one for table 'B' that updates
the corresponding column in table 'A', does that create an endless loop?

Yes.

You could break the loop perhaps by not issuing an UPDATE if the data is
already correct in the other table.

regards, tom lane

Mike Nolan <nolan@gw.tssi.com> writes:

If I set up an on update trigger for table 'A' that updates the
corresponding column in table 'B', and one for table 'B' that updates
the corresponding column in table 'A', does that create an endless loop?

Yes.

You could break the loop perhaps by not issuing an UPDATE if the data is
already correct in the other table.

The trigger on table 'A' is obviously going to see both the old value and the
new value for the column. If it queries table 'B', it would see
the current value there.

However, if I update table 'B' and the 2nd trigger fires, that trigger
will still see the OLD value if does a query on table 'A', since I
think transaction atomic rules require that any updated values aren't
made available to the outside world (including other triggers) until the
transaction is complete.

I tested this, and the 2nd trigger still sees the original value of
the field from the first table, which I think is the proper result.
--
Mike Nolan

Mike Nolan wrote:

However, if I update table 'B' and the 2nd trigger fires, that trigger
will still see the OLD value if does a query on table 'A', since I
think transaction atomic rules require that any updated values aren't
made available to the outside world (including other triggers) until the
transaction is complete.

I could be mistaken here, but... I don't believe that is the case. The
transaction can see what's going on inside of itself. Everything outside
of the transaction typically won't see what is inside the transaction,
until it is committed anyway.

I tested this, and the 2nd trigger still sees the original value of
the field from the first table, which I think is the proper result.

I think this has more to do with whether the first trigger was fired
BEFORE or AFTER the UPDATE. If the first trigger is fired BEFORE the
UPDATE, then the second trigger (fired on the UPDATE) will not see the
AFTER values of the first trigger. If you fire the first trigger as
AFTER, I bet you'll see the changes.

Greg

I think this has more to do with whether the first trigger was fired
BEFORE or AFTER the UPDATE. If the first trigger is fired BEFORE the
UPDATE, then the second trigger (fired on the UPDATE) will not see the
AFTER values of the first trigger. If you fire the first trigger as
AFTER, I bet you'll see the changes.

Yes it does. OK, that means Tom's original suggestion of checking
the other table for the same value before updating it should prevent
an infinite loop, providing that's done from a pair of 'after update'
triggers, using the NEW.column entries in the triggered table to update
the other table.

This tested OK in both directions.

Of course, if I need to do any manipulation of the fields in the
table being updated by the SQL command, that will require a separate
'before update' trigger.
--
Mike Nolan

Mike Nolan <nolan@gw.tssi.com> writes:

Yes it does. OK, that means Tom's original suggestion of checking
the other table for the same value before updating it should prevent
an infinite loop, providing that's done from a pair of 'after update'
triggers, using the NEW.column entries in the triggered table to update
the other table.

Actually, I wasn't thinking very clearly. The easiest way to break
the loop is to avoid updating the other table when OLD.x = NEW.x
in the trigger's arguments. The other way requires a rather-redundant
SELECT to see what is in the other table.

regards, tom lane

Actually, I wasn't thinking very clearly. The easiest way to break
the loop is to avoid updating the other table when OLD.x = NEW.x
in the trigger's arguments. The other way requires a rather-redundant
SELECT to see what is in the other table.

If I have to update the other table for any other purpose as part of
that trigger, or if some other trigger updates that table, couldn't that
result in an infinite loop?

It seems like the select-and-check method, even though it may be redundant
most of the time, is the belt-and-suspenders way of avoiding an infinite loop.

Here's a really weird question. If in the trigger for table A I have
more than one statement that updates table B, or if more than one trigger
procedure updates table B, does that cause multiple firings of either
before or after update triggers on table B?
--
Mike Nolan

Mike Nolan <nolan@gw.tssi.com> writes:

Actually, I wasn't thinking very clearly. The easiest way to break
the loop is to avoid updating the other table when OLD.x = NEW.x
in the trigger's arguments. The other way requires a rather-redundant
SELECT to see what is in the other table.

If I have to update the other table for any other purpose as part of
that trigger, or if some other trigger updates that table, couldn't that
result in an infinite loop?

Well, I'm assuming that your update logic converges to a fixed state;
if it doesn't, seems like you've got problems anyway ...

Here's a really weird question. If in the trigger for table A I have
more than one statement that updates table B, or if more than one trigger
procedure updates table B, does that cause multiple firings of either
before or after update triggers on table B?

Yes, and yes. You get one firing per row update event, IIRC, no matter
where that update came from.

regards, tom lane