[teiid-issues] [JBoss JIRA] (TEIID-3952) Update to updatable internal materialized view should update the materialized view as well as the database

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13351378#comment-13351378 ] 

Steven Hawkins commented on TEIID-3952:
---------------------------------------

The initial commit is in.  It takes a rewrite approach, rather than adding a new plan forms / moving logic into the optimizer.  With a MATVIEW_WRITE_THROUGH extension boolean a materialized view will have updates written both to the target table and have view level operation called as well.  Initially this is implemented differently for internal and external. External will issue the same update as what is targeting the view to the materialization target table.  Internal will determine a primary key set and use the refreshMatViewRow procedure.  The latter is a safer, but more costly, approach in that you are effectively checking that the values will conform to the view.   If needed we can have external operate in this manner as well.  There is also potentially more tweaks that could be made to the rewrites based upon checks of the materialization validity.

> Update to updatable internal materialized view should update the materialized view as well as the database
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-3952
>                 URL: https://issues.jboss.org/browse/TEIID-3952
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: VDB
>    Affects Versions: 8.7
>         Environment: Red Hat JBoss Data Virtualization 6.2 based on Teiid 8.7.x
>            Reporter: Andy Yuen
>            Assignee: Steven Hawkins
>              Labels: jboss
>             Fix For: 9.2
>
>
> Updating an updatable internal materized view updates the database but not the materialized view at present. The requested enhancement is that they, both, should be updated.
> Setup
> Client - SquirrelSQL to access JDV
> JDV 6.2 - with an updatable nternal materialized view of one table from the data source
> Data Source - just one data source: MySQL
> I can see from the console that the target table/view has been materialized
> Then I did the following:
> 1) select a row from the materialized table
> 2) update a field in a row in the materialized view
> 3) select that row (value unchanged ie, same as int 1) - tried multiple times
> 4) issue EXEC SYSADMIN.refreshMatViewRow(...) using primary key for the row that has been changed
> 5) select that row (value unchanged) - now I can see the changed data
> This behaviour is counter-intuitive because I was expecting that since it is an updatable materialized view, my change will write through the materialized view ie, change both the materialized view as well as the database. But in this case, it changed the database and not the materialized view?



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)