Partitioning rule not behaving as expected - Mailing list pgsql-general

From Nik
Subject Partitioning rule not behaving as expected
Date
Msg-id 1145893280.781519.139690@i40g2000cwc.googlegroups.com
Whole thread Raw
List 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


pgsql-general by date:

Previous
From: arthurjr07@gmail.com
Date:
Subject: Re: Transactions, PostgreSQL and MS Access front end.
Next
From: "Claudio Tognolo"
Date:
Subject: Commit rules or Commit trigger