Re: Trigger is not firing immediately - Mailing list pgsql-performance

From Praveen
Subject Re: Trigger is not firing immediately
Date
Msg-id 010901c8e581$a2b85370$274610ac@praveenkm
Whole thread Raw
In response to Trigger is not firing immediately  ("Praveen" <praveen.k@renaissance-it.com>)
Responses Re: Trigger is not firing immediately  ("Pavel Stehule" <pavel.stehule@gmail.com>)
List pgsql-performance
 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" <andreas.kretschmer@schollglas.com>
> To: <pgsql-performance@postgresql.org>
> 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
>>
>
>
>



pgsql-performance by date:

Previous
From: Chris
Date:
Subject: Re: Trigger is not firing immediately
Next
From: "Pavel Stehule"
Date:
Subject: Re: Trigger is not firing immediately