Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and the master table remains empty.

My problem is that one of the triggers on the master table is no longer run on insert or update. Other triggers are run correctly and this trigger worked correctly prior to partitioning.

I suspected this might be similar to constraints and indexes and the trigger needed to be applied to child tables, however this doesn't work either.

Other triggers work, and if they are run alphabetically as the documentation suggests, all should be run before the partition-finding trigger.

The trigger that is causing me grief:

CREATE FUNCTION set_expiry_timestamp() RETURNS trigger AS $BODY$
BEGIN
    -- Don't re-generate if the timeleft value has not changed
    IF (TG_OP = 'UPDATE') THEN
        IF OLD.expires IS NOT NULL AND OLD.timeleft = NEW.timeleft THEN
            RETURN NEW;
        END IF;
    END IF;

    IF (NEW.timeleft = 'SHORT') THEN
        NEW.expires := NEW.updated + interval '30 minutes';
    ELSIF (NEW.timeleft = 'MEDIUM') THEN
        NEW.expires := NEW.updated + interval '2 hours';
    ELSIF (NEW.timeleft = 'LONG') THEN
        NEW.expires := NEW.updated + interval '12 hours';
    ELSIF (NEW.timeleft = 'VERY_LONG') THEN
        NEW.expires := NEW.updated + interval '48 hours';
    ELSE
        RAISE EXCEPTION 'timeleft was unrecognised. Expected SHORT, MEDIUM, LONG or VERY_LONG.';
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER set_expiry AFTER INSERT OR UPDATE ON auction FOR EACH ROW EXECUTE PROCEDURE set_expiry_timestamp()

Query expected to run the trigger:

INSERT INTO auction (created, timeleft) VALUES (NOW(), 'SHORT');

updated is set in a BEFORE INSERT/UPDATE trigger, however it still doesn't fire when updated is supplied.

Any suggestions?

share|improve this question
1  
Please supply the full definition of the trigger function and the full definition of the trigger. There is no point in only showing the function body. Also supply an SQL command where you would expect the trigger to fire along with the values of the old row. –  Erwin Brandstetter Mar 6 '12 at 22:47
    
Updated the post. –  Ross Mar 6 '12 at 22:55
    
Those look suspiciously like WoW auction durations! –  Phil Mar 7 '12 at 0:28

1 Answer 1

up vote 4 down vote accepted

The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:

RETURN NULL;

That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.

You need to do this in a BEFORE trigger or issue an explicit UPDATE statement. The second option is very tricky, however, because you could end up with an endless loop. Better make it a BEFORE trigger.


As an aside: the conditional expression could be simplified with a CASE statement (since version 8.4):

CASE NEW.timeleft
WHEN 'SHORT' THEN
    NEW.expires := NEW.updated + interval '30 minutes';
WHEN 'MEDIUM' THEN
    NEW.expires := NEW.updated + interval '2 hours';
...
ELSE
    RAISE EXCEPTION 'timeleft was unrecognised. ...';
END CASE;
share|improve this answer
    
Oh heck, can't believe I missed that - thanks! –  Ross Mar 6 '12 at 23:19
    
@Ross: It happens to all of us. :) –  Erwin Brandstetter Mar 6 '12 at 23:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.