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] Issue Comment Edited: (DERBY-1482) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
Date Mon, 22 Feb 2010 17:38:29 GMT

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

Mamta A. Satoor edited comment on DERBY-1482 at 2/22/10 5:37 PM:
-----------------------------------------------------------------

I wrote following ij script to understand what kind of information is kept in SYSTRIGGERS
when REFERENCING clause is specified or missing in the CREATE TRIGGER sql. There are 2 booolean
columns, namely, REFERENCINGOLD and REFERENCINGNEW in SYSTRIGGERS table which are set to false
if REFERENCING is missing. If either one of the REFERENCING clauses are specified (for OLD
and NEW), the corresponding columns' values will be set to TRUE, We can hopefully use this
information to disable streaming of LOB columns. I will look at the code to see how this information
can be used. I will assign this jira to me while I look at this aspect of REFERENCING clause.

java -Dij.exceptionTrace=true org.apache.derby.tools.ij 
connect 'jdbc:derby:testDB1482;create=true'; 
create table table1 (c11 int, c12 int default 0);
create index i1 on table1(c11);
create table table2 (c21 int, c22 int default 0);
create index i2 on table2(c21);
--For the trigger below, REFERENCINGOLD and REFERENCINGNEW will be false.
-- REFERENCINGOLD and REFERENCINGNEW are false because no REFERENCING clause was used in the
CREATE TRIGGER.
create trigger trigger1 AFTER INSERT on table1 insert into table2(c21, c22) values (100, -1);
--For the trigger below, REFERENCINGNEW will be true because CREATE TRIGGER used REFERENCING
NEW.
-- REFERENCINGOLD will be false because no REFERENCING OLD was specified in the CREATE TRIGGER
sql.
create trigger trigger2 AFTER UPDATE of c12 on table1 REFERENCING NEW as new FOR EACH ROW

	update table2 set c22=-1 where c21=new.c11;
select CAST(TRIGGERNAME as char(14)), REFERENCEDCOLUMNS, TRIGGERDEFINITION, REFERENCINGOLD,
REFERENCINGNEW from sys.systriggers;


      was (Author: mamtas):
    I wrote following ij script to understand what kind of information is kept in SYSTRIGGERS
when REFERENCING clause is specified or missing in the CREATE TRIGGER sql. There are 2 booolean
columns, namely, REFERENCINGOLD and REFERENCINGNEW in SYSTRIGGERS table which are set to false
if REFERENCING is missing. If either one of the REFERENCING clauses are specified (for OLD
and NEW), the corresponding columns' values will be set to TRUE, We can hopefully use this
information to disable streaming of LOB columns. I will look at the code to see how this information
can be used

java -Dij.exceptionTrace=true org.apache.derby.tools.ij 
connect 'jdbc:derby:testDB1482;create=true'; 
create table table1 (c11 int, c12 int default 0);
create index i1 on table1(c11);
create table table2 (c21 int, c22 int default 0);
create index i2 on table2(c21);
--For the trigger below, REFERENCINGOLD and REFERENCINGNEW will be false.
-- REFERENCINGOLD and REFERENCINGNEW are false because no REFERENCING clause was used in the
CREATE TRIGGER.
create trigger trigger1 AFTER INSERT on table1 insert into table2(c21, c22) values (100, -1);
--For the trigger below, REFERENCINGNEW will be true because CREATE TRIGGER used REFERENCING
NEW.
-- REFERENCINGOLD will be false because no REFERENCING OLD was specified in the CREATE TRIGGER
sql.
create trigger trigger2 AFTER UPDATE of c12 on table1 REFERENCING NEW as new FOR EACH ROW

	update table2 set c22=-1 where c21=new.c11;
select CAST(TRIGGERNAME as char(14)), REFERENCEDCOLUMNS, TRIGGERDEFINITION, REFERENCINGOLD,
REFERENCINGNEW from sys.systriggers;

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


Mime
View raw message