BUG #16671: "generated always as" is ignored when updating table through view
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: rad@killian.email
Date: 2020-10-14T20:30:55Z
Lists: 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?
Commits
-
Calculate extraUpdatedCols in query rewriter, not parser.
- ad77039fad0f 14.0 landed
- 70492195be5e 13.1 landed
- 43330cdd40f1 12.5 landed
-
Optimize update of tables with generated columns
- c6679e4fca21 13.0 cited