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

        Issue Links

          Activity

          Daniel John Debrunner created issue -
          Daniel John Debrunner made changes -
          Field Original Value New Value
          Link This issue incorporates DERBY-442 [ DERBY-442 ]
          Rick Hillegas made changes -
          Urgency Low
          Rick Hillegas made changes -
          Fix Version/s 10.2.1.0 [ 11187 ]
          Fix Version/s 10.2.2.0 [ 12312027 ]
          Rick Hillegas made changes -
          Fix Version/s 10.2.3.0 [ 12312215 ]
          Fix Version/s 10.2.2.0 [ 12312027 ]
          Andrew McIntyre made changes -
          Fix Version/s 10.2.3.0 [ 12312215 ]
          Kathey Marsden made changes -
          Derby Categories [High Value Fix]
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix]
          Mamta A. Satoor made changes -
          Attachment derby1482Repro.java [ 12431091 ]
          Mamta A. Satoor made changes -
          Attachment derby1482DeepCopyAfterTriggerOnLobColumn.java [ 12434422 ]
          Mamta A. Satoor made changes -
          Attachment derby1482DeepCopyAfterTriggerOnLobColumn.java [ 12434422 ]
          Mamta A. Satoor made changes -
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-4537 [ DERBY-4537 ]
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-4538 [ DERBY-4538 ]
          Mamta A. Satoor made changes -
          Attachment derby1482ReproVersion2.java [ 12435915 ]
          Mamta A. Satoor made changes -
          Attachment TriggerTests_ver1_diff.txt [ 12436290 ]
          Attachment TriggerTests_ver1_stat.txt [ 12436291 ]
          Mamta A. Satoor made changes -
          Assignee Mamta A. Satoor [ mamtas ]
          Mamta A. Satoor made changes -
          Attachment junitUpgradeTestFailureWithPatch1.out [ 12439922 ]
          Attachment derby1482_patch1_diff.txt [ 12439923 ]
          Attachment derby1482_patch1_stat.txt [ 12439924 ]
          Mamta A. Satoor made changes -
          Attachment derby1482_patch2_diff.txt [ 12440581 ]
          Attachment derby1482_patch2_stat.txt [ 12440582 ]
          Mamta A. Satoor made changes -
          Attachment derby1482_patch3_diff.txt [ 12445694 ]
          Attachment derby1482_patch3_stat.txt [ 12445695 ]
          Kristian Waagan made changes -
          Labels LOB
          Mamta A. Satoor made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Resolution Fixed [ 1 ]
          Rick Hillegas made changes -
          Link This issue is blocked by DERBY-3940 [ DERBY-3940 ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Rick Hillegas made changes -
          Link This issue breaks DERBY-5121 [ DERBY-5121 ]
          Mamta A. Satoor made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Mamta A. Satoor made changes -
          Attachment DERBY_1482_patch4_diff.txt [ 12475247 ]
          Attachment DERBY_1482_patch4_stat.txt [ 12475248 ]
          Mamta A. Satoor made changes -
          Attachment derby1482_patch5_diff.txt [ 12478985 ]
          Attachment derby1482_patch5_stat.txt [ 12478986 ]
          Mamta A. Satoor made changes -
          Issue & fix info [High Value Fix] [High Value Fix, Patch Available]
          Mamta A. Satoor made changes -
          Attachment derby1482_patch6_diff.txt [ 12479033 ]
          Attachment derby1482_patch6_stat.txt [ 12479034 ]
          Mamta A. Satoor made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Issue & fix info [Patch Available, High Value Fix] [High Value Fix]
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Resolution Fixed [ 1 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-5277 [ DERBY-5277 ]
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-5294 [ DERBY-5294 ]
          Knut Anders Hatlen made changes -
          Labels LOB LOB derby_backport_reject_10_8
          Mamta A. Satoor made changes -
          Attachment releaseNote.html [ 12487570 ]
          Mamta A. Satoor made changes -
          Issue & fix info [High Value Fix] [High Value Fix, Release Note Needed]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12374962 ] Default workflow, editable Closed status [ 12802728 ]

            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