db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5294) Enhance compress table to drop and recreate the triggers. This will enable pre-10.9 triggers (after hard upgrade to 10.9) to read only the required columns from the trigger table.
Date Thu, 30 Jun 2011 16:46:29 GMT

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

Mike Matrigali commented on DERBY-5294:
---------------------------------------

In many support scenario's in the past I often rely on offline compress to "fixup" problems
in the database.  I thought putting
this trigger stuff here would be nice for users.   In the same way that offline compress does
the same work as dropping/recreating the users indexes and constraints, this would take care
of bad/suboptimal triggers.  It is very handy that
often compress can fix bad indexes, and it is easy to write a small program that can go through
and run it against every table
in the db.  

Users in the past of complained about having to drop/recreate things like triggers/indexes/constraints.
 I agree it is a doable
workaround.  Running compress on the otherhand is something that is likely to give them other
benefits and some users
already do this as a normal maintenance.  

I agree it would be best if we could figure out a clean way to do this in upgrade, but did
not see an easy way and thought
it a reasonable alternative to allow users to run compress to fix the problem.  
My guess is that at least doing drop/create in upgrade is going to be hard as we need to make
sure the "right" user is doing 
the drop/create of the given trigger.  This is what made doing it as part of compress tempting.
 Also given that compress is
a very heavy weight thought the extra work for the trigger stuff would be ok.

> Enhance compress table to drop and recreate the triggers. This will enable pre-10.9 triggers
(after hard upgrade to 10.9) to read only the required columns from the trigger table.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 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
>         Attachments: DERBY5289_alltriggers_06282011_diff.txt, DERBY5289_alltriggers_06282011_stat.txt
>
>
> 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. Which in another words means
that such triggers will not benefit from work done for DERBY-1482.
> 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