Granting Row Constraints in Teiid for CUD operations, hence except READ

[cjohn001]

Hello together,

I am trying to create a fine grained access priviledge for the following table:

CREATE FOREIGN TABLE UserDefinedProducts (

     fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),                                     # foreign key to some product id in Products table

     fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),                                          # foreign key to id of the user who created the record in this table, references Account table

     CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

     CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)

...

) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE');

- What I am trying to achieve is to to allow everyone read access to all rows in this table.

- At the same time I only like to allow update, delete operations for the user who is the owner of this row

When I am working with a row constraint:

GRANT ON TABLE "my_nutri_diary.UserDefinedProducts" CONDITION 'UserDefinedProducts.fkProfile in  (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;

This constraint is applied to all CRUD operations. Seems it is not possible to constrain it to be only applied on CUD operations.

1. Is there a clean way to this which I have not found out so far?

The only workaround I  so far came up with is to specify a second table to the same source table, to split read and data manipulation operations to two tables with different access priviledges, hence 2. table =

CREATE FOREIGN TABLE UserDefinedProductsOfAllUsers (...) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE');

GRANT SELECT ON TABLE "my_nutri_diary.UserDefinedProductsOfAllUsers" TO odata;

REVOKE INSERT,UPDATE,DELETE,EXECUTE,ALTER ON TABLE "my_nutri_diary.UserDefinedProductsOfAllUsers" FROM odata;

and use this table for READ access without row constraints. The first table could than be used with row constraints for create, update, delete operations.

2. In case I have to use this dirty workaround, I would be interested if I would run into trouble if I specify two tables in teiid which represent the same foreign source table?

From my current understanding of how Teiid works, I assume, that there is no caching implemented in Teiid and each query leads to a query against the foreign source table. At least as long as the table is not materialized. Hence, I assume there should not be a problem.

3. Is this assumption correct?

In case there is caching it would also be great to get an explanation on the unerlaying mechanisms. Thanks for your help!

Best regards,

Christoph

[shawkins]

> Is there a clean way to this which I have not found out so far?

Currently no.  Row conditions are applied for all operations.

> In case I have to use this dirty workaround, I would be interested if I would run into trouble if I specify two tables in teiid which represent the same foreign source table?

As you allude to the only trouble could be consistent caching, but otherwise it's perfectly fine to create as many views as you need.

> Is this assumption correct?

There are several caches - OData result caching (per paged result), result set caching (must be explicit), and materialization (must also be explicit).  Unless you use some explicit caching the only implicit one is the OData caching per paged result - this is scoped to a single user request and is supposed to be a snapshot anyway.  OData caching can be elevated to per user results, but that requires config change on the OData war.  Result set caching will be invalidated on any update that flows through Teiid against any one of the source tables involved.

[cjohn001]

Hello Steven,

sorry for the late reply, I was on holiday. Thanks for the clarification. I have one further question regarding your comment.

>>Result set caching will be invalidated on any update that flows through Teiid against any one of the source tables involved.

Currently I have defined two views to the same source table which look as follows

CREATE FOREIGN TABLE UserDefinedProducts (

fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),

idCode long OPTIONS(NAMEINSOURCE '"idCode"', NATIVE_TYPE 'BIGINT'),

product_name string(48) OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),

CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode)

) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');

CREATE FOREIGN TABLE UserDefinedProductsOfAllUsers (

fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),

idCode long OPTIONS(NAMEINSOURCE '"idCode"', NATIVE_TYPE 'BIGINT'),

product_name string(48) NOT NULL OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),

CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)

) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', MATERIALIZED 'TRUE');

My intention is the following. A user should only be allowed to edit his products and should always get an up to date list of all records. Therefore I thought I will provide UserDefinedProducts with row constraints which is a non-materialized table.

Furthermore, I want the user to be able to read / search for records from other users. This list of records does not need to reflect the most recent records, hence could be cached. I therefore set the view to materialized. As performance is more important in this case. From the documentation I assumed that the materialized table is refreshed once per second.

In respect to your comment:

>>Result set caching will be invalidated on any update that flows through Teiid against any one of the source tables involved.

I am now wondering if my approach is correct. Does your comment mean that when an update on the source table "UserDefinedProducts" is done via the UserDefinedProducts table, that then also the UserDefinedProductsOfAllUsers view is refreshed as it has the same source table? In this case my strategy would be wrong.

