Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1270475154.295161266311668026.JavaMail.jira@brutus.apache.org>
Date: Tue, 16 Feb 2010 09:14:28 +0000 (UTC)
From: "Kristian Waagan (JIRA)" <jira@apache.org>
To: derby-dev@db.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.
In-Reply-To: <32561667.1152198391960.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Kristian Waagan commented on DERBY-1482:
----------------------------------------

The stack trace above from running derby1482ReproVersion2 is slightly outdated.
Here's a newer stack trace:

ij> update t1 set status = 1 where id = 1; 
ERROR XJ001: Java exception: 'Java heap space: java.lang.OutOfMemoryError'.
[ snip ]
Caused by: java.lang.OutOfMemoryError: Java heap space
	at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
	at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:241)
	at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
	at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156)
	at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135)
	at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:425)
	at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:254)
	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
	... 10 more
ij> 

The code from the old stack trace has been rewritten (see DERBY-4477), loading the stream was only done temporarily until the underlying stream clone functionality was ready.

Regarding the new repro, I do have code that will make that one pass too :)
I will hold it back until you have been able to complete you current work, and my patch also needs more testing.

The newly added clone functionality will basically allow all store streams to be cloned. To provoke an OOME one has to clone a stream that isn't cloneable. I haven't looked into how to do this, but I think one way of doing it is to provide a user stream to use for the update (or insert). I'm sure there are other ways to do it too.
Despite the new cloning abilities, functionality to avoid referencing columns not being accessed will still give us benefits like increased performance and by avoiding OOMEs in certain situations.

> 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
>            Priority: Minor
>         Attachments: derby1482DeepCopyAfterTriggerOnLobColumn.java, derby1482Repro.java, derby1482ReproVersion2.java
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.