And immutable function is stable when parameter not change, when parameter changed , the immutable function will recall and recompute.
but in PG 13 and PG 14 , it is also wrong.
create or replace function im_now (anyelement) returns timestamptz as $$
select now();
$$ language sql strict immutable;
create table t1 (id int primary key, c1 int, info text, crt_time timestamp,
mod_time timestamp GENERATED ALWAYS AS (im_now(t1)) stored);
insert into t1 (id, c1, info, crt_time) values (1,1,'test', now());
postgres=# select * from t1;
id | c1 | info | crt_time | mod_time
----+----+------+----------------------------+----------------------------
1 | 1 | test | 2021-05-27 10:10:16.190408 | 2021-05-27 10:10:16.190408
(1 row)
postgres=# update t1 set info='a' where id=1;
UPDATE 1
postgres=# select * from t1;
id | c1 | info | crt_time | mod_time
----+----+------+----------------------------+----------------------------
1 | 1 | a | 2021-05-27 10:10:16.190408 | 2021-05-27 10:10:16.190408
(1 row)
postgres=# update t1 set info='abcd' where id=1;
UPDATE 1
postgres=# select * from t1;
id | c1 | info | crt_time | mod_time
----+----+------+----------------------------+----------------------------
1 | 1 | abcd | 2021-05-27 10:10:16.190408 | 2021-05-27 10:10:16.190408
(1 row)
在 2021-05-26 20:25:00,"David G. Johnston" <david.g.johnston@gmail.com> 写道:
On Wednesday, May 26, 2021, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17036
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: 14beta1
Operating system: CentOS 7.7 x64
Description:
postgres=> create or replace function im_now () returns timestamptz as $$
select CURRENT_TIMESTAMP;
$$ language sql strict immutable;
CREATE FUNCTION
why mod_time cann't updated automatic?
Because that isn’t how this thing works...the lie you told it about being immutable is a dead giveaway,
David J.