Search Postgresql Archives

Re: Before triggers and usage in partitioned tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazzina@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux