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 05:33:42 GMT

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

Mamta A. Satoor commented on DERBY-1482:

Rick, thanks for reviewing the patch and for your comments. One thing may not have been clear
in my patch comments is that now, with my changes in the patch, it is possible to have referencedColumns
as null but referencedColumnsInTriggerAction as not null. eg
     create trigger tr1 after update on t1 referencing old as oldt  for each row values(oldt.id);
For the trigger above, the user has not provided a list of columns whose update should fire
trigger tr1. Because of that, any column update from table t1 will cause trigger tr1 to fire.
But the columns referenced in trigger action through old/new transiten variables is column
"id". in other words, for this trigger, referencedColumns will be null but referencedColumnsInTriggerAction
will not be null. The changes to readExternal and writeExternal will have to account for possibility
of referencedColumns being null. Prior to my changes in the patch, if referencedColumns was
null for a trigger, the entire object ReferencedColumnDescriptorImpl will be null and hence
we will never get to these methods. The null case was handled by FormatIdOutputStream.writeObject(Object)
with the following code
	public void writeObject(Object ref) throws IOException
		if (ref == null)
			FormatIdUtil.writeFormatIdInteger(this, StoredFormatIds.NULL_FORMAT_ID);

The stack trace of such a call is as follows.
Thread [main] (Suspended (breakpoint at line 95 in FormatIdOutputStream))	
	FormatIdOutputStream.writeObject(Object) line: 95	
	UserType.writeExternal(ObjectOutput) line: 291	
	StoredPage.logColumn(Object[], int, DynamicByteArrayOutputStream, int, int, int) line: 6243

	StoredPage.logRow(int, boolean, int, Object[], FormatableBitSet, DynamicByteArrayOutputStream,
int, byte, int, int, int) line: 3963	
	InsertOperation.writeOptionalDataToBuffer(RawTransaction, DynamicByteArrayOutputStream, Object[],
FormatableBitSet, boolean, int, int, int) line: 370	
	InsertOperation.<init>(RawTransaction, BasePage, int, int, Object[], FormatableBitSet,
LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 115	
	LoggableActions.actionInsert(RawTransaction, BasePage, int, int, Object[], FormatableBitSet,
LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 139	
	StoredPage(BasePage).insertNoOverflow(int, Object[], FormatableBitSet, LogicalUndo, byte,
int) line: 602	
	StoredPage(BasePage).insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int)
line: 523	
	StoredPage.insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 6723

	StoredPage(BasePage).insert(Object[], FormatableBitSet, byte, int) line: 629	
	HeapController.doInsert(DataValueDescriptor[]) line: 288	
	HeapController.insertAndFetchLocation(DataValueDescriptor[], RowLocation) line: 599	
	TabInfoImpl.insertRowListImpl(ExecRow[], TransactionController, RowLocation[]) line: 511

	TabInfoImpl.insertRow(ExecRow, TransactionController) line: 420	
	DataDictionaryImpl.addDescriptor(TupleDescriptor, TupleDescriptor, int, boolean, TransactionController)
line: 1824	
	CreateTriggerConstantAction.executeConstantAction(Activation) line: 321	
	MiscResultSet.open() line: 61	
	GenericPreparedStatement.executeStmt(Activation, boolean, long) line: 436	
	GenericPreparedStatement.execute(Activation, long) line: 317	
	EmbedStatement.executeStatement(Activation, boolean, boolean) line: 1232	
	EmbedStatement.execute(String, boolean, boolean, int, int[], String[]) line: 625	
	EmbedStatement.execute(String) line: 555	
	ij.executeImmediate(String) line: 367	
	utilMain.doCatch(String) line: 521	
	utilMain.runScriptGuts() line: 363	
	utilMain.go(LocalizedInput[], LocalizedOutput) line: 261	
	Main.go(LocalizedInput, LocalizedOutput) line: 229	
	Main.mainCore(String[], Main) line: 184	
	Main.main(String[]) line: 75	
	ij.main(String[]) line: 59	

> 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:
>            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,
> 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


View raw message