Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Tue, 09 Feb 2010 00:50:53 +0100
From: Kristian Waagan <Kristian.Waagan@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...
In-reply-to: <d9619e4a1002081158s12916b58w8740340765ba53a6@mail.gmail.com>
Sender: Kristian.Waagan@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4B70A35D.5080804@Sun.COM>
Organization: Sun Microsystems Inc.
References: <d9619e4a1002051405h525721cbh10a9d0cfeb08117f@mail.gmail.com>
 <4B6D8DE1.5020700@sun.com>
 <d9619e4a1002061407l38e7cf21o5277abd730808251@mail.gmail.com>
 <x6bpg2at1i.fsf@Sun.COM> <4B6EA0E8.40904@Sun.COM>
 <d9619e4a1002081158s12916b58w8740340765ba53a6@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)

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