[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642912#action_12642912
]
Rick Hillegas commented on DERBY-481:
-------------------------------------
Hi Bryan,
Thanks for reading the patches. I appreciate having another set of eyes on this!
I think that your questions will be addressed in the next couple of patches,
which should introduce INSERT-time and UPDATE-time processing. In coding
INSERT/UPDATE support, I took CHECK constraints as my model. This is what's
going on in the prototype:
When you INSERT into a table which has a generated column:
1) During compilation, at generate() time, we build a function to evaluate the
generation clause.
2) At execution time, we run that function on every row that will be inserted
into the table. The function runs AFTER the non-generated columns are populated
but BEFORE we run triggers, enforce constraints, and build index rows. All of
this happens before the base and index rows are handed to the Store.
When you UPDATE a table which has a generated column:
3) During compilation, at bind() time we determine whether we need to
re-evaluate any of the generation clauses. We decide to re-generate a column if
we see that one of the columns that it references is being changed. So in the
example you gave, we would decide to re-generate c given the following update
statement:
update bry set b = b + 3
but we would decide NOT to re-generate c given the following update statement
update bry set a = a + 3
4) At execution time, we run that function on every row that is being updated.
As with INSERT, the function runs AFTER the non-generated columns are populated
but BEFORE we run triggers, enforce constraints, and build index rows.
That, at least, is how the prototype is supposed to work! So to answer your
questions directly:
A) Generation clauses are evaluated when you INSERT and UPDATE rows.
B) For UPDATEs, we re-evaluate a generation clause only if we are changing one
of the columns that it references.
Thanks,
-Rick
> implement SQL generated columns
> -------------------------------
>
> Key: DERBY-481
> URL: https://issues.apache.org/jira/browse/DERBY-481
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.0.2.1
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-481-00-aa-prototype.diff,
> derby-481-01-aa-catalog.diff, derby-481-02-aa-utilities.diff,
> derby-481-03-aa-grammar.diff, GeneratedColumns.html
>
>
> Satheesh has pointed out that generated columns, a SQL 2003 feature, would
> satisfy the performance requirements of Expression Indexes (bug 455).
> Generated columns may not be as elegant as Expression Indexes, but they are
> easier to implement. We would allow the following new kind of column
> definition in CREATE TABLE and ALTER TABLE statements:
> columnName GENERATED ALWAYS AS ( expression )
> If expression were an indexableExpression (as defined in bug 455), then we
> could create indexes on it. There is no work for the optimizer to do here.
> The Language merely has to compute the generated column at INSERT/UPDATE time.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
