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] Commented: (DERBY-1482) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
Date Wed, 10 Mar 2010 21:22:27 GMT

    [ https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12843779#action_12843779

Mamta A. Satoor commented on DERBY-1482:

Just wanted to share that I am working on collecting the column referenced by the trigger
action (when it is not a stored procedure call. I hope to tackle that as a seperate step)
During the create trigger bind time, we go through the columns referenced by the trigger action
and isolate the ones which come from the new/old tables through the REFERENCING clause. The
information about those columns will be saved in a new column in SYSTRIGGERS. At triggering
statement execution time, we will go through SYSTRIGGERS's new column and determine which
columns should be read in rather than all the columns. This should help with OOM issues involving
LOB columns.

Will need to work with upgrade code to support the new column in SYSTRIGGERS. At this point,
I am focusing on non-upgarde code changes.

I have finished the rough code changes on collecting the columns referenced in trigger action
when trigger action is not a stored procedure call. The information collected would be something
like [1,3] which means column numbers 1 and 3 are accessed by the trigger action. But the
tricky party is to transform column 3 from triggering table to column 2 in the columns that
will be read into memory. eg
create table t1 (c11 int, c12 int, c13 int)
insert into t1 values (11,12, 13)
and say there is a trigger defined on t1 which references column numbers 1 and 3 meaning columns
c11 and c13. Once I finish coding the logic completely, we will read only values 11 and 13
since c12 is not referenced by the trigger action. But the way code works right now, we try
to look at the columns through the column number meaning for our eg, c11 will be looked through
position 1 which is good. But c13 will be looked through position 3 but really, c13 is in
position 2 of the subset of the original row from t1. 

My apologies if above eg is little confusing. I will try to make changes so that we look for
c13 in position 2 of the subset row rather than position 3.

Any feedback appreciated.

> 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
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: derby1482DeepCopyAfterTriggerOnLobColumn.java, derby1482Repro.java,
derby1482ReproVersion2.java, TriggerTests_ver1_diff.txt, TriggerTests_ver1_stat.txt
> 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