db-derby-dev mailing list archives

[jira] Updated: (DERBY-1482) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.

Date

Mon, 01 Feb 2010 20:12:19 GMT

[ 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.