[
https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12835321#action_12835321
]
Kristian Waagan commented on DERBY-1482:
----------------------------------------
@Mike (16/Feb/10):
I don't have a write-up, and I'm not sure if the code will handle the scenarios
you mention. I don't fully understand the details of the store and the trigger
code in the case of inserts/updates of long columns.
Can you help me understand your comment about the changing BLOB value:
"But the stored
value of the blob could be changed before we access it, so a a stream pointer to
the original blob is not going to be valid. "
Who / what may change it;
- the current transaction's update query?
- the current transaction's trigger code?
- other transactions?
I hope to be able to have a better look at the trigger code soon, but it's not
on the top of my list atm.
That said, I do see that we are having trouble dealing with "inbound streams"
when they have to be used several times - our only tool there is to materialize
the stream into memory. One extension would be to write data to disk
temporarily to avoid OOME, but I'm not sure if we can manage this without
introducing a performance degradation.
The better option is probably to just write the value to the log and then read
it back from the log as required. Do you have any idea about how much effort
that would take? I'm not suggesting that it should be done now, but it would be
nice to have an idea about how difficult it is.
> 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.
