Re: Generated column is not updated (Postgres 13)

Hi,

Thank you very much for the quick response and feedback. I completely
understand your point, Tom. And I can go back to using triggers
instead. After all, this whole "generated columns" feature is just
syntax sugar. In my real case, the function accepts a row containing
dozens of columns and returns a SHA-1 hash that must be unique,
following pretty sophisticated business logic. Something like: if type
= X and subtype = Y then combine these fields, else if ... and so on.
That's why it's so convenient to pass the whole row.

For the record, I think that passing NULL as a value for all generated
columns would not be such a bad idea, because that's exactly what NULL
represents - an unknown value. And I agree that it would be insane to
rely on the order of calculation, if someone decided to read a value
that is still being computed. It reminds me of the famous "mutating
table" issue while using triggers.

As to the "NOT NULL" and other sorts of constraints - it's also fine,
because integrity constraints are applied later. Just to illustrate my
idea:

create table foo(
  id serial,
  val text,
  hash bytea not null unique
);

insert into foo(val) values('A');

If I had a "before insert on foo for each row" trigger, what would be
the initial "hash" value in it? It would be NULL.
Can I temporarily assign "NEW.hash := NULL" in this trigger, until I
have not yet reached the "return NEW" statement? Yes, I can.
Can I temporarily assign a non-unique value to "NEW.hash"? Yes, I can.

Anyway, I trust your discretion. Thanks!

Regards,
Vitaly Ustinov

Regards,
Vitaly Ustinov


On Wed, May 19, 2021 at 9:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I wrote:
> > ... I think we ought
> > to disallow the case instead.  I observe that we already disallow
> > generated columns depending on each other: ...
> > 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.
>
> After actually looking at the code involved, I'm even more on the
> warpath.  Not only is it failing to reject whole-row vars, but it's
> failing to reject system columns.  That is (a) infeasible to support,
> given that we don't know the values of the system columns at the time
> we compute generated expressions, and (b) just plain ludicrous in
> expressions that are required to be immutable.
>
> I see that there is actually a regression test case that believes
> that "tableoid" should be allowed, but I think that is nonsense.
>
> In the first place, it's impossible to claim that tableoid is an
> immutable expression.  Consider, say, "tableoid > 30000".  Do you
> think such a column is likely to survive dump-and-reload unchanged?
> Also, while that example is artificial, I'm having a hard time
> coming up with realistic immutable use-cases for generation
> expressions involving tableoid.
>
> In the second place, there are a bunch of implementation dependencies
> that we'd have to fix if we want to consider that supported.  I think
> it's mostly accidental that the case seems to work in the mainline
> INSERT code path.  It's not hard to find cases where it does not work,
> for example
>
> regression=# create table foo (f1 int);
> CREATE TABLE
> regression=# insert into foo values (1);
> INSERT 0 1
> regression=# alter table foo add column f2 oid GENERATED ALWAYS AS (tableoid) STORED;
> ALTER TABLE
> regression=# table foo;
>  f1 | f2
> ----+----
>   1 |  0
> (1 row)
>
> So I think we should just forbid tableoid along with other system
> columns, as attached.
>
>                         regards, tom lane
>