[teiid-issues] [JBoss JIRA] (TEIID-4251) Built in support for Postgres DB as materialization target
Steven Hawkins (Jira)
issues at jboss.org
Wed Oct 3 17:22:01 EDT 2018
[ https://issues.jboss.org/browse/TEIID-4251?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13642818#comment-13642818 ]
Steven Hawkins commented on TEIID-4251:
---------------------------------------
This would be further predicated on having a materialization target database created for each service that needs materialization.
In pg sql terms this looks like the initialization steps:
{code}
CREATE EXTENSION postgres_fdw;
CREATE SERVER teiid_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'TEIID_SERVICE_HOST', port 'TEIID_SERVICE_PORT', dbname 'VDB'); -- note, this performs no initial validation
CREATE USER MAPPING FOR pg_user
SERVER teiid_server
OPTIONS (user 'TEIID_SECRET_USER', password 'TEIID_SECRET_PASSWORD');
{code}
Then for each materialized view we would have the initialization script (or new logic for):
{code}
IMPORT FOREIGN SCHEMA remote_schema
LIMIT TO (xxx)
FROM SERVER teiid_server
INTO public;
CREATE MATERIALIZED VIEW xxx_mat AS SELECT * FROM xxx;
{code}
Now on the postgresql instance there would be a materialized view xxx_mat.
Locally the teiid materialized view just needs pointed to xxx_mat on the pg source with our mat view management disabled.
The materialized view can be refreshed via REFRESH MATERIALIZED VIEW - which would require new new logic.
This approach does not allow for the more advanced refresh strategies, but that is probably ok from an initial release perspective.
> 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)
More information about the teiid-issues
mailing list