[teiid-issues] [JBoss JIRA] (TEIID-3509) Consolidate internal and external materialization management

[Kylin Soong (JIRA)]

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

Kylin Soong edited comment on TEIID-3509 at 5/18/16 2:34 AM:
-------------------------------------------------------------

> 1) The execution looks correct, what is the error you are seeing?
The Procedure definition
{code}
CREATE FOREIGN PROCEDURE refreshMatViewRow(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Key object NOT NULL, VARIADIC KeyOther object) OPTIONS (UPDATECOUNT 1)
{code}
have define a VARIADIC, but it not allow multiple pk added, for example, if execute
{code}
"EXEC SYSADMIN.refreshMatViewRow('TestInterMat.SAMPLEMATVIEW', '100', '101', '102')";
{code}
An exception will throw:
{code}
Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30231 Materialized view TestInterMat.SAMPLEMATVIEW cannot have a row refreshed because the number of primary key elements 1 does not match the number of key arguments 3.
        at org.teiid.query.tempdata.TempTableDataManager.updateMatviewRows(TempTableDataManager.java:437)
        at org.teiid.query.tempdata.TempTableDataManager.handleSystemProcedures(TempTableDataManager.java:412)
        at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:195)
{code}
This should be a bug, either update the Procedure definition remove VARIADIC, or update runtime make sure multiple rows can be updated, I prefer the later one.

I got confused from the document, according to
    https://teiid.gitbooks.io/documents/content/reference/sysadmin_schema.html
SYSADMIN.refreshMatViewRow is for refreshing one row,  and SYSADMIN.refreshMatViewRows is for refreshing multiple rows, but this is wrong, if look more about the procedures definition
{code}
CREATE FOREIGN PROCEDURE refreshMatViewRow(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Key object NOT NULL, VARIADIC KeyOther object)
CREATE FOREIGN PROCEDURE refreshMatViewRows(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, VARIADIC Key object[] NOT NULL)
{code}

* refreshMatViewRow is for updating one or more rows and PK only have one column
* refreshMatViewRows is for updating one or more rows and PK have more columns, a group of PK columns form a object array.


was (Author: kylin):
> 1) The execution looks correct, what is the error you are seeing?
The Procedure definition
{code}
CREATE FOREIGN PROCEDURE refreshMatViewRow(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Key object NOT NULL, VARIADIC KeyOther object) OPTIONS (UPDATECOUNT 1)
{code}
have define a VARIADIC, but it not allow multiple pk added, for example, if execute
{code}
"EXEC SYSADMIN.refreshMatViewRow('TestInterMat.SAMPLEMATVIEW', '100', '101', '102')";
{code}
An exception will throw:
{code}
Caused by: org.teiid.api.exception.query.QueryProcessingException: TEIID30231 Materialized view TestInterMat.SAMPLEMATVIEW cannot have a row refreshed because the number of primary key elements 1 does not match the number of key arguments 3.
        at org.teiid.query.tempdata.TempTableDataManager.updateMatviewRows(TempTableDataManager.java:437)
        at org.teiid.query.tempdata.TempTableDataManager.handleSystemProcedures(TempTableDataManager.java:412)
        at org.teiid.query.tempdata.TempTableDataManager.registerRequest(TempTableDataManager.java:195)
{code}
This should be a bug, either update the Procedure definition remove VARIADIC, or update runtime make sure multiple rows can be updated, I prefer the later one.

I got confused from the document, according to
    https://teiid.gitbooks.io/documents/content/reference/sysadmin_schema.html
SYSADMIN.refreshMatViewRow is for refreshing one row,  and SYSADMIN.refreshMatViewRows is for refreshing multiple rows, but this is wrong, if look more about the procedures definition
{code}
CREATE FOREIGN PROCEDURE refreshMatViewRow(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, IN Key object NOT NULL, VARIADIC KeyOther object)
CREATE FOREIGN PROCEDURE refreshMatViewRows(OUT RowsUpdated integer NOT NULL RESULT, IN ViewName string NOT NULL, VARIADIC Key object[] NOT NULL)
{code}

* refreshMatViewRow is for updating one or more rows and PK only have one column
* refreshMatViewRows is for updating one or more rows and PK have more columns, a group og PK columns form a object array.

> Consolidate internal and external materialization management
> ------------------------------------------------------------
>
>                 Key: TEIID-3509
>                 URL: https://issues.jboss.org/browse/TEIID-3509
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Server
>    Affects Versions: 8.7
>            Reporter: Ramesh Reddy
>            Assignee: Kylin Soong
>             Fix For: 9.0
>
>
> - Currently there separate SYSADMIN procedures for internal and external materialization to refresh them, we need to provide single set
> - The management of the (usage of status table) is not fully utilized by the internal materialization this needs to addressed such that both external and internal use this table to keep track of load status



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)