Trigger functions

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)