db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-442) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
Date Wed, 06 Jul 2005 18:39:10 GMT
     [ http://issues.apache.org/jira/browse/DERBY-442?page=all ]

A B updated DERBY-442:

    Attachment: d442.java

Attaching a repro for the problem.  I couldn't find a clean way to show the extra memory usage,
so this program runs in two stages.  First you have to load the data (with default JVM heap
size), and then you run the test while specifying a limited JVM heap size (4M is what works
for me; not sure if that will change for other people).  When running the test, the program
first executes a set of update statements that fire triggers on a table withOUT blob columns.
 Then it does the exact same thing on a table WITH blob columns.  Ideally, the memory usage
for the two scenarios should be similar (Derby would know that the blobs aren't used and so
wouldn't stream them into memory)--but what will happen with this program is that an "OutOfMemory"
exception will occur in the second scenario, because the streaming blobs take up all of the
(limited) JVM heap.

To run:

    java d442 load

    java -Xmx4m d442 run   // "4m" is small enough to show the error, but large enough to
let Derby boot...

> Update triggers on tables with blob columns stream blobs into memory even when the blobs
are not referenced/accessed.
> ---------------------------------------------------------------------------------------------------------------------
>          Key: DERBY-442
>          URL: http://issues.apache.org/jira/browse/DERBY-442
>      Project: Derby
>         Type: Sub-task
>     Versions:
>     Reporter: A B
>     Priority: Minor
>      Fix For:
>  Attachments: d442.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.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message