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

[ https://issues.jboss.org/browse/TEIID-4251?page=com.atlassian.jira.plugin... ] 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. -- This message was sent by Atlassian Jira (v7.12.1#712002)