BUG #16671: "generated always as" is ignored when updating table through view - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #16671: "generated always as" is ignored when updating table through view
Date
Msg-id 16671-2fa55851859fb166@postgresql.org
Whole thread Raw
Responses Re: BUG #16671: "generated always as" is ignored when updating table through view
List 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 by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
Next
From: Tom Lane
Date:
Subject: Re: BUG #16671: "generated always as" is ignored when updating table through view