Postgres Pro
Downloads
Home   >   mailing lists

Re: Before triggers and usage in partitioned tables - Mailing list pgsql-general

From Sergio Ramazzina
Subject Re: Before triggers and usage in partitioned tables
Date
Msg-id aba5f5d71003230534r4244433m2a1256ecdde3fbee@mail.gmail.com
Whole thread Raw
In response to Re: Before triggers and usage in partitioned tables  (Scott Mead <scott.lists@enterprisedb.com>)
List pgsql-general
Tree view
Hi Scott,

here's the trigger and the function

CREATE OR REPLACE FUNCTION tpm_wind_dcn_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.data >= DATE '2010-01-01' AND NEW.data < DATE '2010-01-02' ) THEN
        INSERT INTO tp_wind_dcn_day1 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-02' AND NEW.data < DATE '2010-01-03' ) THEN
        INSERT INTO tp_wind_dcn_day2 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-03' AND NEW.data < DATE '2010-01-04' ) THEN
        INSERT INTO tp_wind_dcn_day3 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-04' AND NEW.data < DATE '2010-01-05' ) THEN
        INSERT INTO tp_wind_dcn_day4 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-05' AND NEW.data < DATE '2010-01-06' ) THEN
        INSERT INTO tp_wind_dcn_day5 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-06' AND NEW.data < DATE '2010-01-07' ) THEN
        INSERT INTO tp_wind_dcn_day6 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-07' AND NEW.data < DATE '2010-01-08' ) THEN
        INSERT INTO tp_wind_dcn_day7 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-08' AND NEW.data < DATE '2010-01-09' ) THEN
        INSERT INTO tp_wind_dcn_day8 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-09' AND NEW.data < DATE '2010-01-10' ) THEN
        INSERT INTO tp_wind_dcn_day9 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-10' AND NEW.data < DATE '2010-01-11' ) THEN
        INSERT INTO tp_wind_dcn_day10 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-11' AND NEW.data < DATE '2010-01-12' ) THEN
        INSERT INTO tp_wind_dcn_day11 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-12' AND NEW.data < DATE '2010-01-13' ) THEN
        INSERT INTO tp_wind_dcn_day12 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-13' AND NEW.data < DATE '2010-01-14' ) THEN
        INSERT INTO tp_wind_dcn_day13 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-14' AND NEW.data < DATE '2010-01-15' ) THEN
        INSERT INTO tp_wind_dcn_day14 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-15' AND NEW.data < DATE '2010-01-16' ) THEN
        INSERT INTO tp_wind_dcn_day15 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-16' AND NEW.data < DATE '2010-01-17' ) THEN
        INSERT INTO tp_wind_dcn_day16 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-17' AND NEW.data < DATE '2010-01-18' ) THEN
        INSERT INTO tp_wind_dcn_day17 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-18' AND NEW.data < DATE '2010-01-19' ) THEN
        INSERT INTO tp_wind_dcn_day18 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-19' AND NEW.data < DATE '2010-01-20' ) THEN
        INSERT INTO tp_wind_dcn_day19 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-20' AND NEW.data < DATE '2010-01-21' ) THEN
        INSERT INTO tp_wind_dcn_day20 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-21' AND NEW.data < DATE '2010-01-22' ) THEN
        INSERT INTO tp_wind_dcn_day21 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-22' AND NEW.data < DATE '2010-01-23' ) THEN
        INSERT INTO tp_wind_dcn_day22 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-23' AND NEW.data < DATE '2010-01-24' ) THEN
        INSERT INTO tp_wind_dcn_day23 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-24' AND NEW.data < DATE '2010-01-25' ) THEN
        INSERT INTO tp_wind_dcn_day24 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-25' AND NEW.data < DATE '2010-01-26' ) THEN
        INSERT INTO tp_wind_dcn_day25 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-26' AND NEW.data < DATE '2010-01-27' ) THEN
        INSERT INTO tp_wind_dcn_day26 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-27' AND NEW.data < DATE '2010-01-28' ) THEN
        INSERT INTO tp_wind_dcn_day27 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-28' AND NEW.data < DATE '2010-01-29' ) THEN
        INSERT INTO tp_wind_dcn_day28 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-29' AND NEW.data < DATE '2010-01-30' ) THEN
        INSERT INTO tp_wind_dcn_day29 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-30' AND NEW.data < DATE '2010-01-31' ) THEN
        INSERT INTO tp_wind_dcn_day30 VALUES (NEW.*);
    ELSIF ( NEW.data >= DATE '2010-01-31' AND NEW.data < DATE '2010-02-01' ) THEN
        INSERT INTO tp_wind_dcn_day31 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the tpm_wind_dcn_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_tpm_wind_dcn_trigger
    BEFORE INSERT ON tpm_wind_dcn
    FOR EACH ROW EXECUTE PROCEDURE tpm_wind_dcn_insert_trigger();

Thanks in advance for your help.

Regards

Sergio


2010/3/23 Scott Mead <scott.lists@enterprisedb.com>
On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazzina@gmail.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

pgsql-general by date:

Previous
From: Scott Mead
Date:
Subject: Re: Before triggers and usage in partitioned tables
Next
From: "Albe Laurenz"
Date:
Subject: Re: pgreplay log file replayer released