Re: BUG #17036: generated column cann't modifyed auto when update

The OP mis-stated the problem.  The complaint here is that an immutable function's value can depend upon the values [of its inputs].  In the provided example the input value is supposedly random, different for every call, due to making the default expression a function call of the random() function (that the return value is still now() shouldn't impact the validity of the question, but having it be an immutable echo function would have been considerably clearer).

Now, the reference in the create table docs that "Any functions and operators used must be immutable." would seem to cover this - the random() function used in the generated expression, indirectlyvia the default, is not immutable.  That the function call is hidden, as opposed to the generated expression being written "im_now(random())", doesn't remove the limitation even if the create table validator isn't smart enough to detect the violation and prohibit it altogether.

I am curious whether the SQL standard is imposing this immutable-only limitation or whether we choose to do it ourselves.  If it really is just to allow for optimization then having the system read the volatility marking and act accordingly would seem reasonable.  On the other hand "there is more than one way to do things" isn't a premise we try to adhere to and triggers are the offered way to deal with non-immutable requirements.  Adding a second way via generated is redundant - though I would agree that the syntax complexity reduction makes it worth considering.

David J.