You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <Ri...@Sun.COM> on 2010/03/22 16:54:39 UTC
Re: Upgrade work when a new column is added to an existing system
table...
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
>
Re: Upgrade work when a new column is added to an existing system
table...
Posted by Mamta Satoor <ms...@gmail.com>.
I have been thinking about the upgrade work for DERBY-1482 and I think
the work I need to do is very similar to what was done for DERBY-606
(committed with revision number 484797). I will study that patch more
to see how it's logic can be used for the changes to
SYSTRIGGERS.REFERENCEDCOLUMNS needed for my jira DERBY-1482.
thanks,
Mamta
On Tue, Mar 30, 2010 at 1:44 PM, Mamta Satoor <ms...@gmail.com> wrote:
> Hi Rick,
>
> I have started exploring your idea of including the trigger action
> column information in SYSTRIGGERS.REFERENCEDCOLUMNS. As you suggested,
> this will require changing the implementation of
> ReferencedColumnsDescriptorImpl. I noticed though that this
> implementation gets used by 2 other system tables, namely SYSCHECKS
> and SYSCONSTRAINTS. Of course, for those 2 system tables, the new
> information in ReferencedColumnsDescriptorImpl means nothing. the new
> information will only have meaning for triggers.
>
> I wanted to bring up this column(ReferencedColumnsDescriptorImpl)
> usage by 3 system tables though, rather than just SYSTRIGGERS.
>
> thanks,
> Mamta
>
> On Tue, Mar 23, 2010 at 9:33 AM, Mamta Satoor <ms...@gmail.com> wrote:
>> Hi Rick,
>>
>> I hope to post a patch on DERBY-1482 by end of this week. What I have
>> done (without any upgrade work) is to add a new column
>> "REFCOLSTRIGGERACTION" to SYSTRIGGERS which will be expected to have
>> all the columns referenced in the trigger action through the
>> REFERENCING clause.
>>
>> The only time that SYSTRIGGERS.REFCOLSTRIGGERACTION would have null
>> value is when the REFERENCE clause is missing in the CREATE TRIGGER
>> statement or when there is a REFERENCE clause but no columns are
>> referenced by the trigger action. eg of the 2 cases
>> 1)No REFERENCING clause in CREATE TRIGGER
>> create trigger trigger1 AFTER UPDATE of status on table1 update table2
>> set updates = updates + 1 where table2.id = 1
>> 2)Yes REFERENCING clause but no columns actually referenced in trigger action
>> create trigger tr1 after update on table1 referencing old as oldt new as newt
>> for each row update table2 set c22=oldt.c12;
>>
>> What happens at triggering statement execution time is we look at the
>> existing SYSTRIGGERS.REFERENCEDCOLUMNS and the new column
>> SYSTRIGGERS.REFCOLSTRIGGERACTION and those are the columns that will
>> be read into memory. So, important thing that my code relies on is the
>> fact that SYSTRIGGERS.REFCOLSTRIGGERACTION will be null only for the
>> above 2 cases. What this means in other words is that for existing
>> triggers in an update scenario, this new column has to be initialized
>> correctly or the triggers will break.
>>
>> Rick, I do see your suggestion of enhancing the data structure for
>> SYSTRIGGERS.REFERENCEDCOLUMNS to include the trigger action columns.
>> If we decide, I think it should be pretty doable for me to change my
>> code to not add another column to SYSTRIGGERS, instead put this
>> information as part of SYSTRIGGERS.REFERENCEDCOLUMNS if that will make
>> upgrade easier (I think it will since we are not adding a new column.
>> Rather we are just adding more information to a datastructure which an
>> end user is not supposed to access anyways.)
>>
>> Would greatly appreciate more feedback from the list.
>>
>> thanks,
>> Mamta
>> As for this information being as part of a new col
>>
>> On Mon, Mar 22, 2010 at 7:54 AM, Rick Hillegas <Ri...@sun.com> wrote:
>>> 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
>>>>
>>>
>>>
>>
>
Re: Upgrade work when a new column is added to an existing system
table...
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Rick,
I have started exploring your idea of including the trigger action
column information in SYSTRIGGERS.REFERENCEDCOLUMNS. As you suggested,
this will require changing the implementation of
ReferencedColumnsDescriptorImpl. I noticed though that this
implementation gets used by 2 other system tables, namely SYSCHECKS
and SYSCONSTRAINTS. Of course, for those 2 system tables, the new
information in ReferencedColumnsDescriptorImpl means nothing. the new
information will only have meaning for triggers.
I wanted to bring up this column(ReferencedColumnsDescriptorImpl)
usage by 3 system tables though, rather than just SYSTRIGGERS.
thanks,
Mamta
On Tue, Mar 23, 2010 at 9:33 AM, Mamta Satoor <ms...@gmail.com> wrote:
> Hi Rick,
>
> I hope to post a patch on DERBY-1482 by end of this week. What I have
> done (without any upgrade work) is to add a new column
> "REFCOLSTRIGGERACTION" to SYSTRIGGERS which will be expected to have
> all the columns referenced in the trigger action through the
> REFERENCING clause.
>
> The only time that SYSTRIGGERS.REFCOLSTRIGGERACTION would have null
> value is when the REFERENCE clause is missing in the CREATE TRIGGER
> statement or when there is a REFERENCE clause but no columns are
> referenced by the trigger action. eg of the 2 cases
> 1)No REFERENCING clause in CREATE TRIGGER
> create trigger trigger1 AFTER UPDATE of status on table1 update table2
> set updates = updates + 1 where table2.id = 1
> 2)Yes REFERENCING clause but no columns actually referenced in trigger action
> create trigger tr1 after update on table1 referencing old as oldt new as newt
> for each row update table2 set c22=oldt.c12;
>
> What happens at triggering statement execution time is we look at the
> existing SYSTRIGGERS.REFERENCEDCOLUMNS and the new column
> SYSTRIGGERS.REFCOLSTRIGGERACTION and those are the columns that will
> be read into memory. So, important thing that my code relies on is the
> fact that SYSTRIGGERS.REFCOLSTRIGGERACTION will be null only for the
> above 2 cases. What this means in other words is that for existing
> triggers in an update scenario, this new column has to be initialized
> correctly or the triggers will break.
>
> Rick, I do see your suggestion of enhancing the data structure for
> SYSTRIGGERS.REFERENCEDCOLUMNS to include the trigger action columns.
> If we decide, I think it should be pretty doable for me to change my
> code to not add another column to SYSTRIGGERS, instead put this
> information as part of SYSTRIGGERS.REFERENCEDCOLUMNS if that will make
> upgrade easier (I think it will since we are not adding a new column.
> Rather we are just adding more information to a datastructure which an
> end user is not supposed to access anyways.)
>
> Would greatly appreciate more feedback from the list.
>
> thanks,
> Mamta
> As for this information being as part of a new col
>
> On Mon, Mar 22, 2010 at 7:54 AM, Rick Hillegas <Ri...@sun.com> wrote:
>> 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
>>>
>>
>>
>
Re: Upgrade work when a new column is added to an existing system
table...
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Rick,
I hope to post a patch on DERBY-1482 by end of this week. What I have
done (without any upgrade work) is to add a new column
"REFCOLSTRIGGERACTION" to SYSTRIGGERS which will be expected to have
all the columns referenced in the trigger action through the
REFERENCING clause.
The only time that SYSTRIGGERS.REFCOLSTRIGGERACTION would have null
value is when the REFERENCE clause is missing in the CREATE TRIGGER
statement or when there is a REFERENCE clause but no columns are
referenced by the trigger action. eg of the 2 cases
1)No REFERENCING clause in CREATE TRIGGER
create trigger trigger1 AFTER UPDATE of status on table1 update table2
set updates = updates + 1 where table2.id = 1
2)Yes REFERENCING clause but no columns actually referenced in trigger action
create trigger tr1 after update on table1 referencing old as oldt new as newt
for each row update table2 set c22=oldt.c12;
What happens at triggering statement execution time is we look at the
existing SYSTRIGGERS.REFERENCEDCOLUMNS and the new column
SYSTRIGGERS.REFCOLSTRIGGERACTION and those are the columns that will
be read into memory. So, important thing that my code relies on is the
fact that SYSTRIGGERS.REFCOLSTRIGGERACTION will be null only for the
above 2 cases. What this means in other words is that for existing
triggers in an update scenario, this new column has to be initialized
correctly or the triggers will break.
Rick, I do see your suggestion of enhancing the data structure for
SYSTRIGGERS.REFERENCEDCOLUMNS to include the trigger action columns.
If we decide, I think it should be pretty doable for me to change my
code to not add another column to SYSTRIGGERS, instead put this
information as part of SYSTRIGGERS.REFERENCEDCOLUMNS if that will make
upgrade easier (I think it will since we are not adding a new column.
Rather we are just adding more information to a datastructure which an
end user is not supposed to access anyways.)
Would greatly appreciate more feedback from the list.
thanks,
Mamta
As for this information being as part of a new col
On Mon, Mar 22, 2010 at 7:54 AM, Rick Hillegas <Ri...@sun.com> wrote:
> 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
>>
>
>