I've created a postgres view (called
metrics) that serves as a proxy for two underlying tables (
live_metrics). I've also created a set of rules to allow clients to interact with that view as if it were just a normal table, and without having to know anything about the tables underneath it. My intention is that select statements on the view return data from the two underlying tables, but that inserts, updates, and deletes will change the data in just one of the underlying tables (
static_metrics). The exact table/view set up is included below.
My problem is that with this arrangement, I get errors when trying to use "upsert" statements in the form
INSERT INTO metrics ... ON CONFLICT ...;. For example, this query:
INSERT INTO metrics (segment_type, segment_id, metric_a, metric_b) VALUES (1, 12345, 0.5, 0.75) ON CONFLICT (segment_type, segment_id) DO UPDATE SET (metric_a, metric_b) = (excluded.metric_a, excluded.metric_b);
I get the following error message:
ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules
So finally my question: Is there some way I can rewrite my rules to handle this use case? If not, is there some other way that I can restructure my database so that clients can continue interacting with the
metrics object but have all reads return data from two distinct tables? The underlying motivation here is that I don't have control over all clients, so I want this to all work seamlessly without any changes needed on their end.
Here are the exact table, view, and rule definitions:
CREATE TABLE static_metrics ( id serial PRIMARY KEY, segment_type int NOT NULL, segment_id int NOT NULL, metric_a float, metric_b float, ); CREATE INDEX static_metrics_idx ON static_metrics (segment_type, segment_id); CREATE TABLE live_metrics ( id int PRIMARY KEY DEFAULT nextval('static_metrics_id_seq'::regclass), segment_type int NOT NULL, segment_id int NOT NULL, metric_a float, metric_b float, ); CREATE INDEX live_metrics_idx ON live_metrics (segment_type, segment_id); CREATE VIEW metrics AS SELECT * FROM static_metrics UNION ALL SELECT * FROM live_metrics; CREATE OR REPLACE RULE metrics_INS AS INSERT TO metrics DO INSTEAD INSERT INTO static_metrics VALUES ( CASE WHEN NEW.id is null THEN nextval('static_metrics_id_seq') ELSE NEW.id END, NEW.segment_type, NEW.segment_id, NEW.metric_a, NEW.metric_b ); CREATE OR REPLACE RULE metrics_UPD AS ON UPDATE TO metrics DO INSTEAD UPDATE static_metrics SET id = NEW.id, segment_type = NEW.segment_type, segment_id = NEW.segment_id, metric_a = NEW.metric_a, metric_b = NEW.metric_b WHERE id = OLD.id; CREATE OR REPLACE RULE metrics_DEL AS ON DELETE TO metrics DO INSTEAD DELETE FROM static_metrics WHERE id = OLD.id;