Re: Generated column is not updated (Postgres 13)

Vitaly Ustinov <vitaly(at)ustinov(dot)ca> writes:
> I would like to report the following:
> - a generated column is never updated if you pass the whole record to a
> stored procedure (an immutable function);

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.

You could perhaps argue that it'd be okay to pass NULL. The problem
with that, though, is that it'd violate the NOT NULL constraint that
exists for the generated column. Quite aside from any confusion that
ensues at the user level, I'm afraid that that could result in C code
crashes --- there are, for example, places in tuple deforming that
assume that NOT NULL constraints are truthful.

Anyway, I tried your test case on a debug build and observed assertion
failures, which I traced to ATRewriteTable not being careful enough
to zero out the whole tuple each time through. Conceivably we could
fix that as per the attached quick hack. However, I think we ought
to disallow the case instead. I observe that we already disallow
generated columns depending on each other:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# alter table foo add f2 int generated always as (f1+1) stored;
ALTER TABLE
regression=# alter table foo add f3 int generated always as (f2+1) stored;
ERROR: cannot use generated column "f2" in column generation expression
DETAIL: A generated column cannot reference another generated column.

But a whole-row var violates this concept completely: it makes the
generated column depend, not only on every other column, but on itself
too. Also, even if you don't mind null-for-not-yet-computed-value,
that would expose the computation order of the generated columns.

regards, tom lane