From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | rad(at)killian(dot)email |
Subject: | BUG #16671: "generated always as" is ignored when updating table through view |
Date: | 2020-10-14 20:30:55 |
Message-ID: | 16671-2fa55851859fb166@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16671
Logged by: Michael Paul Killian
Email address: rad(at)killian(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-10-14 21:49:34 | Re: BUG #16671: "generated always as" is ignored when updating table through view |
Previous Message | Tom Lane | 2020-10-14 19:14:58 | Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted |