Horton

Thread

Commits

  1. Calculate extraUpdatedCols in query rewriter, not parser.

  2. Optimize update of tables with generated columns

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

    The Post Office <noreply@postgresql.org> — 2020-10-14T20:30:55Z

    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?

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-10-14T21:49:34Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-10-14T22:45:53Z

    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: https://www.postgresql.org/message-id/flat/b05e781a-fa16-6b52-6738-761181204567@2ndquadrant.com


			regards, tom lane

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-10-27T23:38:08Z

    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].  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

[1] https://www.postgresql.org/message-id/2351.1589824639%40sss.pgh.pa.us