Before triggers and usage in partitioned tables

From: Sergio Ramazzina <sramazzina(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Before triggers and usage in partitioned tables
Date: 2010-03-23 08:23:22
Message-ID: aba5f5d71003230123u631bd482q2422118b3f965826@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Sergio

Responses

Browse pgsql-general by date

  From Date Subject
Next Message josep porres 2010-03-23 09:43:16 Re: db error messages when I try to debug with pgadmin
Previous Message Albe Laurenz 2010-03-23 08:19:03 Re: pgreplay log file replayer released