Oracle 10g, MySQL 5, and SQL Server 2005 don't appear to support the
syntax. The SQL:2003 SIGMOD paper [1] indicates pretty clearly that
their intention is for the values of generated columns to be stored on disk:
"... commonly used expressions are evaluated once and their results
stored for future use"
"Generated columns can lead to higher performance... because of reduced
computation"
-Dave
[1] Eisenberg, A., Melton, J., Kulkarni, K., Michels, J., and Zemke, F.
2004. SQL:2003 has been published. SIGMOD Rec. 33, 1 (Mar. 2004), 119-126.
http://www.sigmod.org/record/issues/0403/E.JimAndrew-standard.pdf
Tom Lane wrote:
I've been studying the SQL spec in a bit more detail and I'm suddenly
thinking that we've got the behavior all wrong in the current
GENERATED/IDENTITY patch. In particular, it looks to me like we've
been implementing GENERATED ALWAYS AS (expr) according to the rules
that the spec in fact lays down only for GENERATED ALWAYS AS IDENTITY.
You'd think the two constructs would be pretty closely related but
the behaviors specified by the spec are light-years apart. If you
look closely, a "generated column" in the meaning of section 4.14.8
is one that has GENERATED ALWAYS AS (expr), and identity columns are
*not* in this class.
It looks to me like the behavior the spec intends for a generated column
is actually that it can be implemented as a "virtual column" occupying
no space on disk and instead computed on-the-fly when retrieved.
Identity columns can have their values overridden by the
user (it's a little harder if GENERATED ALWAYS, but still possible),
and they don't change during an UPDATE unless specifically forced to.
In contrast, generated columns cannot be overridden by
assignment, and are recomputed from their base columns during updates.
This realization also explains the following, otherwise rather strange,
facts:
* There is no GENERATED BY DEFAULT AS (expr) in the spec.
* GENERATED expressions are specifically disallowed from containing
subselects, calling functions that access any SQL-data, or being
nondeterministic; hence their values depend solely on the regular
columns in the same row.
* While identity columns are updated (if needed) before execution of
BEFORE triggers, generated columns are updated after BEFORE triggers;
hence a BEFORE trigger can override the value in one case and not the
other. (The current patch gets this wrong, btw.)
* Generated columns are forcibly updated when their base columns change
as a result of FK constraints (such as ON UPDATE CASCADE).
It looks to me like a BEFORE trigger is actually the only place that can
(transiently) see values of a generated column that are different from
the result of applying the generation expression on the rest of the row.
It's unclear whether that's intentional or an oversight.
Is anyone familiar with a database that implements SQL-spec generated
columns? Do they actually store the columns?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
