Re: Before triggers and usage in partitioned tables

On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazzina(at)gmail(dot)com>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