On Wednesday, September 26, 2012 9:58 AM Alban Hertroys wrote:
> > 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".
>
>
> Oracle uses some arbitrary number to prevent you from looping (50
> apparently). A limit I've run into for perfectly valid situations.
> Thank you for preventing me from doing my job, Oracle.
>
> Both databases have an upper limit. If you reach that limit with
> Postgres, you made a programming error that is easy to catch in
> development (before it reaches production). With Oracle, not so much.
As a developer for me the PostgreSQL behavior is perfectly valid.
But the users claim that for their scenarios that kind of Limit (50) is
valid
and they get proper error. Also Oracle doesn't give error just if someone
calls
recursive functions, it is for recursive SQL's. For example
insert into tbl values(func(..)).
Now func again has insert/update/delete/select which calls func or some
other func, if in such a
Case it reaches depth level 50, only then it throws error.
Also I am not very sure whether we can always catch such error in dev env.
before it reaches production because
If such SQL statements are inside some app logic which at the time of dev,
got missed for test then it might reach production stage.
With Regards,
Amit Kapila.
