db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Access to old and new values of the triggering table's columns inside the trigger action when trigger action is a stored procedure...
Date Mon, 08 Feb 2010 23:50:53 GMT
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.

> 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 <Kristian.Waagan@sun.com> wrote:
>> Knut Anders Hatlen wrote:
>>> Mamta Satoor <msatoor@gmail.com> 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

View raw message