BUG #16140: View with INSERT, DO INSTEAD, and ON CONFLICT causes an error - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16140
Logged by:          Bryan DiCarlo
Email address:      bryan.dicarlo@gmail.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));