Skip to content

Instantly share code, notes, and snippets.

@assembler
Last active Dec 14, 2015
Embed
What would you like to do?
reproducing nested postgres RULES problem
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment