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 <sramazz...@gmail.com> Date: 2010/3/23 Subject: Re: [GENERAL] Before triggers and usage in partitioned tables To: Albe Laurenz <laurenz.a...@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.a...@wien.gv.at> 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 >
ddl_test_table.sql
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
