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
在 2021-05-27 11:16:10，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
On Wednesday, May 26, 2021, 德哥 <digoal@126.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.