Обсуждение: BUG #17036: generated column cann't modifyed auto when update

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

postgres=> create table t1 (id int primary key, info text, crt_time
timestamp,
                                                         mod_time
timestamptz GENERATED ALWAYS AS (im_now()) stored); 
CREATE TABLE

postgres=> insert into t1 (id, info, crt_time) values (1,'test', now());  
INSERT 0 1
postgres=> select * from t1;
 id | info |          crt_time          |           mod_time            
----+------+----------------------------+-------------------------------
  1 | test | 2021-05-26 16:38:26.675934 | 2021-05-26 16:38:26.675934+08
(1 row)

postgres=> update t1 set info='a' where id=1;  
UPDATE 1
postgres=> select * from t1;
 id | info |          crt_time          |           mod_time            
----+------+----------------------------+-------------------------------
  1 | a    | 2021-05-26 16:38:26.675934 | 2021-05-26 16:38:26.675934+08
(1 row)

why mod_time cann't updated automatic? 

best regards,
digoal

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?

But in PostgreSQL 12, it works fine.

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); 

=?GBK?B?tcK45w==?= <digoal@126.com> writes:
> The generated column can be used to automatically generate the modified timestamp of a tuple, but PG 12 currently
supportsthis scenario. PG 13 has started to change its behavior, which makes our application need to be modified. This
isthe first time I have ever seen a PG upgrade kill a nice feature. 

You have been told several times already that this is not a "feature".
The point of the restriction that GENERATED expressions be immutable
is that the implementation need not recompute them during UPDATE
(if none of their input columns changed).

If v12 failed to exploit that fully, that's more a bug in v12 than
it is in versions that do exploit it.

If you need to update a column during every update, the best way
to do that is with a BEFORE INSERT OR UPDATE trigger.

I will also state categorically that lying about the mutability
of a function is a technique that will bite you on the rear, and
you will get no sympathy when it does.  If it does what you want
in version N, fine, but don't whine when it breaks in N+1.

            regards, tom lane

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.

=?GBK?B?tcK45w==?= <digoal@126.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.
Therefore, the predicate of your statement is vacuous, and we need not
discuss whether the conclusion is interesting.  If there's some behavior
that you don't like as a consequence of your lie, then stop lying.

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I am curious whether the SQL standard is imposing this immutable-only
> limitation or whether we choose to do it ourselves.

Fair question.  What I read in SQL:2021 11.4 <column definition>
SR 10 is

10) If <generation clause> GC is specified, then:
a) Let GE be the <generation expression> contained in GC.
b) C is a generated column.
c) Every <column reference> contained in GE shall reference a base column of T.
d) GE shall not contain a possible source of non-determinism.
e) GE shall not contain a <routine invocation> whose subject routine possibly reads SQL-data.
f) GE shall not contain a <query expression>.

Now, (d) is referring to 9.16 "Potential sources of non-determinism",
which calls out a whole bunch of stuff that we would refer to as
either volatile or stable; for example 9.16 1) k) says that all
<datetime value function>s (that is, CURRENT_TIMESTAMP etc) are
potentially nondeterministic.  So although the spec's two classes
of function stability

<deterministic characteristic> ::=
    DETERMINISTIC
  | NOT DETERMINISTIC

don't map exactly to our IMMUTABLE/STABLE/VOLATILE classification,
it seems correct to me to identify DETERMINISTIC with IMMUTABLE.
It's certainly indisputable that the letter of the spec forbids
CURRENT_TIMESTAMP in GENERATED expressions, and there's nothing
suggesting that hiding that within a user-defined function makes
it okay.

            regards, tom lane