Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D72A265064F for ; Mon, 14 Jul 2008 06:48:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 93267-05 for ; Mon, 14 Jul 2008 06:47:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from renaissance-it.com (unknown [164.164.87.55]) by postgresql.org (Postfix) with ESMTP id EAA8E650630 for ; Mon, 14 Jul 2008 06:48:01 -0300 (ADT) Received: (qmail 15421 invoked by uid 109); 14 Jul 2008 09:47:50 -0000 Received: from 192.168.1.6 by sritmail (envelope-from , uid 112) with qmail-scanner-1.25st (perlscan: 1.25st. Clear:RC:1(192.168.1.6):. Processed in 0.009872 secs); 14 Jul 2008 09:47:50 -0000 Received: from unknown (HELO praveenkm) (praveen.k@[192.168.1.6]) (envelope-sender ) by renaissance-it.com (qmail-ldap-1.03) with SMTP for ; 14 Jul 2008 09:47:50 -0000 Message-ID: <02d201c8e597$3cc86e90$274610ac@praveenkm> From: "Praveen" To: "Pavel Stehule" Cc: , References: <010901c8e581$a2b85370$274610ac@praveenkm> <162867790807140233y56d53436pf194f6e38228052e@mail.gmail.com> Subject: Re: Trigger is not firing immediately Date: Mon, 14 Jul 2008 15:21:50 +0530 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/126 X-Sequence-Number: 30726 Yes , I am sure it is not trigger recursion. ----- Original Message ----- From: "Pavel Stehule" To: "Praveen" Cc: ; Sent: Monday, July 14, 2008 3:03 PM Subject: Re: [PERFORM] Trigger is not firing immediately > hello > > Are you sure, so you don't call trigger recursion? > > Regards > Pavel Stehule > > 2008/7/14 Praveen : >> >> Hi all, >> Please find the procedure and trigger which took more time when we try >> to >> update value in table through Procedure. >> >> CREATE OR REPLACE FUNCTION procname1(args) >> RETURNS void AS >> $BODY$ >> DECLARE >> { >> --- >> Some code blocks >> --- >> } >> BEGIN >> >> --> to here it is executing fastly after reaches this statement it's >> taking time >> >> >> update table1 set col1 = val1 where pk = val2 and col2 = val3; >> >> >> -----> HERE table1 IS HAVING THE TRIGGER I GIVEN BELOW THE TRIGGER( >> trigger1) >> >> >> exception >> WHEN OTHERS >> THEN >> raise notice '''EXCEPTION IN procname1 BLOCK 3 : %''',SQLERRM; >> NULL; >> END; $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> ALTER FUNCTION procname1(args); >> >> >> >> >> CREATE OR REPLACE FUNCTION trigger1() >> RETURNS "trigger" AS >> $BODY$ >> BEGIN >> >> IF (TG_OP='UPDATE') THEN >> IF( some condition ) >> THEN >> BEGIN >> INSERT INTO table2(cols) >> VALUES(values); >> IF NOT FOUND THEN >> NULL; >> END IF; >> EXCEPTION >> WHEN OTHERS >> THEN >> NULL; >> END; >> END IF; >> END IF; >> RETURN NULL; >> END; $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> ALTER FUNCTION trigger1(); >> >> >>> ----- Original Message ----- From: "A. Kretschmer" >>> >>> To: >>> Sent: Monday, July 14, 2008 12:18 PM >>> Subject: Re: [PERFORM] Trigger is not firing immediately >>> >>> >>>> am Mon, dem 14.07.2008, um 12:04:49 +0530 mailte Praveen folgendes: >>>>> >>>>> Hi All, >>>>> I am having a trigger in table, If I update the the table manually >>>>> trigger >>>>> is firing immediately(say 200ms per row), But if I update the table >>>>> through >>>>> procedure the trigger is taking time to fire(say 7 to 10 seconds per >>>>> row). >>>>> >>>>> Please tell me what kind of changes can I make so that trigger fire >>>>> immediately while updating the table through procedure ? >>>> >>>> Show us more details like source-code of the procedure, the trigger and >>>> a demonstration. >>>> >>>> >>>> Andreas >>>> -- >>>> Andreas Kretschmer >>>> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) >>>> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net >>>> >>>> -- >>>> Sent via pgsql-performance mailing list >>>> (pgsql-performance@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-performance >>>> >>> >>> >>> >> >> >> >> -- >> Sent via pgsql-performance mailing list >> (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> >