db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-481) implement SQL generated columns
Date Mon, 27 Oct 2008 13:20:44 GMT

    [ 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.


Mime
View raw message