Re: Generated column is not updated (Postgres 13)

Tom Lane wrote:

> TBH, I think that this is insane and needs to be forbidden. What value
> are you expecting that the function would see in the column of the
> whole-row var that corresponds to the generated column? It surely
> cannot be passed the value that it hasn't computed yet.

Perhaps this problem has already been addressed and resolved.
https://www.postgresql.org/docs/13/trigger-definition.html

> Stored generated columns are computed after BEFORE triggers and
> before AFTER triggers. Therefore, the generated value can be inspected
> in AFTER triggers. In BEFORE triggers, the OLD row contains the old
> generated value, as one would expect, but the NEW row does not yet
> contain the new generated value and should not be accessed.
> In the C language interface, the content of the column is undefined at
> this point; a higher-level programming language should prevent access
> to a stored generated column in the NEW row in a BEFORE trigger.

To bottom-line, this is the responsibility of the app developers.
Disclaimer: we warned you.

Tom Lane wrote:

> However, I think we ought to disallow the case instead. I observe that
> we already disallow generated columns depending on each other.

Right, and a function getting a whole-row var can bypass this limitation.
But let's take a look at the same documentation page again and check
what it says about interdependent triggers:

> If more than one trigger is defined for the same event on the same
> relation, the triggers will be fired in alphabetical order by trigger name.
> In the case of BEFORE and INSTEAD OF triggers, the possibly-modified
> row returned by each trigger becomes the input to the next trigger.

Similar to the described behavior, a solution could be to calculate
the generated
columns in alphabetical order. But I'd rather use the same formulation "should
not be accessed" and shift the responsibility to the app developers.

Regards,
Vitaly Ustinov