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 Thu, 27 May 2010 18:08:37 GMT

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

Mamta A. Satoor commented on DERBY-1482:

Rick, I will post the patch soon for this jira. Thanks for your comments on earlier patch.
I have taken care of all of them except 2.

One of the comment was as follows
o In the comments there are several references to the columns mentioned in the REFERENCING
clause. I think the meaning would be a little more clear and specific if these comments talked
about the columns mentioned in the trigger action. 

The reason I am not directly using trigger action columns is because this deals only the trigger
action columns available through the REFERENCING clause. It does not include the columns from
the objects in the trigger action. To differentiate between these 2 different types of column
possibilites in trigger action, I think it is better to say columns available through REFERNCING
clause. I may be mistaken. Would like to know if you agree with my reasoning.

The second comment was as follows
o This patch changes the serialized form of TriggerDescriptor. This is ok as long as we can
convince ourselves that these objects are never persisted. These objects do live in the ConstantActions
of query plans for INSERT, UPDATE, and DELETE statements. Can we convince ourselves that the
compiled forms of INSERT, UPDATE, and DELETE statements never persist across soft-upgrades?

I don't completely comprehend this comment. When working with pre-10.7 dbs, my changes will
not generate any information about trigger action columns available through REFERENCING clause,
thus marking referencedColsInTriggerAction in TriggerDescriptor null. But I do have new changes
in TriggerDescriptor's readExternal and writeExternal. 
WriteExternal will write 0 if trigger action columns are null
		if (referencedColsInTriggerAction == null)
		{ ... write about trigger action columns

ReadExternal will check if there is a 0 for trigger action columns and if not found, will
read trigger action columns info
		length = in.readInt();
		if (length != 0)
			referencedColsInTriggerAction = new int[length];
			for (int i = 0; i < length; i++)
				referencedColsInTriggerAction[i] = in.readInt();

May be I should check if I am dealing with pre-10.7 dbs, then I should not write 0 for trigger
action columns and do the reverse for readExternal, meaning if dealing with pre-10.7dbs, do
not look for any information about trigger action columns. Is that what you were bringing
up in your comment? Thanks

> 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: derby1482_patch1_diff.txt, derby1482_patch1_stat.txt, derby1482_patch2_diff.txt,
derby1482_patch2_stat.txt, derby1482DeepCopyAfterTriggerOnLobColumn.java, derby1482Repro.java,
derby1482ReproVersion2.java, junitUpgradeTestFailureWithPatch1.out, TriggerTests_ver1_diff.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