[
https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12856168#action_12856168
]
Mamta A. Satoor commented on DERBY-1482:
----------------------------------------
Based on the fact that with the patch, it is possible to have referencedColumns
null, I think readExternal and writeExternal should look like following (this
should make sure that we read the pre-10.6 dbs in soft upgrade mode correctly
and write the new descriptors correctly provided that as suggested by Rick, in
CreateTriggerNode, we use the new code only if the db is at >10.5 level)
I will add following class comment to ReferencedColumnsDescriptorImpl
/**
* For triggers, ReferencedColumnsDescriptorImpl object has 3 possibilites
* 1)referencedColumns is not null but referencedColumnsInTriggerAction
* is null - meaning the trigger is defined on specific columns but trigger
* action does not reference any column through old/new transient variables.
* Another reason for referencedColumnsInTriggerAction to be null(even though
* trigger action does reference columns though old/new transient variables
* would be that we are in soft-upgrade mode for pre-10.6 databases and
* hence we do not want to write anything about
* referencedColumnsInTriggerAction for backward compatibility (DERBY-1482).
* eg create trigger tr1 after update of c1 on t1 for each row values(1);
* 2)referencedColumns is null but referencedColumnsInTriggerAction is not null
* - meaning the trigger is not defined on specific columns but trigger
* action references column through old/new transient variables
* eg create trigger tr1 after update on t1 referencing old as oldt
* for each row values(oldt.id);
* 3)referencedColumns and referencedColumnsInTriggerAction are not null -
* meaning the trigger is defined on specific columns and trigger action
* references column through old/new transient variables
* eg create trigger tr1 after update of c1 on t1 referencing old as oldt
* for each row values(oldt.id);
*/
/**
* For triggers, 3 possible scenarios
* 1)referencedColumns is not null but referencedColumnsInTriggerAction
* is null - then following will get read
* referencedColumns.length
* individual elements from referencedColumns arrary
* eg create trigger tr1 after update of c1 on t1 for each row
values(1);
* 2)referencedColumns is null but referencedColumnsInTriggerAction is
not
* null - then following will get read
* -1
* -1
* referencedColumnsInTriggerAction.length
* individual elements from referencedColumnsInTriggerAction arrary
* eg create trigger tr1 after update on t1 referencing old as oldt
* for each row values(oldt.id);
* 3)referencedColumns and referencedColumnsInTriggerAction are not
null -
* then following will get read
* -1
* referencedColumns.length
* individual elements from referencedColumns arrary
* referencedColumnsInTriggerAction.length
* individual elements from referencedColumnsInTriggerAction arrary
* eg create trigger tr1 after update of c1 on t1 referencing old as
oldt
* for each row values(oldt.id);
*/
public void readExternal(ObjectInput in) throws IOException
{
int rcLength;
int versionNumber = in.readInt();
if ( versionNumber < 0 ) {
//A negative value for versionNumber means that this trigger
action
//references columns through old/new transient variables.
Now, check
//if trigger has been defined on selected columns
rcLength = in.readInt();
if ( rcLength < 0 ) { //trigger is not defined on selected
columns
rcLength = 0;
} else
referencedColumns = new int[rcLength];
} else { //this trigger only has trigger columns saved on the
disc
rcLength = versionNumber;
referencedColumns = new int[rcLength];
}
for (int i = 0; i < rcLength; i++)
{
//if we are in this loop, then it means that this trigger
has been
//defined on specific columns. Read in information about
those columns
referencedColumns[i] = in.readInt();
}
if ( versionNumber < 0 )
{
//As mentioned earlier, a negative value for versionNumber
means that
//this trigger action references columns through old/new
transient variables.
//Read information about those columns into
referencedColumnsInTriggerAction
int rctaLength = in.readInt();
referencedColumnsInTriggerAction = new int[rctaLength];
for (int i = 0; i < rctaLength; i++)
{
referencedColumnsInTriggerAction[i] = in.readInt();
}
}
}
/**
* For triggers, 3 possible scenarios
* 1)referencedColumns is not null but referencedColumnsInTriggerAction
* is null - then following gets written
* referencedColumns.length
* individual elements from referencedColumns arrary
* eg create trigger tr1 after update of c1 on t1 for each row
values(1);
* 2)referencedColumns is null but referencedColumnsInTriggerAction is
not
* null - then following gets written
* -1
* -1
* referencedColumnsInTriggerAction.length
* individual elements from referencedColumnsInTriggerAction arrary
* eg create trigger tr1 after update on t1 referencing old as oldt
* for each row values(oldt.id);
* 3)referencedColumns and referencedColumnsInTriggerAction are not
null -
* then following gets written
* -1
* referencedColumns.length
* individual elements from referencedColumns arrary
* referencedColumnsInTriggerAction.length
* individual elements from referencedColumnsInTriggerAction arrary
* eg create trigger tr1 after update of c1 on t1 referencing old as
oldt
* for each row values(oldt.id);
*/
public void writeExternal(ObjectOutput out) throws IOException
{
//A null value for referencedColumnsInTriggerAction means one
of 2 cases
//1)We are working in soft-upgrade mode dealing with databases
lower than 10.6
// Prior to 10.6 release, we did not keep track of trigger
action columns
//2)We are working with >10.5 release database and the trigger
action does not
// reference any column through old/new transient variables
//versionNumber will be -1 if referencedColumnsInTriggerAction
is not null,
//meaning, we are dealing with >10.5 release database and the
trigger has referenced
//columns in trigger action through old/new transient variables.
//Otherwise, versionNumber will be the length of the arrary
referencedColumns. This
//arrary holds the columns on which trigger is defined. The
detailed meaning of
//these 2 arrays is described at the class level
comments(towards the beginning of
//this class.
int versionNumber = referencedColumnsInTriggerAction == null ?
referencedColumns.length : -1;
out.writeInt( versionNumber );
if ( versionNumber < 0 ) {
//If we are here, then it means that trigger action
references
//columns through old/new transient variables.
//First we will check if there are any trigger columns
selected
//for this trigger. If yes, we will write information
about
//trigger columns and if not, then we will write -1 to
indicate
//that there are no trigger columns selected.
//After that, we will write info about trigger action
columns.
if ( referencedColumns != null ) {
writeReferencedColumns(out);
} else
out.writeInt(versionNumber);
//Write info about trigger action columns referenced
through
//old/new transient variables
out.writeInt(referencedColumnsInTriggerAction.length);
for (int i = 0; i <
referencedColumnsInTriggerAction.length; i++)
{
out.writeInt(referencedColumnsInTriggerAction[i]);
}
} else {
//If we are here, then it means there are no references
in
//trigger action to old/new transient variables. But,
three are
//trigger columns selected for this trigger. Write info
about
//trigger columns
writeReferencedColumns(out);
}
}
private void writeReferencedColumns(ObjectOutput out) throws
IOException
{
//trigger is defined on select columns. Write info about trigger
columns
out.writeInt( referencedColumns.length );
for (int i = 0; i < referencedColumns.length; i++)
{
out.writeInt(referencedColumns[i]);
}
}
> Update triggers on tables with blob columns stream blobs into memory even
> when the blobs are not referenced/accessed.
> ---------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1482
> URL: https://issues.apache.org/jira/browse/DERBY-1482
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Assignee: Mamta A. Satoor
> Priority: Minor
> Attachments: derby1482_patch1_diff.txt, derby1482_patch1_stat.txt,
> derby1482_patch2_diff.txt, derby1482_patch2_stat.txt,
> derby1482DeepCopyAfterTriggerOnLobColumn.java, derby1482Repro.java,
> derby1482ReproVersion2.java, junitUpgradeTestFailureWithPatch1.out,
> TriggerTests_ver1_diff.txt, TriggerTests_ver1_stat.txt
>
>
> Suppose I have 1) a table "t1" with blob data in it, and 2) an UPDATE trigger
> "tr1" defined on that table, where the triggered-SQL-action for "tr1" does
> NOT reference any of the blob columns in the table. [ Note that this is
> different from DERBY-438 because DERBY-438 deals with triggers that _do_
> reference the blob column(s), whereas this issue deals with triggers that do
> _not_ reference the blob columns--but I think they're related, so I'm
> creating this as subtask to 438 ]. In such a case, if the trigger is fired,
> the blob data will be streamed into memory and thus consume JVM heap, even
> though it (the blob data) is never actually referenced/accessed by the
> trigger statement.
> For example, suppose we have the following DDL:
> create table t1 (id int, status smallint, bl blob(2G));
> create table t2 (id int, updated int default 0);
> create trigger tr1 after update of status on t1 referencing new as n_row
> for each row mode db2sql update t2 set updated = updated + 1 where t2.id =
> n_row.id;
> Then if t1 and t2 both have data and we make a call to:
> update t1 set status = 3;
> the trigger tr1 will fire, which will cause the blob column in t1 to be
> streamed into memory for each row affected by the trigger. The result is
> that, if the blob data is large, we end up using a lot of JVM memory when we
> really shouldn't have to (at least, in _theory_ we shouldn't have to...).
> Ideally, Derby could figure out whether or not the blob column is referenced,
> and avoid streaming the lob into memory whenever possible (hence this is
> probably more of an "enhancement" request than a bug)...
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
