db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Upgrade work when a new column is added to an existing system table...
Date Thu, 01 Apr 2010 06:30:42 GMT
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


On Tue, Mar 30, 2010 at 1:44 PM, Mamta Satoor <msatoor@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 <msatoor@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
>> 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 <Richard.Hillegas@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 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                      
>>>> 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

View raw message