If I have several thousand users writing via UserDefinedProducts to the source table, I would probably end up with more refreshing of the UserDefinedProductsOfAllUsers view than if I would not use materialization at all and just let Teiid push down the queries.

In case my interpretation of your statement is correct, would then there be a config option to control the refreshing behavior of the materilized view?

Thanks for your help.

Best regards,

Christoph

[shawkins]

> Currently I have defined two views to the same source table which look as follows

While logically these are views from your perspective, foreign source tables are treated differently than Teiid views.  Only Teiid views are allowed to be marked as materialized, foreign tables currently are not.

> From the documentation I assumed that the materialized table is refreshed once per second.

There is no default refresh interval, you need to configure the TTL.

> Does your comment mean that when an update on the source table "UserDefinedProducts" is done via the UserDefinedProducts table, that then also the UserDefinedProductsOfAllUsers view is refreshed as it has the same source table?

There are two parts to the answer.  The first is that the logic of what constitutes a source table is based upon the Teiid foreign table, not the nameinsource.  So these are seen by Teiid as two different source tables.  The only way for it to be the same is to have a single foreign table, then create Teiid views on top of that.

The second part of the answer is that the refresh strategy for materialized views is more complicated than for result set caching, which is simple invalidation on any update.  See Materialized Views · GitBook - for internal materialized views as shown above the two strategies are TTL and a polling query.  Unfortunately invalidation upon source update is not a built-in strategy for internal materialization.  If you layer things further, you can have a foreign table, then a view on top of that with insert/update/delete triggers that perform an invalidation - however currently those triggers are for each row, not on statement, so they are best suited for making row level updates to the materialized view.  External materialization supports a lazy update strategy based upon the number of source updates seen using the MATVIEW_MAX_STALENESS_PCT, you could also request support for that for internal materialization.

> If I have several thousand users writing via UserDefinedProducts to the source table, I would probably end up with more refreshing of the UserDefinedProductsOfAllUsers view than if I would not use materialization at all and just let Teiid push down the queries.

Yes you have to careful consider lazy vs. eager materialization refreshes.  If you are okay with some staleness and expect a high volume of updates, then a lazy approach will save a lot of refresh overhead.  If you can't tolerate staleness, but this is just a materialization of source table with no denormalization or other expensive operation being cached, then yes you should not be using materialization at all.

For a bigger picture of reacting to change data capture there are also hooks in place for Debezium integration, but it's not yet a great out of the box experience.

[cjohn001]

Hello Steven,

thanks for the explanation. Now I added views to the relevant tables and things are working as expected. Unfortunately, I ran into some further issues (maybe two bugs) and I have  some further questions regarding the correct definition of view tables.

Questions:

1. Materialized Tables:

  The docu is difficult to understand for me. It says

    - "Constraints or other database features cannot be added to internal materialization tables."

    - Constraints can be defined on table/view to define indexes and relationships to other tables/views. This information is used by the Teiid optimizer to plan queries or use the indexes in materialization tables to optimize the access to the data.

    - Access patterns are not currently meaningful to views, but are still allowed by the grammar. Other constraints on views are also not enforced

    - However non-access pattern View constraints are still useful for other purposes such as to convey relationships for optimization and for discovery by clients.

    My View definition looks as follows and it seems to work. To be able to export the View via odata, it seems I at least need to specify the PRIMARY KEY constraint.

CREATE View UserDefinedProductsOfAllUsers (

    fkProduct long NOT NULL,

    fkProfile long NOT NULL,

    idCode long,

    product_name string(48) NOT NULL,

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),

    CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),

    CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)

) OPTIONS(MATERIALIZED 'TRUE', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_TTL" 1000) --// TTL is in miliseconds

AS

SELECT  p.fkProduct as fkProduct, p.fkProfile as fkProfile, p.idCode as idCode, p.product_name as product_name

FROM UserDefinedProducts p;

So my questions regarding the topic:

1a) Are the constraints (indexes, foreign keys) now used to optimize queries in materialized tables or not?

1b) Are the constraints (indexes, foreign keys) now used to optimize queries in non-materialized tables or not?

