BUG #16671: "generated always as" is ignored when updating table through view

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #16671: "generated always as" is ignored when updating table through view
Дата
Msg-id 16671-2fa55851859fb166@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #16671: "generated always as" is ignored when updating table through view  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      16671
Logged by:          Michael Paul Killian
Email address:      rad@killian.email
PostgreSQL version: 13.0
Operating system:   macOS 10.13
Description:

consider running this script to see what I mean: 
```
CREATE TABLE public.table1 (
    id serial,
    idplus1 integer GENERATED ALWAYS AS (id + 1) STORED
);
CREATE VIEW public.view1 AS SELECT * FROM public.table1;

INSERT INTO public.table1 (id, idplus1) VALUES (DEFAULT, DEFAULT);
INSERT INTO public.view1 (id, idplus1) VALUES (DEFAULT, DEFAULT);
INSERT INTO public.table1 (id, idplus1) VALUES (DEFAULT, DEFAULT);
INSERT INTO public.view1 (id, idplus1) VALUES (DEFAULT, DEFAULT);
INSERT INTO public.table1 (id, idplus1) VALUES (DEFAULT, DEFAULT);
INSERT INTO public.view1 (id, idplus1) VALUES (DEFAULT, DEFAULT);

UPDATE view1 SET id = 3000 WHERE id = 3;
UPDATE view1 SET id = 4000 WHERE id = 4;
UPDATE table1 SET id = 5000 WHERE id = 5;
UPDATE table1 SET id = 6000 WHERE id = 6;

select * from table1;
```


I expected the following output from the last line: 

  id  | idplus1 
------+---------
    1 |       2
    2 |       3
 3000 |    3001
 4000 |    4001
 5000 |    5001
 6000 |    6001
(6 rows)

but instead I got:

  id  | idplus1 
------+---------
    1 |       2
    2 |       3
 3000 |       4
 4000 |       5
 5000 |    5001
 6000 |    6001
(6 rows)

I could be ignorant of some limitations that views have, but I have neither
looked for them nor seen them in the documentation. I just assumed in good
faith that updating rows through a view would be safe in simple cases. Is
this a dangerous assumption?


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #16671: "generated always as" is ignored when updating table through view