BUG #16671: "generated always as" is ignored when updating table through view
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?
PG Bug reporting form <noreply@postgresql.org> writes:
[ updating via a view fails to recalculate GENERATED columns ]
Yeah, that's surely a bug. In fact, it's a regression, because
the test case works as-expected in v12. Not sure where we broke it.
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?
Assuming that a dot-zero release is bug-free can be dangerous :-(
regards, tom lane
I wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
[ updating via a view fails to recalculate GENERATED columns ]
Yeah, that's surely a bug. In fact, it's a regression, because
the test case works as-expected in v12. Not sure where we broke it.
git bisect blames
c6679e4fca21d253ced84c51ac1a31c1b2aec72f is the first bad commit
commit c6679e4fca21d253ced84c51ac1a31c1b2aec72f
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Mon Feb 17 15:19:58 2020 +0100
Optimize update of tables with generated columns
When updating a table row with generated columns, only recompute those
generated columns whose base columns have changed in this update and
keep the rest unchanged. This can result in a significant performance
benefit. The required information was already kept in
RangeTblEntry.extraUpdatedCols; we just have to make use of it.
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: /messages/by-id/b05e781a-fa16-6b52-6738-761181204567@2ndquadrant.com
regards, tom lane
I wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
[ updating via a view fails to recalculate GENERATED columns ]
Yeah, that's surely a bug. In fact, it's a regression, because
the test case works as-expected in v12. Not sure where we broke it.
git bisect blames
c6679e4fca21d253ced84c51ac1a31c1b2aec72f is the first bad commit
On digging into this, I see that it's one of the issues I complained
about in [1]/messages/by-id/2351.1589824639@sss.pgh.pa.us. Here is a patch that proposes to fix that by moving
calculation of extraUpdatedCols into the query rewriter.
(With this patch, stored extraUpdatedCols values would always be
null. But it wouldn't matter if we are working with a rule in which
the field isn't null, since the rewriter would just overwrite it.)
regards, tom lane