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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: 德哥 <digoal(at)126(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #17036: generated column cann't modifyed auto when update
Date: 2021-05-27 16:12:37
Message-ID: 1784316.1622131957@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2021-05-27 22:22:43 BUG #17039: Won't Upgrade - Repo Error
Previous Message David G. Johnston 2021-05-27 15:11:48 Re: BUG #17036: generated column cann't modifyed auto when update