Thread: In one of negative test row-level trigger results into loop
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.
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
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.
> 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.
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.