Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5201

Update teiid pg_type schema to match PostgreSQL pg_type in support of NPGsql provider

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: 10.1
    • Component/s: ODBC, Query Engine
    • Labels:
      None
    • Steps to Reproduce:
      Hide

      Execute following command in against teiid connection

      SELECT ns.nspname, a.typname, a.oid, a.typrelid, a.typbasetype,
      CASE WHEN pg_proc.proname = 'array_recv' THEN 'a'
      ELSE a.typtype
      END AS type,
      CASE WHEN pg_proc.proname = 'array_recv' THEN a.typelem
      ELSE 0
      END AS elemoid,
      CASE WHEN pg_proc.proname IN ('array_recv', 'oidvectorrecv') THEN 3
      WHEN a.typtype = 'r' THEN 2
      WHEN a.typtype = 'd' THEN 1
      ELSE 0
      END AS ord
      FROM ((pg_type AS a INNER JOIN pg_namespace AS ns ON ns.oid = a.typnamespace) INNER JOIN pg_proc ON pg_proc.oid = a.typreceive)
      LEFT OUTER JOIN pg_type AS b ON b.oid = a.typelem
      WHERE ((a.typtype IN ('b', 'r', 'e', 'd')) AND ((b.typtype IS NULL) OR (b.typtype IN ('b', 'r', 'e', 'd')))) OR ((a.typname IN ('record', 'void')) AND (a.typtype = 'p')) ORDER BY ord

      Show
      Execute following command in against teiid connection SELECT ns.nspname, a.typname, a.oid, a.typrelid, a.typbasetype, CASE WHEN pg_proc.proname = 'array_recv' THEN 'a' ELSE a.typtype END AS type, CASE WHEN pg_proc.proname = 'array_recv' THEN a.typelem ELSE 0 END AS elemoid, CASE WHEN pg_proc.proname IN ('array_recv', 'oidvectorrecv') THEN 3 WHEN a.typtype = 'r' THEN 2 WHEN a.typtype = 'd' THEN 1 ELSE 0 END AS ord FROM ((pg_type AS a INNER JOIN pg_namespace AS ns ON ns.oid = a.typnamespace) INNER JOIN pg_proc ON pg_proc.oid = a.typreceive) LEFT OUTER JOIN pg_type AS b ON b.oid = a.typelem WHERE ((a.typtype IN ('b', 'r', 'e', 'd')) AND ((b.typtype IS NULL) OR (b.typtype IN ('b', 'r', 'e', 'd')))) OR ((a.typname IN ('record', 'void')) AND (a.typtype = 'p')) ORDER BY ord

      Description

      When using NpgSQL provider to connect to Teiid the connection fails because the teiid.pg_type table does not contain the column "typreceive"

      Add missing columns from Postgres.pg_type (https://www.postgresql.org/docs/9.2/static/catalog-pg-type.html) to teiid

      Add missing columns from Postgres.pg_user (https://www.postgresql.org/docs/9.2/static/view-pg-user.html) to teiid

      Add missing columns from Postgres.pg_constraint (https://www.postgresql.org/docs/9.2/static/catalog-pg-constraint.html) to teiid

        Gliffy Diagrams

          Attachments

          1. information_schema.log
            7 kB
            Divyesh Vallabh

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  dvallabh_doi Divyesh Vallabh
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: