> 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.

> 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.

> - "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.

> 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;

> 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.

> - 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.