Before triggers and usage in partitioned tables

Поиск
Список
Период
Сортировка
От Sergio Ramazzina
Тема Before triggers and usage in partitioned tables
Дата
Msg-id aba5f5d71003230123u631bd482q2422118b3f965826@mail.gmail.com
обсуждение исходный текст
Ответы Re: Before triggers and usage in partitioned tables  (Scott Mead <scott.lists@enterprisedb.com>)
Re: Before triggers and usage in partitioned tables  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Albe Laurenz"
Дата:
Сообщение: Re: pgreplay log file replayer released
Следующее
От: josep porres
Дата:
Сообщение: Re: db error messages when I try to debug with pgadmin