1c) If I use a compound SELECT query which uses a materialized view and a foreign source table at the same time, I would expect this SELECT to be more performant than using two non-materialized tables/views instead. Is this assumption correct or would I have to consider something which renders this assumption wrong?

2. How can I enforce a foreign table / view to become invisible. I remember a statement from Ramesh, that this would be possible by setting INVISIBLE OPTION on the SCHEMA to TRUE. However, I tried this out and it seems to have no effect (BUG?). Every tables and view I specify is becoming visible in the odata service. Of course I can use GRANT to resctrict access, however, it would be more nice to not export the tables in the odata layer in the first place. Would it be possible to add some kind of per table/view INVISIBLE OPTION for odata, or could I reach the same result in a different way and how would this look like?

3 Best practices?

3a) I am wondering how the best practice with Teiid looks like, is it fine to allow for access on the foreign source tables directly or should I wrap a view around each table?

3b) Would such a view result in performance penalties?

4) I have seen that for materialized views that there is a write through option teiid_rel:MATVIEW_WRITE_THROUGH.

4a) I am wondering how it works and in what situations this might be beneficial.

4b) Does it write to the underlaying db and the materialized table at the same time, hence hold sources in sync without reloading the entire materialized table?

4c) In this case I could use it when running multiple teiid instances with materialized tables that use the same db source, to ensure that all teiid instances are in sync in respect to the specified teiid_rel:MATVIEW_TTL?

5) I have a question regarding the use of myvdb.vdb zip archives as my ddl files starts to get quite large and messy. From how I understood the documentation I will still require to specify an entire schema in a single ddl file. Is my understanding correct? I have just a single schema in my service, and I am wondering if I can split the definitions into multiple files, for example separate files for foreign table specifications, views, stored procedures and access priviledges (GRANTS). Would be great if you could provide me with a better strategy than I am currently using, see my ddl in attachment.

6) I have a problem with defining a INSTEAD OF trigger to update on of my views, see attachment for full code. The relevant section is the following

CREATE TRIGGER ON AllProductsFromDiary INSTEAD OF DELETE AS

   FOR EACH ROW

   BEGIN ATOMIC

       -- delete the record in case of a quickly added product

        IF ( OLD.fkDatabaseKey = 2 )

            DELETE FROM QuicklyAddedProducts WHERE fkProduct = OLD.fkProduct;

   END

When trying to upload the deployment to Teiid, I get the following error message

{"WFLYCTL0080: Failed services" => {"jboss.deployment.unit.\"svc-vdb.ddl\".PARSE" => "WFLYSRV0153: Failed to process phase PARSE of deployment \"svc-vdb.ddl\"

  Caused by: org.teiid.metadata.ParseException: TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered \"OLD.fkProduct; END [*]CREATE[*] VIRTUAL PROCEDURE\" at line 302, column 1.

Was expecting: <EOF> | \";\"

  Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered \"OLD.fkProduct; END [*]CREATE[*] VIRTUAL PROCEDURE\" at line 302, column 1.

Was expecting: <EOF> | \";\""}}

Is this an error in my statement, or does something go wrong in the TEIID parser?

Thanks for your help Steven!

Best regards,

Christoph

PS: I have tried to create an UPDATE trigger in the meantime on the same view, this results in the same errors:

CREATE TRIGGER ON AllProductsFromDiary INSTEAD OF UPDATE AS

   FOR EACH ROW

   BEGIN ATOMIC

   IF (CHANGING.AmountInG)

   BEGIN

       IF (OLD.fkDatabaseKey = 1)

   UPDATE FDBProducts SET AmountInG = NEW.AmountInG WHERE fkProduct = NEW.fkProduct;

   END

   END

[shawkins]

> - "Constraints or other database features cannot be added to internal materialization tables."

Meaning that the materialization target table is an internal table that is fully managed by Teiid.  So there isn't a way to create indexes or make other optimizations like you would after the fact with an external materialization target - which is just a regular table managed somewhere else. 

That is not to say there won't be indexes on the internal materialzation target table, it's just they will be created based upon the view pk, unique, and index metadata.  They will just be simple clustered (pk) and nonclustered sorted indexes.

The documentation could certainly elaborate more on that.

> 1a) Are the constraints (indexes, foreign keys) now used to optimize queries in materialized tables or not?

