BUG #16140: View with INSERT, DO INSTEAD, and ON CONFLICT causes an error

The following bug has been logged on the website:

Bug reference: 16140
Logged by: Bryan DiCarlo
Email address: bryan(dot)dicarlo(at)gmail(dot)com
PostgreSQL version: 12.1
Operating system: Debian
Description:

When creating an updatable "complex" view, if an ON CONFLICT clause is
provided, an INSERT SELECT to that view will cause "ERROR: variable not
found in subplan target lists". Removing the ON CONFLICT clause eliminates
the error message.

I discovered this while using postgrest. There are other ways I can handle
it but from what I can tell, this should work.

Repo:
CREATE TABLE slo_meta (
slo_name TEXT UNIQUE NOT NULL,
slo_id SERIAL PRIMARY KEY,
window_seconds INT NOT NULL,
objective NUMERIC NOT NULL,
supported_tags JSONB,
CHECK (objective BETWEEN 0 AND 1)
);
CREATE INDEX SLO_NAME ON slo_meta USING HASH (slo_name);

CREATE TABLE slo_metrics (
slo_id INT NOT NULL REFERENCES
slo_meta (slo_id),
value NUMERIC NOT NULL,
time_window TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
tags JSONB NOT NULL,
PRIMARY KEY (slo_id, time_window, tags),
CHECK (value BETWEEN 0 AND 1)
);

CREATE VIEW metrics AS SELECT
slo_meta.slo_name AS slo,
slo_metrics.value AS value,
slo_metrics.tags AS tags,
slo_metrics.time_window AS time_window
FROM
slo_metrics
LEFT JOIN slo_meta ON slo_metrics.slo_id = slo_meta.slo_id;

-- Metrics INSERT/UPDATE RULE
CREATE OR REPLACE RULE metrics_ins AS ON INSERT TO metrics
DO INSTEAD
INSERT INTO slo_metrics (slo_id, value, time_window, tags)
VALUES (
(SELECT slo_id FROM slo_meta WHERE slo_meta.slo_name = NEW.slo),
NEW.value,
(to_timestamp(EXTRACT(epoch FROM NEW.time_window)::int - (EXTRACT(epoch
FROM NEW.time_window)::int % (SELECT window_seconds FROM slo_meta WHERE
slo_meta.slo_name = NEW.slo)))),
NEW.tags
)
ON CONFLICT (slo_id, time_window, tags) DO UPDATE SET value = NEW.value
RETURNING (SELECT slo_name FROM slo_meta WHERE slo_meta.slo_id = slo_id),
value, tags, time_window;

--- Populate SLO Meta Table
INSERT INTO slo_meta (slo_name, window_seconds, objective,
supported_tags)
VALUES ('gpu_capacity', 300, 0.95, '["zone"]'::json);

--- WORKS Try and add to an updateable view
INSERT INTO metrics ("slo", "tags", "time_window", "value")
VALUES ('gpu_capacity', '{"zone": "NP-FRK3-DC"}', '2019-11-26 10:40:00',
0.94)
RETURNING *;

-- WORKS Test with Table Population
WITH pgrst_body AS (SELECT json_build_array('{"slo_id":
1,"value":0.98,"tags":{"zone":"NP-FRK3-DC"},"time_window":"2019-11-26T10:50:00"}'::json)
AS val)
INSERT INTO slo_metrics (slo_id, tags, time_window, value)
SELECT slo_id, tags, time_window, value
FROM json_populate_recordset(null::public.slo_metrics , (SELECT val FROM
pgrst_body));

-- DOESN'T WORK Test with View Population
-- Fixed by commenting out the following from metrics_ins rule: 'ON
CONFLICT (slo_id, time_window, tags) DO UPDATE SET value = NEW.value'
WITH pgrst_body AS (SELECT json_build_array('{"slo":
"gpu_capacity","value":0.98,"tags":{"zone":"NP-FRK3-DC"},"time_window":"2019-11-26T10:30:00"}'::json)
AS val)
INSERT INTO metrics (slo, tags, time_window, value)
SELECT slo, tags, time_window, value
FROM json_populate_recordset(null::public.metrics , (SELECT val FROM
pgrst_body));

-- Metrics INSERT/UPDATE RULE
CREATE OR REPLACE RULE metrics_ins AS ON INSERT TO metrics
DO INSTEAD
INSERT INTO slo_metrics (slo_id, value, time_window, tags)
VALUES (
(SELECT slo_id FROM slo_meta WHERE slo_meta.slo_name = NEW.slo),
NEW.value,
(to_timestamp(EXTRACT(epoch FROM NEW.time_window)::int - (EXTRACT(epoch
FROM NEW.time_window)::int % (SELECT window_seconds FROM slo_meta WHERE
slo_meta.slo_name = NEW.slo)))),
NEW.tags
)
-- ON CONFLICT (slo_id, time_window, tags) DO UPDATE SET value =
NEW.value
RETURNING (SELECT slo_name FROM slo_meta WHERE slo_meta.slo_id = slo_id),
value, tags, time_window;

-- WORKS NOW Test with View Population
-- Fixed by commenting out the following from metrics_ins rule: 'ON
CONFLICT (slo_id, time_window, tags) DO UPDATE SET value = NEW.value'
WITH pgrst_body AS (SELECT json_build_array('{"slo":
"gpu_capacity","value":0.98,"tags":{"zone":"NP-FRK3-DC"},"time_window":"2019-11-26T10:30:00"}'::json)
AS val)
INSERT INTO metrics (slo, tags, time_window, value)
SELECT slo, tags, time_window, value
FROM json_populate_recordset(null::public.metrics , (SELECT val FROM
pgrst_body));