db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1482) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
Date Mon, 12 Apr 2010 20:58:52 GMT

    [ 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

        

Mime
View raw message