Mamta Satoor wrote:
Hi Kristian,
I was thnking that may be I could look at how to implement 'if there
is no REFERENCING clause in crate trigger definition, then don't keep
before and after copies.' or is that something you are already looking
at. I don't want to duplicate the work if you are already looking at
this and other scenarios for triggers so thought would check with you
first before doing any work.
Hi Mamta,
No, I'm not working at what you describe, feel free to continue! It will
benefit us a lot to have the functionality you are working on.
I'm working on avoiding materializing LOBs. Combined these two fixes
might get us pretty close to a much better solution :)
FYI, I'll need a few more days before I can post something. In any case,
both pieces of work will improve the situation in different ways -
independent of each other.
Regards,
--
Kristian
I am almost finished up writing the stand alone test cases for various
scenarios for triggers and LOB combinations. Hope to have those tests
posted in jira by tomorrow. The reason they are standalone is that at
this point, I do not know if there is any way to have the iteration of
steps a)do the necessary setup for the test b)run the associated test
for the setup with limited heap to have it run into OOM and then go
back to a) for the next test.
thanks,
Mamta
On Sun, Feb 7, 2010 at 3:15 AM, Kristian Waagan <[email protected]> wrote:
Knut Anders Hatlen wrote:
Mamta Satoor <[email protected]> writes:
Rick, thanks as always. All this makes sense. My recent emails about
trigger behaviors are to see where we can avoid having before and
after copies of the LOB columns in the triggering table so we don't
run into OOM when it can be avoided.
Since the only way to pass before and old values to the stored
procedure in trigger action is through formal arguments, then I guess
we can safely assume that before and after values of LOB columns from
triggering tables will never make their way into stored procedure. The
Derby Reference manual says "Note: Data-types such as BLOB, CLOB, LONG
VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not allowed as
parameters in a CREATE PROCEDURE statement."
This limitation is likely to be lifted, though. See DERBY-4066.
So based on the fact that stored procedure gets before and old values
through parameters and LOBs can't be passed as parameters to stored
procedure, there is no need for us to keep before and after values of
LOB columns from the triggering table when the trigger action is a
stored procedure.
I haven't looked at the code, so I don't know how viable the different
approaches are, but I would think that looking at which columns the
different triggers actually reference is a more robust way to determine
which columns to copy into the before and after images. Then we would
save memory/copying for trigger actions that don't call stored
procedures too, and also for non-LOB types.
I agree with Knut Anders on this one, it would be nice if we could determine
which columns to copy.
That said, as part of my current LOB work, I'm looking at how to avoid
materializing (or objectifying) LOB columns for triggers. This will
definitely help for the cases where the LOB columns aren't referenced /
used, and it may also enable us to keep the values as streams in other cases
where the LOB is actually used in the trigger.
This work is still in the early phase, but I have been able to remove some
of the work-arounds / fixes put in place earlier. It is not yet clear to me
if this approach will get me anywhere, but I'll investigate further.
Regards,
--
Kristian
