Hi guys -
I came across TEIID-2584 and thought I'd offer up a use case we're considering implementing. From what I can tell, it's related to some of the new functionality as part of the ticket.
We have several tables from different sources that are expensive to query. Fortunately, the nature of the data is append-only. Our basic plan is to query the underlying table once and materialize it as a materialized view (either internal or external) and, through some means, receive rows to append to the materialized view (probably HornetQ a la Continuous Support for INSERT INTO / MERGE INTO ?). Practically, this means we'll be physically storing the incrementally delivered rows in an H2 table by creating an INSERT trigger on the materialized view.
Since this is basically data synchronization between the materialized view and the underlying data source, I'm concerned about out-of-sync conditions. Detecting out-of-sync is necessarily data source dependent but the recovery is always starting from scratch: a refresh against the materialized view. I haven't experimented with this yet, but I'm thinking/hoping that if I create an appropriate delete trigger on the materialized view, a refresh will cause the H2 table of incrementally delivered rows to be deleted as well. The goal is to atomically refresh the materialized view as well as deleting the H2 table.
I've got a few questions/assumptions:
- Does this seems like a workable approach?
- Does any of TEIID-2584 affect internal materialized views? If so, I see the ALLOW_MATVIEW_PARTIAL_LOADS* properties but not sure I totally understand them. If partial loads are allowed, could internal materialized views support a direct INSERT? This would eliminate the need for an H2 table
- For external materialized views, I see we can specify a truncate procedure. Using external views, I don't think there's any reason to have a separate table for the incrementally delivered rows and I don't think there would any atomic refresh problems.
- Because refreshing the materialized view occurs asynchronously to delivering the incremental rows, I'm pretty sure there's going to need to be some form of coordination between the queue reading stored procedure and the MATVIEW_LOAD_SCRIPT. We could write our own locking service. Do you know if JBoss have some sort of locking service (maybe that's part of JTA)? Maybe we could abuse Infinispan?