From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: rad@killian.email
Subject: BUG #16671: "generated always as" is ignored when updating table through view
Date: Wed, 14 Oct 2020 20:30:55 +0000
Message-ID: <16671-2fa55851859fb166@postgresql.org> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, rad@killian.email
Subject: Re: BUG #16671: "generated always as" is ignored when updating table through view
In-Reply-To: <16671-2fa55851859fb166@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox