Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1728

Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 7.4.1, 7.6
    • 7.4.1
    • Server
    • None
    • Hide

      See the attached Model Project Set.

      • Transformation SQL has a TTL of 30000
      • Add a row to the source table
      • Query the transformation a couple of times then delete the added row
      • Wait > 30 seconds
      • Query again (should be same row count)
      • Query again and it should be the row count after the row was deleted.
      Show
      See the attached Model Project Set. Transformation SQL has a TTL of 30000 Add a row to the source table Query the transformation a couple of times then delete the added row Wait > 30 seconds Query again (should be same row count) Query again and it should be the row count after the row was deleted.

    Description

      With the following transformation SQL, regardless of the time allowed to elapse, the cache does not expire/invalidate.

      I'm running the production profile. Here's the transformation SQL text:
      /*+ cache(ttl:30000) */
      SELECT
      PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE, CONCAT2(PartsSourceB.SUPPLIER.SUPPLIER_CITY, CONCAT2(', ', PartsSourceB.SUPPLIER.SUPPLIER_STATE)) AS City_State
      FROM
      PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
      WHERE
      PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID

      It's not expiring/invalidating the cache unless I CALL SYSADMIN.refreshMatView(viewname=>'PartsVirtual.SupplierInfo', invalidate=>true)

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            pnittel_jira Paul Nittel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: