Partitioning rule not behaving as expected

Поиск
Список
Период
Сортировка
От Nik
Тема Partitioning rule not behaving as expected
Дата
Msg-id 1145893280.781519.139690@i40g2000cwc.googlegroups.com
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: arthurjr07@gmail.com
Дата:
Сообщение: Re: Transactions, PostgreSQL and MS Access front end.
Следующее
От: "Claudio Tognolo"
Дата:
Сообщение: Commit rules or Commit trigger