db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [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
Date Fri, 24 Jun 2011 20:45:47 GMT

    [ 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

       

Mime
View raw message