[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=13752432#comment-13752432 ] 

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

In broad strokes we have two approaches, using the pg fdw, and updating our existing materialization logic.

*pg fdw*
Pros:
* uses pg materialization logic, which includes support for background loads
* uses the fdw import, which minimizes the ddl that we need to generate
* does not require an xa source and the mvstatus function is not necessary (discussed more above)
* automatically a "cluster-wise" mechanism as the load function is centralized

Cons:
* requires that the pg fdw connection into Teiid is some kind of admin 
** one way to address this on openshift is to base this off of ssl, which requires a good bit of setup see TEIID-5780
* is not compatible with row level updates
* has some restrictions on schema names
* requires a lot of changes to the existing materialization procedures to map to pg functionality

*existing materialization*
Pros:
* already exists :) which aligns to the existing procedures.
* (mostly) handles additional status concepts such as wait and ignore.
* allows for incremental updates

Cons:
* there are a couple of holes with our existing approach such as that the mvstatus function is best effort, and not transactionally safe.  Mentioned in the last comment is the idea of moving the effect of the mvstatus function to pg (atomic) triggers so that things are safe.
* not cluster-wise - we need new logic about stalled/abandoned loads in particular
* we need full ddl generation support

The second one is the thorniest issue.  More than likely we'd need to centralize the load logic.

Both approaches have similar issues with index generation.


> Built in support for Postgres DB as materialization target
> ----------------------------------------------------------
>
>                 Key: TEIID-4251
>                 URL: https://issues.jboss.org/browse/TEIID-4251
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Server
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 12.3
>
>
> 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)