Обсуждение: Unexpected behavior when combining `generated always` columns and update rules

Hello all!

I'm uncertain if what I've observed (and describe below) is a bug,
works as expected (but I've not found it mentioned in the
documentation), or it's just a by-product of combining two advanced
features that haven't been thoroughly tested together.

So, to summarize:  I'm trying to use the rules system (as opposed to
triggers) to propagate a "generated always" column update to another
table (actually the update of other columns that are used to compute
the generated column);  however even though I use
`new.<generated_column_name>` I actually get the old computed value.

(My concrete use-case is propagating something resembling a `disabled`
column, computed based on various other columns, from an account to
say some other related tables.  I do this mainly for performance and
ease of use reasons.)

The following is a minimal example that demonstrates the behavior:

~~~~
create table x (x int, d int generated always as (x * 10) stored);
create table y (x int, d int);

create or replace rule propagate_x_and_d as on update to x do also
    update y set x = new.x, d = new.d where y.x = old.x;

insert into x (x) values (1), (2);
insert into y (x) values (1), (2);

select x.x as x, x.d as xd, y.d as yd from x, y where x.x = y.x;

 x | xd | yd
---+----+----
 1 | 10 |
 2 | 20 |

update x set x = x + 1;

select x.x as x, x.d as xd, y.d as yd from x, y where x.x = y.x;

 x | xd | yd
---+----+----
 2 | 20 | 10
 3 | 30 | 20
~~~~

As seen above, although the rule correctly propagates the change to
the `x` column, it fails to use the new value for the `d` column, but
instead uses the previous one.

Thanks,
Ciprian.

create or replace rule propagate_x_and_d as on update to x do also
    update y set x = new.x, d = new.d where y.x = old.x;

As seen above, although the rule correctly propagates the change to
the `x` column, it fails to use the new value for the `d` column, but
instead uses the previous one.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, April 13, 2023, Ciprian Craciun <ciprian.craciun@gmail.com>
> wrote:
>> create or replace rule propagate_x_and_d as on update to x do also
>> update y set x = new.x, d = new.d where y.x = old.x;
>> 
>> As seen above, although the rule correctly propagates the change to
>> the `x` column, it fails to use the new value for the `d` column, but
>> instead uses the previous one.

> ALSO rules behave like before triggers, not after triggers. The original
> command is appended to the end of the list of commands, not the start.

It depends actually ... per [1],

    For ON INSERT rules, the original query (if not suppressed by INSTEAD)
    is done before any actions added by rules. This allows the actions to
    see the inserted row(s). But for ON UPDATE and ON DELETE rules, the
    original query is done after the actions added by rules. This ensures
    that the actions can see the to-be-updated or to-be-deleted rows;
    otherwise, the actions might do nothing because they find no rows
    matching their qualifications.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/rules-update.html

On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list
ofcommands, not the start. 


As Tom observed, the documentation states that in case of update
rules, the original query is executed at the end.

However, regardless of the order of the execution between new and
original query, as per the documentation the `new` table should
contain the new values regardless.

In fact, from my example above, one can see that the `y.x` is properly
updated with the new value, meanwhile the `y.d` is the previous one
(i.e. `old.d`).

So, based on these observations, I think that `generated always`
columns are actually computed on insertion, and thus they are not
reflected in `new` on rules.

Ciprian.

On 4/13/23 09:27, Ciprian Craciun wrote:
> On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list
ofcommands, not the start.
 
> 
> 
> As Tom observed, the documentation states that in case of update
> rules, the original query is executed at the end.
> 
> However, regardless of the order of the execution between new and
> original query, as per the documentation the `new` table should
> contain the new values regardless.
> 
> In fact, from my example above, one can see that the `y.x` is properly
> updated with the new value, meanwhile the `y.d` is the previous one
> (i.e. `old.d`).
> 
> So, based on these observations, I think that `generated always`
> columns are actually computed on insertion, and thus they are not
> reflected in `new` on rules.

That is not the case:

create table x (x int, d int generated always as (x * 10) stored);
insert into x (x) values (1);
select * from x;

  x | d
---+----
  1 | 10

update x set x = 2 where x = 1;
select * from x;

  x | d
---+----
  2 | 20

What I believe is happening is that the generated always is not done 
until the original query is run after the rule action. So at the time of 
the rule action x.d is still 10.

What this points out is that you will lead a simpler life if you use 
triggers instead of rules.

> 
> Ciprian.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/13/23 09:27, Ciprian Craciun wrote:
> On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list
ofcommands, not the start.
 
> 
> 
> As Tom observed, the documentation states that in case of update
> rules, the original query is executed at the end.
> 
> However, regardless of the order of the execution between new and
> original query, as per the documentation the `new` table should
> contain the new values regardless.
> 
> In fact, from my example above, one can see that the `y.x` is properly
> updated with the new value, meanwhile the `y.d` is the previous one
> (i.e. `old.d`).
> 
> So, based on these observations, I think that `generated always`
> columns are actually computed on insertion, and thus they are not
> reflected in `new` on rules.

Just realized we may have both being saying the same thing. That your 
'...actually computed on insertion,...' meant not just for an INSERT but 
for any change in the data. In other words when the original query 
actually ran.

> 
> Ciprian.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/13/23 09:27, Ciprian Craciun wrote:
>> So, based on these observations, I think that `generated always`
>> columns are actually computed on insertion, and thus they are not
>> reflected in `new` on rules.

> Just realized we may have both being saying the same thing. That your 
> '...actually computed on insertion,...' meant not just for an INSERT but 
> for any change in the data. In other words when the original query 
> actually ran.

Yeah.  In an ON UPDATE rule, NEW is basically a macro that, for each
column of the target table, expands to the SET expression if that column
is in the SET list, and a reference to the existing column value
otherwise.  The OP wishes that for a generated column, we'd expand it
to the generation expression instead, presumably with the SET expressions
replacing any references to those columns.  I can see the argument
for that, but it's probably several years too late to change it now.
Even if anybody wanted to put the work into it, which frankly I doubt.
Non-SELECT rules are a development backwater, and are unlikely to
escape that category given how hard they are to use safely.

Looking at the code, it appears that generated columns are also not
updated till after BEFORE INSERT/BEFORE UPDATE triggers fire.  That's
not a development backwater, so I suppose the decision was deliberate
in that case.  Probably the reasoning was that we'd have to calculate
the generated columns twice in case a BEFORE trigger makes any relevant
changes in the row, and that seemed unduly costly.  But anyway, it seems
like being consistent with that behavior is another reason not to change
the behavior of ON UPDATE rules.

            regards, tom lane