pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bryan.dicarlo@gmail.com
Subject: BUG #16140: View with INSERT, DO INSTEAD, and ON CONFLICT causes an error
Date: Wed, 27 Nov 2019 23:00:02 +0000
Message-ID: <16140-4d29cf5390118372@postgresql.org> (raw)

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));



view thread (5+ messages)  latest in thread

Message-ID: <16140-4d29cf5390118372@postgresql.org>
Permalink:  ../16140-4d29cf5390118372@postgresql.org/
Also on:    postgresql.org/message-id/16140-4d29cf5390118372@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, bryan.dicarlo@gmail.com
  Subject: Re: BUG #16140: View with INSERT, DO INSTEAD, and ON CONFLICT causes an error
  In-Reply-To: <16140-4d29cf5390118372@postgresql.org>
  References: <16140-4d29cf5390118372@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox