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, 23 Mar 2010 16:33:25 GMT
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.

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