[teiid-issues] [JBoss JIRA] (TEIID-4251) Built in support for Postgres DB as materialization target

[Steven Hawkins (Jira)]

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

Steven Hawkins commented on TEIID-4251:
---------------------------------------

I'm trying to avoid using the allow-materialization-management construct in order to remove the need for an explicit status table or mvstatus function usage.

To do that though requires some code modifications if any of the materialization scripts will still be run even if allow management is turned off.

The other approach is to leave teiid out of it as much as possible.  With the pg container include a create script:

CREATE EXTENSION postgres_fdw;
CREATE SERVER teiid_server ...;
CREATE USER MAPPING FOR pg_user ...;

-- for each schema with a materialized view
CREATE SCHEMA remote_schema ...;
IMPORT FOREIGN SCHEMA remote_schema (limit to all materializations in the schema) ...;
-- for each materialized view in the schema
CREATE MATERIALIZED VIEW xxx_mat AS SELECT * FROM remote_schema.xxx OPTION NO CACHE WITH NO DATA;
create key / index information on xxx_mat
...

By specifying option no cache we avoid having to worry about any load order and can concurrently load all.

There would then need to be an initial load set of scripts,
REFRESH MATERIALIZED VIEW xxx_mat;

And a set of refresh scripts (with the concurrent option only against materialized views that have a unique key):
REFRESH MATERIALIZED VIEW CONCURRENTLY xxx_mat;

These could be run off of cron jobs, which could also be associated with the pg container image.

> Built in support for Postgres DB as materialization target
> ----------------------------------------------------------
>
>                 Key: TEIID-4251
>                 URL: https://issues.jboss.org/browse/TEIID-4251
>             Project: Teiid
>          Issue Type: Sub-task
>          Components: Server
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 11.2
>
>
> If Postgres database is available along with install or assumed that it is available, then some of the materialization task can be automated, like
> - Creation of a common STATUS table
> - Creation of the materilization targets (create views on dbms)
> - On load, on undeploy and load scripts for all the materialization views
> We need to device a way this to be pluggable, such that based on success of this, we can provide additional support for other sources. 



--
This message was sent by Atlassian Jira
(v7.12.1#712002)