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:
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
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 |