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

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

From
Amit Kapila
Date:

Below test results into Loop:

 

1.create test table

CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);

 

2.create trigger function

CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS

$$

DECLARE

PSQL VARCHAR2;

BEGIN

Raise info 'This is Test!!!';

psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';

execute psql;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

 

3.create trigger

CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON TEST_TABLE FOR EACH ROW

EXECUTE PROCEDURE TRIG_FUNC ();

 

4.Perform an insert statement

INSERT INTO TEST_TABLE VALUES'jack',25);

 

Now, You will see an always loop.

I understand that user can change his code to make it proper.

However shouldn’t PostgreSQL also throws errors in such cases for recursion level or something related?

 

With Regards,

Amit Kapila.

 

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

From
Tom Lane
Date:
Amit Kapila <amit.kapila@huawei.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.
(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.

            regards, tom lane


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

From
Amit Kapila
Date:
  On Monday, September 24, 2012 8:19 PM Tom Lane wrote:
> Amit Kapila <amit.kapila@huawei.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.



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

From
Alban Hertroys
Date:
> 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
validsituations. 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
catchin development (before it reaches production). With Oracle, not so much. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



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

From
Amit Kapila
Date:
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.