Re: In one of negative test row-level trigger results into loop

On Monday, September 24, 2012 8:19 PM Tom Lane wrote:
> Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> > Below test results into Loop:
>
> > [ AFTER INSERT trigger does another insert into its target table ]
>
> Well, of course. The INSERT results in scheduling another AFTER event.
>
> > I understand that user can change his code to make it proper.
>
> > However shouldn$B!G(Bt PostgreSQL also throws errors in such cases
> for recursion
> > level or something related?
>
> No. In the first place, there is no recursion here: the triggers fire
> sequentially, not in a nested way. In the second place, this sort of
> thing is not necessarily wrong --- it's okay for a trigger to do
> something like that, so long as it doesn't repeat it indefinitely.

But in the current case it will repeat until max stack depth is reached.

> (A human can see that this function will never stop adding rows, but
> Postgres' trigger mechanism doesn't have that much insight.) In the
> third place, we don't attempt to prevent queries from taking
> unreasonable amounts of time, and a loop in a trigger is not very
> different from anything else in that line. Use statement_timeout if
> you're concerned about that type of mistake.

I agree with you that such scenario's can be compared with loop in a
trigger.
But some other databases like Oracle handles the scenario reported but not
loop.
To handle for After triggers, there is mutation table concept in Oracle due
to which it errors out
and for Before triggers, it errors out with "maximum number of recursive SQL
levels(50) exceeded".

With Regards,
Amit Kapila.