Thread: 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.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));

PG Bug reporting form <noreply@postgresql.org> writes:
> 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 believe the problem is that you wrote the ON CONFLICT clause
incorrectly:

>   ON CONFLICT (slo_id, time_window, tags) DO UPDATE SET value = NEW.value

You should have written "EXCLUDED.value" not "NEW.value".  There is
a bug here, but it's that the parser accepted this rule.   I'm guessing
that the parsing logic for ON CONFLICT didn't consider the possibility
that NEW and OLD for a rule would already be in the range table.

            regards, tom lane

On Thu, Nov 28, 2019 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You should have written "EXCLUDED.value" not "NEW.value".  There is
> a bug here, but it's that the parser accepted this rule.   I'm guessing
> that the parsing logic for ON CONFLICT didn't consider the possibility
> that NEW and OLD for a rule would already be in the range table.

I must admit that I have zero recollection of this aspect of the ON
CONFLICT work. Do you think that this would be difficult to fix?

-- 
Peter Geoghegan

Peter Geoghegan <pg@bowt.ie> writes:
> On Thu, Nov 28, 2019 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You should have written "EXCLUDED.value" not "NEW.value".  There is
>> a bug here, but it's that the parser accepted this rule.   I'm guessing
>> that the parsing logic for ON CONFLICT didn't consider the possibility
>> that NEW and OLD for a rule would already be in the range table.

> I must admit that I have zero recollection of this aspect of the ON
> CONFLICT work. Do you think that this would be difficult to fix?

Probably not terribly so, but I haven't looked at the code.  Partly
it'd depend on how good an error message we want (e.g., whether there's
to be an error cursor).  The likely-simplest fix would involve making
sure the rangetable has only the two useful entries, so you'd get
some sort of "unknown table name" error for mistakes of this sort.
It'd be more useful to say something like "only <table name> and
EXCLUDED can be referenced in ON CONFLICT"; but getting to that
might be more trouble than it's worth.

            regards, tom lane