Pk, Unique Key, and Indexes on the view will result in the creation of an index on the materialization target table and are fully utilized in planning decisions.  Things are a little more complicated with foreign keys.  Currently they are not propagated onto the generated materialization target table metadata, so they are only considered in pre-planner activities, such as rewrite, before the view reference is replaced with the materialization target - but there aren't meaningful optimizations being made at that time based upon foreign key.  There are couple of narrow situations where that information would be useful, so it probably should be added.

> 1b) Are the constraints (indexes, foreign keys) now used to optimize queries in non-materialized tables or not?

If you are asking about that metadata on Teiid views, then no as that metadata will influence neither costing, nor will it be preserved after view removal.  If you are asking about that metadata on a Teiid foreign table, then yes it will be consulted.

> 1c) If I use a compound SELECT query which uses a materialized view and a foreign source table at the same time, I would expect this SELECT to be more performant than using two non-materialized tables/views instead. Is this assumption correct or would I have to consider something which renders this assumption wrong?

It may not be that simple.  It matters greatly how the non-materialized plan could get processed.  If it's a performant dependent join or can be fully pushed (single source), it could end up performing similarly to materialized case.  You need the materialization to offload something expensive, such as having a slow source or expensive denormalization, to make a broader generalization about the performance benefit.

> 2. How can I enforce a foreign table / view to become invisible. I remember a statement from Ramesh, that this would be possible by setting INVISIBLE OPTION on the SCHEMA to TRUE. However, I tried this out and it seems to have no effect (BUG?).

If you are using a DDL vdb, yes it looks like there's a bug.  The DDL parser and DDL toString logic doesn't seem to capture anything related to if the schema is visible.  That seems to be a general issue as it's capturing the annotation/description, nor path (although that we don't really need anymore).  Can you log something for this?

> Every tables and view I specify is becoming visible in the odata service. Of course I can use GRANT to resctrict access, however, it would be more nice to not export the tables in the odata layer in the first place. Would it be possible to add some kind of per table/view INVISIBLE OPTION for odata, or could I reach the same result in a different way and how would this look like?

Yes, you could log something for a per object flag as well.  The issue would be there making it OData transport specific.  Generally we want the metadata to be as universal as possible.

> 3a) I am wondering how the best practice with Teiid looks like, is it fine to allow for access on the foreign source tables directly or should I wrap a view around each table?

When utilizing something like designer the recommendation was to create a view layer, which was just a right click operation on the source model.  Doing this with a ddl or xml vdb is more tedious and may not be worth the effort.  Generally the benefit was to provide a layer to make data type, naming, and other tweaks.

> 3b) Would such a view result in performance penalties?

No.  Simple views are always removed in optimization.

There's a lot to cover here, so I'm going to split my response.  It may be delayed until Tuesday at the worst.  Thanks for bringing up some good topics.

[shawkins]

> If you are using a DDL vdb, yes it looks like there's a bug.

[TEIID-5754] Schema visibility issues - JBoss Issue Tracker addresses any issues with visibility that were apparent.  Primarily it's a doc issue - the property should be "VISIBLE FALSE".

> 4) I have seen that for materialized views that there is a write through option teiid_rel:MATVIEW_WRITE_THROUGH.

> 4a) I am wondering how it works and in what situations this might be beneficial.

> 4b) Does it write to the underlaying db and the materialized table at the same time, hence hold sources in sync without reloading the entire materialized table?

It is for situations where the materialized view is an updatable view and you issue updates against that view.  In that case the modification will be made via the view logic (inherit update or explicit instead of trigger) and directly to the materialization target table.

4c) In this case I could use it when running multiple teiid instances with materialized tables that use the same db source, to ensure that all teiid instances are in sync in respect to the specified teiid_rel:MATVIEW_TTL?

If your updates are flowing through teiid, then use the use of write through will keep everything in sync.  For internal materialization this will require the multiple Teiid instances to use cluster communication as each member will maintain it's own copy of the table.

> 5) I have a question regarding the use of myvdb.vdb zip archives as my ddl files starts to get quite large and messy. From how I understood the documentation I will still require to specify an entire schema in a single ddl file. Is my understanding correct? I have just a single schema in my service, and I am wondering if I can split the definitions into multiple files, for example separate files for foreign table specifications, views, stored procedures and access priviledges (GRANTS). Would be great if you could provide me with a better strategy than I am currently using, see my ddl in attachment.

