Re: Behavior of GENERATED columns per SQL2003

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