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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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 >>>> >>> >>> >> >
