Re: Before triggers and usage in partitioned tables

Sergio Ramazzina wrote:
> 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.

That should not happen.

Can you send the DDL used to create the tables and the CREATE TRIGGER statement?

Yours,
Laurenz Albe