db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [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 17:50:18 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

I have been spending some time on this jira writing test cases and researching the code a
bit. Also spoke to Mike about the jira in general. Here are the findings so far.

Following is the Derby behavior that we expect in regards to the lob columns and update triggers
1)In case of an AFTER UPDATE TRIGGER on the LOB column, we expect Derby to do a deep copy
of the before and after image of LOB columns in the triggering table. The reason for deep
copy is the contents of LOB will be different in store before the update and after the update.
 I have a java program conforming this behavior. That java program is attached and the program's
name is derby1482DeepCopyAfterTriggerOnLobColumn.java
2)If the AFTER UPDATE TRIGGER is defined on non-LOB column, Derby still does a deep copy of
before and after image of LOB columns in the triggering table. The possible improvements that
can be made to Derby is to detect somehow if there is any need to do deep copy at all if the
LOB columns are not getting accessed in the TRIGGER. If they are never accessed, there is
no need to any before/after update values of the LOB columns thus saving memory requirement
and unnecssary time spent in doing copy. I am working on writing a test case conforming that
deep copy is being done even though there is really no need to since LOB columns are not getting
3)In case of BEFORE UPDATE TRIGGER, we should not be doing any deep copy because the before
and after images of LOB should be same (which will be the LOB value before update was done).
I am working on writing test case for this scenario too.

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

View raw message