From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: 德哥 <digoal@126.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17036: generated column cann't modifyed auto when update
Date: Thu, 27 May 2021 12:12:37 -0400
Message-ID: <1784316.1622131957@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZ_kmtdvED-rycFY=-7VAerbF2Lj5nrM8pEaH=90zbWFw@mail.gmail.com>
References: <17036-e4d41f4221873a17@postgresql.org>
<CAKFQuwarr8b2=2j2J4GuSV-FwqAXq38ebFR_wTUY=Lg9_YLCJg@mail.gmail.com>
<23824c32.12e8.179ab97fa8a.Coremail.digoal@126.com>
<CAKFQuwbkoz3zmL_bEC2ndSX5G9_3xm_+AL2fd+3OTFFH0VePow@mail.gmail.com>
<2e46e6b5.1930.179abb8b6c0.Coremail.digoal@126.com>
<CAKFQuwbDwpPyf=q-LPUfhiLYHhM_2X_86vXL=tQ0vESivtcN7g@mail.gmail.com>
<d89a6f8.4d3a.179ad210afa.Coremail.digoal@126.com>
<1709810.1622123157@sss.pgh.pa.us>
<CAKFQuwZ_kmtdvED-rycFY=-7VAerbF2Lj5nrM8pEaH=90zbWFw@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, digoal@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17036: generated column cann't modifyed auto when update
In-Reply-To: <1784316.1622131957@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox