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

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.

But in PostgreSQL 12, it works fine.

```

postgres=> create or replace function im_now () returns timestamptz as $$

postgres$> select now();

postgres$> $$ language sql strict immutable;

CREATE FUNCTION

postgres=>

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

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

CREATE TABLE

postgres=>

postgres=> insert into t1 (id, info, crt_time) values (1,'test', now());

INSERT 0 1

postgres=>

postgres=> select * from t1;

id | info | crt_time | mod_time

----+------+----------------------------+----------------------------

1 | test | 2021-05-27 10:01:58.361174 | 2021-05-27 10:01:58.361174

(1 row)

postgres=>

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-27 10:01:58.361174 | 2021-05-27 10:01:58.896861

(1 row)

postgres=> create or replace function im_now () returns timestamptz as $$

postgres$> select CURRENT_TIMESTAMP;

postgres$> $$ language sql strict immutable;

CREATE FUNCTION

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-27 10:01:58.361174 | 2021-05-27 10:02:24.134303

(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-27 10:01:58.361174 | 2021-05-27 10:02:34.164966

(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-27 10:01:58.361174 | 2021-05-27 10:02:37.535282

(1 row)

postgres=> create or replace function im_now () returns timestamptz as $$ select now(); $$ language sql strict immutable;

CREATE FUNCTION

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-27 10:01:58.361174 | 2021-05-27 10:03:02.665515

(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-27 10:01:58.361174 | 2021-05-27 10:03:05.414793

(1 row)

```

--

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

在 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.

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)

```

--

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

在 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.

On Wednesday, May 26, 2021, 德哥 <digoal@126.com> wrote:

But in PostgreSQL 12, it works fine.

There may be some other stuff going on but your example relies on what is
undefined behavior. That some particular outcome matches your expectation
doesn’t make it correct. That said, a similar nearby complaint suggests
that this feature does have some issues. Whether the version 12 behavior
is deemed buggy and changed remains to be seen.

David J.

On Wednesday, May 26, 2021, 德哥 <digoal@126.com> wrote:

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

This seems to be related to this already reported bug (the similar one I
noted in my other reply).

/messages/by-id/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com

David J.

When i use the im_now(float8 default random()) dynamic parameter, it also not work for PG 14 within generated column.

```

postgres=# drop function im_now(anyelement) ;

DROP FUNCTION

postgres=# drop function im_now ;

DROP FUNCTION

postgres=# drop table t1;

DROP TABLE

postgres=#

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

postgres=# insert into t1 (id, c1, info, crt_time) values (1,1,'test', now());

INSERT 0 1

postgres=#

postgres=# select * from t1;

id | c1 | info | crt_time | mod_time

----+----+------+----------------------------+----------------------------

1 | 1 | test | 2021-05-27 10:43:32.278616 | 2021-05-27 10:43:32.278616

(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:43:32.278616 | 2021-05-27 10:43:32.278616

(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:43:32.278616 | 2021-05-27 10:43:32.278616

(1 row)

postgres=# select im_now();

im_now

-------------------------------

2021-05-27 10:44:03.749108+08

(1 row)

postgres=# select im_now();

im_now

-------------------------------

2021-05-27 10:44:04.509058+08

(1 row)

postgres=# select im_now();

im_now

-------------------------------

2021-05-27 10:44:06.781393+08

(1 row)

```

--

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

在 2021-05-27 10:33:40，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Wednesday, May 26, 2021, 德哥 <digoal@126.com> wrote:

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

This seems to be related to this already reported bug (the similar one I noted in my other reply).

/messages/by-id/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com

David J.

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.

--

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

在 2021-05-27 10:33:40，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Wednesday, May 26, 2021, 德哥 <digoal@126.com> wrote:

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

This seems to be related to this already reported bug (the similar one I noted in my other reply).

/messages/by-id/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com

David J.

=?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 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.

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

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.

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.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.

Thank you Tom Lane for your reply. I think your explanation is very clear.

So can we talk about the use of volatile or stable functions in generated columns and if there are actual business scenarios, why not design the functionality from the perspective of the scenarios instead of the scenarios?

--

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

At 2021-05-27 11:09:00, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Show quoted text

=?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 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.

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

=?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

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

=?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.

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.

"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