PostgreSQL Discussion - After insert trigger question

Author

After insert trigger question

mmiranda@americatel.com.sv

2005-04-27, 11:23 am

Hi ppl,
i have a specific question about insert triggers, in the docs i found that
you can change the value of an inserted column using the following syntax:

NEW.column_name := value

and then if you return NEW the new value is stored instead of the original.
this is true if it is a before insert trigger.
The manual also says that the return value of an after insert trigger is
ignored, that means that you cannot update the value of a column in the same
way with an after insert trigger?.
I am concerned about how reliable is an before insert trigger, i made some
computation in my trigger and i want that no matter what happens inside the
trigger (exceptions, erros, divide by zero, etc) , the row must be inserted,
i mean if the trigger fails, i always have the row in my table.
Because of that, i think after insert trigger is the best option, beacuse is
fired after the data is in the table, am i wrong?
thanks

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Stephane Bortzmeyer

2005-04-27, 11:23 am

On Wed, Apr 27, 2005 at 08:45:44AM -0600,
mmiranda@americatel.com.sv <mmiranda@americatel.com.sv> wrote
a message of 21 lines which said:

> I am concerned about how reliable is an before insert trigger, i
> made some computation in my trigger and i want that no matter what
> happens inside the trigger (exceptions, erros, divide by zero, etc)
> , the row must be inserted,

I do not think that pl/pgsql has exception handlers
(http://www.postgresql.org/docs/7.4/...d-messages.html). You
can raise exceptions but not catch them. Could you rewrite your
trigger function with another programming language? In Python, it
would be something like (not tested):

try:
... your computations
finally:
# Insert anyway
return "OK"

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2005-04-27, 11:23 am

On Wed, Apr 27, 2005 at 05:24:16PM +0200, Stephane Bortzmeyer wrote:
> On Wed, Apr 27, 2005 at 08:45:44AM -0600,
> mmiranda@americatel.com.sv <mmiranda@americatel.com.sv> wrote
>
> I do not think that pl/pgsql has exception handlers
> (http://www.postgresql.org/docs/7.4/...d-messages.html).

PostgreSQL 8.0 introduced PL/pgSQL exception handlers.

http://www.postgresql.org/docs/8.0/...-ERROR-TRAPPING

Regardless of whether the trigger is BEFORE or AFTER, an untrapped
error will abort the insert.

CREATE FUNCTION trigfunc() RETURNS trigger AS '
DECLARE
i integer;
BEGIN
i := NEW.x / 0;
RETURN NULL;
END;
' LANGUAGE plpgsql;

CREATE TABLE foo (x integer);

CREATE TRIGGER footrig_after AFTER INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigfunc();

INSERT INTO foo VALUES (123);
ERROR: division by zero
CONTEXT: PL/pgSQL function "trigfunc" line 4 at assignment

SELECT * FROM foo;
x
---
(0 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

mmiranda@americatel.com.sv

2005-04-27, 1:23 pm

>PostgreSQL 8.0 introduced PL/pgSQL exception handlers.

>http://www.postgresql.org/docs/8.0/...ol-structures.h
tml#PLPGSQL-ERROR-TRAPPING

>Regardless of whether the trigger is BEFORE or AFTER, an untrapped
>error will abort the insert.

>CREATE FUNCTION trigfunc() RETURNS trigger AS '
>DECLARE
> i integer;
>BEGIN
> i := NEW.x / 0;
> RETURN NULL;
>END;
>' LANGUAGE plpgsql;

>CREATE TABLE foo (x integer);

>CREATE TRIGGER footrig_after AFTER INSERT ON foo
> FOR EACH ROW EXECUTE PROCEDURE trigfunc();

>INSERT INTO foo VALUES (123);
>ERROR: division by zero
>CONTEXT: PL/pgSQL function "trigfunc" line 4 at assignment

>SELECT * FROM foo;
> x
>---
>(0 rows)

>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/

So, the answer is: "double check every operation and use exeption handlers"
What about performance, if its a matter of choice between after or before
insert, what perform better?
thanks

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Michael Fuhr

2005-04-27, 1:23 pm

On Wed, Apr 27, 2005 at 10:38:48AM -0600, mmiranda@americatel.com.sv wrote:
>
> What about performance, if its a matter of choice between after or before
> insert, what perform better?

According to the "Triggers" chapter in the documentation, "If you have
no specific reason to make a trigger before or after, the before case
is more efficient, since the information about the operation doesn't
have to be saved until end of statement."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sponsored Links