Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4249 Materialization Improvements
  3. TEIID-5498

Allow the pg layer to work for simple selects with the postgres_fdw

XMLWordPrintable

    • Icon: Sub-task Sub-task
    • Resolution: Done
    • Icon: Major Major
    • 11.2
    • None
    • ODBC
    • None

      Another avenue for materialization support outlined in TEIID-4251 is to rely upon pg materialization, which would be easiest to setup through their foreign data wrapper. However it expects some additional support for:

      start transaction syntax
      abort transaction
      automatic cleanup of portals/cursors at the end of a transaction
      prepared cursors

      Additionally this will have the same performance issue that we have with declare/fetch initially - that is a cursor requires a transaction, and our current strategy requires pre-buffering the results under a transaction. However since this would only be used for load/refresh that performance hit may be negligible - if now we'd need similar logic to effectively ignore the transaction wrapping.

            [TEIID-5498] Allow the pg layer to work for simple selects with the postgres_fdw

            Steven Hawkins added a comment -

            Simple types and their array variations will work just fine. The standard pg/odbc issues remain:

            • the cursor select is under a transaction
            • the object type is not supported
            • multi-dimensional arrays are not supported
            • lobs are inline in the resultset and limited in size

            Steven Hawkins added a comment - Simple types and their array variations will work just fine. The standard pg/odbc issues remain: the cursor select is under a transaction the object type is not supported multi-dimensional arrays are not supported lobs are inline in the resultset and limited in size

            Steven Hawkins added a comment -

            The initial commit is. I'll do more testing with a variety of types. We may also need to add enforcement of metadata constraints - such as string length - as to ensure proper results. That would be similar to existing logic in odata.

            This is also not a general purpose solution yet as including predicates can result in sql that isn't yet valid for us, such as using pg type names in casts 'a'::text - most of those issues would be easy to address though.

            Steven Hawkins added a comment - The initial commit is. I'll do more testing with a variety of types. We may also need to add enforcement of metadata constraints - such as string length - as to ensure proper results. That would be similar to existing logic in odata. This is also not a general purpose solution yet as including predicates can result in sql that isn't yet valid for us, such as using pg type names in casts 'a'::text - most of those issues would be easy to address though.

              rhn-engineering-shawkins Steven Hawkins
              rhn-engineering-shawkins Steven Hawkins
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated:
                Resolved: