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

On Thu, May 27, 2021 at 6:46 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?GBK?B?tcK45w==?= <digoal(at)126(dot)com> writes:
> > If the value of the immutable function changes, the immutable function
> should be recalculated.
>
> You misunderstand completely. An immutable marking on a function is a
> promise from you to the system that the function's value does NOT change.
>
>
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.