The understanding isn't fully correct.  The documentation (DDL VDB · GitBook) does not spell it out, but you are allowed to use multiple import into a given Teiid schema:

IMPORT FOREIGN SCHEMA public FROM REPOSITORY "DDL-FILE" INTO schema OPTIONS("ddl-file" 'file1.ddl');

IMPORT FOREIGN SCHEMA public FROM REPOSITORY "DDL-FILE" INTO schema OPTIONS("ddl-file" 'file2.ddl');

Note the grammar currently expects IMPORT FOREIGN, even if the target schema is virtual.

> 6) I have a problem with defining a INSTEAD OF trigger to update on of my views, see attachment for full code. The relevant section is the following

Currently a semi-colon is expected at the end of each statement:

CREATE TRIGGER ON AllProductsFromDiary INSTEAD OF DELETE AS

   FOR EACH ROW

   BEGIN ATOMIC

       -- delete the record in case of a quickly added product

        IF ( OLD.fkDatabaseKey = 2 )

            DELETE FROM QuicklyAddedProducts WHERE fkProduct = OLD.fkProduct;

   END;

[cjohn001]

Hello Steven,

I am trying to draw a decission about what I should do now based on your explanations.

Regarding questions 1a-d)

>> That is not to say there won't be indexes on the internal materialzation target table, it's just they will be created based upon the view pk, unique, and index metadata.

>> Pk, Unique Key, and Indexes on the view will result in the creation of an index on the materialization target table and are fully utilized in planning decisions. 

>> If you are asking about that metadata on Teiid views, then no as that metadata will influence neither costing, nor will it be preserved after view removal.  If you are asking about that metadata on a Teiid foreign table, then yes it will be consulted.

From your comments my understanding is yes, PK,Unique Key and Indexes are used and it makes sense for me to specify them at least on foreign table definitions. What is not clear to me is, if I should specify them on a View on top of a foreign table as well, or if Teiid can optimally perform with the definitions on the foreign table alone. I do not want to write unnecessary lines of code.

Example:

I have the following two definitions in my ddl file:

CREATE FOREIGN TABLE UserDefinedProducts (

    fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),

    fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),

    idCode long OPTIONS(NAMEINSOURCE '"idCode"', NATIVE_TYPE 'BIGINT'),

    product_name string(48) OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),

    CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),

    CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),

    CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)

) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');

