Обсуждение: Trigger functions
Hi group,
I managed to set up a trigger function and trigger in my database. At this time its very rudimentary but I find that it seems to slow down the insert procees quite a bit. I'm new to postgresql and database servers in general so i'm not sure wheter this is normal or not.
The function is triggered on insert and is supposed to fill a field in the table based on three other fields of the same table (ex: field D = Field A X Field B X Field C). The addition of the trigger to teh table has slowed down the insert procedd considerably. It takes approximatly two second for a simple insert.
Can someone help me with that.
Like I said i'm new to all this so please be indulgent.
Marc
Marc Drouin <marc.drouin@fondex.ca> writes:
> The function is triggered on insert and is supposed to fill a field in the
> table based on three other fields of the same table (ex: field D = Field A X
> Field B X Field C). The addition of the trigger to teh table has slowed
> down the insert procedd considerably. It takes approximatly two second for
> a simple insert.
That's pretty slow, all right, but if you're not going to show us the
details there's not much advice we can offer.
regards, tom lane
Tom Lane wrote: > > Marc Drouin <marc.drouin@fondex.ca> writes: > > The function is triggered on insert and is supposed to fill a field in the > > table based on three other fields of the same table (ex: field D = Field A X > > Field B X Field C). The addition of the trigger to teh table has slowed > > down the insert procedd considerably. It takes approximatly two second for > > a simple insert. > > That's pretty slow, all right, but if you're not going to show us the > details there's not much advice we can offer. Does the trigger update the table or the pseudo row NEW? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Hi Jan,
The trigger function updates the table. At the request of Tom Lane I will post the actual table and trigger
function for you guys to have a better idea.
Marc
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck
Sent: Tuesday, April 01, 2003 10:44 PM
To: Tom Lane
Cc: marc.drouin@fondex.ca; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trigger functions
Tom Lane wrote:
>
> Marc Drouin <marc.drouin@fondex.ca> writes:
> > The function is triggered on insert and is supposed to fill a field in the
> > table based on three other fields of the same table (ex: field D = Field A X
> > Field B X Field C). The addition of the trigger to teh table has slowed
> > down the insert procedd considerably. It takes approximatly two second for
> > a simple insert.
>
> That's pretty slow, all right, but if you're not going to show us the
> details there's not much advice we can offer.
Does the trigger update the table or the pseudo row NEW?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> Marc Drouin wrote: > > Hi Jan, > > The trigger function updates the table. At the request of Tom Lane I > will post the actual table and trigger > function for you guys to have a better idea. Gives me a fair enough idea of what's going on. Your trigger will get fired for every single row inserted and does (probably) an unconditional recomputation of ALL derived values for the WHOLE table ... let me guess, an UPDATE on that table without a WHERE clause runs approximately 2 seconds, right ;-) Change the thing into a BEFORE INSERT trigger, don't use UPDATE, assign the computed values to the NEW row (just do "NEW.column := <expr>") and finally let the trigger RETURN NEW. Jan > > Marc > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck > Sent: Tuesday, April 01, 2003 10:44 PM > To: Tom Lane > Cc: marc.drouin@fondex.ca; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Trigger functions > > Tom Lane wrote: > > > > Marc Drouin <marc.drouin@fondex.ca> writes: > > > The function is triggered on insert and is supposed to fill a > field in the > > > table based on three other fields of the same table (ex: field D = > Field A X > > > Field B X Field C). The addition of the trigger to teh table has > slowed > > > down the insert procedd considerably. It takes approximatly two > second for > > > a simple insert. > > > > That's pretty slow, all right, but if you're not going to show us > the > > details there's not much advice we can offer. > > Does the trigger update the table or the pseudo row NEW? > > Jan > > -- > #======================================================================# > > # It's easier to get forgiveness for being wrong than for being right. > # > # Let's break this rule - forgive me. > # > #================================================== JanWieck@Yahoo.com > # > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Brilliant, absolutelly brilliant!!!!!!!!
It works. Your absolutely right. The update part of the trigger function was in the form:
BEGIN
UPDATE PHOTO SET GEOMETRY = (function_polygon(new.A,new.B,new.C));
RETURN NEW;
END;
notice the absence of the where clause.
now the trigger function looks like this:
BEGIN
new.GEOMETRY = (function_polygon(new.A,new.B,new.C));
RETURN NEW;
END;
and of course the trigger itself has been converted to a before insert or update.
Thanks a lot,
Marc
PS: i'm using plpgsql
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck
Sent: Wednesday, April 02, 2003 2:41 PM
To: marc.drouin@fondex.ca
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trigger functions
> Marc Drouin wrote:
>
> Hi Jan,
>
> The trigger function updates the table. At the request of Tom Lane I
> will post the actual table and trigger
> function for you guys to have a better idea.
Gives me a fair enough idea of what's going on. Your trigger will get
fired for every single row inserted and does (probably) an unconditional
recomputation of ALL derived values for the WHOLE table ... let me
guess, an UPDATE on that table without a WHERE clause runs approximately
2 seconds, right ;-)
Change the thing into a BEFORE INSERT trigger, don't use UPDATE, assign
the computed values to the NEW row (just do "NEW.column := <expr>") and
finally let the trigger RETURN NEW.
Jan
>
> Marc
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck
> Sent: Tuesday, April 01, 2003 10:44 PM
> To: Tom Lane
> Cc: marc.drouin@fondex.ca; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Trigger functions
>
> Tom Lane wrote:
> >
> > Marc Drouin <marc.drouin@fondex.ca> writes:
> > > The function is triggered on insert and is supposed to fill a
> field in the
> > > table based on three other fields of the same table (ex: field D =
> Field A X
> > > Field B X Field C). The addition of the trigger to teh table has
> slowed
> > > down the insert procedd considerably. It takes approximatly two
> second for
> > > a simple insert.
> >
> > That's pretty slow, all right, but if you're not going to show us
> the
> > details there's not much advice we can offer.
>
> Does the trigger update the table or the pseudo row NEW?
>
> Jan
>
> --
> #======================================================================#
>
> # It's easier to get forgiveness for being wrong than for being right.
> #
> # Let's break this rule - forgive me.
> #
> #================================================== JanWieck@Yahoo.com
> #
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)