Fwd: Before triggers and usage in partitioned tables

Sorry but I mistakenly replied only to Albe. Repost it to the list.
Apologize me but also the server version is not correct is 8.1.11 (I forget
the last 1).

Regards

S

---------- Forwarded message ----------
From: Sergio Ramazzina <sramazzina(at)gmail(dot)com>
Date: 2010/3/23
Subject: Re: [GENERAL] Before triggers and usage in partitioned tables
To: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

Albe,

I attached the complete ddl. I hope this will help you in the investigation.
My Postgres server is 8.1.1. I tried also using rules instead of trigger but
the behavior is the same.
I haven't had the time to test it on 8.3 or 8.4

Regards

Sergio

2010/3/23 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

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
>