Re: Before triggers and usage in partitioned tables

[Scott Mead <scott.lists@xxxxxxxxxxxxxxxx>]

On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazzina@xxxxxxxxx> wrote:

Hi everybody,

I'm new to postgresql and I need some help to understand the behaviour of before insert triggers in postgresql. I'm trying the sample
documented in the user manual about implementing table partitions (http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html)
and I've a problem with my before insert trigger that I'm not able to understand.

I copied the trigger source down here for reference

CREATE OR REPLACE FUNCTION measurement_insert_trigger()


RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND


            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN


        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;

$$

LANGUAGE plpgsql;

The strange thing is that each time I insert a new row in my measurement table (the master one) I get two rows inserted in the database one in the
master table (measurement) and one in the relative partition table. It seems that the RETURN NULL, that is needed to prevent the insertion in
the master table, isn't well understood by the rdbms. Is there anyone that can explain me the why of this behavior or what I'm doing wrong.

Thanks to everyone who helps me.

Are you sure that you're using a BEFORE trigger?  Can you send the actual trigger that calls the above function?

--Scott M