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

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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