[teiid-issues] [JBoss JIRA] (TEIID-5285) Add high-level feature for redirection of updates

[Steven Hawkins (JIRA)]

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

Steven Hawkins edited comment on TEIID-5285 at 4/12/18 12:27 PM:
-----------------------------------------------------------------

Here is a proposal to redirect updates (with no later resolution/merge to the underlying source) down to row level.  Given tbl with a primary key, we need to create tbl_modifications (jdg, pg?) that contains the same columns (and potentially indexes) with an additional state column.  State could be one of three values (created, deleted, updated), which could just be numeric values as we don't have an enum concept.

A view will be created as the new access point for tbl:

{code}
SELECT * from tbl left outer join tbl_modifications on (pk predicates) where tbl_modifications.state is null
union all
SELECT col, ... from tbl_modifications where state <> deleted
{code}

An issue with the above is if a created row end is also then populated in the actual store.  We can leave it as a TODO to resolve that.

Performance could be an issue as any joins or operations performed above the view would likely need to have federated processing as the optimizer would not generally be able to remove the union or the left outer join.

The update triggers on the view would look like:

{code}
create trigger on ... instead of insert 
begin atomic
  //TODO: may need to do an check against tbl
  upsert into tbl_modifications (col, ..., state) values (new.col, ..., created);
end
{code}

{code}
create trigger on ... instead of delete 
begin atomic
  upsert into tbl_modifications (col, ..., state) values (null, ..., deleted);
end
{code}
An enhancement for delete is to differentiate between rows that were created and those that were updated - the former can be deleted completely.  However the update procedure would need further refinement as it transitions rows from created to updated.

{code}
create trigger on ... instead of update
begin atomic
if (changing.pk ...)
begin
  upsert into tbl_modifications (pk cols) values (old.pk, .., deleted);
  //TODO: we probably need to check tbl for existence of the new pk
  upsert into tbl_modifications (col, ..., state) values (new.col, .., created);
end
else
  upsert into tbl_modifications (col, ..., state) values (new.col, .., updated);
end
{code}

[~rareddy] does something based upon the above seem workable?


was (Author: shawkins):
Here is a proposal to redirect updates (with no later resolution/merge to the underlying source) down to row level.  Given tbl with a primary key, we need to create tbl_modifications (jdg, pg?) that contains the same columns (and potentially indexes) with an additional state column.  State could be one of three values (created, deleted, updated), which could just be numeric values as we don't have an enum concept.

A view will be created as the new access point for tbl:

{code}
SELECT * from tbl left outer join tbl_modifications on (pk predicates) where tbl_modifications.state is null
union all
SELECT col, ... from tbl_modifications where state <> deleted
{code}

An issue with the above is if a created row end is also then populated in the actual store.  We can leave it as a TODO to resolve that.

Performance could be an issue as any joins or operations performed above the view would likely need to have federated processing as the optimizer would not generally be able to remove the union or the left outer join.

The update triggers on the view would look like:

{code}
create trigger on ... instead of insert 
begin atomic
merge into tbl_modifications (col, ..., state) values (new.col, ..., created);
end
{code}

{code}
create trigger on ... instead of delete 
begin atomic
merge into tbl_modifications (col, ..., state) values (null, ..., deleted);
end
{code}
An enhancement for delete is to differentiate between rows that were created and those that were updated - the former can be deleted completely.

It would be easy to have:
{code}
create trigger on ... instead of update
begin atomic
merge into tbl_modifications (col, ..., state) values (new.col, .., updated);
end
{code}
However that update is too simplistic as a modification of the primary key would need to be treated as delete and a create.

[~rareddy] does something based upon the above seem workable?

> Add high-level feature for redirection of updates
> -------------------------------------------------
>
>                 Key: TEIID-5285
>                 URL: https://issues.jboss.org/browse/TEIID-5285
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine, Teiid Spring Boot
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 10.3
>
>
> In microservices testing it is desirable to test against production/live data but not commit any updates.  We should offer a simple solution that can defined by extension metadata and enabled/disabled by a feature flag.  We may need to make simplifying assumptions about the scope (per session, per application, etc.) and durability of the updates.
> Under the covers this will be achieved by using views, update triggers, and a store for the updates and when not enabled the expectation is that all operations should pass through.  However the application will be limited to using Teiid SQL and will be required to use the Teiid or pg driver, or Teiid spring boot.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)