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

HI,

Using trigger is a good suggestion, we'll use trigger next, but I want to talk about whether this phenomenon is abnormal.
If the value of the immutable function changes, the immutable function should be recalculated.
For example, I have already demonstrated this:
` ` `
postgres=# create or replace function im_now (float8 default random()) returns timestamptz as $$

postgres$# select now();

postgres$# $$ language sql strict immutable;

CREATE FUNCTION

postgres=#

postgres=# create table t1 (id int primary key, c1 int, info text, crt_time timestamp,

postgres(# mod_time timestamp GENERATED ALWAYS AS (im_now()) stored);

CREATE TABLE
` ` `
2. How to explain this phenomenon? It doesn't seem to have been re-implemented! OK, I believe this is a bug. In addition to limiting the generated column expression to be immutable itself, we should also restrict the default value of the parameter to the function to be immutable.

best regards,
digoal

--

公益是一辈子的事,I'm Digoal,Just Do It.

在 2021-05-27 11:16:10，"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> 写道：

On Wednesday, May 26, 2021, 德哥 <digoal(at)126(dot)com> wrote:

The generated column can be used to automatically generate the modified timestamp of a tuple, but PG 12 currently supports this scenario. PG 13 has started to change its behavior, which makes our application need to be modified. This is the first time I have ever seen a PG upgrade kill a nice feature.

It was never a feature in the first place so nothing has been killed. The documentation says the function must be immutable. Your function is not. You should have used a trigger, and the fact you had to write a wrapper function to hack the volatility means that, frankly, its your disregard for a known limitation that has produced this need to change your application with the release of a new major version - not any bug or decision on the part of PostgreSQL.

David J.