Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 30 Jun 2011 20:17:28 +0000 (UTC)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1435836349.6820.1309465048643.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <966181018.34756.1308866448306.JavaMail.tomcat@hel.zones.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.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-5294?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1305=
8053#comment-13058053 ]=20

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

I appreciate all the discussion on various ways of working with pre-10.9 tr=
iggers. Kristian asked "As a side note, are there any drawbacks/risks to "f=
ixing triggers" unconditionally in compress table? " Following is what I th=
ink of the compress table code changes.

My current approach is to drop and recreate *all* the triggers during compr=
ess time rather than just the ones that would directly benefit from DERBY-1=
482 work(which is row level triggers using REFERENCING clause but have miss=
ing information in system table about actual columns referenced through the=
 REFERENCING clause in trigger action). I think by choosing to drop and rec=
reate all the triggers, we will be consistent with all the triggers and com=
press code will be easier to read. We do need to do the testing to make sur=
e in soft-upgrade mode, we do not start collecting trigger action column in=
fo if the database is at pre-10.9 level, this is to maintain the backward c=
ompatibility. Another thing that will be code to check is that sysdepends h=
as correct number of rows before and after the compress. Another ideal thin=
g will be to have an upgrade test fixture which can be run with low memory =
so we can be sure that after compress, at hard upgrade time, we do not read=
 the LOB columns when not needed, otherwise with low mem configuration, we =
should run into OOM. I am not sure if there is a way to run just one fixtur=
e in a suite with low memory.

My only concern is this work requires a bit of code rearranging so we don't=
 have duplicate code(the patch attached does have code duplication and I wa=
s planning on removing the duplication in my next patch). This central code=
 then will be shared by create trigger, alter table drop column, compress t=
able and invalidated triggers which need recompiling. We just need to be su=
re that the centralized code works fine for all the four scenarios.


> Enhance compress table to drop and recreate the triggers. This will enabl=
e 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_a=
lltriggers_06282011_stat.txt
>
>
> Triggers created prior to 10.9 release will continue to read all the colu=
mns from trigger table even after database has been upgraded to 10.9 and hi=
gher. 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 th=
e columns needed by the triggering sql and firing triggers. But this applie=
s only to triggers created in 10.9 and higher. Any triggers created prior t=
o 10.9 will not be able to take advantage of DERBY-1482 because those trigg=
ers do not keep the information about the trigger action columns. Currently=
, the users will have to drop and recreate the triggers which use the REFER=
ENCING CLAUSE and were created prior to 10.9 to take advantage of DERBY-148=
2.
> The alternative to manual drop and recreate of such triggers can be explo=
red as part of this jira. Couple options are
> 1)UPDATE sql should detect that the trigger does not have information abo=
ut 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 t=
he information about the trigger action columns and make those triggers col=
lect 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 usi=
ng 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 AL=
TER 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 b=
e able to prevent reading of columns that are not necessary for the trigger=
ing sql and firing triggers

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