Uploaded image for project: '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.

    XMLWordPrintableJSON

Details

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

      Attachments

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

        Issue Links

          Activity

            People

              mamtas Mamta A. Satoor
              djd Daniel John Debrunner
              Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: