[
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