CREATE View UserDefinedProductsOfAllUsers (

    fkProduct long NOT NULL,

    fkProfile long NOT NULL,

    idCode long,

    product_name string(48) NOT NULL,

    CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),

    CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),

    CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),

    CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),

    CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)

) OPTIONS(MATERIALIZED 'TRUE', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_UPDATABLE" 'true', "teiid_rel:MATVIEW_TTL" '1000') --// TTL is in miliseconds

AS

SELECT  p.fkProduct as fkProduct, p.fkProfile as fkProfile, p.idCode as idCode, p.product_name as product_name

FROM UserDefinedProducts p;

Now my question is, should I explicitly specify the CONSTRAINTS marked as red on the View UserDefinedProductsOfAllUsers like I did in this example, or does this have no benefit for Teiid as this is already implicitly known to Teiid via the foreign table definition. Hence, I can simply omit the red lines of code. What I observed is, that I at least need to specify the PRIMARY KEY line as the view is not exported to the odata layer otherwise.

———————————

Regarding question 2:

>> [TEIID-5754] Schema visibility issues - JBoss Issue Tracker addresses any issues with visibility that were apparent.  Primarily it's a doc issue - the property should be "VISIBLE FALSE".

In response to your first reply I had added TEIID-5752. So if I understand you correct, Teiid 5754 already address and solved the issue? Hence  TEIID-5752 could be closed?

What does this now mean regarding the correct usage of the feature.? Once Teiid 12.2.1 is available, how can I use this feature to hide parts of my table? My understanding is that setting VISIBLE FALSE on the schema will set all foreign tables and Views in this schema to invisible. Hence the strategy would be that I have to define all foreign source tables which shall become invisible in a Schema A, set this schema to invisible, and create a second Schema B which now is a visible one, and defines viewson the source tables of Schema A to make parts of their data visible / accessible on the odata layer. Is my assumption and the sketched strategy correct?

———————————

Regarding question 4:

<< If your updates are flowing through teiid, then use the use of write through will keep everything in sync.  For internal materialization this will require the multiple Teiid instances to use cluster communication as each member will maintain it's own copy of the table.

Ok, here is what I have planned. Once my basic app is finally running, I would like to switch from a single Teiid Wildfly instance to multiple independently running Teiid Thorntail instances to realize horizontal scaling of my backend. My current assumption was, that these instances do not require cluster communication. My plan for updatable materialized tables was to use write through on them and configure each Thorntail instance to refresh the materialized table once all 10 seconds. My thinking was, that the updated data are directly written to the underlaying database and the data in the other Thorntail instances would be at max stale for 10 seconds (till periodic refresh happens). Hence, I would not require cluster communication between the Thorntail instances. Is my thinking correct or would I still require cluster communication between the instances for other topics I have not thought about yet?

[shawkins]

> Now my question is, should I explicitly specify the CONSTRAINTS marked as red on the View UserDefinedProductsOfAllUsers like I did in this example, or does this have no benefit for Teiid as this is already implicitly known to Teiid via the foreign table definition. Hence, I can simply omit the red lines of code. What I observed is, that I at least need to specify the PRIMARY KEY line as the view is not exported to the odata layer otherwise.

The view logic currently does not directly infer constraints.  Generally we're dealing something more complicated than a simple select.  Since it is an internal materialized view, the presence of INDEXes will create indexes on the materialization target table.  The presence of foreign keys, as captured by a JIRA, won't mean anything for optimization (which is a relatively minor issue), but it will convey the proper relationships for your OData entity model.

> In response to your first reply I had added TEIID-5752. So if I understand you correct, Teiid 5754 already address and solved the issue? Hence  TEIID-5752 could be closed?

TEIID-5752 was created with the security flag enabled.  For whatever reason that means I receive no email notifications about it, and will only see it later in reports about open issues, so I logged TEIID-5754.  Yes, TEIID-5754 should address all concerns about visibility and TEIID-5752 can be closed.

> Is my assumption and the sketched strategy correct?

Until the enhancement is worked that deals with visibility down to table/view level, yes your only option is at the whole schema level.

> Ok, here is what I have planned. Once my basic app is finally running, I would like to switch from a single Teiid Wildfly instance to multiple independently running Teiid Thorntail instances to realize horizontal scaling of my backend. My current assumption was, that these instances do not require cluster communication. My plan for updatable materialized tables was to use write through on them and configure each Thorntail instance to refresh the materialized table once all 10 seconds. My thinking was, that the updated data are directly written to the underlaying database and the data in the other Thorntail instances would be at max stale for 10 seconds (till periodic refresh happens). Hence, I would not require cluster communication between the Thorntail instances. Is my thinking corrector would I still require cluster communication between the instances for other topics I have not thought about yet?

Only if you are using external materialization or are okay with each instance independently maintaining its own internal materialization target table.

[cjohn001]

Hello Steven,

sorry to bother you again. But I am still not sure if I got you right.

>>The view logic currently does not directly infer constraints.  Generally we're dealing something more complicated than a simple select.  Since it is an internal materialized view, the presence of INDEXes will create indexes on the materialization target table.  The presence of foreign keys, as captured by a JIRA, won't mean anything for optimization (which is a relatively minor issue), but it will convey the proper relationships for your OData entity model.

So this means, you would recommend that

- I should keep the red lines with the INDEXES in case I define a materialized view and can delete the FOREIGN KEY CONSTRAINTS ?

- And I can omit INDEXES and FOREIGN KEY CONSTRAINTS  in case the view is not materialized?

[shawkins]

> - I should keep the red lines with the INDEXES in case I define a materialized view and can delete the FOREIGN KEY CONSTRAINTS ?

Yes, keep the INDEXES.  The FOREIGN KEY constraints will still be used by OData to determine the entity model relationships, and will eventually be used in some narrow cases after [TEIID-5751] Please add optimization support for foreign keys in materialized tables - JBoss Issue Tracker

- And I can omit INDEXES and FOREIGN KEY CONSTRAINTS  in case the view is not materialized?

You can omit the indexes if not materialized.  The FOREIGN KEY constraints will still be used by OData to determine the entity model relationships.

[cjohn001]

Hello Steven,

thanks for the explanation.