Re: Behavior of GENERATED columns per SQL2003

URL added to TODO item.  Patch rejected for 8.3.

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

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

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +