Re: [GENERAL] In one of negative test row-level trigger results into loop
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In one of negative test row-level trigger results into loop
> 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. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In one of negative test row-level trigger results into loop
On Monday, September 24, 2012 8:19 PM Tom Lane wrote: > Amit Kapila 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In one of negative test row-level trigger results into loop
> On Monday, September 24, 2012 7:44 PM Adrian Klaver > On 09/24/2012 07:03 AM, Amit Kapila wrote: > > 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? > > What database are you running this on? > I get : > test=> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT); > ERROR: type "varchar2" does not exist > LINE 1: CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT); > I am sorry, actually I was trying to compare behavior with Oracle so used it wrongly. In Create Table statement, change Varchar2 to Varchar(30) And change in trigger function from Varchar2 to Varchar(200) With Regards, Amit Kapila. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In one of negative test row-level trigger results into loop
On 09/24/2012 07:03 AM, Amit Kapila wrote: > 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? What database are you running this on? I get : test=> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT); ERROR: type "varchar2" does not exist LINE 1: CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT); > > With Regards, > > Amit Kapila. > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In one of negative test row-level trigger results into loop
Amit Kapila 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general