[
https://issues.apache.org/jira/browse/DERBY-5294?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-5294:
-----------------------------------
Description:
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
was:
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
Summary: 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
reqiored columns from the trigger table. (was: 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)
> 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 reqiored
> 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
