You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2010/02/05 07:32:27 UTC

[jira] Created: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values

If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values 
------------------------------------------------------------------------------------------------------------------

                 Key: DERBY-4538
                 URL: https://issues.apache.org/jira/browse/DERBY-4538
             Project: Derby
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 10.6.0.0
            Reporter: Mamta A. Satoor
            Priority: Minor


In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Updated: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4538:
-----------------------------------

    Attachment: DERBY4538_NoReferencingClause_stat_v2.txt
                DERBY4538_NoReferencingClause_diff_v2.txt

Attaching patch DERBY4538_NoReferencingClause_diff_v2.txt which is ready for commit. This changes the UPDATE and DELETE statement codes to be little bit smarter when they decide what columns should be part of the read map. Currently, as soon as these 2 nodes find that there are relevant triggers on the table, we decide to read all the columns from the table. I am changing code to check if all the relevant tiggers have missing REFERENCING clause. If yes, then do not need to read all the columns. Just the columns needed by the UPDATE/DELETE statement. This will get rid of OOM we run into when the table has LOB columns BUT only in the case when the UPDATE/DELETE statement does not reference the LOB column and all the triggers defined on them have missing REFERENCING clause. I have enabled the TriggerTests in lowmem suite with the missing REFERENCING clause cases enabled. For all the other test cases, I simply return from those test cases without actually testing it because we do not have fix for those cases yet. The lowmem suite does not get regularly and when it is run, as the name indicates, it runs with limited heap. I wanted us to be able to run these tests with default heap as well. To achieve that, I am including the TriggerTests in lang suite too. Please let me know if there are any questions. I will commit this patch sometime next week.

The INSERT table with INSERT triggers work fine already without my changes as long as the INSERT statement does not reference the LOB column.

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_diff_v2.txt, DERBY4538_NoReferencingClause_stat_v1.txt, DERBY4538_NoReferencingClause_stat_v2.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Assigned: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor reassigned DERBY-4538:
--------------------------------------

    Assignee: Mamta A. Satoor

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Issue Comment Edited: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12839933#action_12839933 ] 

Mamta A. Satoor edited comment on DERBY-4538 at 3/2/10 12:27 AM:
-----------------------------------------------------------------

Knut, thanks for reviwing the patch. I committed the changes(with revision 917771) after removing the redundant ;

Will go ahead and close this issue. 

      was (Author: mamtas):
    Knut, thanks for reviwing the patch. I committed the chnages after removing the redundant ;

I will go ahead and close this issue. 
  
> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_diff_v2.txt, DERBY4538_NoReferencingClause_stat_v1.txt, DERBY4538_NoReferencingClause_stat_v2.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12839933#action_12839933 ] 

Mamta A. Satoor commented on DERBY-4538:
----------------------------------------

Knut, thanks for reviwing the patch. I committed the chnages after removing the redundant ;

I will go ahead and close this issue. 

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_diff_v2.txt, DERBY4538_NoReferencingClause_stat_v1.txt, DERBY4538_NoReferencingClause_stat_v2.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Updated: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4538:
-----------------------------------

    Attachment: DERBY4538_NoReferencingClause_stat_v1.txt
                DERBY4538_NoReferencingClause_diff_v1.txt

I am attaching a patch (not ready for commit yet), DERBY4538_NoReferencingClause_diff_v1.txt. This patch takes care of UPDATE triggers with no REFERENCING clause defined on them. During Update, Derby tries to determine which columns need to be read from the triggering table. As soon as we find out that the update table has triggers defined on it, we decide to read all the columns. We can improve on this algorithm My patch is attempting to be more intelligant about what columns should be read. The new logic is as follows.
	/*
 	** If we have any triggers, then do one of the following
 	** 1)If all of the triggers have MISSING referencing clause, then that
 	** means that the trigger actions do not have access to before and 
 	** after values. In that case, there is no need to blanketly decide to
 	** include all the columns in the read map just because there are
 	** triggers defined on the table.
 	** 2)Since one/more triggers have REFERENCING clause on them, get all 
 	** the columns because we don't know what the user will ultimately reference.
 	*/

Would love to hear if anyone has any feedback on this approach. Similar changes need to go in for INSERT and UPDATE statements. I will post a patch which will include those changes once i have it ready.

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_stat_v1.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12839645#action_12839645 ] 

Knut Anders Hatlen commented on DERBY-4538:
-------------------------------------------

v2 looks like a good incremental improvement. +1 to commit.

(Nit: The declarations of needToIncludeAllColumns end with two semi-colons.)

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_diff_v2.txt, DERBY4538_NoReferencingClause_stat_v1.txt, DERBY4538_NoReferencingClause_stat_v2.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Updated: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4538:
-----------------------------------

    Summary: If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table  (was: If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values )

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Priority: Minor
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Resolved: (DERBY-4538) If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/DERBY-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor resolved DERBY-4538.
------------------------------------

            Resolution: Fixed
         Fix Version/s: 10.6.0.0
    Bug behavior facts: [Performance]

> If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4538
>                 URL: https://issues.apache.org/jira/browse/DERBY-4538
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>             Fix For: 10.6.0.0
>
>         Attachments: DERBY4538_NoReferencingClause_diff_v1.txt, DERBY4538_NoReferencingClause_diff_v2.txt, DERBY4538_NoReferencingClause_stat_v1.txt, DERBY4538_NoReferencingClause_stat_v2.txt
>
>
> In order for the trigger action to have access to before and after values of the triggering table, the CREATE TRIGGER should use the REFERENCING clause. Without the REFERENCING clause, old and new values of triggering table can't be accessed by the trigger action. Based on this, we can improve Derby memory utilization by not keeping old and new values if REFERENCING clause is missing. It will be good to see if the code already does this optimization and if not, then introducing this optimization will definitely be very useful when the triggering table could have LOB columns.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.