Re: Unexpected behavior when combining `generated always` columns and update rules

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thursday, April 13, 2023, Ciprian Craciun <ciprian(dot)craciun(at)gmail(dot)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