[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: --------------------------------------- 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. -- This message was sent by Atlassian Jira (v7.12.1#712002)