Thread: Before triggers and usage in partitioned tables

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;

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

Sergio Ramazzina <sramazzina@gmail.com> writes:
> [ test case ]

It seems to work as expected for me:

regression=# insert into tpm_wind_dcn values ('r','i','2010-01-09',42);
INSERT 0 0
regression=# select * from tpm_wind_dcn;
 router | interface |    data    | qora | m1 | m2 | m3 | m4 | m5 | m6
--------+-----------+------------+------+----+----+----+----+----+----
 r      | i         | 2010-01-09 |   42 |    |    |    |    |    |
(1 row)

regression=# select * from only tpm_wind_dcn;
 router | interface | data | qora | m1 | m2 | m3 | m4 | m5 | m6
--------+-----------+------+------+----+----+----+----+----+----
(0 rows)

regression=# select * from tp_wind_dcn_day9;
 router | interface |    data    | qora | m1 | m2 | m3 | m4 | m5 | m6
--------+-----------+------------+------+----+----+----+----+----+----
 r      | i         | 2010-01-09 |   42 |    |    |    |    |    |
(1 row)


I wonder whether you are confused about how inheritance works.  Rows
that are in child tables are supposed to show up when the parent is
read (as in my first SELECT above), except when you specify ONLY
(as in my second SELECT).

            regards, tom lane