Hi Mamta,
This is a very interesting problem. I am not aware that we have added a
column to a system table since Derby was open-sourced. Whole tables have
been added and the contents of individual columns have been changed. But
I can't remember our adding a column to an existing table.
I do think your problem is solvable--albeit in a tricky way. First let
me describe a possible user experience for your proposal:
1) In soft-upgraded databases, triggers behave as they did in 10.5.
2) In addition, even in hard-upgraded databases, old triggers behave as
they did in 10.5.
3) However, in new 10.6 databases and in hard-upgraded databases, new
triggers on LOB-bearing tables may perform significantly better.
4) So, in order to enjoy the advantages of your work on DERBY-1482,
legacy databases must be hard-upgraded to 10.6 and triggers on
LOB-bearing tables must be dropped and recreated.
If that's the contract, I think that the following solution will work:
A) We change the meaning of SYSTRIGGERS.REFERENCEDCOLUMNS. This column
will carry additional metadata for new triggers created in databases
which are at level 10.6.
B) We do this by changing the implementation of
ReferencedColumnsDescriptorImpl, the object stored in that column. This
object will now carry both the old list of columns in the trigger's
update list as well as a new, optional list of columns mentioned in the
trigger action. Let us call these two lists UpdateColumnList and
TriggerActionColumnList. Note that it is OK to change the implementation
of this class because the Reference Guide explicitly states that
REFERENCEDCOLUMNS is not part of Derby's public api.
C) When creating a new trigger, if the database is at level 10.5 or
earlier, then ReferencedColumnsDescriptorImpl is created only with an
UpdateColumnList, and TriggerActionColumnList is left null. However, if
the database level is 10.6 or later, then the new
ReferencedColumnsDescriptorImpl is created with both an UpdateColumnList
and a TriggerActionColumnList.
D) Now for the tricky part. When serializing/deserializing a
ReferencedColumnsDescriptorImpl, we overload the meaning of the first
integer written/read by the writeExternal()/readExternal() methods. Let
us call this integer VersionNumber.
i) If ReferencedColumnsDescriptorImpl was created with a null
TriggerActionColumnList, then VersionNumber continues to be what it was
in 10.5 and earlier, viz., the length of the UpdateColumnList array.
(De)serialization is the same as it was in 10.5.
ii) However, if ReferencedColumnsDescriptorImpl was created with a
non-null TriggerActionColumnList, then VersionNumber is -1. In this
case, we (de)serialize both UpdateColumnList and TriggerActionColumnList.
E) At query execution time, if TriggerActionColumnList is null, then the
user sees the 10.5 behavior. However, if TriggerActionColumnList is not
null, then the user sees the performance improvement you are introducing.
Hope this makes sense and seems useful,
-Rick
Mamta Satoor wrote:
Hi,
I was looking for some guidence regarding upgrade work involved when a
new column needs to be added to an existing system table and that
column needs to be initialized to different value depending on the row
in the table.
As part of DERBY-1482, I need to add a new column to SYSTRIGGERS. That
column is going to be of the type
org.apache.derby.catalog.ReferencedColumns. The purpose of this column
is to keep track of all the columns (from the trigger table) which are
referenced in the trigger action through the REFERENCE clause of
CREATE TRIGGER. eg
create trigger tr1 after update of id on t1 referencing old as oldt
for each row insert into t2(id2) values (oldt.j);
For the trigger above, we will note in SYSTRIGGERS that column j from
the trigger table t1 is getting referenced in the trigger action. We
will use this information to decide which columns need to be read in
when the user executes an UPDATE t1(updating column id) SQL which will
fire the trigger tr1. This will be especially useful if the table t1
has LOB columns.
My question is did we need to add a new column to an existing system
table in the past? If yes, I would love to reuse that code if possible
rather than reinventing the wheel. I do see following method in
DataDictionaryImpl but do not see it getting called from anywhere
/**
* Upgrade an existing catalog by adding columns.
*
* @param rowFactory Associated with
this catalog.
* @param newColumnIDs Array of 1-based column
ids.
* @param tc
Transaction controller
*
* @exception StandardException Standard Derby error policy
*/
public void upgrade_addColumns( CatalogRowFactory rowFactory, int[]
newColumnIDs,TransactionController tc)
throws StandardException
If we do not have an existing infrastructure for adding a new column,
I will explore the work needed. If anyone has any pointers/thoughts,
would greatly appreciate that.
Another needed step for upgrade for my specific situation is to
initialize the new column correctly so the existing triggers don't
break.
thanks,
Mamta
