Derby
  1. Derby
  2. DERBY-1482

Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.

    Details

    • Urgency:
      Low
    • Issue & fix info:
      High Value Fix, Release Note Needed

      Description

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

      1. derby1482Repro.java
        3 kB
        Mamta A. Satoor
      2. derby1482DeepCopyAfterTriggerOnLobColumn.java
        11 kB
        Mamta A. Satoor
      3. derby1482ReproVersion2.java
        3 kB
        Mamta A. Satoor
      4. TriggerTests_ver1_diff.txt
        64 kB
        Mamta A. Satoor
      5. TriggerTests_ver1_stat.txt
        0.2 kB
        Mamta A. Satoor
      6. junitUpgradeTestFailureWithPatch1.out
        271 kB
        Mamta A. Satoor
      7. derby1482_patch1_diff.txt
        59 kB
        Mamta A. Satoor
      8. derby1482_patch1_stat.txt
        1 kB
        Mamta A. Satoor
      9. derby1482_patch2_diff.txt
        50 kB
        Mamta A. Satoor
      10. derby1482_patch2_stat.txt
        0.9 kB
        Mamta A. Satoor
      11. derby1482_patch3_diff.txt
        102 kB
        Mamta A. Satoor
      12. derby1482_patch3_stat.txt
        1 kB
        Mamta A. Satoor
      13. DERBY_1482_patch4_diff.txt
        22 kB
        Mamta A. Satoor
      14. DERBY_1482_patch4_stat.txt
        0.9 kB
        Mamta A. Satoor
      15. derby1482_patch5_diff.txt
        33 kB
        Mamta A. Satoor
      16. derby1482_patch5_stat.txt
        0.9 kB
        Mamta A. Satoor
      17. derby1482_patch6_diff.txt
        34 kB
        Mamta A. Satoor
      18. derby1482_patch6_stat.txt
        0.9 kB
        Mamta A. Satoor
      19. releaseNote.html
        4 kB
        Mamta A. Satoor

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Mamta A. Satoor added a comment -

          Attaching a release note for the jira.

          Show
          Mamta A. Satoor added a comment - Attaching a release note for the jira.
          Hide
          Mamta A. Satoor added a comment -

          The changes for this jira has been committed to 10.9 codeline. Backport of this to 10.8 can cause an issue because we already have a release out for 10.8(10.8.2) which does not do any column reading optimization. If we were to migrate these changes to the next point release of 10.8, we could run into problems when the 10.8.2 database is used with next point release of 10.8 which does column read optimization. When such a db is taken back to 10.8.2, it can run into issues because 10.8.2 doesn't recognize column read optimization performed by the next point release of 10.8 and hence genereated trigger action SPSes will not work correctly in 10.8.1.

          Show
          Mamta A. Satoor added a comment - The changes for this jira has been committed to 10.9 codeline. Backport of this to 10.8 can cause an issue because we already have a release out for 10.8(10.8.2) which does not do any column reading optimization. If we were to migrate these changes to the next point release of 10.8, we could run into problems when the 10.8.2 database is used with next point release of 10.8 which does column read optimization. When such a db is taken back to 10.8.2, it can run into issues because 10.8.2 doesn't recognize column read optimization performed by the next point release of 10.8 and hence genereated trigger action SPSes will not work correctly in 10.8.1.
          Hide
          Mamta A. Satoor added a comment -

          The attached patch derby1482_patch6_diff.txt is exactly same as the previous patch(derby1482_patch5_diff.txt) except that I have enabled the tests that were written for this jira. We had disabled those tests after encountering the corruption issue DERBY-5121. Those tests now work fine with the attached patch.

          Show
          Mamta A. Satoor added a comment - The attached patch derby1482_patch6_diff.txt is exactly same as the previous patch(derby1482_patch5_diff.txt) except that I have enabled the tests that were written for this jira. We had disabled those tests after encountering the corruption issue DERBY-5121 . Those tests now work fine with the attached patch.
          Hide
          Mamta A. Satoor added a comment -

          I ran lowmem suite(ant junit-lowmem) with my changes and it ran fine.

          Show
          Mamta A. Satoor added a comment - I ran lowmem suite(ant junit-lowmem) with my changes and it ran fine.
          Hide
          Mamta A. Satoor added a comment -

          derbyall ran fine with derby1482_patch5_diff.txt

          Show
          Mamta A. Satoor added a comment - derbyall ran fine with derby1482_patch5_diff.txt
          Hide
          Mamta A. Satoor added a comment -

          Attaching a new patch derby1482_patch5_diff.txt which takes care of the upgrade problems which I ran into with the earlier draft patch.

          During an upgrade(soft/hard), the trigger action SPSes get marked invalid and hence when they fire next time, they will be regenerated and recompiled. The issue with the earlier patch was that when in soft-upgrade mode, the patch was using the new code to do the column read optimization while generating the internal trigger action sql but when such a database goes back to it's original version, the generated trigger action sql won't work anymore because previous releases of Derby do not recognize this column read optimization. In order to fix this, the code has to be smart to see if it is working with a pre-10.9 database(which in other words means that we are in soft-upgrade mode) and if yes, then it should not use the column read optimization code during trigger action SPS regeneration and during UPDATE execution when we read limited columns from the trigger table based on what is required by the UPDATE sql and the firing triggers I have made that change in the attached patch and now
          the upgrade tests work fine. I have also run the complete junit suite and it ranfine. derbyall is still running on my machine.

          I will go ahead and commit this patch on Monday if there are no comments.

          Show
          Mamta A. Satoor added a comment - Attaching a new patch derby1482_patch5_diff.txt which takes care of the upgrade problems which I ran into with the earlier draft patch. During an upgrade(soft/hard), the trigger action SPSes get marked invalid and hence when they fire next time, they will be regenerated and recompiled. The issue with the earlier patch was that when in soft-upgrade mode, the patch was using the new code to do the column read optimization while generating the internal trigger action sql but when such a database goes back to it's original version, the generated trigger action sql won't work anymore because previous releases of Derby do not recognize this column read optimization. In order to fix this, the code has to be smart to see if it is working with a pre-10.9 database(which in other words means that we are in soft-upgrade mode) and if yes, then it should not use the column read optimization code during trigger action SPS regeneration and during UPDATE execution when we read limited columns from the trigger table based on what is required by the UPDATE sql and the firing triggers I have made that change in the attached patch and now the upgrade tests work fine. I have also run the complete junit suite and it ranfine. derbyall is still running on my machine. I will go ahead and commit this patch on Monday if there are no comments.
          Hide
          Mamta A. Satoor added a comment - - edited

          I ran derbyall and junit suite with this patch and found that handful of upgrade tests are failing. After debugging, I have found the reason to be that my patch does optimization of reading only required columns and hence the trigger action column positions in that selective list of read columns may not be same as their column positions in the underlying trigger table. This column mismatch is not recognized by 10.7 released jars. I am making changes that when in soft upgrade mode, do not do the column reading optimization so the generated trigger action sql will still work when it is taken back to the older release which does not do any column reading optimization.

          Show
          Mamta A. Satoor added a comment - - edited I ran derbyall and junit suite with this patch and found that handful of upgrade tests are failing. After debugging, I have found the reason to be that my patch does optimization of reading only required columns and hence the trigger action column positions in that selective list of read columns may not be same as their column positions in the underlying trigger table. This column mismatch is not recognized by 10.7 released jars. I am making changes that when in soft upgrade mode, do not do the column reading optimization so the generated trigger action sql will still work when it is taken back to the older release which does not do any column reading optimization.
          Hide
          Mamta A. Satoor added a comment -

          This patch is not ready for commit but I wanted to put it down for review. I will be on vacation for next few days and
          will look at the review comments once I am back. But what the patch does is to read all the necessary columns during triggering sql's execution rather than read all the columns from the trigger table just because there is a trigger defined on the table. The thing to keep in mind is that the triggering sql might need more columns in it's resultset that what a trigger might need during it's execution. So, the other thing that the patch does is to carve out a temporary resulset for every firing trigger which will be exactly same as the resulset created by the triggering sql or a subset of the resulset created by the triggering sql. Once the right resulset set is constructed for the firing trigger, it will be able to find the columns referenced in it's trigger action through the REFERENCING clause in the correct positions
          in the resulset.

          I have not run the derbyall and junit suite on this. I plan to work on that after I return from by vacation.

          Show
          Mamta A. Satoor added a comment - This patch is not ready for commit but I wanted to put it down for review. I will be on vacation for next few days and will look at the review comments once I am back. But what the patch does is to read all the necessary columns during triggering sql's execution rather than read all the columns from the trigger table just because there is a trigger defined on the table. The thing to keep in mind is that the triggering sql might need more columns in it's resultset that what a trigger might need during it's execution. So, the other thing that the patch does is to carve out a temporary resulset for every firing trigger which will be exactly same as the resulset created by the triggering sql or a subset of the resulset created by the triggering sql. Once the right resulset set is constructed for the firing trigger, it will be able to find the columns referenced in it's trigger action through the REFERENCING clause in the correct positions in the resulset. I have not run the derbyall and junit suite on this. I plan to work on that after I return from by vacation.
          Hide
          Mamta A. Satoor added a comment - - edited

          The work that went under this jira caused data corruption logged under DERBY-5121. The problem is that the resultset expected by the trigger at execution time may not be same as what the triggering sql requires. This can result in trigger picking up the data from incorrect columns.

          Show
          Mamta A. Satoor added a comment - - edited The work that went under this jira caused data corruption logged under DERBY-5121 . The problem is that the resultset expected by the trigger at execution time may not be same as what the triggering sql requires. This can result in trigger picking up the data from incorrect columns.
          Hide
          Mamta A. Satoor added a comment -

          I had missed two master files with my previous commit. Fixed them with revision 957269.

          Kristian, I will work on reducing the size of LOBs to 50MB since junit-lowmem gets run with 16MB. Please let me know if you think it should be even lower. Thanks for looking at the test.

          Show
          Mamta A. Satoor added a comment - I had missed two master files with my previous commit. Fixed them with revision 957269. Kristian, I will work on reducing the size of LOBs to 50MB since junit-lowmem gets run with 16MB. Please let me know if you think it should be even lower. Thanks for looking at the test.
          Hide
          Kristian Waagan added a comment -

          I'm wondering if we really need to test with LOBs as large as 300 MB?
          That's not big enough to trigger the issue with most default JVM heap settings these days (i.e. max heap size is typically 512 MB or 1 GB), which forces us to run with specific heap options. We are currently running with a 16 MB heap in junit-lowmem, and writing/reading 300 MB LOBs take a while.

          For another time and effort, we can consider adding a method to return an appropriate LOB size, i.e. by using Runtime.totalMemory(). I haven't verified that this will work [in all JVMs], and we should also centralize this method to make it easy to track and override its return value if required.

          Show
          Kristian Waagan added a comment - I'm wondering if we really need to test with LOBs as large as 300 MB? That's not big enough to trigger the issue with most default JVM heap settings these days (i.e. max heap size is typically 512 MB or 1 GB), which forces us to run with specific heap options. We are currently running with a 16 MB heap in junit-lowmem, and writing/reading 300 MB LOBs take a while. For another time and effort, we can consider adding a method to return an appropriate LOB size, i.e. by using Runtime.totalMemory(). I haven't verified that this will work [in all JVMs] , and we should also centralize this method to make it easy to track and override its return value if required.
          Hide
          Mamta A. Satoor added a comment -

          I looked through our code during upgrade and found in DD_Version.handleMinorRevisionChange that during any kind of upgrade, we either invalidate or delete the stored prepared statements. This will ensure that we don't hold on TriggerDescriptor data structure that may not be incompatible across the Derby releases.

          As for the serialization, we already have that issue in our network server product. I would like to go ahead and commit my changes. The general serialization between client and server can be taken as a separate task.

          Show
          Mamta A. Satoor added a comment - I looked through our code during upgrade and found in DD_Version.handleMinorRevisionChange that during any kind of upgrade, we either invalidate or delete the stored prepared statements. This will ensure that we don't hold on TriggerDescriptor data structure that may not be incompatible across the Derby releases. As for the serialization, we already have that issue in our network server product. I would like to go ahead and commit my changes. The general serialization between client and server can be taken as a separate task.
          Hide
          Kathey Marsden added a comment -

          Yes Mike, you are right, you need the server jars to select objects from the system tables, for example:
          ij> connect 'jdbc:derby://localhost:1527/wombat';
          ij> select * from sys.sysaliases
          > ;
          ALIASID |ALIAS

          SCHEMAID JAVACLASSNAME
          & & SYST& ALIASINFO SPECIFICNAME

          --------------------------------------------------------------------------------
          --------------------------------------------------------------------------------
          --------------------------------------------------------------------------------
          --------------------------------------------------------------------------------
          --------------------------------------------------------------------------------
          --------------------------------------------------------------------------------
          ------
          ERROR XN020: Error marshalling or unmarshalling a user defined type: org.apache.
          derby.catalog.types.RoutineAliasInfo
          ij>

          With the 10.5 client it gives the text of the procedure or function definition for ALIASINFO may have been useful to someone, e.g.
          SQLCAMESSAGE(IN SQLCODE INTEGER,IN SQLERRML SMALLINT,IN SQLERRMC VARCHAR(2400),I
          N SQLERRP CHAR(8),IN SQLERRD0 INTEGER,IN SQLERR&

          I think you are right about this being a hole in our testing. Perhaps we should have an option to run tests with the client in a separate class loader or something.

          Show
          Kathey Marsden added a comment - Yes Mike, you are right, you need the server jars to select objects from the system tables, for example: ij> connect 'jdbc:derby://localhost:1527/wombat'; ij> select * from sys.sysaliases > ; ALIASID |ALIAS SCHEMAID JAVACLASSNAME & & SYST& ALIASINFO SPECIFICNAME -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------ ERROR XN020: Error marshalling or unmarshalling a user defined type: org.apache. derby.catalog.types.RoutineAliasInfo ij> With the 10.5 client it gives the text of the procedure or function definition for ALIASINFO may have been useful to someone, e.g. SQLCAMESSAGE(IN SQLCODE INTEGER,IN SQLERRML SMALLINT,IN SQLERRMC VARCHAR(2400),I N SQLERRP CHAR(8),IN SQLERRD0 INTEGER,IN SQLERR& I think you are right about this being a hole in our testing. Perhaps we should have an option to run tests with the client in a separate class loader or something.
          Hide
          Mike Matrigali added a comment -

          thanks rick, now I understand why the change. I see why it would be needed for udt's. And it sounds
          like we are covered in the documention about these columns.

          This is probably a hole in our testing, i am not sure there is a way in junit testing to test client with only client jars in the classpath - I believe this is what is documented for derby client users. Have you ever tried accessing these fields from derby client with only the derby client jar in the class path? It seems like it would need the server jar's to be able to work.

          Show
          Mike Matrigali added a comment - thanks rick, now I understand why the change. I see why it would be needed for udt's. And it sounds like we are covered in the documention about these columns. This is probably a hole in our testing, i am not sure there is a way in junit testing to test client with only client jars in the classpath - I believe this is what is documented for derby client users. Have you ever tried accessing these fields from derby client with only the derby client jar in the class path? It seems like it would need the server jar's to be able to work.
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          The handling of Java Objects stored in system tables was changed as part of the UDT work. For the details, see DERBY-4491. That issue brought the client behavior into agreement with the embedded behavior and the JDBC spec.

          The Reference Guide already says that Derby-specific objects in the catalogs are not part of our public API. This disclaimer is tacked onto the description of every catalog column which contains Derby-specific objects. That means that those objects can change shape and behavior and that we make no guarantees of cross-version compatibility. I don't think it's necessary to punch up the disclaimer but it wouldn't hurt.

          Beyond that explicit disclaimer, we have never claimed that the catalogs will retain their current shape. We have always reserved the right to add, delete, and modify catalog columns. Maybe we haven't made that clear enough to users. However, we do say the following in the Reference Guide section titled "Derby system tables":

          "You can query system tables, but you cannot alter them...The recommended way to get more information about these tables is to use an instance of the Java interface java.sql.DatabaseMetaData."

          We let users query the catalogs because JDBC metadata is not rich enough for portability layers to introspect the capabilities of SQL databases and Derby has not implemented the Standard information schema.

          Because we do let users query the catalogs, I think that we should include a 10.7 release note warning users about the compatibility issues with SYSTRIGGERS.REFERENCEDCOLUMNS.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Mike, The handling of Java Objects stored in system tables was changed as part of the UDT work. For the details, see DERBY-4491 . That issue brought the client behavior into agreement with the embedded behavior and the JDBC spec. The Reference Guide already says that Derby-specific objects in the catalogs are not part of our public API. This disclaimer is tacked onto the description of every catalog column which contains Derby-specific objects. That means that those objects can change shape and behavior and that we make no guarantees of cross-version compatibility. I don't think it's necessary to punch up the disclaimer but it wouldn't hurt. Beyond that explicit disclaimer, we have never claimed that the catalogs will retain their current shape. We have always reserved the right to add, delete, and modify catalog columns. Maybe we haven't made that clear enough to users. However, we do say the following in the Reference Guide section titled "Derby system tables": "You can query system tables, but you cannot alter them...The recommended way to get more information about these tables is to use an instance of the Java interface java.sql.DatabaseMetaData." We let users query the catalogs because JDBC metadata is not rich enough for portability layers to introspect the capabilities of SQL databases and Derby has not implemented the Standard information schema. Because we do let users query the catalogs, I think that we should include a 10.7 release note warning users about the compatibility issues with SYSTRIGGERS.REFERENCEDCOLUMNS. Thanks, -Rick
          Hide
          Mike Matrigali added a comment -

          I just read back a bit and see the issue about SERIALIZATION across client/server. Do you know what JIRA caused this behavior change in 10.6? I am not sure if it is even possible to do a proper
          soft/hard upgrade on anything in a system catalog that could properly take account of client/server
          mismatches.

          I would lean toward documenting that we don't support this access, it seems like we should only
          support using the standard jdbc data dictionary interfaces.

          I was not aware of this new client/server behavior. One of the reasons for the current design in this fix, was to avoid the complication of creating new format id's and subclasses. And as you point out even
          if we do this we can't guarantee anything other than a format id not found error on the backward client since in the worst case it can't have any new code to handle new stuff in hard upgraded servers.

          Show
          Mike Matrigali added a comment - I just read back a bit and see the issue about SERIALIZATION across client/server. Do you know what JIRA caused this behavior change in 10.6? I am not sure if it is even possible to do a proper soft/hard upgrade on anything in a system catalog that could properly take account of client/server mismatches. I would lean toward documenting that we don't support this access, it seems like we should only support using the standard jdbc data dictionary interfaces. I was not aware of this new client/server behavior. One of the reasons for the current design in this fix, was to avoid the complication of creating new format id's and subclasses. And as you point out even if we do this we can't guarantee anything other than a format id not found error on the backward client since in the worst case it can't have any new code to handle new stuff in hard upgraded servers.
          Hide
          Mike Matrigali added a comment -

          I like the current appoach of changing what is stored in SYSTRIGGERS. I think that it fits well with current architecture.

          I have reviewed the serialization code and believe it is correct. If there are any problems please comment. It would be nice if it were "less tricky", as it sort of
          overloads existing fields in the data structure - but this is not uncommon in derby when dealing with stored representations of data. I am ok
          with the tradeoff of how it is currently implemented vs. the alternative of upgrading
          the actual type of the column in the system catalog or adding a new column to the
          system catalog. It would be nice if we came up with a standard way, well tested
          way to do this - but would not hold up this change on that.

          If someone is interested I do think that #1 of Rick's 4 cases could be implemented as a separate next increment. A simple non-code workaround would be to drop and recreate the triggers. A code change would be to run
          some code at upgrade time to rewrite the triggers. At that point only soft upgraded databases would not get
          the performance improvement, which should not surprise customers much.

          Show
          Mike Matrigali added a comment - I like the current appoach of changing what is stored in SYSTRIGGERS. I think that it fits well with current architecture. I have reviewed the serialization code and believe it is correct. If there are any problems please comment. It would be nice if it were "less tricky", as it sort of overloads existing fields in the data structure - but this is not uncommon in derby when dealing with stored representations of data. I am ok with the tradeoff of how it is currently implemented vs. the alternative of upgrading the actual type of the column in the system catalog or adding a new column to the system catalog. It would be nice if we came up with a standard way, well tested way to do this - but would not hold up this change on that. If someone is interested I do think that #1 of Rick's 4 cases could be implemented as a separate next increment. A simple non-code workaround would be to drop and recreate the triggers. A code change would be to run some code at upgrade time to rewrite the triggers. At that point only soft upgraded databases would not get the performance improvement, which should not surprise customers much.
          Hide
          Rick Hillegas added a comment -

          Thanks for the patch, Mamta. If I understand correctly, users should expect to see the following behaviors:

          1) No behavior change for legacy triggers created before 10.7 is released.

          2) No behavior change for triggers created in soft-upgraded databases.

          3) Potential performance improvement for triggers created in new 10.7 databases.

          4) Potential performance improvement for triggers created in legacy databases after hard-upgrade to 10.7.

          Before looking into the details of this patch, I would like to explore an alternative solution. Maybe this solution has already been considered and rejected. If so, I apologize for the noise. This alternative approach would bring the performance improvement to more cases and would avoid the soft-upgrade and serialization issues. I think that it would re-use most of the code which you are supplying with the current patch:

          A) Do not change what is stored in SYSTRIGGERS.

          B) Instead, the very first time that a trigger is run, if there is a REFERENCING clause, re-parse the trigger text in order to find the columns that are actually needed.

          C) Store the extra referenced column information in a transient field of the trigger descriptor for use by later firings.

          The disadvantage of this approach is that the first firing of a trigger would incur an extra compilation tax. I think that this tax would not be noticed.

          The advantage of this approach is that the performance improvement would be seen in cases (1) and (2) above and not just in cases (3) and (4). In addition, we would avoid the tricky serialization incompatibilities.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the patch, Mamta. If I understand correctly, users should expect to see the following behaviors: 1) No behavior change for legacy triggers created before 10.7 is released. 2) No behavior change for triggers created in soft-upgraded databases. 3) Potential performance improvement for triggers created in new 10.7 databases. 4) Potential performance improvement for triggers created in legacy databases after hard-upgrade to 10.7. Before looking into the details of this patch, I would like to explore an alternative solution. Maybe this solution has already been considered and rejected. If so, I apologize for the noise. This alternative approach would bring the performance improvement to more cases and would avoid the soft-upgrade and serialization issues. I think that it would re-use most of the code which you are supplying with the current patch: A) Do not change what is stored in SYSTRIGGERS. B) Instead, the very first time that a trigger is run, if there is a REFERENCING clause, re-parse the trigger text in order to find the columns that are actually needed. C) Store the extra referenced column information in a transient field of the trigger descriptor for use by later firings. The disadvantage of this approach is that the first firing of a trigger would incur an extra compilation tax. I think that this tax would not be noticed. The advantage of this approach is that the performance improvement would be seen in cases (1) and (2) above and not just in cases (3) and (4). In addition, we would avoid the tricky serialization incompatibilities. Thanks, -Rick
          Hide
          Mamta A. Satoor added a comment -

          Attaching patch derby1482_patch3_diff.txt which along with the core work also takes care of upgrade. Please share your comments on the patch.

          Here are the patch comments
          DERBY-1482 will improve on Derby's behavior about which columns should be read in when a trigger fires. The current behavior of Derby reads all the columns from the trigger table whether or not all the columns are needed for trigger execution. This can be troublesome when the trigger table has large data LOB columns causing Derby to run out of memory. With this patch, Derby will narrow down cases where it should be sufficient to read only a subset of columns from the trigger table.
          eg of such a trigger would be the trigger below which has both trigger columns and trigger action columns through old/new transition variables

          • CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON t1
          • REFERENCING OLD AS oldt NEW AS newt
          • FOR EACH ROW UPDATE t2 SET c24=oldt.j;
            For a trigger like above, Derby knows exactly which columns are needed from the trigger table during trigger execution. A note of those columns to read will be made in SYSTRIGGERS table.

          The general rules for what columns will be read in during a trigger execution will be as follows
          Rule1)If trigger column information is null, then Derby will read all the columns from trigger table into memory irrespective of whether there is any trigger action column information. eg triggers
          create trigger tr1 after update on t1 for each row values(1);
          create trigger tr1 after update on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2);

          Rule2)If trigger column information is available but no trigger action column information is found and no REFERENCES clause is used for the trigger, then read all the columns identified by the trigger column. eg trigger
          create trigger tr1 after update of c1 on t1 for each row values(1);

          Rule3)If trigger column information and trigger action column information both are not null, then only those columns will be read into memory. This will be a trigger created in 10.7 or higher level db. Because prior to that we did not collect trigger action column informatoin. eg
          create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2);

          Rule4)If trigger column information is available and no trigger action column information is found but REFERENCES clause is used for the trigger, then we will read all the columns from the trigger table. I believe that this will cover soft-upgrade and hard-
          upgrade scenario during trigger execution of triggers created pre-10.7. This will prevent us from having special logic of soft-upgrade.
          Additionally, this logic makes invalidation of existing triggers unnecessary during hard-upgrade. The pre-10.7 created triggers will work just fine even though for some triggers, they would have trigger action columns missing. A user can choose to drop and recreate such triggers to take advantage of DERBY-1482 work which will avoid unnecssary column reads during trigger execution if the trigger action column information was available along with trigger column info. eg trigger created prior to 10.7
          create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2);
          The Rule4) is there to cover triggers created with pre-10,7 releases but now that database has been hard/soft-upgraded to 10.7
          or higher version. Prior to 10.7, we did not collect any information about trigger action columns. So, both of the 2 kinds of triggers shown above prior to 10.7 will not have any trigger action column info on them in SYSTRIGGERS table. Without our last rule about what columns should be read in, we will run into problems for soft-upgrade or hard-upgrade causing us to not read all the required columns.The trigger eg above if created prior to 10.7 mode will have trigger column and REFERENCING clause set in SYSTRIGGERS but will not have trigger action column info even though there are columns referenced in trigger action through old/new transition variables. This same trigger if created in 10.7 or higher version will have trigger column, REFERENCING clause and trigger action column set for it in SYSTRIGGERS. To handle this on a hard/soft-upgraded db, we will just choose to read all the columns from trigger table if SYSTRIGGER shows trigger column and REFERENCING clause for a trigger but no trigger action column.

          Rule5)The only place we will need special code for soft-upgrade is during trigger creation. If we are in soft-upgrade mode, we want to make sure that we do not save information about trigger action columns in SYSTRIGGERS because the releases prior to 10.7 do not understand trigger action column information.

          Let's see with examples, when the rules described above kick in
          Consider following 4 trigger scenarios
          1) create trigger tr1 after update on t1 for each row values(1);
          2) create trigger tr1 after update on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2);
          3) create trigger tr1 after update of c1 on t1 for each row values(1);
          4) create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2);

          And these different triggers could be created in following various combination of soft-upgrade, hard-upgrade, brand new db
          a)trigger is getting created in newly created 10.7 db
          b)trigger already created in the pre-10.7db before soft upgrade
          c)trigger is getting created while in soft upgrad mode with pre-10.7 db
          d)trigger already created in the pre-10.7db before hard upgrade
          e)trigger is getting created after pre-10.7db is hard upgraded

          All the db variations for trigger scenario 1 will be covered by Rule1).
          All the db variations for trigger scenario 2 will be covered by Rule1)
          All the db variations for trigger scenario 3 will be covered by Rule2).
          For trigger scenario 4, different rules will apply depending on db variations
          For 4a), Rule3) will apply
          For 4b), Rule4) will apply
          For 4c), Rule5) will apply
          For 4d), Rule4) will apply
          For 4e), Rule3) will apply

          Show
          Mamta A. Satoor added a comment - Attaching patch derby1482_patch3_diff.txt which along with the core work also takes care of upgrade. Please share your comments on the patch. Here are the patch comments DERBY-1482 will improve on Derby's behavior about which columns should be read in when a trigger fires. The current behavior of Derby reads all the columns from the trigger table whether or not all the columns are needed for trigger execution. This can be troublesome when the trigger table has large data LOB columns causing Derby to run out of memory. With this patch, Derby will narrow down cases where it should be sufficient to read only a subset of columns from the trigger table. eg of such a trigger would be the trigger below which has both trigger columns and trigger action columns through old/new transition variables CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON t1 REFERENCING OLD AS oldt NEW AS newt FOR EACH ROW UPDATE t2 SET c24=oldt.j; For a trigger like above, Derby knows exactly which columns are needed from the trigger table during trigger execution. A note of those columns to read will be made in SYSTRIGGERS table. The general rules for what columns will be read in during a trigger execution will be as follows Rule1)If trigger column information is null, then Derby will read all the columns from trigger table into memory irrespective of whether there is any trigger action column information. eg triggers create trigger tr1 after update on t1 for each row values(1); create trigger tr1 after update on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2); Rule2)If trigger column information is available but no trigger action column information is found and no REFERENCES clause is used for the trigger, then read all the columns identified by the trigger column. eg trigger create trigger tr1 after update of c1 on t1 for each row values(1); Rule3)If trigger column information and trigger action column information both are not null, then only those columns will be read into memory. This will be a trigger created in 10.7 or higher level db. Because prior to that we did not collect trigger action column informatoin. eg create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2); Rule4)If trigger column information is available and no trigger action column information is found but REFERENCES clause is used for the trigger, then we will read all the columns from the trigger table. I believe that this will cover soft-upgrade and hard- upgrade scenario during trigger execution of triggers created pre-10.7. This will prevent us from having special logic of soft-upgrade. Additionally, this logic makes invalidation of existing triggers unnecessary during hard-upgrade. The pre-10.7 created triggers will work just fine even though for some triggers, they would have trigger action columns missing. A user can choose to drop and recreate such triggers to take advantage of DERBY-1482 work which will avoid unnecssary column reads during trigger execution if the trigger action column information was available along with trigger column info. eg trigger created prior to 10.7 create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2); The Rule4) is there to cover triggers created with pre-10,7 releases but now that database has been hard/soft-upgraded to 10.7 or higher version. Prior to 10.7, we did not collect any information about trigger action columns. So, both of the 2 kinds of triggers shown above prior to 10.7 will not have any trigger action column info on them in SYSTRIGGERS table. Without our last rule about what columns should be read in, we will run into problems for soft-upgrade or hard-upgrade causing us to not read all the required columns.The trigger eg above if created prior to 10.7 mode will have trigger column and REFERENCING clause set in SYSTRIGGERS but will not have trigger action column info even though there are columns referenced in trigger action through old/new transition variables. This same trigger if created in 10.7 or higher version will have trigger column, REFERENCING clause and trigger action column set for it in SYSTRIGGERS. To handle this on a hard/soft-upgraded db, we will just choose to read all the columns from trigger table if SYSTRIGGER shows trigger column and REFERENCING clause for a trigger but no trigger action column. Rule5)The only place we will need special code for soft-upgrade is during trigger creation. If we are in soft-upgrade mode, we want to make sure that we do not save information about trigger action columns in SYSTRIGGERS because the releases prior to 10.7 do not understand trigger action column information. Let's see with examples, when the rules described above kick in Consider following 4 trigger scenarios 1) create trigger tr1 after update on t1 for each row values(1); 2) create trigger tr1 after update on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2); 3) create trigger tr1 after update of c1 on t1 for each row values(1); 4) create trigger tr1 after update of c1 on t1 referencing old as oldt for each row insert into t2 values(2,oldt.j,-2); And these different triggers could be created in following various combination of soft-upgrade, hard-upgrade, brand new db a)trigger is getting created in newly created 10.7 db b)trigger already created in the pre-10.7db before soft upgrade c)trigger is getting created while in soft upgrad mode with pre-10.7 db d)trigger already created in the pre-10.7db before hard upgrade e)trigger is getting created after pre-10.7db is hard upgraded All the db variations for trigger scenario 1 will be covered by Rule1). All the db variations for trigger scenario 2 will be covered by Rule1) All the db variations for trigger scenario 3 will be covered by Rule2). For trigger scenario 4, different rules will apply depending on db variations For 4a), Rule3) will apply For 4b), Rule4) will apply For 4c), Rule5) will apply For 4d), Rule4) will apply For 4e), Rule3) will apply
          Hide
          Rick Hillegas added a comment -

          Hi Mamta,

          Concerning the first comment, let's just wait for the new patch. If the comments still puzzle me, I'll let you know.

          Concerning the second comment: If the new serialized form is only used in new databases and hard-upgraded databases, then there should be no problem. There should be no problem if new trigger descriptors in soft-upgraded databases have the same serialized form as 10.6 trigger descriptors. This is the situation you want to avoid:

          1) You create a new trigger in a soft-upgraded database.

          2) Then you soft-downgrade to 10.6.

          3) Because the serialized form has changed, the 10.6 server raises a deserialization error (or worse) every time the new trigger fires.

          Note that, because this patch didn't make it into 10.6, there is now another serialization issue which we have to deal with:

          In 10.5 and earlier, the objects stored in system tables were converted into strings when they were selected by clients. That is, a 10.5 or earlier server returned ReferencedColumnDescriptorImpl.toString() for the following query:

          select referencedcolumns from sys.systriggers

          In 10.6, the above query uses the writeExternal()/readExternal() machinery to send the ReferencedColumnDescriptor object if both the client and the server are at 10.6 or higher. That query will choke a 10.6 client when it hits a trigger with a referencing clause that is created in a 10.7 database. For this sort of problem, the Formatable machinery calls for introducing a new formatable id. In the end, this may make your serialization logic easier to read but it may not improve the situation for 10.6 clients. I think that the following will work:

          Introduce a new subclass of ReferencedColumnDescriptorImpl called ReferencedColumnDescriptorImpl_7_0. That class will have its own formatable id and will serialize itself differently than the old ReferencedColumnDescriptorImpl. If one of these new classes is sent to a 10.6 client, then you will get an error looking up the formatable id. I don't know if that error will be any better or worse. We may want to add a release note saying that we don't handle this edge case gracefully.

          Hope this answers your questions.

          Regards,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Mamta, Concerning the first comment, let's just wait for the new patch. If the comments still puzzle me, I'll let you know. Concerning the second comment: If the new serialized form is only used in new databases and hard-upgraded databases, then there should be no problem. There should be no problem if new trigger descriptors in soft-upgraded databases have the same serialized form as 10.6 trigger descriptors. This is the situation you want to avoid: 1) You create a new trigger in a soft-upgraded database. 2) Then you soft-downgrade to 10.6. 3) Because the serialized form has changed, the 10.6 server raises a deserialization error (or worse) every time the new trigger fires. Note that, because this patch didn't make it into 10.6, there is now another serialization issue which we have to deal with: In 10.5 and earlier, the objects stored in system tables were converted into strings when they were selected by clients. That is, a 10.5 or earlier server returned ReferencedColumnDescriptorImpl.toString() for the following query: select referencedcolumns from sys.systriggers In 10.6, the above query uses the writeExternal()/readExternal() machinery to send the ReferencedColumnDescriptor object if both the client and the server are at 10.6 or higher. That query will choke a 10.6 client when it hits a trigger with a referencing clause that is created in a 10.7 database. For this sort of problem, the Formatable machinery calls for introducing a new formatable id. In the end, this may make your serialization logic easier to read but it may not improve the situation for 10.6 clients. I think that the following will work: Introduce a new subclass of ReferencedColumnDescriptorImpl called ReferencedColumnDescriptorImpl_7_0. That class will have its own formatable id and will serialize itself differently than the old ReferencedColumnDescriptorImpl. If one of these new classes is sent to a 10.6 client, then you will get an error looking up the formatable id. I don't know if that error will be any better or worse. We may want to add a release note saying that we don't handle this edge case gracefully. Hope this answers your questions. Regards, -Rick
          Hide
          Mamta A. Satoor added a comment -

          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
          ***************************************************************
          CreateTriggerNode:
          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)

          { out.writeInt(0); }

          else
          { ... 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
          ***************************************************************

          Show
          Mamta A. Satoor added a comment - 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 *************************************************************** CreateTriggerNode: 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) { out.writeInt(0); } else { ... 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 ***************************************************************
          Hide
          Mike Matrigali added a comment -

          ReadExternal should never need to access anything about "soft/hard upgrade". The state of the object is defined by whatever it is reading from disk. How to build the object is all determined by the first int you read from disk.

          After thinking about it I agree there should not be "soft/hard upgrade" references in the writeExternal. Somehow the object itself should know whether it should write the old or
          new format out. I think you may be describing some tricky ways to do this. I think if I were
          doing this I would just add a new field to the object, something like version_id. If version_id = 0 then write old format, if version_id=1 then write new version. Read external will need to change to set this appropriately. And the code that is called by create trigger will also need to change to set this correctly based on database version, this probably is in the constructor somewhere.

          Do add more comments to the read and write external describing better the old and new formats. What you are proposing is a little tricky, so best to have some block of code that describes "version 0" format and "version 1" format.

          If you get this right from bottom up then I think all the hard/soft upgrade stuff will just work correctly. I would leave how you get the old format converted to new format as a
          last step, as you need to make sure old format in new db's yields correct results (while maybe not optimized results).

          Show
          Mike Matrigali added a comment - ReadExternal should never need to access anything about "soft/hard upgrade". The state of the object is defined by whatever it is reading from disk. How to build the object is all determined by the first int you read from disk. After thinking about it I agree there should not be "soft/hard upgrade" references in the writeExternal. Somehow the object itself should know whether it should write the old or new format out. I think you may be describing some tricky ways to do this. I think if I were doing this I would just add a new field to the object, something like version_id. If version_id = 0 then write old format, if version_id=1 then write new version. Read external will need to change to set this appropriately. And the code that is called by create trigger will also need to change to set this correctly based on database version, this probably is in the constructor somewhere. Do add more comments to the read and write external describing better the old and new formats. What you are proposing is a little tricky, so best to have some block of code that describes "version 0" format and "version 1" format. If you get this right from bottom up then I think all the hard/soft upgrade stuff will just work correctly. I would leave how you get the old format converted to new format as a last step, as you need to make sure old format in new db's yields correct results (while maybe not optimized results).
          Hide
          Mamta A. Satoor added a comment -

          I ran the tests for trigger where there are no trigger columns specified but there are columns referenced in trigger action through old/new transition variables which is the case 4) above and we should not run into any issues with that scenario because Derby decides to read all the columns from the trigger table if there are not trigger columns specified for it. So, no matter which one of the following scenario has been used to create the trigger, of kind 4) case 10.6 code will work fine since Derby is going to read all the columns create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id);
          a)trigger is getting created in newly created 10.6 db
          b)trigger already created in the pre-10.6db before soft upgrade
          c)trigger is getting created while in soft upgrad mode with pre-10.6 db
          d)trigger already created in the pre-10.6db before hard upgrade
          e)trigger is getting created after pre-10.6db is hard upgraded

          So, the only issue we need to worry about is case 3), which is create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id);

          I think we can solve the soft-upgrade problems by just having Derby read all the columns no matter what(or none) trigger columns are specified. I think it is an acceptable solution because user probably would not be running their databases in soft-upgrade mode for a long time. Let me know what your thoughts might be.

          The only issue left then is 3d), triggers which were created prior to 10.6 and that database has been hard-upgrade. In such hard upgraded databases, we will run into not running enough columns for trigger case 3)create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); One way to resolve such trigger cases would be at upgrade time, invalidate the trigger so that get recompiled before they are used next in the hard-upgraded database.

          Any thoughts/feedback greatly appreciated.

          Show
          Mamta A. Satoor added a comment - I ran the tests for trigger where there are no trigger columns specified but there are columns referenced in trigger action through old/new transition variables which is the case 4) above and we should not run into any issues with that scenario because Derby decides to read all the columns from the trigger table if there are not trigger columns specified for it. So, no matter which one of the following scenario has been used to create the trigger, of kind 4) case 10.6 code will work fine since Derby is going to read all the columns create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); a)trigger is getting created in newly created 10.6 db b)trigger already created in the pre-10.6db before soft upgrade c)trigger is getting created while in soft upgrad mode with pre-10.6 db d)trigger already created in the pre-10.6db before hard upgrade e)trigger is getting created after pre-10.6db is hard upgraded So, the only issue we need to worry about is case 3), which is create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); I think we can solve the soft-upgrade problems by just having Derby read all the columns no matter what(or none) trigger columns are specified. I think it is an acceptable solution because user probably would not be running their databases in soft-upgrade mode for a long time. Let me know what your thoughts might be. The only issue left then is 3d), triggers which were created prior to 10.6 and that database has been hard-upgrade. In such hard upgraded databases, we will run into not running enough columns for trigger case 3)create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); One way to resolve such trigger cases would be at upgrade time, invalidate the trigger so that get recompiled before they are used next in the hard-upgraded database. Any thoughts/feedback greatly appreciated.
          Hide
          Mamta A. Satoor added a comment -

          I need to do little more experimentation but I think, after all, case 4 create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); might not be an issue because if no trigger columns are found, Derby decides to read all the columns no matter what columns are referenced in trigger action through old/new transition variable (this happens in impl.sql.compile.UpdateNod.getUpdateReadMap()) and hence even though different things get written for the 5 levels discussed below for case 4, we might be ok. Will post soon after I have done some more testing for case 4)

          Show
          Mamta A. Satoor added a comment - I need to do little more experimentation but I think, after all, case 4 create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); might not be an issue because if no trigger columns are found, Derby decides to read all the columns no matter what columns are referenced in trigger action through old/new transition variable (this happens in impl.sql.compile.UpdateNod.getUpdateReadMap()) and hence even though different things get written for the 5 levels discussed below for case 4, we might be ok. Will post soon after I have done some more testing for case 4)
          Hide
          Rick Hillegas added a comment -

          I recommend that the (de)serialization logic not depend on context outside the object. That is because, with 10.6, objects can be serialized across the network and sent to clients for retrieval via ResultSet.getObject(). Serialization across the network is supposed to work if both the client and the server are 10.6 code. Bad things will happen if the client tries to deserialize one of these objects and can't find a LanguageConnectionContext.

          It ought to be possible to design serialization so that it is completely self-contained.

          Show
          Rick Hillegas added a comment - I recommend that the (de)serialization logic not depend on context outside the object. That is because, with 10.6, objects can be serialized across the network and sent to clients for retrieval via ResultSet.getObject(). Serialization across the network is supposed to work if both the client and the server are 10.6 code. Bad things will happen if the client tries to deserialize one of these objects and can't find a LanguageConnectionContext. It ought to be possible to design serialization so that it is completely self-contained.
          Hide
          Mamta A. Satoor added a comment -

          Mike you suggested that we check if we are in soft upgrade mode inside writeExternal or readExternal methods but I think to find if we are in soft upgrade mode we need access to LanguageConnectionContext(I don't have the exact call but it probably looks something similar to following).
          getLanguageConnectionContext().getDataDictionary().checkVersion(DataDictionary.DD_VERSION_CURRENT,null);
          To my knowledge, readExternal and writeExternal methods do not have access to LanguageConnectionContext and DataDictionary objects.

          Before going further into how we might detect if we are in soft upgrade mode inside those methods, I want to go over what different possibilites we need to handle to make sure that I haven't missed anything.

          When dealing with any trigger, we can have following possible scenarios
          a)trigger is getting created in newly created 10.6 db
          b)trigger already created in the pre-10.6db before soft upgrade
          c)trigger is getting created while in soft upgrad mode with pre-10.6 db
          d)trigger already created in the pre-10.6db before hard upgrade
          e)trigger is getting created after pre-10.6db is hard upgraded

          Now let's let specific trigger scenarios for above db levels
          1) create trigger tr1 after update on t1 for each row values(1);
          Same for all 5 levels of db listed above. Nothing gets written in ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null.

          2) create trigger tr1 after update of c1 on t1 for each row values(1);
          Same for all 5 levels of db listed above. ReferencedColumnsDescriptorImpl.writeExternal will write following
          referencedColumns.length
          referencedColumns elements column positions

          3) create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id);
          different for the 5 possible scenarios descibed above
          a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following
          -1
          referencedColumns.length
          referencedColumns elements column positions
          referencedColumnsInTriggerAction.length
          referencedColumnsInTriggerAction elements column positions

          b)trigger already created in the pre-10.6db before soft upgrade - We will find following
          referencedColumns.length
          referencedColumns elements column positions

          c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make changes in CreateTriggerNode to detect that we are in soft upgrade mode and hence do not collect information about trigger action columns. With those changes, we will write following in ReferencedColumnsDescriptorImpl.writeExternal
          referencedColumns.length
          referencedColumns elements column positions

          d)trigger already created in the pre-10.6db before hard upgrade - We will find following (unless during hard upgrade we find a way to recompile all the triggers so proper information gets created for them)
          referencedColumns.length
          referencedColumns elements column positions

          e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal
          will write following
          -1
          referencedColumns.length
          referencedColumns elements column positions
          referencedColumnsInTriggerAction.length
          referencedColumnsInTriggerAction elements column positions

          4) create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id);
          different for the 5 possible scenarios descibed above
          a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following
          -1
          -1
          referencedColumnsInTriggerAction.length
          referencedColumnsInTriggerAction elements column positions

          b)trigger already created in the pre-10.6db before soft upgrade - Nothing gets written in ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null.

          c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make changes in CreateTriggerNode to detect that we are in soft upgrade mode and hence do not collect information about trigger action columns. With those changes, ReferencedColumnsDescriptorImpl will end up becoming null and we will write StoredFormatIds.NULL_FORMAT_ID in FormatIdOutputStream.writeObject.

          d)trigger already created in the pre-10.6db before hard upgrade - - Nothing gets written in (unless during hard upgrade we find a way to recompile all the triggers so proper information gets created for them) ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null.

          e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal
          will write following
          -1
          -1
          referencedColumnsInTriggerAction.length
          referencedColumnsInTriggerAction elements column positions

          My changes will decide on what columns to read from the trigger table based on this saved information. Which means for cases 3b), 3c), 3d), 4b), 4c) and 4d), we will incorrectly not read columns involved in trigger action thus causing problems.

          For soft upgrade problem scenarios, 3b), 3c), 4b) and 4c), we can probably check that if we are in soft upgrade mode and if there are triggers involved, then read all the columns from the trigger table (just like what gets done today in the trunk and prior releases), no matter if they are all needed or not. This logic though will also require us to read all the columns from trigger table for case 2) above in soft upgrade mode. So, basically, we will read more columns that probably needed but nothing will be broken. Also, we do not anticipate users doing ton of work while in soft-upgrade mode.

          But that still leaves issues with hard upgrade scenarios 3d) and 4d). One way to resolve the problems with 3d) and 4d) would be that during hard upgrade, find a way to recompile all the triggers so proper information gets created for them.

          Show
          Mamta A. Satoor added a comment - Mike you suggested that we check if we are in soft upgrade mode inside writeExternal or readExternal methods but I think to find if we are in soft upgrade mode we need access to LanguageConnectionContext(I don't have the exact call but it probably looks something similar to following). getLanguageConnectionContext().getDataDictionary().checkVersion(DataDictionary.DD_VERSION_CURRENT,null); To my knowledge, readExternal and writeExternal methods do not have access to LanguageConnectionContext and DataDictionary objects. Before going further into how we might detect if we are in soft upgrade mode inside those methods, I want to go over what different possibilites we need to handle to make sure that I haven't missed anything. When dealing with any trigger, we can have following possible scenarios a)trigger is getting created in newly created 10.6 db b)trigger already created in the pre-10.6db before soft upgrade c)trigger is getting created while in soft upgrad mode with pre-10.6 db d)trigger already created in the pre-10.6db before hard upgrade e)trigger is getting created after pre-10.6db is hard upgraded Now let's let specific trigger scenarios for above db levels 1) create trigger tr1 after update on t1 for each row values(1); Same for all 5 levels of db listed above. Nothing gets written in ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null. 2) create trigger tr1 after update of c1 on t1 for each row values(1); Same for all 5 levels of db listed above. ReferencedColumnsDescriptorImpl.writeExternal will write following referencedColumns.length referencedColumns elements column positions 3) create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); different for the 5 possible scenarios descibed above a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following -1 referencedColumns.length referencedColumns elements column positions referencedColumnsInTriggerAction.length referencedColumnsInTriggerAction elements column positions b)trigger already created in the pre-10.6db before soft upgrade - We will find following referencedColumns.length referencedColumns elements column positions c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make changes in CreateTriggerNode to detect that we are in soft upgrade mode and hence do not collect information about trigger action columns. With those changes, we will write following in ReferencedColumnsDescriptorImpl.writeExternal referencedColumns.length referencedColumns elements column positions d)trigger already created in the pre-10.6db before hard upgrade - We will find following (unless during hard upgrade we find a way to recompile all the triggers so proper information gets created for them) referencedColumns.length referencedColumns elements column positions e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal will write following -1 referencedColumns.length referencedColumns elements column positions referencedColumnsInTriggerAction.length referencedColumnsInTriggerAction elements column positions 4) create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); different for the 5 possible scenarios descibed above a)10.6 - ReferencedColumnsDescriptorImpl.writeExternal will write following -1 -1 referencedColumnsInTriggerAction.length referencedColumnsInTriggerAction elements column positions b)trigger already created in the pre-10.6db before soft upgrade - Nothing gets written in ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null. c)trigger is getting created while in soft upgrad mode with pre-10.6 db - I need to make changes in CreateTriggerNode to detect that we are in soft upgrade mode and hence do not collect information about trigger action columns. With those changes, ReferencedColumnsDescriptorImpl will end up becoming null and we will write StoredFormatIds.NULL_FORMAT_ID in FormatIdOutputStream.writeObject. d)trigger already created in the pre-10.6db before hard upgrade - - Nothing gets written in (unless during hard upgrade we find a way to recompile all the triggers so proper information gets created for them) ReferencedColumnsDescriptorImpl.writeExternal. Instead, FormatIdOutputStream.writeObject writes StoredFormatIds.NULL_FORMAT_ID to indicate that ReferencedColumnsDescriptorImpl object is null. e)trigger is getting created after pre-10.6db is hard upgraded - ReferencedColumnsDescriptorImpl.writeExternal will write following -1 -1 referencedColumnsInTriggerAction.length referencedColumnsInTriggerAction elements column positions My changes will decide on what columns to read from the trigger table based on this saved information. Which means for cases 3b), 3c), 3d), 4b), 4c) and 4d), we will incorrectly not read columns involved in trigger action thus causing problems. For soft upgrade problem scenarios, 3b), 3c), 4b) and 4c), we can probably check that if we are in soft upgrade mode and if there are triggers involved, then read all the columns from the trigger table (just like what gets done today in the trunk and prior releases), no matter if they are all needed or not. This logic though will also require us to read all the columns from trigger table for case 2) above in soft upgrade mode. So, basically, we will read more columns that probably needed but nothing will be broken. Also, we do not anticipate users doing ton of work while in soft-upgrade mode. But that still leaves issues with hard upgrade scenarios 3d) and 4d). One way to resolve the problems with 3d) and 4d) would be that during hard upgrade, find a way to recompile all the triggers so proper information gets created for them.
          Hide
          Mamta A. Satoor added a comment -

          I have next started looking at how soft upgrade should be handled.

          Show
          Mamta A. Satoor added a comment - I have next started looking at how soft upgrade should be handled.
          Hide
          Mamta A. Satoor added a comment -

          I was under the impression that I would need a new format id but since the code changes in 10.6 will still be able to read the old ReferencedColumnsDescriptor there is not a need for new format id. I have removed my format id changes from my codeline (before removing those changes, I was constantly getting no class implementation found for format id). But there is a bug in the writeExternal that I posted above. In the case of missing trigger action column references, my code was incorrectly writing the length of trigger columns twice. I have fixed that problem. The new writeExternal will look like following

          /**

          • For triggers, 3 possible scenarios
          • 1)referencedColumns is not null but referencedColumnsInTriggerAction
          • is null - then following gets written
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • eg create trigger tr1 after update of c1 on t1 for each row values(1);
          • 2)referencedColumns is null but referencedColumnsInTriggerAction is not
          • null - then following gets written
          • -1
          • -1
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update on t1 referencing old as oldt
          • for each row values(oldt.id);
          • 3)referencedColumns and referencedColumnsInTriggerAction are not null -
          • then following gets written
          • -1
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update of c1 on t1 referencing old as oldt
          • for each row values(oldt.id);
            */
            public void writeExternal(ObjectOutput out) throws IOException
            {
            //A null value for referencedColumnsInTriggerAction means one of 2 cases
            //1)We are working in soft-upgrade mode dealing with databases lower than 10.6
            // Prior to 10.6 release, we did not keep track of trigger action columns
            //2)We are working with >10.5 release database and the trigger action does not
            // reference any column through old/new transient variables

          //versionNumber will be -1 if referencedColumnsInTriggerAction is not null,
          //meaning, we are dealing with >10.5 release database and the trigger has referenced
          //columns in trigger action through old/new transient variables.
          //Otherwise, versionNumber will be the length of the arrary referencedColumns. This
          //arrary holds the columns on which trigger is defined. The detailed meaning of
          //these 2 arrays is described at the class level comments(towards the beginning of
          //this class.
          int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1;

          if ( versionNumber < 0 ) {
          out.writeInt( versionNumber );
          //If we are here, then it means that trigger action references
          //columns through old/new transient variables.
          //First we will check if there are any trigger columns selected
          //for this trigger. If yes, we will write information about
          //trigger columns and if not, then we will write -1 to indicate
          //that there are no trigger columns selected.
          //After that, we will write info about trigger action columns.
          if ( referencedColumns != null )

          { writeReferencedColumns(out); }

          else

          { out.writeInt(versionNumber); }

          //Write info about trigger action columns referenced through
          //old/new transient variables
          out.writeInt(referencedColumnsInTriggerAction.length);
          for (int i = 0; i < referencedColumnsInTriggerAction.length; i++)

          { out.writeInt(referencedColumnsInTriggerAction[i]); }

          } else

          { //If we are here, then it means there are no references in //trigger action to old/new transient variables. But, three are //trigger columns selected for this trigger. Write info about //trigger columns writeReferencedColumns(out); }


          }
          private void writeReferencedColumns(ObjectOutput out) throws IOException
          {
          //trigger is defined on select columns. Write info about trigger columns
          out.writeInt( referencedColumns.length );
          for (int i = 0; i < referencedColumns.length; i++)

          { out.writeInt(referencedColumns[i]); }

          }

          Show
          Mamta A. Satoor added a comment - I was under the impression that I would need a new format id but since the code changes in 10.6 will still be able to read the old ReferencedColumnsDescriptor there is not a need for new format id. I have removed my format id changes from my codeline (before removing those changes, I was constantly getting no class implementation found for format id). But there is a bug in the writeExternal that I posted above. In the case of missing trigger action column references, my code was incorrectly writing the length of trigger columns twice. I have fixed that problem. The new writeExternal will look like following /** For triggers, 3 possible scenarios 1)referencedColumns is not null but referencedColumnsInTriggerAction is null - then following gets written referencedColumns.length individual elements from referencedColumns arrary eg create trigger tr1 after update of c1 on t1 for each row values(1); 2)referencedColumns is null but referencedColumnsInTriggerAction is not null - then following gets written -1 -1 referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); 3)referencedColumns and referencedColumnsInTriggerAction are not null - then following gets written -1 referencedColumns.length individual elements from referencedColumns arrary referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); */ public void writeExternal(ObjectOutput out) throws IOException { //A null value for referencedColumnsInTriggerAction means one of 2 cases //1)We are working in soft-upgrade mode dealing with databases lower than 10.6 // Prior to 10.6 release, we did not keep track of trigger action columns //2)We are working with >10.5 release database and the trigger action does not // reference any column through old/new transient variables //versionNumber will be -1 if referencedColumnsInTriggerAction is not null, //meaning, we are dealing with >10.5 release database and the trigger has referenced //columns in trigger action through old/new transient variables. //Otherwise, versionNumber will be the length of the arrary referencedColumns. This //arrary holds the columns on which trigger is defined. The detailed meaning of //these 2 arrays is described at the class level comments(towards the beginning of //this class. int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1; if ( versionNumber < 0 ) { out.writeInt( versionNumber ); //If we are here, then it means that trigger action references //columns through old/new transient variables. //First we will check if there are any trigger columns selected //for this trigger. If yes, we will write information about //trigger columns and if not, then we will write -1 to indicate //that there are no trigger columns selected. //After that, we will write info about trigger action columns. if ( referencedColumns != null ) { writeReferencedColumns(out); } else { out.writeInt(versionNumber); } //Write info about trigger action columns referenced through //old/new transient variables out.writeInt(referencedColumnsInTriggerAction.length); for (int i = 0; i < referencedColumnsInTriggerAction.length; i++) { out.writeInt(referencedColumnsInTriggerAction[i]); } } else { //If we are here, then it means there are no references in //trigger action to old/new transient variables. But, three are //trigger columns selected for this trigger. Write info about //trigger columns writeReferencedColumns(out); } } private void writeReferencedColumns(ObjectOutput out) throws IOException { //trigger is defined on select columns. Write info about trigger columns out.writeInt( referencedColumns.length ); for (int i = 0; i < referencedColumns.length; i++) { out.writeInt(referencedColumns[i]); } }
          Hide
          Mike Matrigali added a comment -

          i like the approach rick has suggested, it makes the upgrade a lot easier and avoids the need to have new format id's. The first int becomes a "version" of the data structure. As a way to incrementally implement this I suggest you first get soft upgrade to work, and then just get new triggers to work, and finally if necessary worry about hard upgrading the old triggers to new trigger data structure.

          I think you can handle it all just in the read and write external routines, if you code your in memory datastructures right. What looks missing is that even if your in memory data structure has extra reference column information, you should test for soft upgrade in the write external method and not write that information out.

          Show
          Mike Matrigali added a comment - i like the approach rick has suggested, it makes the upgrade a lot easier and avoids the need to have new format id's. The first int becomes a "version" of the data structure. As a way to incrementally implement this I suggest you first get soft upgrade to work, and then just get new triggers to work, and finally if necessary worry about hard upgrading the old triggers to new trigger data structure. I think you can handle it all just in the read and write external routines, if you code your in memory datastructures right. What looks missing is that even if your in memory data structure has extra reference column information, you should test for soft upgrade in the write external method and not write that information out.
          Hide
          Mamta A. Satoor added a comment -

          Based on the fact that with the patch, it is possible to have referencedColumns null, I think readExternal and writeExternal should look like following (this should make sure that we read the pre-10.6 dbs in soft upgrade mode correctly and write the new descriptors correctly provided that as suggested by Rick, in CreateTriggerNode, we use the new code only if the db is at >10.5 level)

          I will add following class comment to ReferencedColumnsDescriptorImpl
          /**

          • For triggers, ReferencedColumnsDescriptorImpl object has 3 possibilites
          • 1)referencedColumns is not null but referencedColumnsInTriggerAction
          • is null - meaning the trigger is defined on specific columns but trigger
          • action does not reference any column through old/new transient variables.
          • Another reason for referencedColumnsInTriggerAction to be null(even though
          • trigger action does reference columns though old/new transient variables
          • would be that we are in soft-upgrade mode for pre-10.6 databases and
          • hence we do not want to write anything about
          • referencedColumnsInTriggerAction for backward compatibility (DERBY-1482).
          • eg create trigger tr1 after update of c1 on t1 for each row values(1);
          • 2)referencedColumns is null but referencedColumnsInTriggerAction is not null
          • - meaning the trigger is not defined on specific columns but trigger
          • action references column through old/new transient variables
          • eg create trigger tr1 after update on t1 referencing old as oldt
          • for each row values(oldt.id);
          • 3)referencedColumns and referencedColumnsInTriggerAction are not null -
          • meaning the trigger is defined on specific columns and trigger action
          • references column through old/new transient variables
          • eg create trigger tr1 after update of c1 on t1 referencing old as oldt
          • for each row values(oldt.id);
            */

          /**

          • For triggers, 3 possible scenarios
          • 1)referencedColumns is not null but referencedColumnsInTriggerAction
          • is null - then following will get read
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • eg create trigger tr1 after update of c1 on t1 for each row values(1);
          • 2)referencedColumns is null but referencedColumnsInTriggerAction is not
          • null - then following will get read
          • -1
          • -1
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update on t1 referencing old as oldt
          • for each row values(oldt.id);
          • 3)referencedColumns and referencedColumnsInTriggerAction are not null -
          • then following will get read
          • -1
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update of c1 on t1 referencing old as oldt
          • for each row values(oldt.id);
            */
            public void readExternal(ObjectInput in) throws IOException
            {
            int rcLength;
            int versionNumber = in.readInt();

          if ( versionNumber < 0 ) {
          //A negative value for versionNumber means that this trigger action
          //references columns through old/new transient variables. Now, check
          //if trigger has been defined on selected columns
          rcLength = in.readInt();
          if ( rcLength < 0 )

          { //trigger is not defined on selected columns rcLength = 0; }

          else
          referencedColumns = new int[rcLength];
          } else

          { //this trigger only has trigger columns saved on the disc rcLength = versionNumber; referencedColumns = new int[rcLength]; }

          for (int i = 0; i < rcLength; i++)

          { //if we are in this loop, then it means that this trigger has been //defined on specific columns. Read in information about those columns referencedColumns[i] = in.readInt(); }

          if ( versionNumber < 0 )
          {
          //As mentioned earlier, a negative value for versionNumber means that
          //this trigger action references columns through old/new transient variables.
          //Read information about those columns into referencedColumnsInTriggerAction
          int rctaLength = in.readInt();

          referencedColumnsInTriggerAction = new int[rctaLength];
          for (int i = 0; i < rctaLength; i++)

          { referencedColumnsInTriggerAction[i] = in.readInt(); }

          }
          }

          /**

          • For triggers, 3 possible scenarios
          • 1)referencedColumns is not null but referencedColumnsInTriggerAction
          • is null - then following gets written
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • eg create trigger tr1 after update of c1 on t1 for each row values(1);
          • 2)referencedColumns is null but referencedColumnsInTriggerAction is not
          • null - then following gets written
          • -1
          • -1
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update on t1 referencing old as oldt
          • for each row values(oldt.id);
          • 3)referencedColumns and referencedColumnsInTriggerAction are not null -
          • then following gets written
          • -1
          • referencedColumns.length
          • individual elements from referencedColumns arrary
          • referencedColumnsInTriggerAction.length
          • individual elements from referencedColumnsInTriggerAction arrary
          • eg create trigger tr1 after update of c1 on t1 referencing old as oldt
          • for each row values(oldt.id);
            */
            public void writeExternal(ObjectOutput out) throws IOException
            {
            //A null value for referencedColumnsInTriggerAction means one of 2 cases
            //1)We are working in soft-upgrade mode dealing with databases lower than 10.6
            // Prior to 10.6 release, we did not keep track of trigger action columns
            //2)We are working with >10.5 release database and the trigger action does not
            // reference any column through old/new transient variables

          //versionNumber will be -1 if referencedColumnsInTriggerAction is not null,
          //meaning, we are dealing with >10.5 release database and the trigger has referenced
          //columns in trigger action through old/new transient variables.
          //Otherwise, versionNumber will be the length of the arrary referencedColumns. This
          //arrary holds the columns on which trigger is defined. The detailed meaning of
          //these 2 arrays is described at the class level comments(towards the beginning of
          //this class.
          int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1;

          out.writeInt( versionNumber );

          if ( versionNumber < 0 ) {
          //If we are here, then it means that trigger action references
          //columns through old/new transient variables.
          //First we will check if there are any trigger columns selected
          //for this trigger. If yes, we will write information about
          //trigger columns and if not, then we will write -1 to indicate
          //that there are no trigger columns selected.
          //After that, we will write info about trigger action columns.
          if ( referencedColumns != null )

          { writeReferencedColumns(out); }

          else
          out.writeInt(versionNumber);
          //Write info about trigger action columns referenced through
          //old/new transient variables
          out.writeInt(referencedColumnsInTriggerAction.length);
          for (int i = 0; i < referencedColumnsInTriggerAction.length; i++)

          { out.writeInt(referencedColumnsInTriggerAction[i]); }

          } else

          { //If we are here, then it means there are no references in //trigger action to old/new transient variables. But, three are //trigger columns selected for this trigger. Write info about //trigger columns writeReferencedColumns(out); }


          }

          private void writeReferencedColumns(ObjectOutput out) throws IOException
          {
          //trigger is defined on select columns. Write info about trigger columns
          out.writeInt( referencedColumns.length );
          for (int i = 0; i < referencedColumns.length; i++)

          { out.writeInt(referencedColumns[i]); }

          }

          Show
          Mamta A. Satoor added a comment - Based on the fact that with the patch, it is possible to have referencedColumns null, I think readExternal and writeExternal should look like following (this should make sure that we read the pre-10.6 dbs in soft upgrade mode correctly and write the new descriptors correctly provided that as suggested by Rick, in CreateTriggerNode, we use the new code only if the db is at >10.5 level) I will add following class comment to ReferencedColumnsDescriptorImpl /** For triggers, ReferencedColumnsDescriptorImpl object has 3 possibilites 1)referencedColumns is not null but referencedColumnsInTriggerAction is null - meaning the trigger is defined on specific columns but trigger action does not reference any column through old/new transient variables. Another reason for referencedColumnsInTriggerAction to be null(even though trigger action does reference columns though old/new transient variables would be that we are in soft-upgrade mode for pre-10.6 databases and hence we do not want to write anything about referencedColumnsInTriggerAction for backward compatibility ( DERBY-1482 ). eg create trigger tr1 after update of c1 on t1 for each row values(1); 2)referencedColumns is null but referencedColumnsInTriggerAction is not null - meaning the trigger is not defined on specific columns but trigger action references column through old/new transient variables eg create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); 3)referencedColumns and referencedColumnsInTriggerAction are not null - meaning the trigger is defined on specific columns and trigger action references column through old/new transient variables eg create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); */ /** For triggers, 3 possible scenarios 1)referencedColumns is not null but referencedColumnsInTriggerAction is null - then following will get read referencedColumns.length individual elements from referencedColumns arrary eg create trigger tr1 after update of c1 on t1 for each row values(1); 2)referencedColumns is null but referencedColumnsInTriggerAction is not null - then following will get read -1 -1 referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); 3)referencedColumns and referencedColumnsInTriggerAction are not null - then following will get read -1 referencedColumns.length individual elements from referencedColumns arrary referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); */ public void readExternal(ObjectInput in) throws IOException { int rcLength; int versionNumber = in.readInt(); if ( versionNumber < 0 ) { //A negative value for versionNumber means that this trigger action //references columns through old/new transient variables. Now, check //if trigger has been defined on selected columns rcLength = in.readInt(); if ( rcLength < 0 ) { //trigger is not defined on selected columns rcLength = 0; } else referencedColumns = new int [rcLength] ; } else { //this trigger only has trigger columns saved on the disc rcLength = versionNumber; referencedColumns = new int[rcLength]; } for (int i = 0; i < rcLength; i++) { //if we are in this loop, then it means that this trigger has been //defined on specific columns. Read in information about those columns referencedColumns[i] = in.readInt(); } if ( versionNumber < 0 ) { //As mentioned earlier, a negative value for versionNumber means that //this trigger action references columns through old/new transient variables. //Read information about those columns into referencedColumnsInTriggerAction int rctaLength = in.readInt(); referencedColumnsInTriggerAction = new int [rctaLength] ; for (int i = 0; i < rctaLength; i++) { referencedColumnsInTriggerAction[i] = in.readInt(); } } } /** For triggers, 3 possible scenarios 1)referencedColumns is not null but referencedColumnsInTriggerAction is null - then following gets written referencedColumns.length individual elements from referencedColumns arrary eg create trigger tr1 after update of c1 on t1 for each row values(1); 2)referencedColumns is null but referencedColumnsInTriggerAction is not null - then following gets written -1 -1 referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); 3)referencedColumns and referencedColumnsInTriggerAction are not null - then following gets written -1 referencedColumns.length individual elements from referencedColumns arrary referencedColumnsInTriggerAction.length individual elements from referencedColumnsInTriggerAction arrary eg create trigger tr1 after update of c1 on t1 referencing old as oldt for each row values(oldt.id); */ public void writeExternal(ObjectOutput out) throws IOException { //A null value for referencedColumnsInTriggerAction means one of 2 cases //1)We are working in soft-upgrade mode dealing with databases lower than 10.6 // Prior to 10.6 release, we did not keep track of trigger action columns //2)We are working with >10.5 release database and the trigger action does not // reference any column through old/new transient variables //versionNumber will be -1 if referencedColumnsInTriggerAction is not null, //meaning, we are dealing with >10.5 release database and the trigger has referenced //columns in trigger action through old/new transient variables. //Otherwise, versionNumber will be the length of the arrary referencedColumns. This //arrary holds the columns on which trigger is defined. The detailed meaning of //these 2 arrays is described at the class level comments(towards the beginning of //this class. int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1; out.writeInt( versionNumber ); if ( versionNumber < 0 ) { //If we are here, then it means that trigger action references //columns through old/new transient variables. //First we will check if there are any trigger columns selected //for this trigger. If yes, we will write information about //trigger columns and if not, then we will write -1 to indicate //that there are no trigger columns selected. //After that, we will write info about trigger action columns. if ( referencedColumns != null ) { writeReferencedColumns(out); } else out.writeInt(versionNumber); //Write info about trigger action columns referenced through //old/new transient variables out.writeInt(referencedColumnsInTriggerAction.length); for (int i = 0; i < referencedColumnsInTriggerAction.length; i++) { out.writeInt(referencedColumnsInTriggerAction[i]); } } else { //If we are here, then it means there are no references in //trigger action to old/new transient variables. But, three are //trigger columns selected for this trigger. Write info about //trigger columns writeReferencedColumns(out); } } private void writeReferencedColumns(ObjectOutput out) throws IOException { //trigger is defined on select columns. Write info about trigger columns out.writeInt( referencedColumns.length ); for (int i = 0; i < referencedColumns.length; i++) { out.writeInt(referencedColumns[i]); } }
          Hide
          Mamta A. Satoor added a comment -

          I will post by tomorrow what I think the changed readExternal and writeExternal should look like(taking into account the fact that referencedColumns can be null and the fact that we could be dealing with pre-10.6 database as part of soft-upgrade). Rick and others, I would greatly appreciate if you will take a look at that code once I have it up in the jira.

          Show
          Mamta A. Satoor added a comment - I will post by tomorrow what I think the changed readExternal and writeExternal should look like(taking into account the fact that referencedColumns can be null and the fact that we could be dealing with pre-10.6 database as part of soft-upgrade). Rick and others, I would greatly appreciate if you will take a look at that code once I have it up in the jira.
          Hide
          Mamta A. Satoor added a comment -

          Rick, thanks for reviewing the patch and for your comments. One thing may not have been clear in my patch comments is that now, with my changes in the patch, it is possible to have referencedColumns as null but referencedColumnsInTriggerAction as not null. eg
          create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id);
          For the trigger above, the user has not provided a list of columns whose update should fire trigger tr1. Because of that, any column update from table t1 will cause trigger tr1 to fire. But the columns referenced in trigger action through old/new transiten variables is column "id". in other words, for this trigger, referencedColumns will be null but referencedColumnsInTriggerAction will not be null. The changes to readExternal and writeExternal will have to account for possibility of referencedColumns being null. Prior to my changes in the patch, if referencedColumns was null for a trigger, the entire object ReferencedColumnDescriptorImpl will be null and hence we will never get to these methods. The null case was handled by FormatIdOutputStream.writeObject(Object) with the following code
          public void writeObject(Object ref) throws IOException
          {
          if (ref == null)

          { FormatIdUtil.writeFormatIdInteger(this, StoredFormatIds.NULL_FORMAT_ID); return; }

          .........

          The stack trace of such a call is as follows.
          Thread [main] (Suspended (breakpoint at line 95 in FormatIdOutputStream))
          FormatIdOutputStream.writeObject(Object) line: 95
          UserType.writeExternal(ObjectOutput) line: 291
          StoredPage.logColumn(Object[], int, DynamicByteArrayOutputStream, int, int, int) line: 6243
          StoredPage.logRow(int, boolean, int, Object[], FormatableBitSet, DynamicByteArrayOutputStream, int, byte, int, int, int) line: 3963
          InsertOperation.writeOptionalDataToBuffer(RawTransaction, DynamicByteArrayOutputStream, Object[], FormatableBitSet, boolean, int, int, int) line: 370
          InsertOperation.<init>(RawTransaction, BasePage, int, int, Object[], FormatableBitSet, LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 115
          LoggableActions.actionInsert(RawTransaction, BasePage, int, int, Object[], FormatableBitSet, LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 139
          StoredPage(BasePage).insertNoOverflow(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 602
          StoredPage(BasePage).insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 523
          StoredPage.insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 6723
          StoredPage(BasePage).insert(Object[], FormatableBitSet, byte, int) line: 629
          HeapController.doInsert(DataValueDescriptor[]) line: 288
          HeapController.insertAndFetchLocation(DataValueDescriptor[], RowLocation) line: 599
          TabInfoImpl.insertRowListImpl(ExecRow[], TransactionController, RowLocation[]) line: 511
          TabInfoImpl.insertRow(ExecRow, TransactionController) line: 420
          DataDictionaryImpl.addDescriptor(TupleDescriptor, TupleDescriptor, int, boolean, TransactionController) line: 1824
          CreateTriggerConstantAction.executeConstantAction(Activation) line: 321
          MiscResultSet.open() line: 61
          GenericPreparedStatement.executeStmt(Activation, boolean, long) line: 436
          GenericPreparedStatement.execute(Activation, long) line: 317
          EmbedStatement.executeStatement(Activation, boolean, boolean) line: 1232
          EmbedStatement.execute(String, boolean, boolean, int, int[], String[]) line: 625
          EmbedStatement.execute(String) line: 555
          ij.executeImmediate(String) line: 367
          utilMain.doCatch(String) line: 521
          utilMain.runScriptGuts() line: 363
          utilMain.go(LocalizedInput[], LocalizedOutput) line: 261
          Main.go(LocalizedInput, LocalizedOutput) line: 229
          Main.mainCore(String[], Main) line: 184
          Main.main(String[]) line: 75
          ij.main(String[]) line: 59

          Show
          Mamta A. Satoor added a comment - Rick, thanks for reviewing the patch and for your comments. One thing may not have been clear in my patch comments is that now, with my changes in the patch, it is possible to have referencedColumns as null but referencedColumnsInTriggerAction as not null. eg create trigger tr1 after update on t1 referencing old as oldt for each row values(oldt.id); For the trigger above, the user has not provided a list of columns whose update should fire trigger tr1. Because of that, any column update from table t1 will cause trigger tr1 to fire. But the columns referenced in trigger action through old/new transiten variables is column "id". in other words, for this trigger, referencedColumns will be null but referencedColumnsInTriggerAction will not be null. The changes to readExternal and writeExternal will have to account for possibility of referencedColumns being null. Prior to my changes in the patch, if referencedColumns was null for a trigger, the entire object ReferencedColumnDescriptorImpl will be null and hence we will never get to these methods. The null case was handled by FormatIdOutputStream.writeObject(Object) with the following code public void writeObject(Object ref) throws IOException { if (ref == null) { FormatIdUtil.writeFormatIdInteger(this, StoredFormatIds.NULL_FORMAT_ID); return; } ......... The stack trace of such a call is as follows. Thread [main] (Suspended (breakpoint at line 95 in FormatIdOutputStream)) FormatIdOutputStream.writeObject(Object) line: 95 UserType.writeExternal(ObjectOutput) line: 291 StoredPage.logColumn(Object[], int, DynamicByteArrayOutputStream, int, int, int) line: 6243 StoredPage.logRow(int, boolean, int, Object[], FormatableBitSet, DynamicByteArrayOutputStream, int, byte, int, int, int) line: 3963 InsertOperation.writeOptionalDataToBuffer(RawTransaction, DynamicByteArrayOutputStream, Object[], FormatableBitSet, boolean, int, int, int) line: 370 InsertOperation.<init>(RawTransaction, BasePage, int, int, Object[], FormatableBitSet, LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 115 LoggableActions.actionInsert(RawTransaction, BasePage, int, int, Object[], FormatableBitSet, LogicalUndo, byte, int, boolean, int, DynamicByteArrayOutputStream, int, int) line: 139 StoredPage(BasePage).insertNoOverflow(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 602 StoredPage(BasePage).insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 523 StoredPage.insertAtSlot(int, Object[], FormatableBitSet, LogicalUndo, byte, int) line: 6723 StoredPage(BasePage).insert(Object[], FormatableBitSet, byte, int) line: 629 HeapController.doInsert(DataValueDescriptor[]) line: 288 HeapController.insertAndFetchLocation(DataValueDescriptor[], RowLocation) line: 599 TabInfoImpl.insertRowListImpl(ExecRow[], TransactionController, RowLocation[]) line: 511 TabInfoImpl.insertRow(ExecRow, TransactionController) line: 420 DataDictionaryImpl.addDescriptor(TupleDescriptor, TupleDescriptor, int, boolean, TransactionController) line: 1824 CreateTriggerConstantAction.executeConstantAction(Activation) line: 321 MiscResultSet.open() line: 61 GenericPreparedStatement.executeStmt(Activation, boolean, long) line: 436 GenericPreparedStatement.execute(Activation, long) line: 317 EmbedStatement.executeStatement(Activation, boolean, boolean) line: 1232 EmbedStatement.execute(String, boolean, boolean, int, int[], String[]) line: 625 EmbedStatement.execute(String) line: 555 ij.executeImmediate(String) line: 367 utilMain.doCatch(String) line: 521 utilMain.runScriptGuts() line: 363 utilMain.go(LocalizedInput[], LocalizedOutput) line: 261 Main.go(LocalizedInput, LocalizedOutput) line: 229 Main.mainCore(String[], Main) line: 184 Main.main(String[]) line: 75 ij.main(String[]) line: 59
          Hide
          Rick Hillegas added a comment -

          Thanks for the patch, Mamta. I have a couple comments:

          CreateTriggerNode:

          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.

          o There are two places in bindReferencesClause() where -1s are squeezed out of arrays. This is done for triggerColsAndTriggerActionCols and triggerActionColsOnly. It seems to me that there is an opportunity here to factor this squeezing code into a helper method. That will make the code a little easier to read and maintain.

          o I believe that the new code (computing the columns referenced in trigger actions and optimizing the generated text) should only be performed if the database is at level 10.6 or above. I am worried that the trigger will fail if the user soft-downgrades back to 10.5. If the database is at level 10.5 or lower, then the old code should be used.

          TriggerDescriptor

          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?

          ReferencedColumnDescriptorImpl

          o I think that readExternal() won't be able to read descriptors from soft-upgraded 10.5 databases. And writeExternal() will write descriptors which won't be readable if the user soft-downgrades to 10.5. I think that the following alternative implementations will work.

          public void readExternal(ObjectInput in) throws IOException
          {
          int rcLength;
          int versionNumber = in.readInt();

          if ( versionNumber < 0 )

          { rcLength = in.readInt(); }

          else

          { rcLength = versionNumber; }

          referencedColumns = new int[rcLength];
          for (int i = 0; i < rcLength; i++)

          { referencedColumns[i] = in.readInt(); }

          if ( versionNumber < 0 )
          {
          int rctaLength = in.readInt();

          referencedColumnsInTriggerAction = new int[rctaLength];
          for (int i = 0; i < rctaLength; i++)

          { referencedColumnsInTriggerAction[i] = in.readInt(); }

          }
          }

          public void writeExternal(ObjectOutput out) throws IOException
          {
          int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1;

          out.writeInt( versionNumber );

          if ( versionNumber < 0 )

          { out.writeInt( referencedColumns.length ); }

          for (int i = 0; i < referencedColumns.length; i++)

          { out.writeInt(referencedColumns[i]); }

          if ( versionNumber < 0 )
          {
          out.writeInt(referencedColumnsInTriggerAction.length);
          for (int i = 0; i < referencedColumnsInTriggerAction.length; i++)

          { out.writeInt(referencedColumnsInTriggerAction[i]); }

          }

          }

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the patch, Mamta. I have a couple comments: CreateTriggerNode: 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. o There are two places in bindReferencesClause() where -1s are squeezed out of arrays. This is done for triggerColsAndTriggerActionCols and triggerActionColsOnly. It seems to me that there is an opportunity here to factor this squeezing code into a helper method. That will make the code a little easier to read and maintain. o I believe that the new code (computing the columns referenced in trigger actions and optimizing the generated text) should only be performed if the database is at level 10.6 or above. I am worried that the trigger will fail if the user soft-downgrades back to 10.5. If the database is at level 10.5 or lower, then the old code should be used. TriggerDescriptor 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? ReferencedColumnDescriptorImpl o I think that readExternal() won't be able to read descriptors from soft-upgraded 10.5 databases. And writeExternal() will write descriptors which won't be readable if the user soft-downgrades to 10.5. I think that the following alternative implementations will work. public void readExternal(ObjectInput in) throws IOException { int rcLength; int versionNumber = in.readInt(); if ( versionNumber < 0 ) { rcLength = in.readInt(); } else { rcLength = versionNumber; } referencedColumns = new int [rcLength] ; for (int i = 0; i < rcLength; i++) { referencedColumns[i] = in.readInt(); } if ( versionNumber < 0 ) { int rctaLength = in.readInt(); referencedColumnsInTriggerAction = new int [rctaLength] ; for (int i = 0; i < rctaLength; i++) { referencedColumnsInTriggerAction[i] = in.readInt(); } } } public void writeExternal(ObjectOutput out) throws IOException { int versionNumber = referencedColumnsInTriggerAction == null ? referencedColumns.length : -1; out.writeInt( versionNumber ); if ( versionNumber < 0 ) { out.writeInt( referencedColumns.length ); } for (int i = 0; i < referencedColumns.length; i++) { out.writeInt(referencedColumns[i]); } if ( versionNumber < 0 ) { out.writeInt(referencedColumnsInTriggerAction.length); for (int i = 0; i < referencedColumnsInTriggerAction.length; i++) { out.writeInt(referencedColumnsInTriggerAction[i]); } } } Thanks, -Rick
          Hide
          Mamta A. Satoor added a comment -

          Attaching patch, derby1482_patch2_diff.txt, which is very similar to the 1st patch except that rather than adding a new column to SYSTRIGGERS to save the trigger action columns information, it expands the functionality of ReferenceColumn implementation to include trigger action columns. This will hopefully make it easier upgrade path. The only surprise I ran into so far is that ReferenceColumn gets used by 2 more system tables namely SYSCHECKS
          and SYSCONSTRAINTS which means we need to make sure that upgrade code works for those system tables too. Next, I will start looking at the upgrade code. I think, the upgrade work that needs to go in for DERBY-1482 will be similar to what was done for DERBY-606
          (committed with revision number 484797). I will study that patch more to see how it's logic can be used for the changes to SYSTRIGGERS.REFERENCEDCOLUMNS needed for this jira.

          Show
          Mamta A. Satoor added a comment - Attaching patch, derby1482_patch2_diff.txt, which is very similar to the 1st patch except that rather than adding a new column to SYSTRIGGERS to save the trigger action columns information, it expands the functionality of ReferenceColumn implementation to include trigger action columns. This will hopefully make it easier upgrade path. The only surprise I ran into so far is that ReferenceColumn gets used by 2 more system tables namely SYSCHECKS and SYSCONSTRAINTS which means we need to make sure that upgrade code works for those system tables too. Next, I will start looking at the upgrade code. I think, the upgrade work that needs to go in for DERBY-1482 will be similar to what was done for DERBY-606 (committed with revision number 484797). I will study that patch more to see how it's logic can be used for the changes to SYSTRIGGERS.REFERENCEDCOLUMNS needed for this jira.
          Hide
          Mamta A. Satoor added a comment -

          I am attaching a patch, derby1482_patch1_diff.txt, which is NOT READY FOR commit yet because the required upgrade work for system table change has not been done yet. If this patch is committed in it's current state, the older releases databases will fail with the trunk even with hard upgrade. I plan to work on the upgrade work soon.

          I ran derbyall and junit All suite with my patch. During the junite All suite run, I am getting lots of errors in the upgrade suite. The failures are attached to this jira as junitUpgradeTestFailureWithPatch1.out I think this probably is because of the new column that I have added to SYSTRIGGERS. I have not investigated this failure enough to know for sure but I will be tackling the upgrade changes soon.

          Next, I am planning on focusing on following tasks
          1)Implement Rick's suggestion of including the extra information we need about trigger in existing column SYSTRIGGERS.REFERENCEDCOLUMNS This columns is of type ReferencedColumnsDescriptorImpl and I can hopefully enhance that type to include the additional information. Hopefully this approach will make it the next task easier which is to make upgrade changes.
          2)Work on the upgrade code so this change in system table does not break older release databases when they are run in soft/hard upgrade mode with the suggested work done through this jira.
          3)Add more test cases in addition to what was added through TriggerTests.java

          One thing to keep in mind is the improvement proposed by this patch will work only for UPDATE triggers defined at row level AND they identify specific columns on which the trigger is defined. eg
          CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1
          REFERENCING OLD AS oldt NEW AS newt
          FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
          The trigger above is a row level UPDATE trigger and it identifies column "c12" as the triggering column. For all other kinds of triggers(INSERT, DELETE, UPDATE trigger defined at table leve, UPDATE trigger defined at row level but does not identify trigger columns), the improvement proposed by this patch will not kick in. That is because all the other kinds of triggers do not identify specific trigger columns. In the absence of specific trigger columns, Derby decides to fetch in all the columns from the triggering table since it doesn't know what column it might need to fetch at trigger execution time.

          Following is the description of the existing behavior and changes made through my patch. It is rather long but I wanted to provide as much information as I can since some part of the code is little tricky.

          I am going to work with following example to show what is being done by Derby today and what changes I am suggesting.
          CREATE TABLE table1 (c11 int, c12 int, c13 int, c14 int, c15 int);
          INSERT INTO table1 VALUES(1,2,3,4,5);
          CREATE TABLE table2 (c21 int, c22 int, c23 int, c24 int, c25 int);
          INSERT INTO table2 VALUES(2,2,3,-1,5);
          CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1
          REFERENCING OLD AS oldt NEW AS newt
          FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
          In the update trigger tr1 above, the trigger column is c12 and trigger action references old/new transition variable oldt.c14 Note that the trigger table, table1, has 5 columns and of those only 2 columns are really needed by the trigger tr1. The trunk code converts the trigger action UPDATE table2 SET c24=oldt.c14 to UPDATE table2 SET c24=org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getInt(4)
          Note that oldt.c14 gets referenced through it's column position in triggering table table1.
          Now let's consider the triggering statement
          UPDATE table1 SET c12 = -9 WHERE c11=1;
          The trunk code is written to fetch all the 5 columns in trigger table table1 even though the trigger is only in need of columns c12 and c14. If in our example, if table1 had a LOB column with large amount of data, we would fetch that LOB column in memory too even though trigger does not need that column.

          With my patch, I am keeping track of references to old/new transition variables in trigger action. Trunk code already keeps track of trigger columns in SYSTRIGGERS.REFERENCEDCOLUMNS. The combination of the two will tell us exactly what columns are needed for trigger execution from the triggering table and we fetch only those columns. For the trigger tr1, that would mean column c12(which is the trigger column) and column c14(which is in the trigger action). Because of this, columns c11, c13 and c15 will not be read into memory. If one or more of those columns were LOB columns, that can result in lot of performance and memory consumption improvement. My code will convert the trigger action UPDATE table2 SET c24=oldt.c14
          to
          UPDATE table2 SET c24=org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getInt(2)
          Note that oldt.c14 get referenced through it's column position is what columns are fetched and not by it's column position in the trigger table table1. In our specific eg, we are fetching column c12 and c14 and column c14 is in the 2nd position in that set. But when we make a note of the trigger action columns in SYSTRIGGERS, we want to record it by it's column position in the trigger table to know exactly what columns we are talking about. So, for our trigger eg below, SYSTRIGGERS will look as follows
          CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1
          REFERENCING OLD AS oldt NEW AS newt
          FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
          select TRIGGERNAME, EVENT, TYPE, REFERENCEDCOLUMNS, REFCOLSTRIGGERACTION, TRIGGERDEFINITION from sys.systriggers;
          TRIGGERNAME|EVENT|TYPE|REFERENCEDCOLUMNS|REFCOLSTRIGGERACTION|TRIGGERDEFINITION
          --------------------------------------------------------------------------------------------
          TR1 |U |R |(2) |(4) |update table2 set c24=oldt.c14
          So, the column numbers to recognize the trigger columns and trigger action columns, we use their position in triggering table. But to convert the trigger action to org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getxxxRow().getXXX, for ?, we
          need to use the relative column position in the columns actually fetched from triggering table. My apologies if this sounds confusing. In one of intermediate work on my machine, my code didn't account for this difference in column position in table vs column position in the columns
          actually fetched, and I started getting NPE because there is no column at position 4 since we are in a need to fetch only 2 columns. This 2 different column numbering scheme makes the code little tricky to follow but if we one understands the eg above, I think then it becomes easier to understand the code. I will be happy to hear if someone has ideas on may be code this differently.

          Show
          Mamta A. Satoor added a comment - I am attaching a patch, derby1482_patch1_diff.txt, which is NOT READY FOR commit yet because the required upgrade work for system table change has not been done yet. If this patch is committed in it's current state, the older releases databases will fail with the trunk even with hard upgrade. I plan to work on the upgrade work soon. I ran derbyall and junit All suite with my patch. During the junite All suite run, I am getting lots of errors in the upgrade suite. The failures are attached to this jira as junitUpgradeTestFailureWithPatch1.out I think this probably is because of the new column that I have added to SYSTRIGGERS. I have not investigated this failure enough to know for sure but I will be tackling the upgrade changes soon. Next, I am planning on focusing on following tasks 1)Implement Rick's suggestion of including the extra information we need about trigger in existing column SYSTRIGGERS.REFERENCEDCOLUMNS This columns is of type ReferencedColumnsDescriptorImpl and I can hopefully enhance that type to include the additional information. Hopefully this approach will make it the next task easier which is to make upgrade changes. 2)Work on the upgrade code so this change in system table does not break older release databases when they are run in soft/hard upgrade mode with the suggested work done through this jira. 3)Add more test cases in addition to what was added through TriggerTests.java One thing to keep in mind is the improvement proposed by this patch will work only for UPDATE triggers defined at row level AND they identify specific columns on which the trigger is defined. eg CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 REFERENCING OLD AS oldt NEW AS newt FOR EACH ROW UPDATE table2 SET c24=oldt.c14; The trigger above is a row level UPDATE trigger and it identifies column "c12" as the triggering column. For all other kinds of triggers(INSERT, DELETE, UPDATE trigger defined at table leve, UPDATE trigger defined at row level but does not identify trigger columns), the improvement proposed by this patch will not kick in. That is because all the other kinds of triggers do not identify specific trigger columns. In the absence of specific trigger columns, Derby decides to fetch in all the columns from the triggering table since it doesn't know what column it might need to fetch at trigger execution time. Following is the description of the existing behavior and changes made through my patch. It is rather long but I wanted to provide as much information as I can since some part of the code is little tricky. I am going to work with following example to show what is being done by Derby today and what changes I am suggesting. CREATE TABLE table1 (c11 int, c12 int, c13 int, c14 int, c15 int); INSERT INTO table1 VALUES(1,2,3,4,5); CREATE TABLE table2 (c21 int, c22 int, c23 int, c24 int, c25 int); INSERT INTO table2 VALUES(2,2,3,-1,5); CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 REFERENCING OLD AS oldt NEW AS newt FOR EACH ROW UPDATE table2 SET c24=oldt.c14; In the update trigger tr1 above, the trigger column is c12 and trigger action references old/new transition variable oldt.c14 Note that the trigger table, table1, has 5 columns and of those only 2 columns are really needed by the trigger tr1. The trunk code converts the trigger action UPDATE table2 SET c24=oldt.c14 to UPDATE table2 SET c24=org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getInt(4) Note that oldt.c14 gets referenced through it's column position in triggering table table1. Now let's consider the triggering statement UPDATE table1 SET c12 = -9 WHERE c11=1; The trunk code is written to fetch all the 5 columns in trigger table table1 even though the trigger is only in need of columns c12 and c14. If in our example, if table1 had a LOB column with large amount of data, we would fetch that LOB column in memory too even though trigger does not need that column. With my patch, I am keeping track of references to old/new transition variables in trigger action. Trunk code already keeps track of trigger columns in SYSTRIGGERS.REFERENCEDCOLUMNS. The combination of the two will tell us exactly what columns are needed for trigger execution from the triggering table and we fetch only those columns. For the trigger tr1, that would mean column c12(which is the trigger column) and column c14(which is in the trigger action). Because of this, columns c11, c13 and c15 will not be read into memory. If one or more of those columns were LOB columns, that can result in lot of performance and memory consumption improvement. My code will convert the trigger action UPDATE table2 SET c24=oldt.c14 to UPDATE table2 SET c24=org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getInt(2) Note that oldt.c14 get referenced through it's column position is what columns are fetched and not by it's column position in the trigger table table1. In our specific eg, we are fetching column c12 and c14 and column c14 is in the 2nd position in that set. But when we make a note of the trigger action columns in SYSTRIGGERS, we want to record it by it's column position in the trigger table to know exactly what columns we are talking about. So, for our trigger eg below, SYSTRIGGERS will look as follows CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 REFERENCING OLD AS oldt NEW AS newt FOR EACH ROW UPDATE table2 SET c24=oldt.c14; select TRIGGERNAME, EVENT, TYPE, REFERENCEDCOLUMNS, REFCOLSTRIGGERACTION, TRIGGERDEFINITION from sys.systriggers; TRIGGERNAME|EVENT|TYPE|REFERENCEDCOLUMNS|REFCOLSTRIGGERACTION|TRIGGERDEFINITION -------------------------------------------------------------------------------------------- TR1 |U |R |(2) |(4) |update table2 set c24=oldt.c14 So, the column numbers to recognize the trigger columns and trigger action columns, we use their position in triggering table. But to convert the trigger action to org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getxxxRow().getXXX , for ?, we need to use the relative column position in the columns actually fetched from triggering table. My apologies if this sounds confusing. In one of intermediate work on my machine, my code didn't account for this difference in column position in table vs column position in the columns actually fetched, and I started getting NPE because there is no column at position 4 since we are in a need to fetch only 2 columns. This 2 different column numbering scheme makes the code little tricky to follow but if we one understands the eg above, I think then it becomes easier to understand the code. I will be happy to hear if someone has ideas on may be code this differently.
          Hide
          Mamta A. Satoor added a comment -

          Just wanted to share that I am working on collecting the column referenced by the trigger action (when it is not a stored procedure call. I hope to tackle that as a seperate step) During the create trigger bind time, we go through the columns referenced by the trigger action and isolate the ones which come from the new/old tables through the REFERENCING clause. The information about those columns will be saved in a new column in SYSTRIGGERS. At triggering statement execution time, we will go through SYSTRIGGERS's new column and determine which columns should be read in rather than all the columns. This should help with OOM issues involving LOB columns.

          Will need to work with upgrade code to support the new column in SYSTRIGGERS. At this point, I am focusing on non-upgarde code changes.

          I have finished the rough code changes on collecting the columns referenced in trigger action when trigger action is not a stored procedure call. The information collected would be something like [1,3] which means column numbers 1 and 3 are accessed by the trigger action. But the tricky party is to transform column 3 from triggering table to column 2 in the columns that will be read into memory. eg
          create table t1 (c11 int, c12 int, c13 int)
          insert into t1 values (11,12, 13)
          and say there is a trigger defined on t1 which references column numbers 1 and 3 meaning columns c11 and c13. Once I finish coding the logic completely, we will read only values 11 and 13 since c12 is not referenced by the trigger action. But the way code works right now, we try to look at the columns through the column number meaning for our eg, c11 will be looked through position 1 which is good. But c13 will be looked through position 3 but really, c13 is in position 2 of the subset of the original row from t1.

          My apologies if above eg is little confusing. I will try to make changes so that we look for c13 in position 2 of the subset row rather than position 3.

          Any feedback appreciated.

          Show
          Mamta A. Satoor added a comment - Just wanted to share that I am working on collecting the column referenced by the trigger action (when it is not a stored procedure call. I hope to tackle that as a seperate step) During the create trigger bind time, we go through the columns referenced by the trigger action and isolate the ones which come from the new/old tables through the REFERENCING clause. The information about those columns will be saved in a new column in SYSTRIGGERS. At triggering statement execution time, we will go through SYSTRIGGERS's new column and determine which columns should be read in rather than all the columns. This should help with OOM issues involving LOB columns. Will need to work with upgrade code to support the new column in SYSTRIGGERS. At this point, I am focusing on non-upgarde code changes. I have finished the rough code changes on collecting the columns referenced in trigger action when trigger action is not a stored procedure call. The information collected would be something like [1,3] which means column numbers 1 and 3 are accessed by the trigger action. But the tricky party is to transform column 3 from triggering table to column 2 in the columns that will be read into memory. eg create table t1 (c11 int, c12 int, c13 int) insert into t1 values (11,12, 13) and say there is a trigger defined on t1 which references column numbers 1 and 3 meaning columns c11 and c13. Once I finish coding the logic completely, we will read only values 11 and 13 since c12 is not referenced by the trigger action. But the way code works right now, we try to look at the columns through the column number meaning for our eg, c11 will be looked through position 1 which is good. But c13 will be looked through position 3 but really, c13 is in position 2 of the subset of the original row from t1. My apologies if above eg is little confusing. I will try to make changes so that we look for c13 in position 2 of the subset row rather than position 3. Any feedback appreciated.
          Hide
          Mike Matrigali added a comment -

          I think reading it again from the store is a much better approach. Best if the mechanism can be somehow hidden withing the stream that the is passed from the
          store up to the user.

          This may or may not solve the various trigger issues, as the trigger needs a specific
          version of the row which might change between when the stream is returned and when it is asked for. For instance an update trigger must provide the before and after version of the value, but the trigger action itself may change the row. This may not be an issue if the getting it "from the store" in this case is actually getting it from a deffered update temporary table, then there would be no worry about the trigger action changing it.

          I think this is easier than the log approach, but not a simple change. I was thinking of taking a look at this but would rather let you get all your stream changes in to see what problems remain.

          Show
          Mike Matrigali added a comment - I think reading it again from the store is a much better approach. Best if the mechanism can be somehow hidden withing the stream that the is passed from the store up to the user. This may or may not solve the various trigger issues, as the trigger needs a specific version of the row which might change between when the stream is returned and when it is asked for. For instance an update trigger must provide the before and after version of the value, but the trigger action itself may change the row. This may not be an issue if the getting it "from the store" in this case is actually getting it from a deffered update temporary table, then there would be no worry about the trigger action changing it. I think this is easier than the log approach, but not a simple change. I was thinking of taking a look at this but would rather let you get all your stream changes in to see what problems remain.
          Hide
          Kristian Waagan added a comment -

          Mike wrote:


          Kristian> The better option is probably to just write the value to the log and then read it back from the log as required.
          Kristian> Do you have any idea about how much effort that would take?
          Kristian> I'm not suggesting that it should be done now, but it would be nice to have an idea about how difficult it is.

          That sounds "hard" to me. I would definitely not want to see the sql layer
          accessing the log. The clean separation of sql layer from the store layer
          has greatly helped the reliablity of the product.


          Do you feel the same about reading the value back from the table itself (instead of the log)?
          I've seen this approach suggested in another Jira. If feasible, is this also hard to do?

          Show
          Kristian Waagan added a comment - Mike wrote: Kristian> The better option is probably to just write the value to the log and then read it back from the log as required. Kristian> Do you have any idea about how much effort that would take? Kristian> I'm not suggesting that it should be done now, but it would be nice to have an idea about how difficult it is. That sounds "hard" to me. I would definitely not want to see the sql layer accessing the log. The clean separation of sql layer from the store layer has greatly helped the reliablity of the product. Do you feel the same about reading the value back from the table itself (instead of the log)? I've seen this approach suggested in another Jira. If feasible, is this also hard to do?
          Hide
          Mike Matrigali added a comment -

          > @Mike (16/Feb/10):
          > I don't have a write-up, and I'm not sure if the code will handle the scenario
          s you mention. I don't fully understand the details of the store and the trigger
          code in the case of inserts/updates of long columns.
          My comments apply on to the store side of the streaming blobs.

          I am not exactly sure where the blobs are coming from in the sql layer. I
          think there may be some defered update tables, so the question is if the
          streams are from the original table original table or a deferred update
          table.
          >
          > Can you help me understand your comment about the changing BLOB value:
          > "But the stored
          > value of the blob could be changed before we access it, so a a stream pointer
          to
          > the original blob is not going to be valid. "
          >
          > Who / what may change it;
          > - the current transaction's update query?
          > - the current transaction's trigger code?
          > - other transactions?

          I believe both the first 2 are possibilities. Locking should prevent any
          changes to the affected row by other transactions. So there are 2 cases:

          1) the current tranaction statement that causes the trigger may change the
          blob.

          insert - value in store table could be used to stream
          delete - value in store table won't be avaliable after the delete
          update - value in store table may be changed by the update itself

          2) the trigger action code might change the blob.

          This case is more obscure and needs to happen in a procedure. And the
          only way to use the old and new values is to pass them into the procedure,
          and currently our procedures don't support blobs as arguments.
          >
          > That said, I do see that we are having trouble dealing with "inbound stream
          s" when they have to be used several times - our only tool there is to materiali
          ze the stream into memory. One extension would be to write data to disk temporar
          ily to avoid OOME, but I'm not sure if we can manage this without introducing a
          performance degradation.
          > The better option is probably to just write the value to the log and then r
          ead it back from the log as required. Do you have any idea about how much effort
          that would take? I'm not suggesting that it should be done now, but it would be
          nice to have an idea about how difficult it is.
          That sounds "hard" to me. I would definitely not want to see the sql layer
          accessing the log. The clean separation of sql layer from the store layer
          has greatly helped the reliablity of the product. Also timing of it might
          be a problem as the current trigger code may need a second access to a stream
          before the actual value has been logged. Again I think the key here may be
          in understanding the deferred update tables and how they apply to all
          the matrix of before/after insert/delete/update triggers.

          You also raise an interesting point about performance. There are definitely
          a set of applications out there that are just fine with us temporarily
          instantiating copies of blobs into memory. Where our stream threshold is
          a max of 32k, 2 gig of memory is very likely available. So there should be
          some discussion before any change goes in that would automatically stream
          something to disk that currently uses in memory.

          Show
          Mike Matrigali added a comment - > @Mike (16/Feb/10): > I don't have a write-up, and I'm not sure if the code will handle the scenario s you mention. I don't fully understand the details of the store and the trigger code in the case of inserts/updates of long columns. My comments apply on to the store side of the streaming blobs. I am not exactly sure where the blobs are coming from in the sql layer. I think there may be some defered update tables, so the question is if the streams are from the original table original table or a deferred update table. > > Can you help me understand your comment about the changing BLOB value: > "But the stored > value of the blob could be changed before we access it, so a a stream pointer to > the original blob is not going to be valid. " > > Who / what may change it; > - the current transaction's update query? > - the current transaction's trigger code? > - other transactions? I believe both the first 2 are possibilities. Locking should prevent any changes to the affected row by other transactions. So there are 2 cases: 1) the current tranaction statement that causes the trigger may change the blob. insert - value in store table could be used to stream delete - value in store table won't be avaliable after the delete update - value in store table may be changed by the update itself 2) the trigger action code might change the blob. This case is more obscure and needs to happen in a procedure. And the only way to use the old and new values is to pass them into the procedure, and currently our procedures don't support blobs as arguments. > > That said, I do see that we are having trouble dealing with "inbound stream s" when they have to be used several times - our only tool there is to materiali ze the stream into memory. One extension would be to write data to disk temporar ily to avoid OOME, but I'm not sure if we can manage this without introducing a performance degradation. > The better option is probably to just write the value to the log and then r ead it back from the log as required. Do you have any idea about how much effort that would take? I'm not suggesting that it should be done now, but it would be nice to have an idea about how difficult it is. That sounds "hard" to me. I would definitely not want to see the sql layer accessing the log. The clean separation of sql layer from the store layer has greatly helped the reliablity of the product. Also timing of it might be a problem as the current trigger code may need a second access to a stream before the actual value has been logged. Again I think the key here may be in understanding the deferred update tables and how they apply to all the matrix of before/after insert/delete/update triggers. You also raise an interesting point about performance. There are definitely a set of applications out there that are just fine with us temporarily instantiating copies of blobs into memory. Where our stream threshold is a max of 32k, 2 gig of memory is very likely available. So there should be some discussion before any change goes in that would automatically stream something to disk that currently uses in memory.
          Hide
          Kristian Waagan added a comment -

          Fixed some JavaDoc warnings with revision 915310.

          Show
          Kristian Waagan added a comment - Fixed some JavaDoc warnings with revision 915310.
          Hide
          Mamta A. Satoor added a comment -

          One of the questions that has come up in this jira is are we reading all the columns when there is a trigger defined on a table with LOB column(s). I found following relevant code comment in impl.sql.compile.UpdateNode:getUpdateReadMap which confirms that we indeed are reading all the columns any time there is a trigger involved on a table involved in definitely an UPDATE DML.
          /*

            • If we have any triggers, then get all the columns
            • because we don't know what the user will ultimately
            • reference.
              */
              The job of getUpdateReadMap as the method javadoc says
          • Builds a bitmap of all columns which should be read from the
          • Store in order to satisfy an UPDATE statement.
            But as soon as we find that there are triggers involved on the table, we mark all the columns as required to be read from
            the store. This is the comment from the method's javadoc
          • 6) if there are any triggers, marks all columns in the bitmap
          Show
          Mamta A. Satoor added a comment - One of the questions that has come up in this jira is are we reading all the columns when there is a trigger defined on a table with LOB column(s). I found following relevant code comment in impl.sql.compile.UpdateNode:getUpdateReadMap which confirms that we indeed are reading all the columns any time there is a trigger involved on a table involved in definitely an UPDATE DML. /* If we have any triggers, then get all the columns because we don't know what the user will ultimately reference. */ The job of getUpdateReadMap as the method javadoc says Builds a bitmap of all columns which should be read from the Store in order to satisfy an UPDATE statement. But as soon as we find that there are triggers involved on the table, we mark all the columns as required to be read from the store. This is the comment from the method's javadoc 6) if there are any triggers, marks all columns in the bitmap
          Hide
          Mamta A. Satoor added a comment -

          Commited the test TriggerTests.java with revision 915177.

          The commit comments are as follows
          DERBY-1482

          We do not have many tests for triggers on tables with LOB columns. Checking in few tests with this checkin. The tests are disabled at this point because they run out of memory when run with limited heap because of streaming of the LOB columns whether they are needed or not. The test is part of lowmem suite which will automatically take care of running the tests with limited heap which is 16M for that suite at this point.

          More information on the test can be found in the comments in the test and also in the jira.

          Show
          Mamta A. Satoor added a comment - Commited the test TriggerTests.java with revision 915177. The commit comments are as follows DERBY-1482 We do not have many tests for triggers on tables with LOB columns. Checking in few tests with this checkin. The tests are disabled at this point because they run out of memory when run with limited heap because of streaming of the LOB columns whether they are needed or not. The test is part of lowmem suite which will automatically take care of running the tests with limited heap which is 16M for that suite at this point. More information on the test can be found in the comments in the test and also in the jira.
          Hide
          Mamta A. Satoor added a comment - - edited

          I wrote following ij script to understand what kind of information is kept in SYSTRIGGERS when REFERENCING clause is specified or missing in the CREATE TRIGGER sql. There are 2 booolean columns, namely, REFERENCINGOLD and REFERENCINGNEW in SYSTRIGGERS table which are set to false if REFERENCING is missing. If either one of the REFERENCING clauses are specified (for OLD and NEW), the corresponding columns' values will be set to TRUE, We can hopefully use this information to disable streaming of LOB columns. I will look at the code to see how this information can be used. I will assign this jira to me while I look at this aspect of REFERENCING clause.

          java -Dij.exceptionTrace=true org.apache.derby.tools.ij
          connect 'jdbc:derby:testDB1482;create=true';
          create table table1 (c11 int, c12 int default 0);
          create index i1 on table1(c11);
          create table table2 (c21 int, c22 int default 0);
          create index i2 on table2(c21);
          --For the trigger below, REFERENCINGOLD and REFERENCINGNEW will be false.
          – REFERENCINGOLD and REFERENCINGNEW are false because no REFERENCING clause was used in the CREATE TRIGGER.
          create trigger trigger1 AFTER INSERT on table1 insert into table2(c21, c22) values (100, -1);
          --For the trigger below, REFERENCINGNEW will be true because CREATE TRIGGER used REFERENCING NEW.
          – REFERENCINGOLD will be false because no REFERENCING OLD was specified in the CREATE TRIGGER sql.
          create trigger trigger2 AFTER UPDATE of c12 on table1 REFERENCING NEW as new FOR EACH ROW
          update table2 set c22=-1 where c21=new.c11;
          select CAST(TRIGGERNAME as char(14)), REFERENCEDCOLUMNS, TRIGGERDEFINITION, REFERENCINGOLD, REFERENCINGNEW from sys.systriggers;

          Show
          Mamta A. Satoor added a comment - - edited I wrote following ij script to understand what kind of information is kept in SYSTRIGGERS when REFERENCING clause is specified or missing in the CREATE TRIGGER sql. There are 2 booolean columns, namely, REFERENCINGOLD and REFERENCINGNEW in SYSTRIGGERS table which are set to false if REFERENCING is missing. If either one of the REFERENCING clauses are specified (for OLD and NEW), the corresponding columns' values will be set to TRUE, We can hopefully use this information to disable streaming of LOB columns. I will look at the code to see how this information can be used. I will assign this jira to me while I look at this aspect of REFERENCING clause. java -Dij.exceptionTrace=true org.apache.derby.tools.ij connect 'jdbc:derby:testDB1482;create=true'; create table table1 (c11 int, c12 int default 0); create index i1 on table1(c11); create table table2 (c21 int, c22 int default 0); create index i2 on table2(c21); --For the trigger below, REFERENCINGOLD and REFERENCINGNEW will be false. – REFERENCINGOLD and REFERENCINGNEW are false because no REFERENCING clause was used in the CREATE TRIGGER. create trigger trigger1 AFTER INSERT on table1 insert into table2(c21, c22) values (100, -1); --For the trigger below, REFERENCINGNEW will be true because CREATE TRIGGER used REFERENCING NEW. – REFERENCINGOLD will be false because no REFERENCING OLD was specified in the CREATE TRIGGER sql. create trigger trigger2 AFTER UPDATE of c12 on table1 REFERENCING NEW as new FOR EACH ROW update table2 set c22=-1 where c21=new.c11; select CAST(TRIGGERNAME as char(14)), REFERENCEDCOLUMNS, TRIGGERDEFINITION, REFERENCINGOLD, REFERENCINGNEW from sys.systriggers;
          Hide
          Mamta A. Satoor added a comment -

          The attached patch, TriggerTests_ver1_diff.txt, includes junit test which has few test fixtures to test triggers on table with LOB columns. Each and every one of those tests run into OOM probably because we are streaming the before and after values of LOB columns even when they are not being referenced by the trigger action. More code investigation is needed to identify different trigger cases where we should not stream the LOBs.

          I have added this junit test, TriggerTests.java, in org.apache.derbyTesting.functionTests.tests.memory package. It has been added to the _Suite in that package but has been commented for now with the following comments.
          //Disable following TriggerTests until DERBY-1482 has been fixed.
          //Without that fix, the test will run into OOM errors for all
          //the test fixtures. This test is written for triggers defined
          //on table with LOB columns. No matter whether the LoB columns
          //are touched in the trigger action, it appears that Derby is
          //streaming the before and after values of LOB columns. Once
          //the streaming problem has been resolved, we should be able
          //to uncomment the following test.
          //suite.addTest(TriggerTests.suite());
          The org.apache.derbyTesting.functionTests.tests.memory._Suite gets run as part of the lowmem suite. lowmem suite runs all the tests in it with 16M heap. I have tweaked the page cache size for TriggerTests to 100 pages. The reason for this is that the test deals with pretty big LOBs(320M each) and we will soon run into OOM because of no memory left for page cache if we ran with the default page cache of 1000 against the 16M heap.

          For now, this suite runs only in embedded mode. When I ran with network server mode, I got following error. But for now, I just want to focus on the OOM failures. Once they are fixed, we can probably look at enabling this test for network server mode.
          java.sql.SQLNonTransientConnectionException: Network protocol exception: actual code point, 4,692, does not match expected code point, 9,224. The connection has been terminated.

          One interesting exception I have noticed for few of the test fixtures (in addition to OOM) looks something like following.
          java.io.FileNotFoundException: system\wombat\log\log211.dat (The system cannot find the file specified.)
          at java.io.FileInputStream.open(Native Method)
          at java.io.FileInputStream.<init>(FileInputStream.java:112)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copySingleFile(PrivilegedFileOpsForTests.java:245)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:222)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
          at java.security.AccessController.doPrivileged(AccessController.java:251)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:220)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
          at java.security.AccessController.doPrivileged(AccessController.java:251)
          at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:133)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at junit.framework.TestSuite.runTest(TestSuite.java:208)
          at junit.framework.TestSuite.run(TestSuite.java:203)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:23)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:23)
          at junit.framework.TestSuite.runTest(TestSuite.java:208)
          at junit.framework.TestSuite.run(TestSuite.java:203)
          at junit.textui.TestRunner.doRun(TestRunner.java:116)
          at junit.textui.TestRunner.start(TestRunner.java:172)
          at junit.textui.TestRunner.main(TestRunner.java:138)
          I do not see this exception for first test fixtures. The reason behind these additional exceptions may be that we are running into OOM and jvm can start behaving unpredictably after running into OOMs. To verify that the problem is not with my tests, I changed the LOB size to 60K from 320M and ran with default heap and default page cache size and didn't run into above exceptions. Of course there were no OOMs either because we have enough heap memory to be able to stream the LOBs. In the patch attached, I have reverted my junit test back to use 320M LOB data, page cache size of 100 and limited heap of 16M enforced by the lowmem suite.

          The typical OOM exception for the test looks as follows
          7) test1InsertBeforeTrigger(org.apache.derbyTesting.functionTests.tests.memory.TriggerTests)java.sql.SQLException: Java exception: ': java.lang.OutOfMemoryError'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
          at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142)
          at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1321)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1673)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:303)
          at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.runInsertTriggerTest(TriggerTests.java:1479)
          at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.test1InsertBeforeTrigger(TriggerTests.java:541)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
          at junit.extensions.TestSetup.run(TestSetup.java:23)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
          at junit.extensions.TestSetup.run(TestSetup.java:23)
          Caused by: java.sql.SQLException: Java exception: ': java.lang.OutOfMemoryError'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
          ... 38 more
          Caused by: java.lang.OutOfMemoryError
          at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
          at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:244)
          at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135)
          at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:494)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
          ... 31 more

          In order to run the junit test to repro the problem, first you will need to uncomment the test from org.apache.derbyTesting.functionTests.tests.memory._Suite and compile the code. Next, you can run the lowmem suite one of the following 2 ways
          1)You can run it as an ant target
          ant junit-lowmem
          2)java -Xmx16M junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.memory._Suite
          Either one of above runs should show you OOMs for each of the test fixtures in TriggerTests.java

          Following documents the different trigger and LOB combinations that are being tested in TriggerTests.java. It also shows the test fixture name for that trigger and LOB test combination
          NO REFERENCE CLAUSE in trigger definition
          AFTER INSERT trigger test5InsertAfterTriggerNoReferencingClause
          BEFORE INSERT trigger test5InsertBeforeTriggerNoReferencingClause
          AFTER DELETE trigger test5DeleteAfterTriggerNoReferencingClause
          BEFORE DELETE trigger test5DeleteBeforeTriggerNoReferencingClause
          AFTER UPDATE trigger test5UpdateAfterTriggerNoReferencingClause
          BEFORE UPDATE trigger test5UpdateBeforeTriggerNoReferencingClause

          tirgger created on non-LOB column and no LOB is referenced in trigger action
          AFTER INSERT trigger test1InsertAfterTrigger
          BEFORE INSERT trigger test1InsertBeforeTrigger
          AFTER DELETE trigger test1DeleteAfterTrigger
          BEFORE DELETE trigger test1DeleteBeforeTrigger
          AFTER UPDATE trigger test1UpdateAfterTrigger
          BEFORE UPDATE trigger test1UpdateBeforeTrigger

          tirgger created on non-LOB column and LOB is accessed in trigger action
          AFTER INSERT trigger test2InsertAfterTriggerAccessLOB
          BEFORE INSERT trigger test2InsertBeforeTriggerAccessLOB
          AFTER DELETE trigger test2DeleteAfterTriggerAccessLOB
          BEFORE DELETE trigger test2DeleteBeforeTriggerAccessLOB
          AFTER UPDATE trigger test2UpdateAfterTriggerAccessLOB
          BEFORE UPDATE trigger test2UpdateBeforeTriggerAccessLOB

          tirgger created on non-LOB column and LOB is getting UPDATEed in trigger action
          AFTER INSERT trigger test2InsertAfterTriggerUpdatedLOB
          BEFORE INSERT trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action
          AFTER DELETE trigger No test - the row has been deleted and hence no way for trigger action to modify the LOB of the deleted row
          BEFORE DELETE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action
          AFTER UPDATE trigger test2UpdateAfterTriggerUpdatedLOB
          BEFORE UPDATE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action

          tirgger created on LOB column and no LOB is referenced in trigger action
          AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test1InsertAfterTrigger will suffice.
          BEFORE INSERT trigger No test because INSERT trigger is defined on a row, not a column. test1InsertBeforeTrigger will suffice.
          AFTER DELETE trigger No test because DELETE trigger is defined on a row, not a column. test1DeleteAfterTrigger will suffice.
          BEFORE DELETE trigger No test because DELETE trigger is defined on a row, not a column. test1DeleteBeforeTrigger will suffice.
          AFTER UPDATE trigger test3UpdateAfterTrigger
          BEFORE UPDATE trigger test3UpdateBeforeTrigger

          tirgger created on LOB column and LOB is accessed in trigger action
          AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertAfterTriggerAccessLOB will suffice.
          BEFORE INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertBeforeTriggerAccessLOB will suffice.
          AFTER DELETE trigger No test because DELETE trigger is defined on a row, not a column. test2DeleteAfterTriggerAccessLOB will suffice.
          BEFORE DELETE trigger No test because DELETE trigger is defined on a row, not a column. test2DeleteBeforeTriggerAccessLOB will suffice.
          AFTER UPDATE trigger test4UpdateAfterTriggerAccessLOB
          BEFORE UPDATE trigger test4UpdateBeforeTrigger

          tirgger created on LOB column and LOB is getting UPDATEed in trigger action
          AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertAfterTriggerUpdatedLOB will suffice.
          BEFORE INSERT trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action
          AFTER DELETE trigger No test - the row has been deleted and hence no way for trigger action to modify the LOB of the deleted row
          BEFORE DELETE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action
          AFTER UPDATE trigger test4UpdateAfterTriggerUpdatedLOB
          BEFORE UPDATE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action

          Has two versions of this trigger+LOB combination. The trigger action is coded as either a stored procedure call or as a direct SQL statement.
          (+)These test cases do not have stored procedure version of them. The reason is that the trigger action references LOB columns and at this point, there is no way to pass LOB parameters to a stored procedure

          Show
          Mamta A. Satoor added a comment - The attached patch, TriggerTests_ver1_diff.txt, includes junit test which has few test fixtures to test triggers on table with LOB columns. Each and every one of those tests run into OOM probably because we are streaming the before and after values of LOB columns even when they are not being referenced by the trigger action. More code investigation is needed to identify different trigger cases where we should not stream the LOBs. I have added this junit test, TriggerTests.java, in org.apache.derbyTesting.functionTests.tests.memory package. It has been added to the _Suite in that package but has been commented for now with the following comments. //Disable following TriggerTests until DERBY-1482 has been fixed. //Without that fix, the test will run into OOM errors for all //the test fixtures. This test is written for triggers defined //on table with LOB columns. No matter whether the LoB columns //are touched in the trigger action, it appears that Derby is //streaming the before and after values of LOB columns. Once //the streaming problem has been resolved, we should be able //to uncomment the following test. //suite.addTest(TriggerTests.suite()); The org.apache.derbyTesting.functionTests.tests.memory._Suite gets run as part of the lowmem suite. lowmem suite runs all the tests in it with 16M heap. I have tweaked the page cache size for TriggerTests to 100 pages. The reason for this is that the test deals with pretty big LOBs(320M each) and we will soon run into OOM because of no memory left for page cache if we ran with the default page cache of 1000 against the 16M heap. For now, this suite runs only in embedded mode. When I ran with network server mode, I got following error. But for now, I just want to focus on the OOM failures. Once they are fixed, we can probably look at enabling this test for network server mode. java.sql.SQLNonTransientConnectionException: Network protocol exception: actual code point, 4,692, does not match expected code point, 9,224. The connection has been terminated. One interesting exception I have noticed for few of the test fixtures (in addition to OOM) looks something like following. java.io.FileNotFoundException: system\wombat\log\log211.dat (The system cannot find the file specified.) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.<init>(FileInputStream.java:112) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copySingleFile(PrivilegedFileOpsForTests.java:245) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:222) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184) at java.security.AccessController.doPrivileged(AccessController.java:251) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:220) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184) at java.security.AccessController.doPrivileged(AccessController.java:251) at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:133) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:23) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:23) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) at junit.textui.TestRunner.doRun(TestRunner.java:116) at junit.textui.TestRunner.start(TestRunner.java:172) at junit.textui.TestRunner.main(TestRunner.java:138) I do not see this exception for first test fixtures. The reason behind these additional exceptions may be that we are running into OOM and jvm can start behaving unpredictably after running into OOMs. To verify that the problem is not with my tests, I changed the LOB size to 60K from 320M and ran with default heap and default page cache size and didn't run into above exceptions. Of course there were no OOMs either because we have enough heap memory to be able to stream the LOBs. In the patch attached, I have reverted my junit test back to use 320M LOB data, page cache size of 100 and limited heap of 16M enforced by the lowmem suite. The typical OOM exception for the test looks as follows 7) test1InsertBeforeTrigger(org.apache.derbyTesting.functionTests.tests.memory.TriggerTests)java.sql.SQLException: Java exception: ': java.lang.OutOfMemoryError'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1321) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1673) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:303) at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.runInsertTriggerTest(TriggerTests.java:1479) at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.test1InsertBeforeTrigger(TriggerTests.java:541) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) Caused by: java.sql.SQLException: Java exception: ': java.lang.OutOfMemoryError'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70) ... 38 more Caused by: java.lang.OutOfMemoryError at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421) at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:244) at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135) at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:494) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232) ... 31 more In order to run the junit test to repro the problem, first you will need to uncomment the test from org.apache.derbyTesting.functionTests.tests.memory._Suite and compile the code. Next, you can run the lowmem suite one of the following 2 ways 1)You can run it as an ant target ant junit-lowmem 2)java -Xmx16M junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.memory._Suite Either one of above runs should show you OOMs for each of the test fixtures in TriggerTests.java Following documents the different trigger and LOB combinations that are being tested in TriggerTests.java. It also shows the test fixture name for that trigger and LOB test combination NO REFERENCE CLAUSE in trigger definition AFTER INSERT trigger test5InsertAfterTriggerNoReferencingClause BEFORE INSERT trigger test5InsertBeforeTriggerNoReferencingClause AFTER DELETE trigger test5DeleteAfterTriggerNoReferencingClause BEFORE DELETE trigger test5DeleteBeforeTriggerNoReferencingClause AFTER UPDATE trigger test5UpdateAfterTriggerNoReferencingClause BEFORE UPDATE trigger test5UpdateBeforeTriggerNoReferencingClause tirgger created on non-LOB column and no LOB is referenced in trigger action AFTER INSERT trigger test1InsertAfterTrigger BEFORE INSERT trigger test1InsertBeforeTrigger AFTER DELETE trigger test1DeleteAfterTrigger BEFORE DELETE trigger test1DeleteBeforeTrigger AFTER UPDATE trigger test1UpdateAfterTrigger BEFORE UPDATE trigger test1UpdateBeforeTrigger tirgger created on non-LOB column and LOB is accessed in trigger action AFTER INSERT trigger test2InsertAfterTriggerAccessLOB BEFORE INSERT trigger test2InsertBeforeTriggerAccessLOB AFTER DELETE trigger test2DeleteAfterTriggerAccessLOB BEFORE DELETE trigger test2DeleteBeforeTriggerAccessLOB AFTER UPDATE trigger test2UpdateAfterTriggerAccessLOB BEFORE UPDATE trigger test2UpdateBeforeTriggerAccessLOB tirgger created on non-LOB column and LOB is getting UPDATEed in trigger action AFTER INSERT trigger test2InsertAfterTriggerUpdatedLOB BEFORE INSERT trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action AFTER DELETE trigger No test - the row has been deleted and hence no way for trigger action to modify the LOB of the deleted row BEFORE DELETE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action AFTER UPDATE trigger test2UpdateAfterTriggerUpdatedLOB BEFORE UPDATE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action tirgger created on LOB column and no LOB is referenced in trigger action AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test1InsertAfterTrigger will suffice. BEFORE INSERT trigger No test because INSERT trigger is defined on a row, not a column. test1InsertBeforeTrigger will suffice. AFTER DELETE trigger No test because DELETE trigger is defined on a row, not a column. test1DeleteAfterTrigger will suffice. BEFORE DELETE trigger No test because DELETE trigger is defined on a row, not a column. test1DeleteBeforeTrigger will suffice. AFTER UPDATE trigger test3UpdateAfterTrigger BEFORE UPDATE trigger test3UpdateBeforeTrigger tirgger created on LOB column and LOB is accessed in trigger action AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertAfterTriggerAccessLOB will suffice. BEFORE INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertBeforeTriggerAccessLOB will suffice. AFTER DELETE trigger No test because DELETE trigger is defined on a row, not a column. test2DeleteAfterTriggerAccessLOB will suffice. BEFORE DELETE trigger No test because DELETE trigger is defined on a row, not a column. test2DeleteBeforeTriggerAccessLOB will suffice. AFTER UPDATE trigger test4UpdateAfterTriggerAccessLOB BEFORE UPDATE trigger test4UpdateBeforeTrigger tirgger created on LOB column and LOB is getting UPDATEed in trigger action AFTER INSERT trigger No test because INSERT trigger is defined on a row, not a column. test2InsertAfterTriggerUpdatedLOB will suffice. BEFORE INSERT trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action AFTER DELETE trigger No test - the row has been deleted and hence no way for trigger action to modify the LOB of the deleted row BEFORE DELETE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action AFTER UPDATE trigger test4UpdateAfterTriggerUpdatedLOB BEFORE UPDATE trigger No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger action Has two versions of this trigger+LOB combination. The trigger action is coded as either a stored procedure call or as a direct SQL statement. (+)These test cases do not have stored procedure version of them. The reason is that the trigger action references LOB columns and at this point, there is no way to pass LOB parameters to a stored procedure
          Hide
          Kristian Waagan added a comment -

          @Mike (16/Feb/10):
          I don't have a write-up, and I'm not sure if the code will handle the scenarios you mention. I don't fully understand the details of the store and the trigger code in the case of inserts/updates of long columns.

          Can you help me understand your comment about the changing BLOB value:
          "But the stored
          value of the blob could be changed before we access it, so a a stream pointer to
          the original blob is not going to be valid. "

          Who / what may change it;

          • the current transaction's update query?
          • the current transaction's trigger code?
          • other transactions?

          I hope to be able to have a better look at the trigger code soon, but it's not on the top of my list atm.

          That said, I do see that we are having trouble dealing with "inbound streams" when they have to be used several times - our only tool there is to materialize the stream into memory. One extension would be to write data to disk temporarily to avoid OOME, but I'm not sure if we can manage this without introducing a performance degradation.
          The better option is probably to just write the value to the log and then read it back from the log as required. Do you have any idea about how much effort that would take? I'm not suggesting that it should be done now, but it would be nice to have an idea about how difficult it is.

          Show
          Kristian Waagan added a comment - @Mike (16/Feb/10): I don't have a write-up, and I'm not sure if the code will handle the scenarios you mention. I don't fully understand the details of the store and the trigger code in the case of inserts/updates of long columns. Can you help me understand your comment about the changing BLOB value: "But the stored value of the blob could be changed before we access it, so a a stream pointer to the original blob is not going to be valid. " Who / what may change it; the current transaction's update query? the current transaction's trigger code? other transactions? I hope to be able to have a better look at the trigger code soon, but it's not on the top of my list atm. That said, I do see that we are having trouble dealing with "inbound streams" when they have to be used several times - our only tool there is to materialize the stream into memory. One extension would be to write data to disk temporarily to avoid OOME, but I'm not sure if we can manage this without introducing a performance degradation. The better option is probably to just write the value to the log and then read it back from the log as required. Do you have any idea about how much effort that would take? I'm not suggesting that it should be done now, but it would be nice to have an idea about how difficult it is.
          Hide
          Mamta A. Satoor added a comment -

          Kristian, I reran derby1482ReproVersion2 after doing a sync of the codeline and do see the updated stack. Thanks for taking the time to run the repro on your machine.

          I am almost done with bunch of trigger tests in combination with LOB column. Hope to post that in a day or two,

          My current work has to do with writing tests so feel free to checkin your code if you think it is ready.

          Show
          Mamta A. Satoor added a comment - Kristian, I reran derby1482ReproVersion2 after doing a sync of the codeline and do see the updated stack. Thanks for taking the time to run the repro on your machine. I am almost done with bunch of trigger tests in combination with LOB column. Hope to post that in a day or two, My current work has to do with writing tests so feel free to checkin your code if you think it is ready.
          Hide
          Mike Matrigali added a comment -

          Kristian, is there a writeup on how you will make all store streams clonable? I am not sure how the trigger code is working, but will your cloning work for the following case.
          With after triggers we are interested in the "before" and "after" values. Does your code
          somehow copy the data somewhere else from store, or do some sort of copy on
          write?

          It seemed to me that the trigger full copy of the stream was trying to solve the following
          problem, for a before trigger. We need the before copy of the blob. But the stored
          value of the blob could be changed before we access it, so a a stream pointer to
          the original blob is not going to be valid.

          Show
          Mike Matrigali added a comment - Kristian, is there a writeup on how you will make all store streams clonable? I am not sure how the trigger code is working, but will your cloning work for the following case. With after triggers we are interested in the "before" and "after" values. Does your code somehow copy the data somewhere else from store, or do some sort of copy on write? It seemed to me that the trigger full copy of the stream was trying to solve the following problem, for a before trigger. We need the before copy of the blob. But the stored value of the blob could be changed before we access it, so a a stream pointer to the original blob is not going to be valid.
          Hide
          Kristian Waagan added a comment -

          The stack trace above from running derby1482ReproVersion2 is slightly outdated.
          Here's a newer stack trace:

          ij> update t1 set status = 1 where id = 1;
          ERROR XJ001: Java exception: 'Java heap space: java.lang.OutOfMemoryError'.
          [ snip ]
          Caused by: java.lang.OutOfMemoryError: Java heap space
          at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
          at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:241)
          at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:425)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:254)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
          ... 10 more
          ij>

          The code from the old stack trace has been rewritten (see DERBY-4477), loading the stream was only done temporarily until the underlying stream clone functionality was ready.

          Regarding the new repro, I do have code that will make that one pass too
          I will hold it back until you have been able to complete you current work, and my patch also needs more testing.

          The newly added clone functionality will basically allow all store streams to be cloned. To provoke an OOME one has to clone a stream that isn't cloneable. I haven't looked into how to do this, but I think one way of doing it is to provide a user stream to use for the update (or insert). I'm sure there are other ways to do it too.
          Despite the new cloning abilities, functionality to avoid referencing columns not being accessed will still give us benefits like increased performance and by avoiding OOMEs in certain situations.

          Show
          Kristian Waagan added a comment - The stack trace above from running derby1482ReproVersion2 is slightly outdated. Here's a newer stack trace: ij> update t1 set status = 1 where id = 1; ERROR XJ001: Java exception: 'Java heap space: java.lang.OutOfMemoryError'. [ snip ] Caused by: java.lang.OutOfMemoryError: Java heap space at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421) at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:241) at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135) at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:425) at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:254) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232) ... 10 more ij> The code from the old stack trace has been rewritten (see DERBY-4477 ), loading the stream was only done temporarily until the underlying stream clone functionality was ready. Regarding the new repro, I do have code that will make that one pass too I will hold it back until you have been able to complete you current work, and my patch also needs more testing. The newly added clone functionality will basically allow all store streams to be cloned. To provoke an OOME one has to clone a stream that isn't cloneable. I haven't looked into how to do this, but I think one way of doing it is to provide a user stream to use for the update (or insert). I'm sure there are other ways to do it too. Despite the new cloning abilities, functionality to avoid referencing columns not being accessed will still give us benefits like increased performance and by avoiding OOMEs in certain situations.
          Hide
          Mamta A. Satoor added a comment -

          For the repro derby1482Repro.java that I attached earlier today, I played a little with the heap size to see if it can be big enough to satisfy the default page cache size of 1000 pages but not big enough to be able to stream the LOB into memory. The heap size of 100M seems to meet that requirement. So the OOM above can be reproduced using following ij command once the database has been created.

          java -Xmx100m -Dij.exceptionTrace=true org.apache.derby.tools.ij
          connect 'jdbc:derby:testDB1482;create=true';
          --following update of table t3 works with no problem. This is the table with no BLOB column
          update t3 set status = 1 where id = 1;
          --But the following update will fail with OOM. This update is on the table with BLOB column
          update t1 set status = 1 where id = 1;

          Show
          Mamta A. Satoor added a comment - For the repro derby1482Repro.java that I attached earlier today, I played a little with the heap size to see if it can be big enough to satisfy the default page cache size of 1000 pages but not big enough to be able to stream the LOB into memory. The heap size of 100M seems to meet that requirement. So the OOM above can be reproduced using following ij command once the database has been created. java -Xmx100m -Dij.exceptionTrace=true org.apache.derby.tools.ij connect 'jdbc:derby:testDB1482;create=true'; --following update of table t3 works with no problem. This is the table with no BLOB column update t3 set status = 1 where id = 1; --But the following update will fail with OOM. This update is on the table with BLOB column update t1 set status = 1 where id = 1;
          Hide
          Mamta A. Satoor added a comment -

          The stack trace of OOM for the reprodcuible is as follows
          2010-02-15 20:37:07.296 GMT Thread[main,5,main] (XID = 37836), (SESSIONID = 1), (DATABASE = testDB1482), (DRDAID = null), Failed Statement is: update t1 set status = 1 where id = 1
          java.lang.OutOfMemoryError
          at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
          at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:241)
          at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
          at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(ProjectRestrictResultSet.java:533)
          at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:297)
          at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:185)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:425)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:254)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
          at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
          at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
          at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363)
          at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
          at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
          at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
          at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
          at org.apache.derby.tools.ij.main(ij.java:59)
          Cleanup action completed

          Show
          Mamta A. Satoor added a comment - The stack trace of OOM for the reprodcuible is as follows 2010-02-15 20:37:07.296 GMT Thread [main,5,main] (XID = 37836), (SESSIONID = 1), (DATABASE = testDB1482), (DRDAID = null), Failed Statement is: update t1 set status = 1 where id = 1 java.lang.OutOfMemoryError at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421) at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:241) at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(ProjectRestrictResultSet.java:533) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:297) at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:185) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127) at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:425) at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:254) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:363) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261) at org.apache.derby.impl.tools.ij.Main.go(Main.java:229) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) at org.apache.derby.tools.ij.main(ij.java:59) Cleanup action completed
          Hide
          Mamta A. Satoor added a comment -

          The current reproducible attached to this jira derby1482Repro.java really runs into OOM because it can't get enough page cache from the heap available to the jvm. This problem can be easily resolved by decreasing the amount of page cache requested from the heap. This can be achieved by using the property -Dderby.storage.pageCacheSize=smallerpagecache. Once the page cache requirement is lowered derby1482Repro.java runs fine without any problems with LOB streaming. The reason we don't run into OOM for streaming of LOB with derby1482Repro.java is that the LOB data size is not big enough. derby1482Repro.java is inserting only 30K data into the 2G LOB(notice I am talking about data size and not column size. LOB column is declared as 2G but data loaded into it is only 30K). Store does not stream data out of store when the data size is less than the page size (in Derby, page size is 32K). In order for us to cause the data to be read in the memory, the data size has to be bigger.

          What we are really after is a reproducible program which runs into OOM while LOBs are being streamed into memory as part of trigger execution when the LOBs are really not needed to be streamed in because they are not being accessed/updated. I have spent some time on coming up with a repro for OOM as a result of LOB streaming. That repro is attached as derby1482ReproVersion2.java This test case runs into OOM while streaming the LOB because now the LOB data is 320M in size, much larger than the page size of 32K. Since the BLOB is so large, Store will stream it into memory, thus running into OOM).
          To reproduce the problem, use the following steps
          1)First create the database with 3 populated tables using the attached java program
          derby1482ReproVersion2.java as follows
          java org.apache.derbyTesting.functionTests.tests.lang.derby1482ReproVersion2
          Some information about the 3 tables
          a)Table t1 has a BLOB column along with other columns.
          b)Table t3 is similar to t1 but without a BLOB column
          c)Table t1 has an update trigger defined on it which updates table t2 in the trigger action.
          d)Table t3 also has an update trigger defined on it which also updates table t2 in the trigger action.
          2)To reproduce the problem of OutOfMemory, connect to the database using ij with very limited heap
          java -Xmx4m -Dderby.storage.pageCacheSize=40 -Dij.exceptionTrace=true org.apache.derby.tools.ij
          connect 'jdbc:derby:testDB1482;create=true';
          --following update of table t3 works with no problem. This is the table with no BLOB column
          update t3 set status = 1 where id = 1;
          --But the following update will fail with OOM. This update is on the table with BLOB column
          update t1 set status = 1 where id = 1;

          Show
          Mamta A. Satoor added a comment - The current reproducible attached to this jira derby1482Repro.java really runs into OOM because it can't get enough page cache from the heap available to the jvm. This problem can be easily resolved by decreasing the amount of page cache requested from the heap. This can be achieved by using the property -Dderby.storage.pageCacheSize=smallerpagecache. Once the page cache requirement is lowered derby1482Repro.java runs fine without any problems with LOB streaming. The reason we don't run into OOM for streaming of LOB with derby1482Repro.java is that the LOB data size is not big enough. derby1482Repro.java is inserting only 30K data into the 2G LOB(notice I am talking about data size and not column size. LOB column is declared as 2G but data loaded into it is only 30K). Store does not stream data out of store when the data size is less than the page size (in Derby, page size is 32K). In order for us to cause the data to be read in the memory, the data size has to be bigger. What we are really after is a reproducible program which runs into OOM while LOBs are being streamed into memory as part of trigger execution when the LOBs are really not needed to be streamed in because they are not being accessed/updated. I have spent some time on coming up with a repro for OOM as a result of LOB streaming. That repro is attached as derby1482ReproVersion2.java This test case runs into OOM while streaming the LOB because now the LOB data is 320M in size, much larger than the page size of 32K. Since the BLOB is so large, Store will stream it into memory, thus running into OOM). To reproduce the problem, use the following steps 1)First create the database with 3 populated tables using the attached java program derby1482ReproVersion2.java as follows java org.apache.derbyTesting.functionTests.tests.lang.derby1482ReproVersion2 Some information about the 3 tables a)Table t1 has a BLOB column along with other columns. b)Table t3 is similar to t1 but without a BLOB column c)Table t1 has an update trigger defined on it which updates table t2 in the trigger action. d)Table t3 also has an update trigger defined on it which also updates table t2 in the trigger action. 2)To reproduce the problem of OutOfMemory, connect to the database using ij with very limited heap java -Xmx4m -Dderby.storage.pageCacheSize=40 -Dij.exceptionTrace=true org.apache.derby.tools.ij connect 'jdbc:derby:testDB1482;create=true'; --following update of table t3 works with no problem. This is the table with no BLOB column update t3 set status = 1 where id = 1; --But the following update will fail with OOM. This update is on the table with BLOB column update t1 set status = 1 where id = 1;
          Hide
          Mamta A. Satoor added a comment -

          Deleted old derby1482DeepCopyAfterTriggerOnLobColumn.java and attaching a newer version derby1482DeepCopyAfterTriggerOnLobColumn.java which not only shows how available heap memory impact an AFTER UPDATE TRIGGER on a LOB column by making it run into out of memory error, but it also shows that the heap memory change does not impact similar AFTER UPDATE TRIGGER but on a CHAR column rather than a LOB column. I am copying the javadoc comments from the program here to show what it is doing

          /**

          • DERBY-1482 - This program shows that we are successfully doing deepcopy
          • of before and after images of a lob object during an after update
          • trigger(that is provided we are running with enough memory.)
          • In case of after update trigger, we MUST do a deepcopy since the store
          • value of lob is going to be different before and after the update.
          • There are 2 sets of test cases
          • 1)The first test case(runLobTest), the AFTER UPDATE TRIGGER has been defined
          • on LOB column which means that Derby has to have enough memory to do the
          • deep copy of the before and after image of the LOB column. With enough
          • memory, the program will run with no OOM errors. Steps to runs the
          • program successfully (w/o OOM error) for this test case is as follows
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runLobTest
          • For this same test case, if we run the test with limited memory, we will run
          • into OOM error showing that there is not enough memory available to do the
          • deep copy. Follow the steps below to see that behavior
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load
          • java -Xmx4m org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runLobTest
          • 2)The second test case(runCharTest) shows that there is no issue with OOM
          • even when running with limited memory when LOB column is not used in the
          • triggering table, meaning Derby does not have any LOB columns to have to
          • copy in memory. This test case has the AFTER UPDATE TRIGGER on a CHAR
          • column. The steps to run with plenty memory is as follows
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runCharTest
          • The steps to run this CHAR trigger with limited memory is as shown below.
          • The limited memory has no impact (in orther words no OOM) for CHAR trigger
          • java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load
          • java -Xmx4m org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runCharTest
          • The first step in all the test scenarios is to first load the data needed
          • and then perform the next step.
            */
          Show
          Mamta A. Satoor added a comment - Deleted old derby1482DeepCopyAfterTriggerOnLobColumn.java and attaching a newer version derby1482DeepCopyAfterTriggerOnLobColumn.java which not only shows how available heap memory impact an AFTER UPDATE TRIGGER on a LOB column by making it run into out of memory error, but it also shows that the heap memory change does not impact similar AFTER UPDATE TRIGGER but on a CHAR column rather than a LOB column. I am copying the javadoc comments from the program here to show what it is doing /** DERBY-1482 - This program shows that we are successfully doing deepcopy of before and after images of a lob object during an after update trigger(that is provided we are running with enough memory.) In case of after update trigger, we MUST do a deepcopy since the store value of lob is going to be different before and after the update. There are 2 sets of test cases 1)The first test case(runLobTest), the AFTER UPDATE TRIGGER has been defined on LOB column which means that Derby has to have enough memory to do the deep copy of the before and after image of the LOB column. With enough memory, the program will run with no OOM errors. Steps to runs the program successfully (w/o OOM error) for this test case is as follows java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runLobTest For this same test case, if we run the test with limited memory, we will run into OOM error showing that there is not enough memory available to do the deep copy. Follow the steps below to see that behavior java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load java -Xmx4m org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runLobTest 2)The second test case(runCharTest) shows that there is no issue with OOM even when running with limited memory when LOB column is not used in the triggering table, meaning Derby does not have any LOB columns to have to copy in memory. This test case has the AFTER UPDATE TRIGGER on a CHAR column. The steps to run with plenty memory is as follows java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runCharTest The steps to run this CHAR trigger with limited memory is as shown below. The limited memory has no impact (in orther words no OOM) for CHAR trigger java org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn load java -Xmx4m org.apache.derbyTesting.functionTests.tests.lang.derby1482DeepCopyAfterTriggerOnLobColumn runCharTest The first step in all the test scenarios is to first load the data needed and then perform the next step. */
          Hide
          Mamta A. Satoor added a comment -

          I have been spending some time on this jira writing test cases and researching the code a bit. Also spoke to Mike about the jira in general. Here are the findings so far.

          Following is the Derby behavior that we expect in regards to the lob columns and update triggers
          1)In case of an AFTER UPDATE TRIGGER on the LOB column, we expect Derby to do a deep copy of the before and after image of LOB columns in the triggering table. The reason for deep copy is the contents of LOB will be different in store before the update and after the update. I have a java program conforming this behavior. That java program is attached and the program's name is derby1482DeepCopyAfterTriggerOnLobColumn.java
          2)If the AFTER UPDATE TRIGGER is defined on non-LOB column, Derby still does a deep copy of before and after image of LOB columns in the triggering table. The possible improvements that can be made to Derby is to detect somehow if there is any need to do deep copy at all if the LOB columns are not getting accessed in the TRIGGER. If they are never accessed, there is no need to any before/after update values of the LOB columns thus saving memory requirement and unnecssary time spent in doing copy. I am working on writing a test case conforming that deep copy is being done even though there is really no need to since LOB columns are not getting accessed.
          3)In case of BEFORE UPDATE TRIGGER, we should not be doing any deep copy because the before and after images of LOB should be same (which will be the LOB value before update was done). I am working on writing test case for this scenario too.

          Show
          Mamta A. Satoor added a comment - I have been spending some time on this jira writing test cases and researching the code a bit. Also spoke to Mike about the jira in general. Here are the findings so far. Following is the Derby behavior that we expect in regards to the lob columns and update triggers 1)In case of an AFTER UPDATE TRIGGER on the LOB column, we expect Derby to do a deep copy of the before and after image of LOB columns in the triggering table. The reason for deep copy is the contents of LOB will be different in store before the update and after the update. I have a java program conforming this behavior. That java program is attached and the program's name is derby1482DeepCopyAfterTriggerOnLobColumn.java 2)If the AFTER UPDATE TRIGGER is defined on non-LOB column, Derby still does a deep copy of before and after image of LOB columns in the triggering table. The possible improvements that can be made to Derby is to detect somehow if there is any need to do deep copy at all if the LOB columns are not getting accessed in the TRIGGER. If they are never accessed, there is no need to any before/after update values of the LOB columns thus saving memory requirement and unnecssary time spent in doing copy. I am working on writing a test case conforming that deep copy is being done even though there is really no need to since LOB columns are not getting accessed. 3)In case of BEFORE UPDATE TRIGGER, we should not be doing any deep copy because the before and after images of LOB should be same (which will be the LOB value before update was done). I am working on writing test case for this scenario too.
          Hide
          Kristian Waagan added a comment -

          Regarding the tests that don't actually access the LOBs, it would be helpful to have one where the LOB is larger than the Java heap.

          While working on the LOB streaming / cloning issues, I discovered that the sorter always materializes LOBs, causing queries like 'select ... from ... order by length(myLOBs)' to be either very inefficient (despite that we in many cases have stored information about the length) or to fail with an OOME (depends on whether the code writes the records to temporary disk storage or not).
          I'll log an issue to track this when I have investigated a bit more.

          Show
          Kristian Waagan added a comment - Regarding the tests that don't actually access the LOBs, it would be helpful to have one where the LOB is larger than the Java heap. While working on the LOB streaming / cloning issues, I discovered that the sorter always materializes LOBs, causing queries like 'select ... from ... order by length(myLOBs)' to be either very inefficient (despite that we in many cases have stored information about the length) or to fail with an OOME (depends on whether the code writes the records to temporary disk storage or not). I'll log an issue to track this when I have investigated a bit more.
          Hide
          Mamta A. Satoor added a comment -

          I am working on writing few trigger test cases where it demonstrates various requirements for LOB streaming/objectifying because of them being accesses by trigger statement or trigger action or both. Additionally, few trigger test cases where LOB is not accessed at all by the trigger statement or trigger action. I think these test cases will come handy with any work that goes in for this jira. Currently, I am writing them as a standalone java program rather than a junit test.

          Show
          Mamta A. Satoor added a comment - I am working on writing few trigger test cases where it demonstrates various requirements for LOB streaming/objectifying because of them being accesses by trigger statement or trigger action or both. Additionally, few trigger test cases where LOB is not accessed at all by the trigger statement or trigger action. I think these test cases will come handy with any work that goes in for this jira. Currently, I am writing them as a standalone java program rather than a junit test.
          Hide
          Mike Matrigali added a comment -

          Does anyone know what guarantees there are on the "old" and "new" values in
          the trigger action. I understand they should represent before and after values
          with respect to triggering statement. But must they be unaffected by actions
          taken in the trigger action.

          So for instance you have a before trigger, and the trigger action changes
          the value of the row twice. Do we guarantee that the "old" value will not
          change?

          I ask this while trying to understand what is necessary for LOB old and new
          values.

          I am trying to understand how this issue could be addressed without doing
          the work Kathey describes to figure out what old and new column values are
          referenced. Without this info I don't think it is possible to guarantee
          an old value of a LOB around without somehow saving a copy of the whole stream.
          The easiest to understand case is if someone actually updates the LOB
          between the time the row was first saved and when the trigger action
          references it. In this case if we have not drained the stream before then
          it is not guaranteed to still be valid in the store. The store does not
          guarantee access to the old lob if it has been updated. Maintaining a lock
          on the row will stop other threads from updating it but it could still be
          changed by the same transaction.

          It would not be hard to figure out if the trigger statement could change
          various columns. It would be slightly harder to figure out if the trigger
          action could change the values, as it could be multiple statements.

          On the face of this I only see the following possibilities:
          1) do the work to figure out what columns the trigger action accesses. Then
          we only need to save old/new values of those columns referenced.

          2) Rather than reading the lobs into memory, existing interfaces could be used
          to stream the lobs to temp tables, and then save a stream reference to these.
          But this still would cause a lot of overhead for a trigger that never
          references the lobs.

          3) Define the behaviour in a subset of the cases such that we can use the
          existing lob in the db, and just maintain a stream reference to it. Somethin
          g like:
          o after trigger uses existing lob if trigger statement does not update
          lob column. Ignore trigger action possibly changing the lob value.
          o before triggers always use exising lob in db, ignores possibility of
          same xact changing the lob value in trigger action.

          Show
          Mike Matrigali added a comment - Does anyone know what guarantees there are on the "old" and "new" values in the trigger action. I understand they should represent before and after values with respect to triggering statement. But must they be unaffected by actions taken in the trigger action. So for instance you have a before trigger, and the trigger action changes the value of the row twice. Do we guarantee that the "old" value will not change? I ask this while trying to understand what is necessary for LOB old and new values. I am trying to understand how this issue could be addressed without doing the work Kathey describes to figure out what old and new column values are referenced. Without this info I don't think it is possible to guarantee an old value of a LOB around without somehow saving a copy of the whole stream. The easiest to understand case is if someone actually updates the LOB between the time the row was first saved and when the trigger action references it. In this case if we have not drained the stream before then it is not guaranteed to still be valid in the store. The store does not guarantee access to the old lob if it has been updated. Maintaining a lock on the row will stop other threads from updating it but it could still be changed by the same transaction. It would not be hard to figure out if the trigger statement could change various columns. It would be slightly harder to figure out if the trigger action could change the values, as it could be multiple statements. On the face of this I only see the following possibilities: 1) do the work to figure out what columns the trigger action accesses. Then we only need to save old/new values of those columns referenced. 2) Rather than reading the lobs into memory, existing interfaces could be used to stream the lobs to temp tables, and then save a stream reference to these. But this still would cause a lot of overhead for a trigger that never references the lobs. 3) Define the behaviour in a subset of the cases such that we can use the existing lob in the db, and just maintain a stream reference to it. Somethin g like: o after trigger uses existing lob if trigger statement does not update lob column. Ignore trigger action possibly changing the lob value. o before triggers always use exising lob in db, ignores possibility of same xact changing the lob value in trigger action.
          Hide
          Mamta A. Satoor added a comment -

          Including the steps here to reproduce the problem of out of memory.
          1)First create the database with 3 populated tables using the attached java program derby1482Repro.java as follows
          java org.apache.derbyTesting.functionTests.tests.lang.derby1482Repro
          Some information about the 3 tables
          a)Table t1 has a BLOB column along with other columns.
          b)Table t3 is similar to t1 but without a BLOB column
          c)Table t1 has an update trigger defined on it which updates table t2 in the trigger action.
          d)Table t3 also has an update trigger defined on it which also updates table t2 in the trigger action.
          2)To reproduce the problem of OutOfMemory, connect to the database using ij with very limited heap
          java -Xmx4m -Dij.exceptionTrace=true org.apache.derby.tools.ij
          connect 'jdbc:derby:testDB1482;create=true';
          --following update of table t3 works with no problem. This is the table with no BLOB column
          update t3 set status = 1 where id = 1;
          --But the following update will fail with OOM. This update is on the table with BLOB column
          update t1 set status = 1 where id = 1;

          The attached derby1482Repro.java is subset of the repro provided by Army for DERBY-442.

          Show
          Mamta A. Satoor added a comment - Including the steps here to reproduce the problem of out of memory. 1)First create the database with 3 populated tables using the attached java program derby1482Repro.java as follows java org.apache.derbyTesting.functionTests.tests.lang.derby1482Repro Some information about the 3 tables a)Table t1 has a BLOB column along with other columns. b)Table t3 is similar to t1 but without a BLOB column c)Table t1 has an update trigger defined on it which updates table t2 in the trigger action. d)Table t3 also has an update trigger defined on it which also updates table t2 in the trigger action. 2)To reproduce the problem of OutOfMemory, connect to the database using ij with very limited heap java -Xmx4m -Dij.exceptionTrace=true org.apache.derby.tools.ij connect 'jdbc:derby:testDB1482;create=true'; --following update of table t3 works with no problem. This is the table with no BLOB column update t3 set status = 1 where id = 1; --But the following update will fail with OOM. This update is on the table with BLOB column update t1 set status = 1 where id = 1; The attached derby1482Repro.java is subset of the repro provided by Army for DERBY-442 .
          Hide
          Daniel John Debrunner added a comment -

          Turns out this is an issue (BLOBs are streamed into memory) but the repro program does not show it.

          The repro uses blobs of 30k which will not be streamed by the store, and even if they were 30k is unlikely to cause any memory issues.

          Changing the BLOB size to 30Mb showed the problem, here's the stack trace of where the BLOB is being serialized:

          at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:219)
          at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:573)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:152)
          at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:133)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:437)
          at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:256)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1228)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1650)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299)
          at d442.run(d442.java:192)

          Show
          Daniel John Debrunner added a comment - Turns out this is an issue (BLOBs are streamed into memory) but the repro program does not show it. The repro uses blobs of 30k which will not be streamed by the store, and even if they were 30k is unlikely to cause any memory issues. Changing the BLOB size to 30Mb showed the problem, here's the stack trace of where the BLOB is being serialized: at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:219) at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:573) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:152) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:133) at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:437) at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:256) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1228) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1650) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299) at d442.run(d442.java:192)
          Hide
          Daniel John Debrunner added a comment -

          Just to add the page cache is larger in size for the BLOB table case because the page size for the table will have increased to 32k instead of 4k for the table without BLOB columns.

          Show
          Daniel John Debrunner added a comment - Just to add the page cache is larger in size for the BLOB table case because the page size for the table will have increased to 32k instead of 4k for the table without BLOB columns.
          Hide
          Daniel John Debrunner added a comment -

          I don't think this issue is related to streaming BLOBs into memory. I think the out of memory in the repro is due to the page cache blowing the memory limits.

          If I run the repro with a page cache size of 50 then the repro program succeeds with -Xm4m.

          I put trace code in SQLBlob that would print out if the BLOB values were being converted from a stream to a byte[] and non were printed.

          Show
          Daniel John Debrunner added a comment - I don't think this issue is related to streaming BLOBs into memory. I think the out of memory in the repro is due to the page cache blowing the memory limits. If I run the repro with a page cache size of 50 then the repro program succeeds with -Xm4m. I put trace code in SQLBlob that would print out if the BLOB values were being converted from a stream to a byte[] and non were printed.
          Hide
          Daniel John Debrunner added a comment -

          Sorry, I'm confused again:

          >My ultimate goal is to get a patch for 10.1 which will allow triggers on tables with blob columns if none of the blob columns are referenced. I see three possible approaches.
          > 1) Port DERBY-438.

          DERBY-438 is about triggers that reference BLOB columns, how does that relate to the problem you are seeing which is for triggers that do not reference BLOB columns?

          Show
          Daniel John Debrunner added a comment - Sorry, I'm confused again: >My ultimate goal is to get a patch for 10.1 which will allow triggers on tables with blob columns if none of the blob columns are referenced. I see three possible approaches. > 1) Port DERBY-438 . DERBY-438 is about triggers that reference BLOB columns, how does that relate to the problem you are seeing which is for triggers that do not reference BLOB columns?
          Hide
          Kathey Marsden added a comment -

          I came across this relevant comment in DMLWriteResultSet and thought I would post for reference.

          /* We only need to objectify the streams here if they are exposed to the users through the

          • trigger context. For "before" trigger, we could just return the stream wrapped in
          • RememberBytesInputStream to the user and reset it after usage, but this wouldn't work
          • because the user may get the stream in trigger action and do something with it in parallel
          • with the store doing insert. We could also delay the materializing until the stream is
          • fetched in before trigger but that would complicate the code. For "after" trigger, we have
          • to materialize it here because store only keeps a page's amount for each round. For other
          • reasons of "deferred" operations we don't need to objectify here. Simply going through a
          • temp table (either in memory part or spilled to disk) is fine for the stream, unless a
          • same stream appears in two rows in the temp table, which could happen for an "update", in
          • which case we do the objectifying in UpdateResultSet. Beetle 4896. Related bug entries:
          • 2432, 3383.
            */
          Show
          Kathey Marsden added a comment - I came across this relevant comment in DMLWriteResultSet and thought I would post for reference. /* We only need to objectify the streams here if they are exposed to the users through the trigger context. For "before" trigger, we could just return the stream wrapped in RememberBytesInputStream to the user and reset it after usage, but this wouldn't work because the user may get the stream in trigger action and do something with it in parallel with the store doing insert. We could also delay the materializing until the stream is fetched in before trigger but that would complicate the code. For "after" trigger, we have to materialize it here because store only keeps a page's amount for each round. For other reasons of "deferred" operations we don't need to objectify here. Simply going through a temp table (either in memory part or spilled to disk) is fine for the stream, unless a same stream appears in two rows in the temp table, which could happen for an "update", in which case we do the objectifying in UpdateResultSet. Beetle 4896. Related bug entries: 2432, 3383. */
          Hide
          Daniel John Debrunner added a comment -

          Sorry, misunderstood what you were trying to achieve. I guess I was just pointing out that 2) is one possible fix to this bug, but will have upgrade costs. Look strictly at the symptoms of this bug there may be an easier fix (for this bug) that reads the columns but does not stream them in. The first action would be to find why the LOB values are being streamed in, that's not a requirement for the column be read from the store.

          As for DERBY-438, maybe the discussion would be better in that issue, since it's then clear what is being attempted.

          Show
          Daniel John Debrunner added a comment - Sorry, misunderstood what you were trying to achieve. I guess I was just pointing out that 2) is one possible fix to this bug, but will have upgrade costs. Look strictly at the symptoms of this bug there may be an easier fix (for this bug) that reads the columns but does not stream them in. The first action would be to find why the LOB values are being streamed in, that's not a requirement for the column be read from the store. As for DERBY-438 , maybe the discussion would be better in that issue, since it's then clear what is being attempted.
          Hide
          Kathey Marsden added a comment -

          My ultimate goal is to get a patch for 10.1 which will allow triggers on tables with blob columns if none of the blob columns are referenced. I see three possible approaches.

          1) Port DERBY-438. This is a huge change and my initial cursory attempts weren't successful, but I could give it another shot.

          2) Try to fix this bug in the hopes that eliminating the read of the lob columns will allow us to have triggers on non-LOB columns.

          3) Something completely different for 10.1 to not read LOB columns at all in triggers.

          Do you think fixing this bug to not fully read the lob values will allow us to have triggers on non-LOB columns?

          Show
          Kathey Marsden added a comment - My ultimate goal is to get a patch for 10.1 which will allow triggers on tables with blob columns if none of the blob columns are referenced. I see three possible approaches. 1) Port DERBY-438 . This is a huge change and my initial cursory attempts weren't successful, but I could give it another shot. 2) Try to fix this bug in the hopes that eliminating the read of the lob columns will allow us to have triggers on non-LOB columns. 3) Something completely different for 10.1 to not read LOB columns at all in triggers. Do you think fixing this bug to not fully read the lob values will allow us to have triggers on non-LOB columns?
          Hide
          Daniel John Debrunner added a comment -

          Possibly not, don't jump to the conclusion that reading all the columns implies that LOB columns must be loaded into memory.

          So while reading all columns for the row is undesirable, it may not be causing the actual problem. The problem is that once a LOB column is read it is being streamed into memory even if it isn't referenced.

          Compare to a SELECT * from a table with LOB columns. All the columns are read from the table, but if the application never fetches the LOB columns they will not be read into memory, so what in the trigger case is causing the LOB values to be fully read.

          Show
          Daniel John Debrunner added a comment - Possibly not, don't jump to the conclusion that reading all the columns implies that LOB columns must be loaded into memory. So while reading all columns for the row is undesirable, it may not be causing the actual problem. The problem is that once a LOB column is read it is being streamed into memory even if it isn't referenced. Compare to a SELECT * from a table with LOB columns. All the columns are read from the table, but if the application never fetches the LOB columns they will not be read into memory, so what in the trigger case is causing the LOB values to be fully read.
          Hide
          Kathey Marsden added a comment -

          In DERBY-442, Manish said:
          https://issues.apache.org/jira/browse/DERBY-442#action_12374273

          "If we want to be smart and not read the columns which are not needed by the trigger, the trigger descriptor and the system table will have to remember which columns are referenced by the trigger. Confusingly, the referencedcolumns column of SYSTRIGGERS actually contains the triggering columns!"

          Does this mean that in order to fix this upgrade changes will be required?

          Show
          Kathey Marsden added a comment - In DERBY-442 , Manish said: https://issues.apache.org/jira/browse/DERBY-442#action_12374273 "If we want to be smart and not read the columns which are not needed by the trigger, the trigger descriptor and the system table will have to remember which columns are referenced by the trigger. Confusingly, the referencedcolumns column of SYSTRIGGERS actually contains the triggering columns!" Does this mean that in order to fix this upgrade changes will be required?
          Hide
          Andrew McIntyre added a comment -

          Unsetting Fix Version for unassigned issues.

          Show
          Andrew McIntyre added a comment - Unsetting Fix Version for unassigned issues.
          Hide
          Rick Hillegas added a comment -

          Move to 10.2.3.0.

          Show
          Rick Hillegas added a comment - Move to 10.2.3.0.
          Hide
          Rick Hillegas added a comment -

          Moving to 10.2.2.0.

          Show
          Rick Hillegas added a comment - Moving to 10.2.2.0.
          Hide
          Daniel John Debrunner added a comment -

          DERBY-442 is marked as a sub-taks of DERBY-438 but in reality this is a separate bug.
          The existing comments in DERBY-442 and repro conttinue to apply for this bug.

          Show
          Daniel John Debrunner added a comment - DERBY-442 is marked as a sub-taks of DERBY-438 but in reality this is a separate bug. The existing comments in DERBY-442 and repro conttinue to apply for this bug.

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Daniel John Debrunner
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development