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