Optimize a trigger excluding a particular user in PostgreSQL

Question

I would like to improve the way a trigger in PostgreSQL is disabled when a particular user makes modifications.

E.g.: the trigger should be oblivious to changes made by an 'app' user and start on every modification from any other user.

I am currently using the following code:

IF (CURRENT_USER = 'app') THEN 
    RETURN NULL; 
END IF;

As in :

CREATE OR REPLACE FUNCTION update_flag() RETURNS trigger AS $BODY$
BEGIN
    IF (CURRENT_USER = 'app') THEN 
        RETURN NULL; 
    END IF;

    IF (TG_OP = 'INSERT') THEN
        ...
    ELSIF (TG_OP = 'UPDATE') THEN
        ...
    ELSIF (TG_OP = 'DELETE') THEN
        ...
    END IF;

    RETURN NULL;

END;$BODY$
LANGUAGE plpgsql;


CREATE TRIGGER trigger_categories AFTER INSERT OR UPDATE OR DELETE
    ON "categories"
    FOR EACH ROW
    EXECUTE PROCEDURE update_flag();

Do you see room for improvement here? I thought about embedding the condition in the CREATE TRIGGER part, before the call to update_flag(), but I can't find out how. Would it make any difference?

did you try for each row when current_user = 'app'? (Available since 9.0) — user330315
– user330315, Commented Jan 9, 2013 at 11:01
@a_horse_with_no_name Sorry, my version is too old. I guess it would be when current_user <> 'app' though. — David M.
– David M., Commented Jan 9, 2013 at 11:05
Yes, of course it should be <>. Good time to think about an upgrade ;) — user330315
– user330315, Commented Jan 9, 2013 at 11:10

Craig Ringer · Accepted Answer · 2013-01-09 12:29:16Z

3

As a_horse_with_no_name points out, on newer versions triggers can be made conditional on an expression using the WHEN clause to CREATE TRIGGER, as per the CREATE TRIGGER documentation.

This is not available in 8.4, so you're already using the best available option, testing current_user inside the trigger and returning.

(Note that current_user is affected by SECURITY DEFINER. You might prefer to use session_user instead.)

In general, the performance impact of running a trigger that just exits immediately is fairly small, especially compared to the cost of index and table inserts/updates. I wouldn't worry too much; there are probably lower hanging performance fruit.

answered Jan 9, 2013 at 12:29

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David M. Over a year ago

"there are probably lower hanging performance fruit": I love this one! This question was merely for the sake of perfectionism and documentation :)

Collectives™ on Stack Overflow

Optimize a trigger excluding a particular user in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related