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

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?

Responses

Browse pgsql-bugs by date

  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