insert on conflict on updatable views

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: insert on conflict on updatable views
Date: 2018-08-02 02:15:14
Message-ID: 806e426e-533b-a5fe-0a9f-0f219ddaed72@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi.

While working on updating the patch for the bug reported below:

https://www.postgresql.org/message-id/flat/CAFYwGJ0xfzy8jaK80hVN2eUWr6huce0RU8AgU04MGD00igqkTg%40mail.gmail.com

I noticed that the EXCLUDED pseudo-relation allows accessing columns that,
ISTM, should rather be inaccessible.

Example:

create table foo (a int unique, b text);
create view foo_view as select b, a+1 as c, a from foo;

explain insert into foo_view (a, b) select 1, 2 on conflict (a) do update
set b = excluded.b where excluded.c > 0;

The excluded.c above should result in an error, because it is impossible
for a user to specify a value for it, as shown below:

insert into foo_view (a, b, c) select 1, 2, 3 on conflict (a) do update
set b = excluded.b where excluded.c > 0;
ERROR: cannot insert into column "c" of view "foo_view"
DETAIL: View columns that are not columns of their base relation are not
updatable

IOW, the EXCLUDED pseudo-relation should only allow referencing the
columns present in the underlying physical relation. Thoughts?

Thanks,
Amit

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-08-02 02:24:44 Re: insert on conflict on updatable views
Previous Message Kyotaro HORIGUCHI 2018-08-02 02:10:13 Re: Problems with plan estimates in postgres_fdw