From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | updatable view set default interact with base rel generated stored columns |
Date: | 2024-09-05 08:08:23 |
Message-ID: | CACJufxG4SWuFRq0its0wfpVZ8HENFEWWg8jrdUgzqP9JaxfBRg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
hi.
While reviewing virtual generated columns,
I find some issues with the updatable view interacting with stored
generated columns.
-----------------------
drop table if exists base_tbl cascade;
CREATE TABLE base_tbl (a int, b int GENERATED ALWAYS AS (22) stored, d
int default 22);
create view rw_view1 as select * from base_tbl;
insert into rw_view1(a) values (12) returning *;
alter view rw_view1 alter column b set default 11.1;
insert into rw_view1(a,b,d) values ( 12, default,33) returning *;
insert into rw_view1(a,d) values (12,33) returning *;
insert into rw_view1 default values returning *;
SELECT events & 4 != 0 AS can_upd,
events & 8 != 0 AS can_ins,
events & 16 != 0 AS can_del
FROM pg_catalog.pg_relation_is_updatable('rw_view1'::regclass, false) t(events);
-----------------------
"alter view rw_view1 alter column b set default 11.1;"
because rw_view1 view relation in ATExecColumnDefault
TupleDesc->attgenerated == '\0',
otherwise it can error out in ATExecColumnDefault.
Now after we set default, we cannot insert any value to rw_view1,
which makes it not updatable.
From | Date | Subject | |
---|---|---|---|
Next Message | jian he | 2024-09-05 08:27:00 | Re: Virtual generated columns |
Previous Message | Hunaid Sohail | 2024-09-05 08:07:03 | Re: [PATCH] Add roman support for to_number function |