Re: Behavior of GENERATED columns per SQL2003

On Thu, 2007-05-10 at 10:11 -0400, Tom Lane wrote:

> As for GENERATED ALWAYS AS (expr), now that we understand that it's not
> supposed to define a virtual column, what's the point? You can get the
> same behavior with a trivial BEFORE INSERT/UPDATE trigger that
> recomputes the derived value, and you don't have to buy into the rather
> ill-defined spec behavior (in particular the point that the generated
> column is effectively undefined during trigger firing seems really
> poorly done). In fact, given that the only plausible use-cases involve
> expressions that are expensive to compute, a trigger can probably do
> *better* than the built-in feature, since it can make use of application
> knowledge about when a recomputation is really necessary. The current
> patch recomputes the expression on every UPDATE, and would have a hard
> time being any brighter than that, given that we don't know what BEFORE
> triggers might do to the row.

We do need virtual columns, whether the spec requires them or not. They
would allow us to completely remove the column value when using
value-list based partitioning, giving considerable space savings for
VLDBs.

ISTM that we should interpret this as a requirement for a virtual
column. We can always move from that to a stored column if the spec
becomes more specific, though it would be harder to move the other way.
And as you point out, storing the value would make no sense.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com