Before triggers and usage in partitioned tables

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

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>

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

Sorry but I mistakenly replied only to Albe. Repost it to the list.
Apologize me but also the server version is not correct is 8.1.11 (I forget
the last 1).

Regards

S

---------- Forwarded message ----------
From: Sergio Ramazzina <sramazzina@gmail.com>
Date: 2010/3/23
Subject: Re: [GENERAL] Before triggers and usage in partitioned tables
To: Albe Laurenz <laurenz.albe@wien.gv.at>

Albe,

I attached the complete ddl. I hope this will help you in the investigation.
My Postgres server is 8.1.1. I tried also using rules instead of trigger but
the behavior is the same.
I haven't had the time to test it on 8.3 or 8.4

Regards

Sergio

2010/3/23 Albe Laurenz <laurenz.albe@wien.gv.at>

Sergio Ramazzina wrote:

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