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 Fri, 16 Apr 2010 19:14:26 GMT

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

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

Mike you suggested that we check if we are in soft upgrade mode inside writeExternal or readExternal
methods but I think to find if we are in soft upgrade mode we need access to LanguageConnectionContext(I
don't have the exact call but it probably looks something similar to following). 
getLanguageConnectionContext().getDataDictionary().checkVersion(DataDictionary.DD_VERSION_CURRENT,null);
To my knowledge, readExternal and writeExternal methods do not have access to LanguageConnectionContext
and DataDictionary objects.

Before going further into how we might detect if we are in soft upgrade mode inside those
methods, I want to go over what different possibilites we need to handle to make sure that
I haven't missed anything.

When dealing with any trigger, we can have following possible scenarios
	a)trigger is getting created in newly created 10.6 db
	b)trigger already created in the pre-10.6db before soft upgrade
	c)trigger is getting created while in soft upgrad mode with pre-10.6 db
	d)trigger already created in the pre-10.6db before hard upgrade
	e)trigger is getting created after pre-10.6db is hard upgraded

Now let's let specific trigger scenarios for above db levels
1) create trigger tr1 after update on t1 for each row values(1); 
        Same for all 5 levels of db listed above. Nothing gets written in ReferencedColumnsDescriptorImpl.writeExternal.
Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate
that ReferencedColumnsDescriptorImpl object is null.

2) create trigger tr1 after update of c1 on t1 for each row values(1); 
        Same for all 5 levels of db listed above. ReferencedColumnsDescriptorImpl.writeExternal
will write following
		referencedColumns.length
		referencedColumns elements column positions

3) create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id);

        different for the 5 possible scenarios descibed above
	a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following
		-1 
		referencedColumns.length
		referencedColumns elements column positions
		referencedColumnsInTriggerAction.length
		referencedColumnsInTriggerAction elements column positions

	b)trigger already created in the pre-10.6db before soft upgrade - We will find following

		referencedColumns.length
		referencedColumns elements column positions

	c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make
changes in CreateTriggerNode  to detect that we are in soft upgrade mode and hence do not
collect information about trigger action columns. With those changes, we will write following
in ReferencedColumnsDescriptorImpl.writeExternal 
		referencedColumns.length
		referencedColumns elements column positions

	d)trigger already created in the pre-10.6db before hard upgrade - We will find following
(unless during hard upgrade we find a way to recompile all the triggers so proper information
gets created for them)
		referencedColumns.length
		referencedColumns elements column positions

	e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal

	  will write following
		-1 
		referencedColumns.length
		referencedColumns elements column positions
		referencedColumnsInTriggerAction.length
		referencedColumnsInTriggerAction elements column positions

4) create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id);

        different for the 5 possible scenarios descibed above
	a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following
		-1 
		-1 
		referencedColumnsInTriggerAction.length
		referencedColumnsInTriggerAction elements column positions

	b)trigger already created in the pre-10.6db before soft upgrade - Nothing gets written in
ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes
StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is
null.

	c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make
changes in CreateTriggerNode to detect that we are in soft upgrade mode and hence do not collect
information about trigger action columns. With those changes, ReferencedColumnsDescriptorImpl
will end up becoming null and we will write StoredFormatIds.NULL_FORMAT_ID in FormatIdOutputStream.writeObject.

	d)trigger already created in the pre-10.6db before hard upgrade - - Nothing gets written
in (unless during hard upgrade we find a way to recompile all the triggers so proper information
gets created for them) ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject
writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object
is null.

	e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal

	  will write following
		-1 
		-1 
		referencedColumnsInTriggerAction.length
		referencedColumnsInTriggerAction elements column positions

My changes will decide on what columns to read from the trigger table based on this saved
information. Which means for cases 3b), 3c), 3d), 4b), 4c) and 4d), we will incorrectly not
read columns involved in trigger action thus causing problems. 

For soft upgrade problem scenarios, 3b), 3c), 4b) and 4c), we can probably check that if we
are in soft upgrade mode and if there are triggers involved, then read all the columns from
the trigger table (just like what gets done today in the trunk and prior releases), no matter
if they are all needed or not. This logic though will also require us to read all the columns
from trigger table for case 2) above in soft upgrade mode. So, basically, we will read more
columns that probably needed but nothing will be broken. Also, we do not anticipate users
doing ton of work while in soft-upgrade mode.

But that still leaves issues with hard upgrade scenarios 3d) and 4d). One way to resolve the
problems with 3d) and 4d) would be that during hard upgrade, find a way to recompile all the
triggers so proper information gets created for them. 


> 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