[
https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-1482:
-----------------------------------
Attachment: derby1482DeepCopyAfterTriggerOnLobColumn.java
Deleted old derby1482DeepCopyAfterTriggerOnLobColumn.java and attaching a newer
version derby1482DeepCopyAfterTriggerOnLobColumn.java which not only shows how
available heap memory impact an AFTER UPDATE TRIGGER on a LOB column by making
it run into out of memory error, but it also shows that the heap memory change
does not impact similar AFTER UPDATE TRIGGER but on a CHAR column rather than a
LOB column. I am copying the javadoc comments from the program here to show
what it is doing
/**
* DERBY-1482 - This program shows that we are successfully doing deepcopy
* of before and after images of a lob object during an after update
* trigger(that is provided we are running with enough memory.)
*
* In case of after update trigger, we MUST do a deepcopy since the store
* value of lob is going to be different before and after the update.
*
* There are 2 sets of test cases
* 1)The first test case(runLobTest), the AFTER UPDATE TRIGGER has been defined
* on LOB column which means that Derby has to have enough memory to do the
* deep copy of the before and after image of the LOB column. With enough
* memory, the program will run with no OOM errors. Steps to runs the
* program successfully (w/o OOM error) for this test case is as follows
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
load
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
runLobTest
* For this same test case, if we run the test with limited memory, we will run
* into OOM error showing that there is not enough memory available to do the
* deep copy. Follow the steps below to see that behavior
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
load
* java -Xmx4m
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
runLobTest
* 2)The second test case(runCharTest) shows that there is no issue with OOM
* even when running with limited memory when LOB column is not used in the
* triggering table, meaning Derby does not have any LOB columns to have to
* copy in memory. This test case has the AFTER UPDATE TRIGGER on a CHAR
* column. The steps to run with plenty memory is as follows
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
load
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
runCharTest
* The steps to run this CHAR trigger with limited memory is as shown below.
* The limited memory has no impact (in orther words no OOM) for CHAR trigger
* java
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
load
* java -Xmx4m
org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn
runCharTest
*
*
* The first step in all the test scenarios is to first load the data needed
* and then perform the next step.
*/
> 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
>
>
> 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.
