[jira] [Commented] (DERBY-5294) Triggers created prior to 10.9 release will continue to read all the columns from trigger table even after database has been upgraded to 10.9 and higher

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-5294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13054668#comment-13054668 ] 

Mamta A. Satoor commented on DERBY-5294:
----------------------------------------

I spent some time exploring option 2)At the time of upgrade, when we mark all the SPSes invalid, detect the triggers which do not have the information about the trigger action columns and make those triggers collect that information. 

What I found is at the time of upgrade, we can find which triggers have trigger action column info missing but we can't make it collect that information at upgrade time because LCC is not available for compiling the trigger definition to find the trigger action column info. (I tried forcing the trigger definition compile at the time of upgrade and got error that context setup is not available. There is also a comment regarding LCC being not available in DataDictionaryImpl.createSPSSet.). So, the other option could be to mark the trigger invalid but TriggerDescriptors do not have a flag for validity because we never needed one so far. SPSDescriptors have a flag called valid which is how it knows that it needs to recompiled. There is no equivalent flag in TriggerDescriptor to mark it invalid. In fact, the method isValid() in TriggerDescriptor always returns true. So, the TriggerDescriptor can't be marked invalid at the time of upgrade AND it can't be compiled at upgrade time because LCC is not available for compiling.
We can start keeping the valid flag in TriggerDescriptor and change the isValid() method to use that flag. This flag will be true by default and will be set to false only at the time of upgrade when upgrade detects that the trigger does not have the trigger action column info. The first time the trigger will fire, we will find it invalid, we will collect the trigger action column info and update the row in SYSTRIGGERS and mark the trigger valid after that. Trigger will never be invalid again. Catch with this is that the first time a sql fires which will cause the trigger to fire, sql is going to read all the columns from the table because it looks at the triggers at the compile phase and in that phase, we are not allowed to perform a DML to update system table. During the execution of the firing sql, we will detect that trigger is invalid and we will collect the trigger action info for it and update SYSTRIGGERS. As part of collecting this info,we need to make sure that we regenerate the trigger action SPS.

This is pretty involved. Option 3) might be relatively easy to implement. May be we should consider implementing that option. It might be a good thing for ALTER TABLE COMPRESS to do anyways and it will be far better than users having to drop and recreate the triggers by hand and it is something we can test in our upgrade test by doing ALTER TABLE COMPRESS first on tables with LOBs and then issue triggering sql (only with 10.9 and higher) 


> Triggers created prior to 10.9 release will continue to read all the columns from trigger table even after database has been upgraded to 10.9 and higher
> --------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5294
>                 URL: https://issues.apache.org/jira/browse/DERBY-5294
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.9.0.0
>            Reporter: Mamta A. Satoor
>
> With DERBY-1482 (which went in 10.9 codeline), triggers will read only the columns needed by the triggering sql and firing triggers. But this applies only to triggers created in 10.9 and higher. Any triggers created prior to 10.9 will not be able to take advantage of DERBY-1482 because those triggers do not keep the information about the trigger action columns. Currently, the users will have to drop and recreate the triggers which use the REFERENCING CLAUSE and were created prior to 10.9 to take advantage of DERBY-1482.
> The alternative to manual drop and recreate of such triggers can be explored as part of this jira. Couple options are
> 1)UPDATE sql should detect that the trigger does not have information about the trigger action columns and hence it should make the trigger collect that information.
> 2)At the time of upgrade, when we mark all the SPSes invalid, detect the triggers which do not have the information about the trigger action columns and make those triggers collect that information.
> 3)Enhance ALTER TABLE COMPRESS to detect the triggers which do not have the information about the trigger action columns and make those triggers collect that information. With this option, users will still have to manually do ALTER TABLE COMPRESS to fix the triggers but atleast they won't have to get the original trigger definitions and drop and recreate the triggers using those original trigger definitions.
> 10.9 currently does not have central place where the trigger will go and collect the information about trigger action columns. We do have code in ALTER TABLE DROP COLUMN to collect the trigger action column info but it will probably better to have such a code in TriggerDescriptor so it can be used by the approach taken to fix this jira.
> Note that without the fix for this jira, the triggers created prior to 10.9 will work just fine after upgrade to 10.9 and higher but they will not be able to prevent reading of columns that are not necessary for the triggering sql and firing triggers

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira