Partitioning rule not behaving as expected

From: "Nik" <XLPizza(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Partitioning rule not behaving as expected
Date: 2006-04-24 15:41:20
Message-ID: 1145893280.781519.139690@i40g2000cwc.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I created a master table and three partition tables as per Postgres
documentation. I also created three rules that match the check
constraints. When I insert the values into the master table, the rules
seem to be functional since the data is routed to the appropriate
partition tables. However, data is also inserted into the master table
which is not what I want. Is there a reason DO INSTEAD would not be
evaluated as such?

Here's the setup:

CREATE TABLE zone_data
(
zone_id int4 NOT NULL,
measurement_start timestamptz NOT NULL
)
WITHOUT OIDS TABLESPACE tss_tbs;

CREATE TABLE zone_data_01_01
(
CONSTRAINT zone_data_01_01_measurement_start_check CHECK
(measurement_start <= '2001-01-31 23:59:59-05'::timestamp with time
zone)
) INHERITS (zone_data);

CREATE TABLE zone_data_02_01
(
CONSTRAINT zone_data_02_01_measurement_start_check CHECK
(measurement_start >= '2001-02-01 00:00:00-05'::timestamp with time
zone AND measurement_start <= '2001-02-28 23:59:59-05'::timestamp with
time zone)
) INHERITS (zone_data);

CREATE TABLE zone_data_03_01
(
CONSTRAINT zone_data_03_01_measurement_start_check CHECK
(measurement_start >= '2001-03-01 00:00:00-05'::timestamp with time
zone AND measurement_start <= '2001-03-31 23:59:59-05'::timestamp with
time zone)
) INHERITS (zone_data);

CREATE OR REPLACE RULE zone_data_01_01_insert AS
ON INSERT TO zone_data
WHERE new.measurement_start <= '2001-01-31 23:59:59-05'::timestamp
with time zone DO INSTEAD INSERT INTO zone_data_01_01 (zone_id,
measurement_start)
VALUES (new.zone_id, new.measurement_start);

CREATE OR REPLACE RULE zone_data_02_01_insert AS
ON INSERT TO zone_data
WHERE new.measurement_start >= '2001-02-01 00:00:00-05'::timestamp
with time zone AND new.measurement_start <= '2001-02-28
23:59:59-05'::timestamp with time zone DO INSTEAD INSERT INTO
zone_data_02_01 (zone_id, measurement_start)
VALUES (new.zone_id, new.measurement_start);

CREATE OR REPLACE RULE zone_data_03_01_insert AS
ON INSERT TO zone_data
WHERE new.measurement_start >= '2001-03-01 00:00:00-05'::timestamp
with time zone AND new.measurement_start <= '2001-03-31
23:59:59-05'::timestamp with time zone DO INSTEAD INSERT INTO
zone_data_03_01 (zone_id, measurement_start)
VALUES (new.zone_id, new.measurement_start);

Here is the result:

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-01-13 00:00:09-04');

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-02-13 00:00:09-04');

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-03-13 00:00:09-04');

Query returned successfully: 0 rows affected, 32 ms execution time.

SELECT * FROM zone_data;

zone_id measurement_start
-------- ------------------
81 2001-01-13 00:00:09-04
81 2001-02-13 00:00:09-04
81 2001-03-13 00:00:09-04

SELECT * FROM zone_data_01_01;

zone_id measurement_start
-------- ------------------
81 2001-01-13 00:00:09-04

SELECT * FROM zone_data_02_01;

zone_id measurement_start
-------- ------------------
81 2001-02-13 00:00:09-04

SELECT * FROM zone_data_03_01;

zone_id measurement_start
-------- ------------------
81 2001-03-13 00:00:09-04

Responses

Browse pgsql-general by date

  From Date Subject
Next Message P G 2006-04-24 15:52:14 Install issue on Kubuntu
Previous Message Wim Bertels 2006-04-24 14:14:46 DatawareHousing