Fwd: Before triggers and usage in partitioned tables

[Sergio Ramazzina <sramazzina@xxxxxxxxx>]

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@xxxxxxxxx>
Date: 2010/3/23
Subject: Re: Before triggers and usage in partitioned tables
To: Albe Laurenz <laurenz.albe@xxxxxxxxxx>


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@xxxxxxxxxx>

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

Attachment: ddl_test_table.sql
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general