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 Tue, 30 Mar 2010 20:44:28 GMT
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.


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