reproducing nested postgres RULES problem

nested_rules.sql

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path = tmp ;


-- table definitions

CREATE TABLE user_hits (
  day DATE,
  user_id INT,
  project_id INT,
  client_id INT,
  application_id INT,
  hits INT
  );

CREATE TABLE project_hits (
  day DATE,
  project_id INT,
  client_id INT,
  application_id INT,
  hits INT
  );

CREATE TABLE client_hits (
  day DATE,
  client_id INT,
  application_id INT,
  hits INT
  );

CREATE TABLE application_hits (
  day DATE,
  application_id INT,
  hits INT
  );


-- indexes

CREATE INDEX ON user_hits (day, user_id);
CREATE INDEX ON user_hits (day, project_id);
CREATE INDEX ON user_hits (day, client_id);
CREATE INDEX ON user_hits (day, application_id);

CREATE INDEX ON project_hits (day, project_id);
CREATE INDEX ON project_hits (day, client_id);
CREATE INDEX ON project_hits (day, application_id);

CREATE INDEX ON client_hits (day, client_id);
CREATE INDEX ON client_hits (day, application_id);

CREATE INDEX ON application_hits (day, application_id);


--- rules

CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  DELETE FROM client_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  DELETE FROM application_hits WHERE day = OLD.day;


-- filling user_hits
INSERT INTO user_hits
  (day, user_id, project_id, client_id, application_id, hits)
SELECT
  current_date - (random())::INT, (s % 1000) * random(), (s % 100) * random(), 1, random() * 3, 50 * random()
FROM
  generate_series(1, 100000) s;

-- filling project_hits
INSERT INTO project_hits
  (day, project_id, client_id, application_id, hits)
SELECT
  day,
  project_id,
  client_id,
  application_id,
  SUM(hits)
FROM
  user_hits
GROUP BY
  day, project_id, client_id, application_id;

-- filling client_hits
INSERT INTO client_hits
  (day, client_id, application_id, hits)
SELECT
  day,
  client_id,
  application_id,
  SUM(hits)
FROM
  project_hits
GROUP BY
  day, client_id, application_id;

-- filling application_hits
INSERT INTO application_hits
  (day, application_id, hits)
SELECT
  day,
  application_id,
  SUM(hits)
FROM
  client_hits
GROUP BY
  day, application_id;


-- create view for today
CREATE VIEW v_today
AS SELECT
  (SELECT COUNT(*) FROM user_hits WHERE day = current_date) AS user_hits,
  (SELECT COUNT(*) FROM project_hits WHERE day = current_date) AS project_hits,
  (SELECT COUNT(*) FROM client_hits WHERE day = current_date) AS client_hits,
  (SELECT COUNT(*) FROM application_hits WHERE day = current_date) AS application_hits;


SELECT * FROM v_today;
explain analyse DELETE FROM user_hits WHERE day = current_date;
SELECT * FROM v_today;