pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger is not firing immediately
6+ messages / 4 participants
[nested] [flat]
* Trigger is not firing immediately
@ 2008-07-14 06:34 Praveen <praveen.k@renaissance-it.com>
0 siblings, 2 replies; 6+ messages in thread
From: Praveen @ 2008-07-14 06:34 UTC (permalink / raw)
To: pgsql-performance
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 ?
Regards,
Praveen
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger is not firing immediately
@ 2008-07-14 06:48 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Praveen <praveen.k@renaissance-it.com>
1 sibling, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2008-07-14 06:48 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger is not firing immediately
@ 2008-07-14 06:52 Chris <dmagick@gmail.com>
parent: Praveen <praveen.k@renaissance-it.com>
1 sibling, 0 replies; 6+ messages in thread
From: Chris @ 2008-07-14 06:52 UTC (permalink / raw)
To: Praveen <praveen.k@renaissance-it.com>; +Cc: pgsql-performance
Praveen wrote:
>
> 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 ?
Sending the same email over and over again isn't going to get you a
response any quicker.
If you send the details of the trigger and the tables/fields it affects
then you might get a more helpful response.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger is not firing immediately
@ 2008-07-14 07:17 Praveen <praveen.k@renaissance-it.com>
0 siblings, 1 reply; 6+ messages in thread
From: Praveen @ 2008-07-14 07:17 UTC (permalink / raw)
To: pgsql-performance; +Cc: andreas.kretschmer@schollglas.com
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
>>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger is not firing immediately
@ 2008-07-14 09:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: Praveen <praveen.k@renaissance-it.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2008-07-14 09:33 UTC (permalink / raw)
To: Praveen <praveen.k@renaissance-it.com>; +Cc: pgsql-performance; andreas.kretschmer@schollglas.com
hello
Are you sure, so you don't call trigger recursion?
Regards
Pavel Stehule
2008/7/14 Praveen <praveen.k@renaissance-it.com>:
>
> 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
>>>
>>
>>
>>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger is not firing immediately
@ 2008-07-14 09:51 Praveen <praveen.k@renaissance-it.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Praveen @ 2008-07-14 09:51 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance; andreas.kretschmer@schollglas.com
Yes , I am sure it is not trigger recursion.
----- Original Message -----
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Praveen" <praveen.k@renaissance-it.com>
Cc: <pgsql-performance@postgresql.org>; <andreas.kretschmer@schollglas.com>
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 <praveen.k@renaissance-it.com>:
>>
>> 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
>>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list
>> (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-07-14 09:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-14 06:34 Trigger is not firing immediately Praveen <praveen.k@renaissance-it.com>
2008-07-14 06:48 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-07-14 06:52 ` Chris <dmagick@gmail.com>
2008-07-14 07:17 Re: Trigger is not firing immediately Praveen <praveen.k@renaissance-it.com>
2008-07-14 09:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-07-14 09:51 ` Praveen <praveen.k@renaissance-it.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox