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 Thu, 15 Apr 2010 20:02:52 GMT

    [ https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857516#action_12857516
] 

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

I was under the impression that I would need a new format id but since the code changes in
10.6 will still be able to read the old ReferencedColumnsDescriptor there is not a need for
new format id. I have removed my format id changes from my codeline (before removing those
changes, I was constantly getting no class implementation found for format id). But there
is a bug in the writeExternal that I posted above. In the case of missing trigger action column
references, my code was incorrectly writing the length of trigger columns twice. I have fixed
that problem. The new writeExternal will look like following

	/**
	 * 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; 

	        if ( versionNumber < 0 ) { 
		        out.writeInt( versionNumber ); 
	        	//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